Unable To Prepare Statement - code review.

  1. ‹ Older
  2. 3 years ago

    Hamish S

    22 Aug 2014 Europe (Cambridge, UK)

    Is NoteSelected a string? If not, that may be your problem.

  3. Yes - NoteSelected is a string.

  4. Hamish S

    22 Aug 2014 Europe (Cambridge, UK)

    Try

    Dim ps As SQLitePreparedStatement
    ps = SQLitePreparedStatement(NotesDB.Prepare("UPDATE Notes SET NoteName='something' WHERE NoteName='something else'"))
    ps.SQLExecute

    - i.e. take out the parameters, and see if that works; if not, it's a problem in the query.

  5. No change - still got the same error message.

  6. Albin K

    22 Aug 2014 Europe (Sweden, Jönköping)

    What's the ErrorCode?

  7. Error 0: Unable to Prepare Statement

  8. Hamish S

    22 Aug 2014 Europe (Cambridge, UK)

    OK, then it's nothing to do with binding.
    Try executing that like this:

    call NotesDB.SQLExecute("UPDATE Notes SET NoteName='something' WHERE NoteName='something else'")

    If that fails, inspect the Error and ErrorCode properties.

  9. Hmmm....it is saying cannot be performed because the database is closed.
    I will need to go back and check the connection.

  10. Ok.
    I had a fiddle with my code regarding the connection.

    When I use this:

    call NotesDB.SQLExecute("UPDATE Notes SET NoteName='something' WHERE NoteName='something else'")

    Nothing happens as expected (due to no columns containing a value of "something else").
    At least this works and I get no errors - so I know my connection is fine :)

    However, when I adapt that line of code to this:

    call NotesDB.SQLExecute("UPDATE Notes SET NoteName='something' WHERE NoteName=NoteSelected")

    I get an error saying: Error: 1 - no such column: NoteSelected

    When I use my original code again:

    Dim ps As SQLitePreparedStatement = NotesDB.Prepare("UPDATE Notes SET NoteName=?, WHERE NoteName=?")
    ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
    ps.Bind(0, ChangeNoteNameWindow.NameField.text)
    ps.Bind(1, NoteSelected)
    ps.SQLExecute

    I get the error: Error: 1 - Unable to prepare statement

  11. Albin K

    22 Aug 2014 Europe (Sweden, Jönköping)

    What happens with:

    call NotesDB.SQLExecute("UPDATE Notes SET NoteName='something' WHERE NoteName='" + NoteSelected + "';")
  12. Edited 3 years ago

    Albin - when I use that, it works correctly :) :) :)
    Any idea why that line of code works, but my prepared statement fails (they are both using the NoteSelected property value)?

  13. Albin K

    22 Aug 2014 Europe (Sweden, Jönköping)

    No idea :/
    But you really need a PreparedStatement as the user controls the input. We'll figure it out! :D

  14. Albin K

    22 Aug 2014 Europe (Sweden, Jönköping)

    This is just a shot in the dark..

    Dim ps As SQLitePreparedStatement
    ps = SQLitePreparedStatement(NotesDB.Prepare("UPDATE Notes SET NoteName='?' WHERE NoteName='?'"))
    ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
    ps.Bind(0, ChangeNoteNameWindow.NameField.text)
    ps.Bind(1, NoteSelected)
    ps.SQLExecute
  15. Albin K

    22 Aug 2014 Europe (Sweden, Jönköping)

    At least we now know there's something vajsing with the binding :)

  16. Edited 3 years ago

    Basically - this works:

    call NotesDB.SQLExecute("UPDATE Notes SET NoteName='" + ChangeNoteNameWindow.NameField.text + "' WHERE NoteName='" + NoteSelected + "';")

    But this doesn't:

    Dim ps As SQLitePreparedStatement = NotesDB.Prepare("UPDATE Notes SET NoteName=?, WHERE NoteName=?")
    ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
    ps.Bind(0, ChangeNoteNameWindow.NameField.text)
    ps.Bind(1, NoteSelected)
    ps.SQLExecute

    Your new prepared statement produced no error message - but also changed nothing :(
    There is definitely something VAJSING with the bindings :)

  17. Don L

    22 Aug 2014 Pre-Release Testers, Xojo Pro Answer Florence, South Carolina USA

    @Richard S Dim ps As SQLitePreparedStatement = NotesDB.Prepare("UPDATE Notes SET NoteName=?, WHERE NoteName=?")

    Richard, I still think that comma after "SET NoteName=?," before the "WHERE" needs to be removed. That had caused a problem for me recently.

  18. Weird - I removed the comma (which I had already tried) and now it works :)

    Oh well - problem solved.
    The code below now works, if anyone else is having a similar problem:

    Dim ps As SQLitePreparedStatement = NotesDB.Prepare("UPDATE Notes SET NoteName=? WHERE NoteName=?")
    ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
    ps.Bind(1, NoteSelected)
    ps.SQLExecute

    Thank you all for your help - it was much appreciated :)

  19. Don L

    22 Aug 2014 Pre-Release Testers, Xojo Pro Florence, South Carolina USA

    @Richard S Thank you all for your help - it was much appreciated :)

    That's the beauty of this forum ... it was Patrick Delaney who straightened me out last week on that problem with the extra comma (and yes, I had pulled a few hairs out wondering what in hell's bells was going on also ^^) ... so, I'm throwing him a "shout out" here for both of us!

  20. last week

or Sign Up to reply!