The sqlite_sequence table is created by sqlite to keep track of your autoincrement numbers for each table that uses one. That is normal.
Your prepare statement is referencing “db” not “db2”.
I suggest you rename your databases to avoid this recurring confusion.
Arghhhhhhhhhhh - the dreaded db2 !!!
Can’t believe I fell for that same mistake AGAIN!
Thanks Peter 
Corrected that, but now when I drag a row to another position, the CancelClose event causes all of the columns to change to the same colour??
Ahhh - think I have found the problem - the values of bind(0) and bind(1) may need to be changed.
hmmmm - changed them and still the same problem??
Check your cellbackgroundpaint event
I looked at that, but can’t work out what is wrong with it?
After ALL the database entries have been deleted - my bind statement looks like this:
[code]// LOOP THROUGH THE LISTBOX
For i As integer = 0 to Listbox2.listcount - 1
// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db2.Prepare(“INSERT INTO Colours (Hex, Rgb, Position) VALUES (?, ?, ?);”)
// BIND THE TEXT FIELD VALUES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, Listbox2.cell(Listbox2.ListIndex,1))
ps.Bind(1, Listbox2.cell(Listbox2.ListIndex,2))
ps.Bind(2, i)[/code]
And my CellBackgroundPaint look likes this:
// COLOURISE COLUMN 0 - CORRESPONDING TO THE Hex COLUMN VALUE (column1)
dim v as variant
if column = 0 then
v = me.cell(row,1)
g.ForeColor = v.ColorValue
g.fillrect(0,0, g.Width, g.Height)
end if
Instead of deleting all database entries and then re-inserting them, couldn’t I just do an update, like this:
[code]// LOOP THROUGH THE LISTBOX
For i As integer = 0 to Listbox2.listcount - 1
// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db2.Prepare(“UPDATE Colours SET Position=? WHERE Hex=?”)
// BIND THE TEXT FIELD VALUES
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, i)
ps.Bind(1, Listbox2.cell(Listbox2.ListIndex,1))
// UPDATE THE VALUES IN THE DATABASE
ps.SQLExecute[/code]
See the bold text below: This will give the same value to each record because the listbox listindex doesn’t change in a loop unless you tell it to.
The “i” loop variable will hold the row number being processed.
// BIND THE TEXT FIELD VALUES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, Listbox2.cell(Listbox2.ListIndex,1))
ps.Bind(1, Listbox2.cell(Listbox2.ListIndex,2))
ps.Bind(2, i)
Change to:
// BIND THE TEXT FIELD VALUES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, Listbox2.cell(i,1))
ps.Bind(1, Listbox2.cell(i,2))
ps.Bind(2, i)
The listindex is the selected row (0 or greater) or indicates no row selected (-1)
If multiple rows are selected the listindex will be the lowest selected row.
I just looked back at your loop before. You were using
ps.Bind(0, FinalHexadecimal)
ps.Bind(1, FinalRgb)
These would never change as they are not calculated within the loop for each row.
Peter - Yes, I noticed that too and changed them to Listbox2.cell(Listbox2.ListIndex,1)).
I have just changed them to what you advised and it is now working as expected
Just out of curiosity - would doing an UPDATE instead of deleting all the entries and then re-creating them be a better option, or is there a reason why deleting and re-creating is better?
Also, what about the 2 lines below - are they both valid statements?:
db2.rollback // SHOULDNT THIS LINE BE............. db2.SQLExecute "ROLLBACK"
db2.commit // SHOULDNT THIS LINE BE............. db2.SQLExecute "COMMIT"
Thanks.
If all the records in your listbox are in the database i.e. you add them to the database as you add them to the listbox then that is fine. If they don’t exist in the database then there will be nothing to update.
I don’t recall if you save the records as you add them or when you close the window.
I save each record to the database as I add them.
The window CancelClose deletes them all, and then re-adds them.
Hi,
After my listbox rows have been re-ordered, I need to update each POSITION column, to it’s corresponding current row number.
I am a bit lost how to write the prepare statement?? I know the code is wrong, but I can’t work out how to write the WHERE part of the prepare statement???
Can someone please help me 
[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 (what the hell goes here) VALUES (?);")
// BIND THE VALUE
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, i)[/code]Didn’t we go through this in the other thread?
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 = ?”)
Tim,
first of all - thanks - I knew I had posted about this problem, but couldn’t seem to find it.
I have recently learnt how to write the prepared statements correctly, but the problem I have here, is I can’t even write it out as regular SQL, because I have no idea what to set the position to, and also what to set the where to??
Because I am not setting them both to something hard-coded (such as textfields etc.), I am lost as what to put there?
I need the top row to have it’s position column updated to 1, the second row’s position column set to 2 etc. etc.
I have spent hours staring at it - but my brain refuses to work out what I set the position and where clauses to
I presume I set the position column to i, but the where clause has me totally stumped.
Does that make any more sense?
Yes, and that was all covered in the other thread. The only thing left is to convert the update statements to prepared statements. Or am I missing something?
You are correct about i representing the position. I believe you were saving the ID in the listbox RowTag, so that would be RowTag(i).
Really sorry to be so stupid regarding this problem, but some things I seem to have a complete mental block on, and I am trying my hardest to get this nailed
I no longer have RowTag due to code modification, and therefore, cannot work out (even from the other post) what now goes in the where clause.
I am almost there.
I loop through each row, set the position to the value of i (which will increment each iteration), but the where clause is killing me
Are you saving the ID from each row somewhere in the listbox? Without it, it is impossible to accomplish what you want, which may explain why this seems so hard right now.
No - I’m not.
I guess that is why my brain is going round in circles and I am getting nowhere (apart from making myself frustrated and angry).
So I presume I need to re-insert the row tag and then use RowTag(i) ?