Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    How to check if the Database was update

    HI,

    Im trying to create a program that updates a record on my database. It works just fine. However, it always displays "Record was updated!" even if the filters in my SQL statement were not satisfied. I know that i need to create another condition to check whether my SQL statement was satisfied but i dont know where to start and where to put it... Thanks in advance...

    here is my code.... database is access 2003...

    <%


    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open "C:\Documents and Settings\CTSLab\Desktop\lab webby\webby 2\labapp.mdb"

    sql="UPDATE borrowTable SET "
    sql=sql & "timeReturned='" & Request.Form("timeReturned") & "'"
    sql=sql & " WHERE userName='" & Request.Form("userName") & "' and userPassword='" & Request.Form("userPassword") & "'"





    on error resume next
    conn.Execute sql, resaffected
    if err<>0 then
    response.write("No update permissions!")
    else
    response.write("Record was updated!")
    end if

    conn.close


    %>

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    I am not sure that Access does this.

    The best thing that you can do is to add a TIMESTAMP field onto your records, and set the default to NOW(). That way, you can query the database to look at which records have an updated TIMESTAMP field and do a count there.

    EXAMPLE:

    SELECT COUNT(RecordID) FROM your_table WHERE LastEdit >= (NOW()-1);

    That tells the database to count the updated records with a LastEdit timestamp of NOW() minus 1 second.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    thanks for you reply...

    i understand what you are asking me to do... but can you give me more details on how to do it? like where to insert SELECT COUNT(RecordID) FROM your_table WHERE LastEdit >= (NOW()-1); and how to display the result of that query...

    thanks a lot....

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Once you have added the field onto your database records, then you would do this:

    Code:
    'on error resume next
    conn.Execute sql, resaffected
    if err<>0 then
    response.write("No update permissions!")
    else
      Set rs = Server.CreateObject("ADODB.Recordset")
      rs.open "SELECT COUNT(RecordID) AS 'RecAffected' FROM borrowTable WHERE LastEdit >= (NOW()-1);", conn
        recordsAffected = rs("RecAffected")
      rs.close
      Set rs = nothing
      '
    response.write(recordsAffected & " records were updated!")
    end if
    
    conn.close
    I don't know what your RecordID field is called, nor your 'lastedit' field, so make sure that you update those when you paste this into your code.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • Users who have thanked Daemonspyre for this post:

    xavatar (08-31-2007)

  • #5
    New Coder
    Join Date
    Aug 2007
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you very much... this is how my code looks like now....



    <%


    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open "C:\Documents and Settings\CTSLab\Desktop\lab webby\webby 2\labapp.mdb"

    sql="UPDATE borrowTable SET "
    sql=sql & "timeReturned='" & Request.Form("timeReturned") & "'"
    sql=sql & "timeStamp='" & Now() & "'"
    sql=sql & " WHERE userName='" & Request.Form("userName") & "' and userPassword='" & Request.Form("userPassword") & "'"





    'on error resume next
    conn.Execute sql, resaffected
    if err<>0 then
    response.write("No update permissions!")
    else
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.open "SELECT COUNT(timeReturned) AS 'RecAffected' FROM borrowTable WHERE timeStamp >= (NOW()-1);", conn
    recordsAffected = rs("RecAffected")
    rs.close
    Set rs = nothing
    '
    response.write(recordsAffected & " records were updated!")
    end if

    conn.close


    %>


    ....
    Now im getting this error message....

    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error (missing operator) in query expression ''11'timeStamp='9/14/2007 1:23:41 AM''.
    /CTs2/returnsystem.asp, line 206

    ....

    what i did was i created a new field in my database called timeStamp.... data type is time/date...

    I am trying to update only 1 field at a time....
    the field that i am trying to update is the timeReturned field.... at the same time, im am putting a time stamp value on the field timeStamp (as per your advice earlier)...

    the concept of the whole program is much like that of a library system where you borrow and return books and you update the database every transaction...

    I pray for your patience... Thanks!!!

  • #6
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    I've got to be misunderstanding something here, you're calling:

    conn.Execute sql, resaffected

    If there was at least one record that matched the criteria in your SQL statement, and the attempt to update was successful, then resaffected will contain the number of records that were successfully updated. So, unless I'm missing something obvious (hey, it's Friday), then all you need to do is:

    Code:
    resaffected = 0
    on error resume next
    conn.Execute sql, resaffected
    if err<>0 then
    response.write("No update permissions!")
    else
    if resaffected > 0 then
    response.write("Record was updated!")
    else
    response.write("No matching records")
    end if
    end if

  • Users who have thanked Spudhead for this post:

    xavatar (09-05-2007)


  •  

    Posting Permissions

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