Unable To Prepare Statement - code review.

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.

[code]// --------------------------------------- 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[/code]

Remove the “,” after NoteName=?
That might be it :slight_smile:

Nope - that made no difference ;(

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

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

Yes - NoteSelected is a string.

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.

No change - still got the same error message.

What’s the ErrorCode?

Error 0: Unable to Prepare Statement

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.

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

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 :slight_smile:

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

What happens with:

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

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

No idea :confused:
But you really need a PreparedStatement as the user controls the input. We’ll figure it out! :smiley:

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

At least we now know there’s something vajsing with the binding :slight_smile:

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 :frowning:
There is definitely something VAJSING with the bindings :slight_smile:

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