ADO Code VB &SQL

[vidkrish]

I have connected to SQL Server 6.5 through ADO Data Control ( VB 6.0 ) using a DSN.

I have declared the ADO connection object Dim con As New ADODB.Connection
& the recordset ( rs ) As New ADODB.Recordset

When I try to add a new record through VB I get a
Run-time error '3251'

"The operation requested by the application is not supported by the provider."

at - > rs.addnew.

Please let me know why.
I would appreciate any help.

Thanks,
Vidya.

[preeti]

Hi,

Is this all the code or is there more between the declaration and the addnew statement?

Preeti

[vidkrish]

Hi,

That's the code.
I have a command button called Display to display the records.
Then I have anothere command button Add to add new records thr' VB.

Thanks,
Vidya.

[vidkrish]

Hi Jason Williams,

Thanks a lot for the reply.
When the VB application is run I want the user to enter data into the table, that is through text boxes in VB.
I used the DSN.
Also please send me the function that will connect to SQL Via OLE DB, with error trapping.I would really appreciate your help.
My email id is ( vidkrish@yahoo.com ).

Thanking you,
Vidya.

[JayWms]

I am on ICQ right now, can you log in to it?

#7292656

[vidkrish]

When I closed the resultset as it is was already open using
If RS.State = adStateOpen Then RS.
I get a Run-time error '3704':-
" The operation requested by the application is not allowed if the object is closed. "

Thanks,
Vidya.

[preeti]

Before you can add a record to a table, you need to connect to the database, and then create an object to that table.

Are you doing this before you try and add records to the table?

If yes, what is the code that connects to the database and then opens the table?

Preeti

[JayWms]

In order to [addnew] you need to have something you are adding to?

Dim RS as new ADODB.Recordset
Dim CN as new ADODB.Connection

Connect to the database any way you know how... I use the OLE DB, it seems to be the best way.

SQL$ is a String that holds the SQL query you are going to run.

SQL$ = "Select * from TABLE"

'Close the resultset if it is already open
If RS.State = adStateOpen Then RS.Close

'Retrieve records from the SQL database
RS.Open SQL$, CN, adOpenStatic, adLockReadOnly

Okay, now that you have some records, you can add some data.
RS.Addnew
RS("Fieldname1") = "Look"
RS("Fieldname2") = "At"
RS("Fieldname3") = "The"
RS("Fieldname4") = "Data!"
RS.Update

And there you go... Simple! If you want, I have a function that will connect to SQL Via OLE DB, with error trapping.

Jason Williams
Arlington, TX



[This message has been edited by JayWms (edited 07-22-1999).]

[vidkrish]

My Connection to the db & then table goes thr' real fine.

Connect to the database :-

Private Sub Form_Load()
Dim con As New ADODB.Connection
Dim rs
con.ConnectionString = "DSN=test;uid=sa;pwd=pwd;"
con.Open
MsgBox "The Connection is now open!"
' Open the recordset rs
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "test", con, , , adCmdTable
End Sub


Vidya.