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
1 5669
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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
|
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...
|
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 =...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |