Cerating and updating new record using ADO

Hi, i want to create new record in remote accsess db using ADO. I'm using this code:

VB:
------------------------
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open "Provider=MS Remote;Remote Server=http://www.xyz.com;" & "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\Inetpub\wwwroot\biblio.mdb;"
rs.Open "SELECT * From USERS", cnn, adOpenKeyset, adLockOptimistic, adCmdText

rs.AddNew
rs("Name") = "Jon"
rs("PhoneNR") = "00375221"
rs.Update
------------------------

error apears when i updating recrodset:

'-21474671259' operation must use an updateable query

Why ??Originally posted by fizikas
Hi, i want to create new record in remote accsess db using ADO. I'm using this code:

VB:
------------------------
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open "Provider=MS Remote;Remote Server=http://www.xyz.com;" & "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\Inetpub\wwwroot\biblio.mdb;"
rs.Open "SELECT * From USERS", cnn, adOpenKeyset, adLockOptimistic, adCmdText

rs.AddNew
rs("Name") = "Jon"
rs("PhoneNR") = "00375221"
rs.Update
------------------------

error apears when i updating recrodset:

'-21474671259' operation must use an updateable query

Why ??

see the following article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;175168

CE Compatibility with ADO.NET and Orcas

Does SQL Server Compact Edition fully support ADO.NET?

Will SQL Server Compact Edition fully support the ADO.NET Entity Framework?

Will SQL Server Compact Edition fully support LINQ?

1. I don't know what you would consider "fully support" but there's special SQL CE ADO.Net managed provider for both desktop and devices. It has pretty much all classes SQL Client has (with few nonessential exceptions) and even something extra (like SqlCeResultSet).

2. Don't know about desktop but there's no EF support on devices. EF on desktop is extensible, even if not supported out of the box support might be available later on.

3. Again, not sure what you mean by that. As name implies LINQ is integrated into programming languages, not into databases. If you’re asking if you’ll be able to query SQL CE database from C# or VB using, say, EF and LINQ then see #2.

Catch Duplicate records

I have a table that I am inserting records to via VB6 using an ADO command.
If I try to insert a duplicate record I get the error message
-2147217873 Access Primary Key violation etc
This error doesn't jump to my error handler so how can I catch it? is there
something I can do from with SQL? or am I missing something in VB
ThanksThe best way to handle the error is to prevent it from happening in the firs
t
place. That can be done by, for example, checking to see if the value exists
before you try to insert it.
You can certainly catch this error in VB and I would propose that is the bes
t
place to do it.
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:%239gwgADTFHA.3544@.TK2MSFTNGP10.phx.gbl...
>I have a table that I am inserting records to via VB6 using an ADO command.
> If I try to insert a duplicate record I get the error message
> -2147217873 Access Primary Key violation etc
> This error doesn't jump to my error handler so how can I catch it? is the
re
> something I can do from with SQL? or am I missing something in VB
> Thanks
>|||If u use stored procedure
u can return value from it
e.g
....
.....
...
INSERT INTO ....
......
RETURN @.@.ERROR
in VB u must check the return value if return value != 0
then an error occuerd. In case everything ok zero will returnd
Message posted via http://www.webservertalk.com|||Thanks, I have tried this
declare @.Stockcode char(30)
declare @.Barcode char(20)
declare @.Qty int
set @.Stockcode = '123456'
set @.Barcode = '1234567890123'
set @.Qty = 12
INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
RETURN @.@.ERROR
GO
but I get the error
A RETURN statement with a return value cannot be used in this context.
What am I doing wrong?
"E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
> If u use stored procedure
> u can return value from it
> e.g
> ....
> .....
> ...
> INSERT INTO ....
> ......
> RETURN @.@.ERROR
>
> in VB u must check the return value if return value != 0
> then an error occuerd. In case everything ok zero will returnd
> --
> Message posted via http://www.webservertalk.com|||Again, prevent the error from happening by checking for the value. Given tha
t
you did not specifiy the PK I'll use the Force and guess:
Create Table dbo.OuterBarCodes
(
StockCode Char(30)
, BarCode Char(20)
, Quantity Int
, Constraint PK_OuterBarCodes Primary Key (StockCode, BarCode)
)
If Not Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
If you really want to know whether the value exists then do this:
If Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Begin
Raiserror('Duplicate StockCode and BarCode', 16, 1)
Return
End
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:ODBIBhDTFHA.560@.TK2MSFTNGP10.phx.gbl...
> Thanks, I have tried this
> declare @.Stockcode char(30)
> declare @.Barcode char(20)
> declare @.Qty int
> set @.Stockcode = '123456'
> set @.Barcode = '1234567890123'
> set @.Qty = 12
>
> INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
> RETURN @.@.ERROR
> GO
> but I get the error
> A RETURN statement with a return value cannot be used in this context.
> What am I doing wrong?
> "E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
> news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
>