INSERT INTO (SQL statement with RDO)

[duke1000]

Hi :-)

I would like to know how to insert a new row in a table with RDO programming ? Should I use the INSERT TO (SQL) or the .AddNew method. I tried both, without success.

Here's my code :
--------------------

Option Explicit
Dim conn As rdoConnection
Dim qy As rdoQuery
Dim rs As rdoResultset


Private Sub Form_Load()

Set conn = New rdoConnection

With conn
.Connect = "dsn=vernon"
.EstablishConnection rdDriverNoPrompt, True
End With

Set qy = New rdoQuery

qy.SQL = "select * from authors"

Set qy.ActiveConnection = conn
Set rs = qy.OpenResultset(rdOpenKeyset)

End Sub



Private Sub B_Add_Click()

' Here is How I use the .AddNew method
With rs
.AddNew
!au_id = T_au_id
!au_lname = T_au_lname
!au_fname = T_au_fname
!contract = T_contract
!address = T_address
!city = T_city
!state = T_state
!zip = T_zip
!phone = T_phone
.Update
End With

' And here is how I use the INSERT TO (SQL)
conn.BeginTrans
qy.Execute " insert into authors " _
& "(au_id, au_lname, au_fname, contract, address, city, state, zip, phone) values " _
& "(T_au_id.Text, T_au_lname.Text, T_au_fname.Text, T_contract.Text, T_address.Text, T_city.Text, T_state.Text, T_zip.Text, T_phone.Text);"
conn.CommitTrans

End Sub

---------------------------------

Thank you so much ! )

Duke1000 :-)

[santulan]

Duke,
There is error in your INSERT statement. I am correcting and sending you back. Use it and let me know if it work
' And here is how I use the INSERT TO (SQL)
conn.BeginTrans
qy.Execute " insert into authors " _
& "(au_id, au_lname, au_fname, contract, address, city, state, zip, phone) values " _
& "('" & T_au_id.Text & "','" & T_au_lname.Text & "','" & "','" & T_au_fname.Text & "','" & T_contract.Text & "','" & "','" & T_address.Text & "','" & T_city.Text & "','" & T_state.Text "','" & T_zip.Text & "','" & T_phone.Text"')"
conn.CommitTrans

I assumed that all field of character type. If some fields are numeric, please remove ' sign. If the field is of date type place # both side instead of '.
I hope this solves your problem. This will be better if you see the SQL statement in debug before executing it.



Santulan