[RESOLVED] Updated data is not showing using sql update query

[infomamun]

Hi there
I have made a login program where I used an Access Database and ADO for populating data. I have made a common module, named 'modCommon' where connecting string code exist like this:

Option Explicit
 
Public rsData As ADODB.Recordset
Public conData As ADODB.Connection
 
'Public DBConn As ADODB.Connection
Public Function LoadDatabase() As ADODB.Connection
    Set conData = New ADODB.Connection
    
    conData.Provider = "Microsoft.Jet.OLEDB.4.0"
    conData.ConnectionString = "Data Source = " & App.Path & "\database.mdb"
    conData.Properties("Jet OLEDB:Database Password") = "password"
    conData.CursorLocation = adUseClient
    conData.Open
    Set LoadDatabase = conData
End Function

Now I have made a user maintenance form which is like below:


When I try to update a record like adding a nick name to fullname like this:


Record is being updated successfully:


But If I press 'Next' Button and then 'Previous' the form is not showing updated data:


But If I exit the form and reopen the form, then I can see the updated data.

Name of the user maintenance form is form_admin in vb6 project.
Code for form_load is as follows:

Private Sub Form_Load()
Set rsData = LoadDatabase.Execute("Select * FROM users order by ID asc")
DisplayRecord
End Sub
 
Private Function DisplayRecord()
Dim Ut As String
 
Ut = rsData.Fields("utype").Value
txtFname.Text = rsData.Fields("fname").Value
txtUser.Text = rsData.Fields("uname").Value
txtPassword.Text = rsData.Fields("pword").Value
cmboUType.Clear
cmboUType.AddItem rsData("utype").Value
cmboUType.ListIndex = 0
 
 
If Ut <> "User" Then
cmboUType.AddItem "User"
End If
If Ut <> "Admin" Then
cmboUType.AddItem "Admin"
End If
If Ut <> "Banned" Then
cmboUType.AddItem "Banned"
End If
If Ut <> "Restricted" Then
cmboUType.AddItem "Restricted"
End If
If Ut <> "SuperAdmin" Then
cmboUType.AddItem "SuperAdmin"
End If
 
End Function

And code for Update button is as follows:

Private Sub cmdUpdate_Click()
Dim staffName As String
Dim uid As Integer
 
'rsBookmark = rsData.Bookmark
staffName = rsData("uname").Value
uid = rsData("ID").Value
If MsgBox("If you click 'Yes', data will be updated." & vbCrLf & "Are you sure to update the data?", vbQuestion + vbYesNo, "Confirm Update") = vbYes Then
LoadDatabase.Execute ("UPDATE users SET fname='" & txtFname.Text & "',uname='" & txtUser.Text & "',pword='" & txtPassword.Text & "',utype='" & cmboUType.List(cmboUType.ListIndex) & "' WHERE ID=" & uid & "")
MsgBox "Record of " & staffName & " has been Updated", vbExclamation, "Record Updated"
rsData.Requery
 
End If
End If
End Sub

And the code for the 'Next' button is as follows:

Private Sub cmdNext_Click()
rsData.MoveNext
If rsData.EOF = True Then
rsData.MoveLast
MsgBox "No More User Exist!", vbInformation, "No User"
Exit Sub
Else
DisplayRecord
End If
End Sub

My question is why updated data is not showing after executing update sql query even after recordset.requery(rsData.Requery)? Where is the problem?

Thanks in Advance

[si_the_geek]

There are various options that can be specified when opening a recordset, including whether edited/added records should be shown.

As you have used connection.Execute , you haven't specified the relevant option, so the behaviour you get depends on the default (of ADO, and/or the database system). If you change to recordset.Open , you will be able to specify the option.

For more information, see the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[gibra]

After INSERT or UPDATE need to refresh the cache:
http://www.xtremevbtalk.com/showthread.php?t=102739#5

Of course, you need to refresh recordset.

[infomamun]

HI si_the_greek,
I have changed the modCommon.bas as follows:

Option Explicit
Global conData As ADODB.Connection
Global rsData As New ADODB.Recordset
'Public conData As ADODB.Connection
Global SQL As String
 
Public Sub LoadDatabase()
    Set conData = New ADODB.Connection
    
    conData.Provider = "Microsoft.Jet.OLEDB.4.0"
    conData.ConnectionString = "Data Source = " & App.Path & "\database.mdb"
    conData.Properties("Jet OLEDB:Database Password") = "password"
    conData.CursorLocation = adUseClient
    conData.Open
End Sub

And have changed the form_load code as below:

Private Sub Form_Load()
 
SQL = "SELECT * FROM users"
Call LoadDatabase
rsData.Open SQL, conData, adOpenDynamic
DisplayRecord
End Sub

And the code for cmdNext Button remained same:

Private Sub cmdNext_Click()
rsData.MoveNext
If rsData.EOF = True Then
rsData.MoveLast
MsgBox "No More User Exist!", vbInformation, "No User"
Exit Sub
Else
DisplayRecord
End If
End Sub

Using this altered code, I can browse the users table smoothly by next button. But when I Update data by the following:

Private Sub cmdUpdate_Click()
Dim staffName As String
Dim uid As Integer
Dim rsBookmark As Variant
 
 
'rsBookmark = rsData.Bookmark
staffName = rsData("uname").Value
uid = rsData("ID").Value
'rsData.Close
If MsgBox("If you click 'Yes', data will be updated." & vbCrLf & "Are you sure to update the data?", vbQuestion + vbYesNo, "Confirm Update") = vbYes Then
SQL = "UPDATE users SET fname='" & txtFname.Text & "',uname='" & txtUser.Text & "',pword='" & txtPassword.Text & "',utype='" & cmboUType.List(cmboUType.ListIndex) & "' WHERE ID=" & uid & ""
Call LoadDatabase
rsData.Open SQL, conData, adOpenDynamic
MsgBox "Record of " & staffName & " has been Updated", vbExclamation, "Record Updated"
 
'DisplayRecord
'rsData.MoveNext
End If
End Sub

It can update the database correctly. But after that if I press Next Button it shows:


Would you show me the correct code, so that both after form_load and after cmdUpdate_Click I can use cmdNext Button to browse Next Record of user table?

[si_the_geek]

HI si_the_greek,

Erm, no


LoadDatabase is better than it was, as now only need to call it from Form_Load, rather than every time you use the connection... you should remove the other call(s) to it.

You should only use a recordset to execute things that return records. For an action statement (Update/Delete/etc), you should use connection.Execute (in this particular case: conData.Execute SQL). Once you change that, you will eliminate another problem (the fact you have used rsData for another purpose, thus can't re-use it for the original purpose).


However... why are you using an SQL statement rather than editing via the recordset that already has the data? You have made things more complicated for no noticeable gain, and added potential problems (such as the ' character in data causing errors).

I recommend taking a look at the ADO Tutorial in our Database Development FAQs/Tutorials (at the top of the Database Development forum), as it does basically the same thing you are attempting... but without the issues you currently have.

[infomamun]

Hi si_the_geek

Sorry for misspelling of your name

Anyway, I can now update as follows:

with rsData
!(FieldName) = Control.Text
.Update
End with

And it is showing updated data while pressing Next or Previous Button. Actually I knew that. But as I was using LoadDatabase.Execute("Sql query") directly, I was unable to use '.Update' method, as I could not set locktype to adLockOptimistic.

However, is there any way to set locktype in my previous LoadDatabase.Execute("Sql query")? If can, then I can use '.Update' method to my previous code. I am seeking that because I have to recode the whole program using rsData.Open system.

Thanks

[si_the_geek]

Hi si_the_geek

Sorry for misspelling of your name

No worries, you aren't the first

However, is there any way to set locktype in my previous LoadDatabase.Execute("Sql query")?

No.

I am seeking that because I have to recode the whole program using rsData.Open system.

I don't see why... the recordset still has the same methods etc, all you should need to change is a few lines in cmdUpdate_Click (from the Update SQL statement you had originally to the code in your previous post).

[infomamun]

Because If I try to update after loading recorset using LoadDatabase.Execute Method and then '.Update' method, A dialog box shows when clicked the Update Button:

"Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."

This is may be due to the Locktype because even after use rsData.Open method, if I do not include Locktype (rsData.Open SQL, conData, adOpenDynamic), the same dialog shows while pressing Update button. But if I use rsData.Open SQL, conData, adOpenDynamic, adLockOptimistic, Then all things goes normally. But I can't use adLockOptimistic while using LoadDatabase.Execute method as don't know how to include this locktype in this method.
Thanks

[si_the_geek]

That is expected, because if you don't specify a Lock type then read-only is used (as it is a bit faster, and it is much less likely to cause problems in various situations).


Do not open the recordset (in Form_Load) using connection.Execute , use recordset.Open as you showed in post #4. You do not need to open a recordset (or use connection.Execute) anywhere else.

For the update, use the style you showed in post #6. The rest of your code is irrelevant.

[infomamun]

use recordset.Open as you showed in post #4.

Yes, that will do. But for that, I have to change -
Global rsData As ADODB.Recordset to
Global rsData As New ADODB.Recordset
- in modCommon.bas

Also I can't use set rsData, only option is rsData.Open.
Benifit of set rsData is that if I can set a Second rsData and it overrides the first one which prevents memory overload. But using rsData.Open method, If I use rsData.Open second time for any reason, therewill be two instance of rsData(Recordset) as in modCommon, rsData is declared as New ADODB.Recordset or a second rsData cannot be opened without closing the first one which may create complicity in some cases.

Anyway, thanks for your help. It made me more clear about my coding.

[si_the_geek]

Yes, that will do. But for that, I have to change -
Global rsData As ADODB.Recordset to
Global rsData As New ADODB.Recordset
- in modCommon.bas

No you don't, and it's better if you don't - as explained in the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs (in the FAQ forum)

Rather than the "new" on that line, you should add another line:

Set rsData = New ADODB.Recordset

(this should go before you first try to use rsData)

Benifit of set rsData is that if I can set a Second rsData and it overrides the first one which prevents memory overload.

No it doesn't... the first one still exists, you just don't have access to it.

At some point ADO will work out that it needs to be tidied up, and do it automatically... but in the mean time, it wastes memory and can cause various problems based on locking etc.

To avoid that, you should tidy it up yourself (by closing it, and if you aren't going to use the same variable again set it to Nothing).

But using rsData.Open method, If I use rsData.Open second time for any reason, therewill be two instance of rsData(Recordset) as in modCommon, rsData is declared as New ADODB.Recordset or a second rsData cannot be opened without closing the first one which may create complicity in some cases.

You should close a recordset when you are done with it (no matter why you don't want the data any more). Doing that will allow you to re-open it with a different SQL statement, and takes just 2 lines of code:

rsData.Close
rsData.Open ....

However, more often than not you will want different recordsets (each having a different variable), or not want to use a recordset at all (such as for an Update SQL statement).

[infomamun]

Ok thanks for your clarification. It helped me a lot.

[infomamun]

Hello geek
Would you help me one more?

I have changed codes for recordset like this:

Set recordset = New ADODB.Recordset
SQL = "SELECT * FROM dbtable ORDER BY id"
recordset.Open SQL, conn, adOpenDynamic, adLockOptimistic

by this code all things are going Ok including adding, updating and deleting. But in case of 'Search' option such as 'Search by Name', I must have to change SQL Query like this:

SQL = "SELECT * FROM dbtable WHERE userName = '" & txtUser.Text & "'"

and either have to run the query by:

recordset.close
set recordset = Nothing
recordset.Open SQL, conn, adOpenDynamic, adLockOptimistic

or by:

conn.Execute SQL

In both cases, the search function works well. But if I use second way, main problem is, If there are more than one user of the same name (e.g Suppose one user took training from different organization and I want to use search by name), then when I press next button for second record of the same user, vb shows: Current RecordSet does not support updating. This may be due to .......Selected lock type.

So If I want to perform Next or Previous search after conducting Search BY Name action, then I have to use First Method (i.e recordset.Open) method. In this case I have to-

1) open recordset during form_Load with first SQL query(SELECT * FROM dbtable ORDER BY id)
1) bookmark current recordset, 
2) close the current recordset,
3) Set recordset again as New ADODB.Recordset 
3) reopen recordset with another SQL query (SELECT * FROM dbtable WHERE userName = '" & txtUser.Text & "')
4) again stop that recordset,
5) again have to reopen recordset with first SQL query(SELECT * FROM dbtable ORDER BY id)
6) and then have to bring recordset in bookmarked condition.

In this way I can use Next or Previous button with a Search by Name operation. But this method is lengthy process. I have to close and reopen recorset several times to get desired result.

Is there any way to get the same result with least code using recordset.Open during form_Load and then conn.execute or any suitable method for Searching Name together with Next and Previous option?

Wishing your help eagerly.

Thanks

[si_the_geek]

As has already been said above, if you want a recordset to be editable, you need to use .Open ... That wont change no matter how many times or ways you ask it.

In terms of the steps you listed, there are too many... If you are re-opening a recordset, .Close it and .Open it - you do not need to use Set in between. If you are re-opening via .Execute, you should .Close it, set it to nothing, and then .Execute

As to your concerns about Next/Prev after a search, as a user I would expect them to show the records that meet the search criteria - and only be able to get to other records if I end the search (most likely by manually searching again with no criteria, but you could make a button to do the same).