Excuse me if I am being dense, but I don’t see how your code above which is setting up a prepared SQL statement is doing anything about updating the listbox.
I see your code as code to update your database. So you are updating the table Snippets with a couple of text fields and using the listbox cell to be the search criteria.
I don’t see anything there that is doing any updating back to the listbox. Perhaps you included the wrong code?
Jon,
I omitted the code which actually updates the listbox, as I believed the code above was the problem.
Here is the complete code:
[code]// PROCEED IF BOTH TEXT FIELDS HAVE DATA
If TitleField.text <> “” And CodeField.text <> “” Then
// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare("UPDATE Snippets SET Title=?, Code=?, WHERE Title=?")
// SET THE BIND TYPES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
// SET THE BIND VALUES
ps.Bind(0, TitleField.text)
ps.Bind(1, CodeField.text)
ps.Bind(2, MainWindow.Listbox1.cell(MainWindow.Listbox1.listindex,0))
// INSERT THE RESULTS INTO THE RECORDSET
Dim rs As RecordSet = ps.SQLSelect
// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
Else
// CLOSE THE EDIT WINDOW
EditSnippetWindow.close
// CLEAR THE LISTBOX
MainWindow.Listbox1.deleteAllRows
// RE-POPULATE THE LISTBOX WITH ALL ENTRIES
while not rs.eof
MainWindow.Listbox1.addRow rs.Field("Title").StringValue
rs.moveNext
wend
end If
else
// DISPLAY ERROR MESSAGEBOX
MsgBox(“Please ensure both fields are completed, and then retry!”)
You are deleting all the rows in your listbox. Then you have your select statement which you have created and you are basically inserting the same record set every time. You may have created multiple entries in the database but it looks like they’ll all be the same. From what I can see, I don’t see how records in the database are not all the same. Why bind the specific contents of the listbox cell into the select statement when you later delete that row anyhow? There’s definitely an issue there. I would need to play with it a bit to see exactly what and describe it better.
Why delete all the rows in the listbox? Why not just simply update the active cell with the text returned from the edit window that pops up?
[code]// PROCEED IF BOTH TEXT FIELDS HAVE DATA
If TitleField.text <> “” And CodeField.text <> “” Then
// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare("UPDATE Snippets SET Title=?, Code=?, WHERE Title=?")
// SET THE BIND TYPES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
// SET THE BIND VALUES
ps.Bind(0, TitleField.text)
ps.Bind(1, CodeField.text)
ps.Bind(2, MainWindow.Listbox1.cell(MainWindow.Listbox1.listindex,0))
// INSERT THE RESULTS INTO THE RECORDSET
ps.SQLExecute
// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
Else
// CLOSE THE EDIT WINDOW
EditSnippetWindow.close
else
// DISPLAY ERROR MESSAGEBOX
MsgBox(“Please ensure both fields are completed, and then retry!”)
[quote=93084:@Richard Summers]Think I have just realised my mistake:
I am missing the code:
ps.SQLExecute
Which will update the row.
I then intended to delete all rows and then re-populate the listbox with all the entries.
Yes - all the rows become the same :([/quote]
OK. Yes, you were missing the execute command.
But your select statement has not changed. That’s the problem. You select statement is always going to return the value where title is equal to the listbox cell you had selected and changed.
You need to do a new select statement where you don’t filter it by title. Then you should get all the records and can do the update correctly.
I still question why you delete and reload all the rows instead of just returning the edited text to the listbox cell.
And actually, now that I am digesting it more, you are using a prepared statement which is a SQL SET statement and then trying to do a Select statement on that same prepared statement. I’m surprised you are not getting some errors there.
Dim ps As SQLitePreparedStatement = db.Prepare("UPDATE Snippets SET Title=?, Code=?, WHERE Title=?")
//SNIP
// INSERT THE RESULTS INTO THE RECORDSET
Dim rs As RecordSet = ps.SQLSelect
So you have an update statement that is made to update the table in the db once you do the execute command. Then you try to run a select command on that pre parted statement. But the prepared statement is NOT a select statement. That’s an issue as well.
[code]// PROCEED IF BOTH TEXT FIELDS HAVE DATA
If TitleField.text <> “” And CodeField.text <> “” Then
// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare("UPDATE Snippets SET Title=?, Code=?, WHERE Title=?")
// SET THE BIND TYPES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
// SET THE BIND VALUES
ps.Bind(0, TitleField.text)
ps.Bind(1, CodeField.text)
ps.Bind(2, MainWindow.Listbox1.cell(MainWindow.Listbox1.listindex,0))
// PERFORM THE INSERTION
ps.SQLExecute
// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
Else
// CLOSE THE EDIT WINDOW
EditSnippetWindow.close
end if
else
// DISPLAY ERROR MESSAGEBOX
MsgBox(“Please ensure both fields are completed, and then retry!”)
[quote=93096:@Richard Summers]This is the line of code (in a timer) which updates all records into the listbox:
PopulateSnippets(Listbox1,db,"select SRef, Title, Code from Snippets order by Title Asc")
OK. It’s hard to see it with pieces of your code. Previously, you were not creating a new select statement. This statement looks like it’s correct.
One thing you might want to do is get a dB editor like Valentina Studio (it’s free and what I use) and play around with executing the statements in the dB editor. Make sure the tables update the way you want them. Then translate all that into the statements in Xojo.
Also, after you write to the db after the edits, check in the editor if the write occurred properly and you are getting what you want.
Arrrrrrrrrrgh
UNABLE TO PREPARE STATEMENT error???
[code]// PROCEED IF BOTH TEXT FIELDS HAVE DATA
If TitleField.text <> “” And CodeField.text <> “” Then
// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare("UPDATE Snippets SET Title=?, Code=?, WHERE Title=?")
// SET THE BIND TYPES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
// SET THE BIND VALUES
ps.Bind(0, TitleField.text)
ps.Bind(1, CodeField.text)
ps.Bind(2, MainWindow.Listbox1.cell(MainWindow.Listbox1.listindex,0))
// INSERT THE UPDATED VALUES
ps.SQLExecute
// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
Else
// CLOSE THE EDIT WINDOW
EditSnippetWindow.close
// UPDATE THE LISTBOX
dim sql as String = "SELECT SRef, Title, Code FROM Snippets order by Title Asc"
dim rs as RecordSet = db.SQLSelect(sql)
if rs <> Nil then
// EMPTY LISTBOX ROWS
MainWindow.Listbox1.deleteAllRows
// LOOP THROUGH RESULTS AND POPULATE LISTBOX
while not rs.EOF
MainWindow.Listbox1.AddRow(rs.Field("Title").StringValue)
rs.MoveNext
wend
// CLOSE THE RECORDSET
rs.Close
else
// DISPLAY ERROR MESSAGE
MsgBox("No records found!")
end if
end if
else
// DISPLAY ERROR MESSAGEBOX
MsgBox(“Please ensure both fields are completed, and then retry!”)
End if[/code]
Thank you so much for your help and patience - it is much appreciated !
You need to take out the comma before the WHERE in your prepared statement.[/quote]
Damn! It’s that simple. You are right, Wayne. I totally missed that… I’m surprised the statement even works at all then.
Thanks to your help now and a few days ago - I finally think I understand using prepared statements with databases.
It was a long, frustrating, learning process, but I can now successfully Add, Edit, Delete, Search and Update databases using prepared statements.
