Unable To Prepare Statement - code review.

  1. 3 years ago

    Hi,
    Could anyone advise me why I am getting an Unable To Prepare Statement error?

    When I select a listbox row - the content (name of the note) gets set into a property called NoteSelected.

    When I double click - I am presented with a window where a NEW note name can be set. When the proceed button is clicked - the code below SHOULD rename all column values which have the same value as NoteSelected - to the text contained in NameField.text.

    // --------------------------------------- DATABASE CONNECTED, SO UPDATE ALL ROWS WHICH CONTAIN THE ORIGINAL NAME
        
    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

    @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.

  2. Albin K

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

    Remove the "," after NoteName=?
    That might be it :)

  3. Nope - that made no difference ;(

  4. Albin K

    22 Aug 2014 Europe (Sweden, Jönköping)
    Edited 3 years ago

    Hmm, I would write it like this but I don't think it makes a differens thought.

    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
  5. Hamish S

    22 Aug 2014 Europe (Cambridge, UK)

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

  6. Yes - NoteSelected is a string.

  7. 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.

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

  9. Albin K

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

    What's the ErrorCode?

  10. Error 0: Unable to Prepare Statement

  11. 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.

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

  13. 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

  14. Albin K

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

    What happens with:

    call NotesDB.SQLExecute("UPDATE Notes SET NoteName='something' WHERE NoteName='" + NoteSelected + "';")
  15. 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)?

  16. 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

  17. 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
  18. Albin K

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

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

  19. 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 :)

  20. Newer ›

or Sign Up to reply!