Results 1 to 6 of 6
  1. #1
    Join Date
    May 2013
    Posts
    9

    Unanswered: Prepared Statement - works for SELECT not for UPDATE

    I'm connecting to an Access database via ODBC connection. My frontend code is Real Studio (soon to be Xojo).

    I'm successfully using a Prepared Statement for the SQL SELECT statement. I'm having all sorts of problems trying to use a Prepared Statement for the SQL UPDATE statement. I'm using the ? as the place holder (works for the SELECT statement) and the UPDATE statement. On executing the UPDATE statement I'm not receiving any database error messages and it appears the update was completed. On checking the table it's not updated. I've checked and double checked that my data correctly matches up with the ? placeholders.

    I've posted a thread over at the Real Studio forum regarding this issue though Access is not their primary concern and have only had minimal feedback. That particular thread includes a copy of my code constructing the Prepared Statement. As I mentioned, the SELECT Prepared Statement works, the UPDATE doesn't.

    Are there any issues with Prepared Statements and Access? Any suggestions, clues or hints are most welcome.

    ps. Access Database is 2003 version, mdb files.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the SQL expression of the query?
    Have a nice day!

  3. #3
    Join Date
    May 2013
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    What's the SQL expression of the query?
    Here is my SQL expression:

    UPDATE tbl_Contacts SET DateOfBirth = ? , Gender = ? , Middle_Name = ? , First_Name = ? , Last_Name = ? , Details = ? , IsCriminal = ? WHERE tbl_Contacts.Contact_ID = ?;

    The values that get inserted into this prepared statement are:

    BindParameters: i = 0 var_Value = Null
    BindParameters: i = 1 var_Value = F
    BindParameters: i = 2 var_Value = Jill
    BindParameters: i = 3 var_Value = Jenny
    BindParameters: i = 4 var_Value = SMITH
    BindParameters: i = 5 var_Value = Some stuff "goes" in here. ''But'' whats about this which is enclosed in double quote''s.

    BindParameters: i = 6 var_Value = True
    BindParameters: i = 7 5


    The above values displayed are from my debugging session. The actual values are those at right of list, starting with Null.

    Hope this makes sense?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't know how Real Studio works but, except if it's performing a last-minute conversion (immediately before submitting the query), it seems to me that the prepared statement should be something like:
    Code:
    BindParameters: i = 0 var_Value = Null
    BindParameters: i = 1 var_Value = 'F'
    BindParameters: i = 2 var_Value = 'Jill'
    BindParameters: i = 3 var_Value = 'Jenny'
    BindParameters: i = 4 var_Value = 'SMITH'
    Have a nice day!

  5. #5
    Join Date
    May 2013
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    I don't know how Real Studio works but, except if it's performing a last-minute conversion (immediately before submitting the query), it seems to me that the prepared statement should be something like:
    Code:
    BindParameters: i = 0 var_Value = Null
    BindParameters: i = 1 var_Value = 'F'
    BindParameters: i = 2 var_Value = 'Jill'
    BindParameters: i = 3 var_Value = 'Jenny'
    BindParameters: i = 4 var_Value = 'SMITH'

    Thanks Sinndho, on seeing your response I had that sinking (but relieved) feeling that I forgot something so simple. Alas, this didn't fix the problem.

    In further testing I discovered if I purposely use incorrect field names that a database error is not thrown. I'm assuming that the prepared statement is not actually hitting the Access database so something is amiss either at the ODBC driver or client code (Real Studio).

    Thank you for assisting, very much appreciated.

    Steve.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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