vb6 update record in the database

[kinv001]

Hello, I have a combo box to select the ID, then it will display the particular record from from the database into the text boxes. And now I'm trying to make a update command button. Then I can just change the record which displaying in the text box. And click update, then the record will be updated in the database. But my cmdUpdate_Click() statement seems doesn't work. Anyone can help please? Cheers

Option Explicit
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub cmbID_Click()
If cmbID.Text = "[Make Selection]" Then Exit Sub
rs.Open "SELECT * FROM co_authors WHERE ID = " & cmbID.Text
txtName.Text = rs!author_Name
txtCountry.Text = rs!country
txtPaperID.Text = rs!paper_ID
rs.Close
End Sub

Private Sub cmdGo_Add_submission_info_Click()
submission_info.Show
update_co_authors.Hide
End Sub

Private Sub cmdUpdate_Click()
Dim ar As ADODB.Recordset
Set ar = New ADODB.Recordset
ar.Open "SELECT * FROM co_authors WHERE ID = " & cmbID.Text
If ar.EOF Then ar.AddNew
Else: ar.Update
End If
ar!author_Name = txtName.Text
ar!country = txtCountry.Text
ar!paper_ID = txtPaperID.Text
ar.Update
End Sub

Private Sub Form_Load()

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

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\262CS\project\db1.mdb;" & _
"Persist Security Info=False"

rs.Open "SELECT ID FROM co_authors", _
conn, adOpenForwardOnly, adLockReadOnly, adCmdText

cmbID.Text = "[Make Selection]"

Do Until rs.EOF
cmbID.AddItem rs!ID
rs.MoveNext
Loop

rs.Close

End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
If rs.State = adStateOpen Then rs.Close
If conn.State = adStateOpen Then conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub

[NewVBguy]

Hi,
If this problem is still not solve you can try this:::

This is your code:

Private Sub cmdUpdate_Click()
Dim ar As ADODB.Recordset
Set ar = New ADODB.Recordset
ar.Open "SELECT * FROM co_authors WHERE ID = " & cmbID.Text
If ar.EOF Then ar.AddNew
Else: ar.Update
End If
ar!author_Name = txtName.Text
ar!country = txtCountry.Text
ar!paper_ID = txtPaperID.Text
ar.Update
End Sub

'Edit this line

else: ar.update
'make it

else: ar.edit

NOTE: is your ID in your query numeric? if it does you might need to do like this:
ar.Open "SELECT * FROM co_authors WHERE ID = " & val(cmbID.Text)

hope it helps.

Newvbguy

[mnemtsas]

Take a look at this ADO tutorial, it will tell you the correct syntax to use:

http://www.timesheetsmts.com/adotutorial.htm

[pradeeparutla]

Hello, I have a combo box to select the ID, then it will display the particular record from from the database into the text boxes. And now I'm trying to make a update command button. Then I can just change the record which displaying in the text box. And click update, then the record will be updated in the database. But my cmdUpdate_Click() statement seems doesn't work. Anyone can help please? Cheers

Option Explicit
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub cmbID_Click()
If cmbID.Text = "[Make Selection]" Then Exit Sub
rs.Open "SELECT * FROM co_authors WHERE ID = " & cmbID.Text
txtName.Text = rs!author_Name
txtCountry.Text = rs!country
txtPaperID.Text = rs!paper_ID
rs.Close
End Sub

Private Sub cmdGo_Add_submission_info_Click()
submission_info.Show
update_co_authors.Hide
End Sub

Private Sub cmdUpdate_Click()
Dim ar As ADODB.Recordset
Set ar = New ADODB.Recordset
ar.Open "SELECT * FROM co_authors WHERE ID = " & cmbID.Text
If ar.EOF Then ar.AddNew
Else: ar.Update
End If
ar!author_Name = txtName.Text
ar!country = txtCountry.Text
ar!paper_ID = txtPaperID.Text
ar.Update
End Sub

Private Sub Form_Load()

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

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\262CS\project\db1.mdb;" & _
"Persist Security Info=False"

rs.Open "SELECT ID FROM co_authors", _
conn, adOpenForwardOnly, adLockReadOnly, adCmdText

cmbID.Text = "[Make Selection]"

Do Until rs.EOF
cmbID.AddItem rs!ID
rs.MoveNext
Loop

rs.Close

End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
If rs.State = adStateOpen Then rs.Close
If conn.State = adStateOpen Then conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub

Hi,

This is pradeep. I am working on a project to migrate code from Visualbasic to C#.NET. I got confused the usage of Recordset. Can you tell what is the meaning of rs!author_Name in "txtName.Text = rs!author_Name". Is that accessing the member "author_Name" in recordset. Please reply me ASAP. As it is urjent requirement for me..

Regards,
Pradeep.

[Comatose]

This thread is like, four years old.... how about you start a thread of your own?