Results 1 to 14 of 14

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

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

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

    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:

    VBCode Code:
    1. Option Explicit
    2.  
    3. Public rsData As ADODB.Recordset
    4. Public conData As ADODB.Connection
    5.  
    6. 'Public DBConn As ADODB.Connection
    7. Public Function LoadDatabase() As ADODB.Connection
    8.     Set conData = New ADODB.Connection
    9.    
    10.     conData.Provider = "Microsoft.Jet.OLEDB.4.0"
    11.     conData.ConnectionString = "Data Source = " & App.Path & "\database.mdb"
    12.     conData.Properties("Jet OLEDB:Database Password") = "password"
    13.     conData.CursorLocation = adUseClient
    14.     conData.Open
    15.     Set LoadDatabase = conData
    16. 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:
    VBCode Code:
    1. Private Sub Form_Load()
    2. Set rsData = LoadDatabase.Execute("Select * FROM users order by ID asc")
    3. DisplayRecord
    4. End Sub
    5.  
    6. Private Function DisplayRecord()
    7. Dim Ut As String
    8.  
    9. Ut = rsData.Fields("utype").Value
    10. txtFname.Text = rsData.Fields("fname").Value
    11. txtUser.Text = rsData.Fields("uname").Value
    12. txtPassword.Text = rsData.Fields("pword").Value
    13. cmboUType.Clear
    14. cmboUType.AddItem rsData("utype").Value
    15. cmboUType.ListIndex = 0
    16.  
    17.  
    18. If Ut <> "User" Then
    19. cmboUType.AddItem "User"
    20. End If
    21. If Ut <> "Admin" Then
    22. cmboUType.AddItem "Admin"
    23. End If
    24. If Ut <> "Banned" Then
    25. cmboUType.AddItem "Banned"
    26. End If
    27. If Ut <> "Restricted" Then
    28. cmboUType.AddItem "Restricted"
    29. End If
    30. If Ut <> "SuperAdmin" Then
    31. cmboUType.AddItem "SuperAdmin"
    32. End If
    33.  
    34. End Function

    And code for Update button is as follows:
    VBCode Code:
    1. Private Sub cmdUpdate_Click()
    2. Dim staffName As String
    3. Dim uid As Integer
    4.  
    5. 'rsBookmark = rsData.Bookmark
    6. staffName = rsData("uname").Value
    7. uid = rsData("ID").Value
    8. If MsgBox("If you click 'Yes', data will be updated." & vbCrLf & "Are you sure to update the data?", vbQuestion + vbYesNo, "Confirm Update") = vbYes Then
    9. LoadDatabase.Execute ("UPDATE users SET fname='" & txtFname.Text & "',uname='" & txtUser.Text & "',pword='" & txtPassword.Text & "',utype='" & cmboUType.List(cmboUType.ListIndex) & "' WHERE ID=" & uid & "")
    10. MsgBox "Record of " & staffName & " has been Updated", vbExclamation, "Record Updated"
    11. rsData.Requery
    12.  
    13. End If
    14. End If
    15. End Sub

    And the code for the 'Next' button is as follows:
    VBCode Code:
    1. Private Sub cmdNext_Click()
    2. rsData.MoveNext
    3. If rsData.EOF = True Then
    4. rsData.MoveLast
    5. MsgBox "No More User Exist!", vbInformation, "No User"
    6. Exit Sub
    7. Else
    8. DisplayRecord
    9. End If
    10. 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
    Last edited by infomamun; May 6th, 2012 at 10:35 PM. Reason: Hiding original password and database name

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Updated data is not showing using sql update query

    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)

  3. #3
    gibra
    Guest

    Re: Updated data is not showing using sql update query

    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.


  4. #4

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Re: Updated data is not showing using sql update query

    HI si_the_greek,
    I have changed the modCommon.bas as follows:
    2 Code:
    1. Option Explicit
    2. Global conData As ADODB.Connection
    3. Global rsData As New ADODB.Recordset
    4. 'Public conData As ADODB.Connection
    5. Global SQL As String
    6.  
    7. Public Sub LoadDatabase()
    8.     Set conData = New ADODB.Connection
    9.    
    10.     conData.Provider = "Microsoft.Jet.OLEDB.4.0"
    11.     conData.ConnectionString = "Data Source = " & App.Path & "\database.mdb"
    12.     conData.Properties("Jet OLEDB:Database Password") = "password"
    13.     conData.CursorLocation = adUseClient
    14.     conData.Open
    15. End Sub

    And have changed the form_load code as below:
    2 Code:
    1. Private Sub Form_Load()
    2.  
    3. SQL = "SELECT * FROM users"
    4. Call LoadDatabase
    5. rsData.Open SQL, conData, adOpenDynamic
    6. DisplayRecord
    7. End Sub

    And the code for cmdNext Button remained same:
    2 Code:
    1. Private Sub cmdNext_Click()
    2. rsData.MoveNext
    3. If rsData.EOF = True Then
    4. rsData.MoveLast
    5. MsgBox "No More User Exist!", vbInformation, "No User"
    6. Exit Sub
    7. Else
    8. DisplayRecord
    9. End If
    10. End Sub

    Using this altered code, I can browse the users table smoothly by next button. But when I Update data by the following:
    2 Code:
    1. Private Sub cmdUpdate_Click()
    2. Dim staffName As String
    3. Dim uid As Integer
    4. Dim rsBookmark As Variant
    5.  
    6.  
    7. 'rsBookmark = rsData.Bookmark
    8. staffName = rsData("uname").Value
    9. uid = rsData("ID").Value
    10. 'rsData.Close
    11. If MsgBox("If you click 'Yes', data will be updated." & vbCrLf & "Are you sure to update the data?", vbQuestion + vbYesNo, "Confirm Update") = vbYes Then
    12. SQL = "UPDATE users SET fname='" & txtFname.Text & "',uname='" & txtUser.Text & "',pword='" & txtPassword.Text & "',utype='" & cmboUType.List(cmboUType.ListIndex) & "' WHERE ID=" & uid & ""
    13. Call LoadDatabase
    14. rsData.Open SQL, conData, adOpenDynamic
    15. MsgBox "Record of " & staffName & " has been Updated", vbExclamation, "Record Updated"
    16.  
    17. 'DisplayRecord
    18. 'rsData.MoveNext
    19. End If
    20. 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?
    Last edited by infomamun; May 6th, 2012 at 10:34 PM. Reason: hiding original password and database name

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Updated data is not showing using sql update query

    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.

  6. #6

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Re: Updated data is not showing using sql update query

    Hi si_the_geek

    Sorry for misspelling of your name

    Anyway, I can now update as follows:
    Vbcode: Code:
    1. with rsData
    2. !(FieldName) = Control.Text
    3. .Update
    4. 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

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Updated data is not showing using sql update query

    Quote Originally Posted by infomamun View Post
    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).

  8. #8

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Re: Updated data is not showing using sql update query

    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
    Last edited by infomamun; May 7th, 2012 at 03:54 AM.

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Updated data is not showing using sql update query

    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.

  10. #10

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Re: Updated data is not showing using sql update query

    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.
    Last edited by infomamun; May 7th, 2012 at 12:47 PM.

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Updated data is not showing using sql update query

    Quote Originally Posted by infomamun View Post
    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:
    Code:
    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:
    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).

  12. #12

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Re: Updated data is not showing using sql update query

    Ok thanks for your clarification. It helped me a lot.
    Last edited by infomamun; May 8th, 2012 at 03:17 PM.

  13. #13

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Re: Updated data is not showing using sql update query

    Hello geek
    Would you help me one more?

    I have changed codes for recordset like this:

    Code:
    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:
    Code:
    SQL = "SELECT * FROM dbtable WHERE userName = '" & txtUser.Text & "'"
    and either have to run the query by:

    Code:
    recordset.close
    set recordset = Nothing
    recordset.Open SQL, conn, adOpenDynamic, adLockOptimistic
    or by:
    Code:
    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-

    Code:
    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

  14. #14
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Updated data is not showing using sql update query

    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width