locked
Problem updating autonumber record RRS feed

  • Question

  • Hi to all. I know how to add, update and delete in VB.NET using "Text" fields. Now, I want to update a field that is "Number" or "Autonumber" but my problem is I always got an error of Data mismatch in criteria expression. Here is my code:

    public auditno as integer
    
    dim cmd as new oledbcommand
    cmd.commandtype = commandtype.text
    cmd.commandtext = "UPDATE Audit_Trail SET Date_Logout = '" & now.toshortdatestring & "', Time_Logout = '" & now.toshorttimestring & "' WHERE Audit_No = '" & auditno & "'"
    cmd.connection = con
    
    con.open
    cmd.executenonquery
    con.close

    auditno will hold a value when the user log in. Date_Logout and Time_Logout columns are set to "Text" field, while Audit_No is "Autonumber". I have no problems in Date_Logout and Time_Logout, the only problem I think is the Audit_No which is "Autonumber". If I set it to "Text", everything is fine, but with "Autonumber" or "Number", I always come up with an error. I don't know how to get rid of this.

    Thanks in advance...


    iamjheyb

    Monday, November 5, 2012 4:56 PM

Answers

  • You should really be using a date type in the database to hold the date and time.

    As a temporary solution while you correct that and convert your SQL queries to use SQL parameters (which are much safer as they greatly reduce the chance of SQL injection attacks), you could use

    cmd.commandtext = "UPDATE Audit_Trail SET Date_Logout = '" & now.toshortdatestring & "', Time_Logout = '" & now.toshorttimestring & "' WHERE Audit_No = " & auditno.ToString & ";"

    You were putting in the auditno as a string - i.e. you had its value in single quotes.

    Also, please use Option Strict On - it might have given you the hint needed to see what was wrong.

    HTH,

    Andrew

    • Proposed as answer by Rian Widianto Tuesday, November 6, 2012 1:51 AM
    • Marked as answer by jheyb Tuesday, November 6, 2012 4:45 AM
    Monday, November 5, 2012 7:41 PM

All replies

  • You should really be using a date type in the database to hold the date and time.

    As a temporary solution while you correct that and convert your SQL queries to use SQL parameters (which are much safer as they greatly reduce the chance of SQL injection attacks), you could use

    cmd.commandtext = "UPDATE Audit_Trail SET Date_Logout = '" & now.toshortdatestring & "', Time_Logout = '" & now.toshorttimestring & "' WHERE Audit_No = " & auditno.ToString & ";"

    You were putting in the auditno as a string - i.e. you had its value in single quotes.

    Also, please use Option Strict On - it might have given you the hint needed to see what was wrong.

    HTH,

    Andrew

    • Proposed as answer by Rian Widianto Tuesday, November 6, 2012 1:51 AM
    • Marked as answer by jheyb Tuesday, November 6, 2012 4:45 AM
    Monday, November 5, 2012 7:41 PM
  • Great! Thank you very much.. Now I have get rid with this autonumber problem of mine. Thanks again sir! I now realized that if I will use '" & auditno & "' I an representing a string, right? but when I will not put single quotes like this " & auditno & " I am representing an integer? right? Thanks you again sir...

    iamjheyb

    Tuesday, November 6, 2012 4:48 AM
  • I now realized that if I will use '" & auditno & "' I an representing a string, right? but when I will not put single quotes like this " & auditno & " I am representing an integer? right?

    Yes, that is correct.

    --
    Andrew

    Tuesday, November 6, 2012 9:29 AM