Syntax error?

Databases
63

Yes.

64

weeping uncontrollably

This code populates the listbox:

// POPULATE LISTBOX2 while not rs.eof Listbox2.AddRow Listbox2.Cell(Listbox2.LastIndex, 1) = rs.Field("Hex").StringValue Listbox2.Cell(Listbox2.LastIndex, 2) = rs.Field("Rgb").StringValue Listbox2.RowTag(Listbox2.LastIndex) = rs.Field("id").IntegerValue rs.moveNext wend

And my prepared statement now looks like this:

[code]// LOOP THROUGH THE LISTBOX ROWS
For i As integer = 0 to Listbox2.listcount - 1

// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db2.Prepare("UPDATE Colours SET(Position) WHERE (?) VALUES (?);")

// BIND THE VALUE
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, ListBox2.RowTag(i))
ps.Bind(1, i)[/code]
65

[quote=104004:@Tim Hare]Write it out as regular SQL first.

UPDATE Colours SET Position = 1 WHERE ID = 5

Then change the literal values to ‘?’

db2.Prepare(“UPDATE Colours SET Position = ? WHERE ID = ?”)[/quote]
And you’ve got the bind statements reversed.

ps.Bind(0, i)
ps.Bind(1, ListBox2.RowTag(i))
66

I give up.
I am now actually starting to feel ill as I am sooooooo angry with myself.

I have tried 200 combinations and I cannot get the syntax right for the prepared statement.

I have tried this:

db2.Prepare("UPDATE Colours SET (Position) WHERE (id) VALUES (?,?);")

This:

db2.Prepare("UPDATE Colours SET (Position, id) WHERE VALUES (?,?);")

This:

db2.Prepare("UPDATE Colours SET (Position=, id=) WHERE VALUES (?,?);")

This:

db2.Prepare("UPDATE Colours SET (Position=) WHERE (id=) VALUES (?,?);")

This:

db2.Prepare("UPDATE Colours SET Position= WHERE id= VALUES (?,?);")

And untold other combinations.

Nothing is making sense anymore.
The more I look at it - the less it makes sense :frowning:

Thanks for trying to help - I really appreciate it.
I will have to stick with deleting all entries and then re-inserting them all.

67

Richard

The sql syntax and prepared statement will be:

db2.Prepare("UPDATE Colours SET Position = ? WHERE id = ?;")

and the bindings:

// BIND THE VALUE ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER) ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER) ps.Bind(1, ListBox2.RowTag(i)) ps.Bind(0, i)
Note that you had the bindings the wrong way around.

68

OH WOW - I must be more tired than I realised.
I can’t believe Tim had actually written the answer in his earlier post and I completely misread it.

I am now giving up for the night, as my brain must have completely switched off !

Simon:
Thank you so much for helping.

Tim:
Thank you very much for giving me the answer earlier.
It must have been annoying for you to give me the answer, and then have me still go on about how I cannot get the syntax right.

Therefore, I am now going to watch an old horror movie and get away from this for a while.

THANK YOU BOTH VERY MUCH!

69

No worries. Get a good night’s rest.

70

You gotta know when to hang up the gloves for the day. The next day will usually start with a loud DOH!

71

I usually sit here from around 11am until 4 am the next morning (17 hrs).
I guess that is too much learning for 1 day ?