Adding records

Hello guys,

I hope someone can help me!

I have a (probably simple) problem with compact edition, I can't seem to add records using VB 6.0. Here's some sample code I've been testing with:

Dim pConn As ADODB.Connection
Dim pRS As ADODB.Recordset

Set pConn = New ADODB.Connection
Set pRS = New ADODB.Recordset

pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\test.sdf"
'pConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb"
pConn.Open

pConn.Execute "INSERT INTO tblActions ([Branch ID], [Computer ID], [User ID], [Program ID], [Type ID], ID, [Extra 1], [Extra 2]) " & _
"VALUES (1, 1, 1, 1, 1, 1, 'some text', 'more text')"

' Open the recordset
pRS.Open "tblActions", pConn, adOpenForwardOnly, adLockOptimistic

pRS.AddNew
pRS![Branch ID] = 1
pRS![Computer ID] = 1
pRS![User ID] = 1
pRS![Program ID] = 1
pRS![Type ID] = 1
pRS![ID] = 1
pRS![Extra 1] = 1
pRS![Extra 2] = 1
pRS.Update

pRS.Close
pConn.Close

Set pRS = Nothing
Set pConn = Nothing

I can add the records using an SQL statement, but when I try to open the recordset and add a record using the AddNew method it fails with the message:

The command contained one or more errors. [,,,,,]

But, if I connect to an Access database (which uses exactly the same tables, etc.) using the commented out connection string in the above sample, the code works fine. What am I doing wrong?

Many thanks in advance!

I think you will have to rewrite your code to use a SqlCeResultSet instead of a ADODB.RecordSet if you want to use funtionality similar to .AddNew. For a example using using SqlCeResultSet.CreateRecord and using the SqlCeUpdateableRecord, see http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset.createrecord.aspx

|||

Thanks for the response, but the code you've linked to shows VB.net code, and I need to use VB 6.0. Surely there must be some way to create records in a similar way to using the .AddNew method and an SQL CE database.

My problem is that the code needs to be able to link to either an SQL CE database or an Access database, and must be written in VB 6.0.

|||Sorry, didn't think about the VB 6 issue. I think the only way forward will be to use pConn.Execute with an INSERT statement. Where does the error occur - when calling .AddNew or .Update ?|||

Neither unfortunately, it fails when opening the recordset.

I can open it fine for reading, but as soon as I add the adLockOptimistic parameter, it fails, I've tried all lock types too just in case.

|||

I think the only way forward will be to use pConn.Execute with an INSERT statement.

|||Thanks again Erik, I guess I'll have to struggle through with that for now.