Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    21

    Unanswered: An Update statement won't update records for a table

    I am trying to update a record. The update statement runs without any errors but no record is updated although the autonumber key passed in exists in the database
    I have updated other table using the same connection, and same method without any problems.
    This errors only happens to 2 tables.
    I'm, at a loss as to why this happens

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pt2004
    This errors only happens to 2 tables.
    I'm, at a loss as to why this happens
    so are we, without seeing the sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    21
    Quote Originally Posted by r937
    so are we, without seeing the sql
    Here's the sql statement. I dont get any error with this though. The update runs but the ExecuteNonQuery() function returns 0 showing that it doesn't update any rows

    ----------------------------------------------------------------------
    string stringSQL = "UPDATE stats SET grade1=@grade1, grade2=@grade2, conditions=@conditions WHERE statsId=@statsId";

    cCommand.Parameters.Add("@grade1", OleDbType.Numeric).Value = grade1;
    cCommand.Parameters.Add("@grade2", OleDbType.Numeric).Value = grade2;
    cCommand.Parameters.Add("@conditions", OleDbType.VarChar).Value = Conditions;
    cCommand.Parameters.Add("@statsId", OleDbType.Numeric).Value = statsId;

  4. #4
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36

    Question GUID or long integer?

    Is your autonumber record ID (@StatsID) in long integer or GUID format? If it is GUID, check your syntax when filling in the variable @StatsID -- Access and especially VBA in Access has tremendous problems understanding GUIDs. I've actually given up a number of times on using GUIDs and reworked everyting with long integers instead because of the same stuff -- VBA either gives out error messages or does not find the necessary records (even though everything was done according to MSDN white papers). Seems very similar to the problems I've come across.
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  5. #5
    Join Date
    Mar 2004
    Posts
    21
    Quote Originally Posted by Virgo-Libra
    Is your autonumber record ID (@StatsID) in long integer or GUID format? If it is GUID, check your syntax when filling in the variable @StatsID -- Access and especially VBA in Access has tremendous problems understanding GUIDs. I've actually given up a number of times on using GUIDs and reworked everyting with long integers instead because of the same stuff -- VBA either gives out error messages or does not find the necessary records (even though everything was done according to MSDN white papers). Seems very similar to the problems I've come across.
    I was using an int.

    public int StatsId;

    I tryed changing it a long integer but the update still doesn't work.

  6. #6
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36
    Strange. It should normally work fine with integer AutoNumber IDs. Did you try running the code step-by-step, with monitoring watches on all variables, especially the StatsID? Most likely the WHERE criteria is wrong, since there are no errors but no update occurs... Can you also post the setup of the table on which it is happening?
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  7. #7
    Join Date
    Mar 2004
    Posts
    21
    Quote Originally Posted by Virgo-Libra
    Strange. It should normally work fine with integer AutoNumber IDs. Did you try running the code step-by-step, with monitoring watches on all variables, especially the StatsID? Most likely the WHERE criteria is wrong, since there are no errors but no update occurs... Can you also post the setup of the table on which it is happening?
    I've already run through the code several times. The WHERE is passing in the right ID.

    ------------Table Setup ----------------------
    statsID autunumber
    grade1 number
    grade2 number
    conditions text

Posting Permissions

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