Update <table Name> Set <fields> where condition

[komatouch09]

Hi
I am new to visual basic. I write program to navigate & update data from table.
My database is in Informix & use ODBC connection. When I nevigate the data it works properly. When I Update the record its also updated but after that if I try to navigate it its giving error "Row cannot be located for updating. Some values may have been change since it was last read". My code is as follows -

Option Explicit
Dim cnn As Connection
Dim WithEvents rs As Recordset
Dim stmt As String
Dim sql As String
Dim mm_rec As Integer
Dim mm_cnt As Integer


Private Sub cmdDel_Click()
On Error GoTo errdel
Dim txtmsg As Integer
txtmsg = MsgBox("This will Delete Record From MASTER", vbOKCancel, "WARNING")
If txtmsg = 1 Then
sql = "delete from masters where ma_trcd = '" & txtFields(0) & "' and ma_code = '" & txtFields(1) & "' "
cnn.Execute sql

MsgBox "Record Deleted Successfully !!"
Exit Sub
Else
stBar.Panels(3) .Text = "DELETE ABORT"


Exit Sub
End If
errdel:
MsgBox "Can Not Delete Record Check Error "
End Sub

Private Sub cmdExit_Click()
cnn.Close
Unload Me
End Sub
Private Sub disprecno()
stBar.Panels(3) .Text = "Record : " & CStr(rs.Absolut ePosition)
End Sub


Private Sub cmdNext_Click()
On Error GoTo errnext
If Not rs.EOF Then rs.MoveNext
disprecno

If rs.EOF Then
rs.MoveLast
MsgBox "This Is Last Record !!!"
End If
Exit Sub
errnext:
MsgBox Err.Description
End Sub
Private Sub rs_MoveComplete (ByVal adReason As ADODB.EventReas onEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStat usEnum, ByVal pRecordset As ADODB.Recordset )

stBar.Panels(3) .Text = "Record: " & CStr(rs.Absolut ePosition)
End Sub
Private Sub cmdPrevious_Cli ck()
On Error GoTo errpr
If Not rs.BOF Then rs.MovePrevious
disprecno

If rs.BOF And rs.RecordCount > 0 Then
rs.MoveFirst
MsgBox "This Is First Record !!!"
End If
Exit Sub

errpr:
MsgBox Err.Description
End Sub

Private Sub cmdUpdate_Click ()
On Error GoTo errupdt
sql = "update masters set ma_name = '" & txtFields(2) & "' where ma_trcd = '" & txtFields(0) & "' and ma_code = '" & txtFields(1) & "'"
cnn.Execute sql

MsgBox "Record Updated Successfully !!"
Exit Sub
errupdt:
MsgBox Err.Description

End Sub

Private Sub Form_Load()
Set cnn = New Connection
cnn.ConnectionS tring = "Provider=MSDAS QL.1;Persist Security Info=False;User ID=lmdbs;Data Source=inf"
cnn.CursorLocat ion = adUseClient

cnn.Open
stmt = "select ma_trcd,ma_code ,ma_name from masters order by ma_trcd"
Set rs = New Recordset
rs.Open stmt, cnn, adOpenDynamic, adLockOptimisti c

Dim oText As TextBox
mm_cnt = rs.AbsolutePosi tion

For Each oText In Me.txtFields
Set oText.DataSourc e = rs
Next

End Sub


Can anyone help me in this.
Thax
Komal

[willakawill]

Hi
I am new to visual basic. I write program to navigate & update data from table.
My database is in Informix & use ODBC connection. When I nevigate the data it works properly. When I Update the record its also updated but after that if I try to navigate it its giving error "Row cannot be located for updating. Some values may have been change since it was last read". My code is as follows -

Option Explicit
Dim cnn As Connection
Dim WithEvents rs As Recordset
Dim stmt As String
Dim sql As String
Dim mm_rec As Integer
Dim mm_cnt As Integer


Private Sub cmdDel_Click()
On Error GoTo errdel
Dim txtmsg As Integer
txtmsg = MsgBox("This will Delete Record From MASTER", vbOKCancel, "WARNING")
If txtmsg = 1 Then
sql = "delete from masters where ma_trcd = '" & txtFields(0) & "' and ma_code = '" & txtFields(1) & "' "
cnn.Execute sql

MsgBox "Record Deleted Successfully !!"
Exit Sub
Else
stBar.Panels(3) .Text = "DELETE ABORT"


Exit Sub
End If
errdel:
MsgBox "Can Not Delete Record Check Error "
End Sub

Private Sub cmdExit_Click()
cnn.Close
Unload Me
End Sub
Private Sub disprecno()
stBar.Panels(3) .Text = "Record : " & CStr(rs.Absolut ePosition)
End Sub


Private Sub cmdNext_Click()
On Error GoTo errnext
If Not rs.EOF Then rs.MoveNext
disprecno

If rs.EOF Then
rs.MoveLast
MsgBox "This Is Last Record !!!"
End If
Exit Sub
errnext:
MsgBox Err.Description
End Sub
Private Sub rs_MoveComplete (ByVal adReason As ADODB.EventReas onEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStat usEnum, ByVal pRecordset As ADODB.Recordset )

stBar.Panels(3) .Text = "Record: " & CStr(rs.Absolut ePosition)
End Sub
Private Sub cmdPrevious_Cli ck()
On Error GoTo errpr
If Not rs.BOF Then rs.MovePrevious
disprecno

If rs.BOF And rs.RecordCount > 0 Then
rs.MoveFirst
MsgBox "This Is First Record !!!"
End If
Exit Sub

errpr:
MsgBox Err.Description
End Sub

Private Sub cmdUpdate_Click ()
On Error GoTo errupdt
sql = "update masters set ma_name = '" & txtFields(2) & "' where ma_trcd = '" & txtFields(0) & "' and ma_code = '" & txtFields(1) & "'"
cnn.Execute sql

MsgBox "Record Updated Successfully !!"
Exit Sub
errupdt:
MsgBox Err.Description

End Sub

Private Sub Form_Load()
Set cnn = New Connection
cnn.ConnectionS tring = "Provider=MSDAS QL.1;Persist Security Info=False;User ID=lmdbs;Data Source=inf"
cnn.CursorLocat ion = adUseClient

cnn.Open
stmt = "select ma_trcd,ma_code ,ma_name from masters order by ma_trcd"
Set rs = New Recordset
rs.Open stmt, cnn, adOpenDynamic, adLockOptimisti c

Dim oText As TextBox
mm_cnt = rs.AbsolutePosi tion

For Each oText In Me.txtFields
Set oText.DataSourc e = rs
Next

End Sub


Can anyone help me in this.
Thax
Komal

Your problem is that you are deleting a record in the database using your connection, cnn. Your recordset, rs, is not updated bythe database. It is holding the records that you selected in memory. The conflict is that the record you are trying to navigate to or from is no longer in the database but it is referenced in the recordset.

To avoid this problem you can do one of two things.
a. Never use global recordsets. always declare a new one in each sub
b. Use the recordset to delete the record:

rs.Delete adAffectCurrent
rs.Update