473,320 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Update <table Name> Set <fields> where condition

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.AbsolutePosition)
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.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

stBar.Panels(3).Text = "Record: " & CStr(rs.AbsolutePosition)
End Sub
Private Sub cmdPrevious_Click()
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.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=lmdbs;Data Source=inf"
cnn.CursorLocation = 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, adLockOptimistic

Dim oText As TextBox
mm_cnt = rs.AbsolutePosition

For Each oText In Me.txtFields
Set oText.DataSource = rs
Next

End Sub


Can anyone help me in this.
Thax
Komal
Oct 12 '06 #1
1 5669
willakawill
1,646 1GB
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.AbsolutePosition)
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.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

stBar.Panels(3).Text = "Record: " & CStr(rs.AbsolutePosition)
End Sub
Private Sub cmdPrevious_Click()
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.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=lmdbs;Data Source=inf"
cnn.CursorLocation = 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, adLockOptimistic

Dim oText As TextBox
mm_cnt = rs.AbsolutePosition

For Each oText In Me.txtFields
Set oText.DataSource = 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
Oct 12 '06 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Paul Scotchford | last post by:
Env : SLQSERVER2000 - DTS I want to import a text file with 3 fields in it ... F1: Ident char(3) F2: Note (Text) char(32,000) yeah big eh! F3: Date Field F2: has...
5
by: Jonathan Daggar | last post by:
Hello, I'm trying to put together a form with a very tight table formatting. However, every time I put an text-type input field in, the browser pads the area to the right of it with space. I've...
61
by: Toby Austin | last post by:
I'm trying to replace <table>s with <div>s as much as possible. However, I can't figure out how to do the following… <table> <tr> <td valign="top" width="100%">some data that will...
2
by: js | last post by:
I have a table rendered with XSLT. The first column has a radio button controls for user to make a selection for a particular row. All the values in the remaining columns are all concated with a...
6
by: Joe Morrison | last post by:
By my reading of the HTML standard: http://www.w3.org/TR/REC-html40/struct/tables.html the HTML shown below should display the text "Enter password" with the input field immediately adjacent,...
2
by: RBohannon | last post by:
I had some help on this one earlier, but I'm still having a bit of trouble. I'm sure it's something simple that I just don't know. I'm using Access2000. I have one table with employee salary...
2
by: Frank van Vugt | last post by:
Hi, Not exactly a showstopper, but I noticed this behaviour: db=# create table f1 (id int, value int); CREATE TABLE db=# insert into f1 select 1 as id, null; INSERT 25456306 1
5
by: Steven | last post by:
I have the following in my web.config: <system.web> <profile defaultProvider="MyASPSqlProfileProvider" enabled="true" > <properties> <add name="FirstName" defaultValue="" type="string"/> <add...
2
by: jessy | last post by:
I have a table at which i need to add fields in it whenever the user clicks on the Add button , here's my trial but seems sth is wrong : function AddTool() { formdiv =...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.