Inserting data into SQL Server table with ADO

[JE]

I'm trying to insert data into a SQL Server 2000 table using the following
code:

Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=MyDb;Data
Source=SQL01"
cn.Open

Dim rs As New ADODB.Recordset

With rs
.CursorLocation = adUseServer
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.Open ("SELECT * FROM Claims")
End With

rs.AddNew

The code fails on rs.AddNew with the message "The connection cannot be used
to perform this operation. It is either closed or invalid in this context"

What am I doing wrong?

Thanks!

 

[Sylvain Lafontaine]

You must associate your connection cn with the recordset rs:

Set rs.ActiveConnection = cn

Don't forget the "Set" at the beginning of the line. You could also change
your Open statement for the recordset:

rs.Open sql_String, cn, adOpenStatic, adLockOptimistic

Also, don't use the syntaxe « Dim rs As New ADODB.Recordset » (it has a bad
mojo for complex objects) and don't use a server cursor if all you want to
do is to insert new records:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "Select * From Claims Where 1=0", ......

Finally, another good newsgroup for ADO would be microsoft.public.data.ado.

 

[JE]

Thanks, that was exactly what I needed!