Hello,
I have a ListBox with three columns the first of which is a checkbox.
Since the ListBox pours into a SQLite Database, how do I send the checkbox status to the SQLite so that it can be retrieved into the ListBox once I start the app again?
Thank you
you can use the .EditRow from RowSet
or use a sql
update tablexy set field = true/false/1/0 where id=
and store the rowid of each record in the rowtag of the listbox
I’m sorry. I tried all the combinations I could think of but with no results.
Say you have a “Bing.sqlite” and a ListBox with a column named “Bong” being a checkbox.
The db is connected and responding.
Could you give some sample lines of code on how to save and later retrieve the checkbox status?
Maybe because I’m still fresh at SQLite, but, for the life of me, I cannot get it.
Thanks
You must show us your code. Even better, make a simple example. At the very least show us your SQL.
Example SQL from my app:
try
dim thePreparedStatement as SQLitePreparedStatement = PlanDataDB.Prepare("UPDATE AccountsForPlans SET Active = ? WHERE PlanID = ? AND AccountID = ?")
thePreparedStatement.BindType(0, SQLitePreparedStatement.SQLITE_BOOLEAN)
thePreparedStatement.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
thePreparedStatement.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
thePreparedStatement.ExecuteSQL(isActive, PlanID, AccountID)
catch err as DatabaseException
theResult.Value("error") = PlanDataErrors.kErrorSavePlan + " " + PlanDataErrors.kError + Err.Message
Return theResult
end try
Here’s the link to my App: Smash
“If are wondering, I named it Eisenhower because it is a method he used to keep track of things to be done”
I’m not really good with Italian. But I think that the problem is here:
for i=0 to ToDoWin.ImpUrg.LastAddedRowIndex
DB.ExecuteSQL( bql, ToDoWin.ImpUrg.CellTextAt(i, 0), ToDoWin.ImpUrg.CellTextAt(i, 1), ToDoWin.ImpUrg.CellTextAt(i, 2))
next
You need CellCheckBoxValueAt . See https://documentation.xojo.com/api/deprecated/listbox.html#listbox-cellcheckboxvalueat.
Please consider better names for your tables and fields instead of for instance NimpUrg.
ToDoWin.ImpUrg.LastAddedRowIndex
i think you will save all checkboxes
ToDoWin.ImpUrg.LastRowIndex
Thanks Beatrix. I made it and you’re right, I should choose better names.
Now I have the reverse problem: when it downloads back in the ListBox it puts True or False as text right next the checkbox but not the tick in it
CellCheckBoxValueAt is used as get and set
maybe you just add columns as string.
As usual it helps to see your code for doing this. Are you storing TRUE/FALSE in the database as strings? Better to store them as 0 and 1.
Saving goes like this:
"sql = “DELETE FROM ImpUrg” // Comando Sql per eliminare tutte le righe salvate nel database Sql
Try
DB.ExecuteSQL(sql) //Esegue il comando DELETE
Catch err As DatabaseException
MessageDialog.Show("Error deleting the file from the database: " + err.Message)
Return
End Try
try
for i=0 to ToDoWin.ImpUrg.LastAddedRowIndex
'check=ToDoWin.ImpUrg.CellCheckBoxValueAt(i, 0)
DB.ExecuteSQL( bql, ToDoWin.ImpUrg.CellCheckBoxValueAt(i, 0), ToDoWin.ImpUrg.CellTextAt(i, 1), ToDoWin.ImpUrg.CellTextAt(i, 2))
next
Catch error As DatabaseException
ok=False
MessageBox("Error: " + error.Message)
end try
if ok then
DB.commit //solo ora salvi tutto
else
DB.rollback // non
end if"
Column 0 is a Boolena in the SQLite DB
It retrieves the data using the following code:
"ToDoWin.ImpUrg.RemoveAllRows //Vengono eliminate tutte le righe eventualmente presenti nella ListBox
bql = “SELECT * FROM ImpUrg ORDER BY Scad”
eisen = DB.sqlSelect(bql)
If DBError Then Return
// Fetch each visite from the RecordSet and add it
// to the ListBox.
If eisen <> Nil Then
While Not eisen.EOF
ToDoWin.ImpUrg.AddRow(eisen.Field("ch").StringValue, _
eisen.Field("Comp").StringValue, _
eisen.Field("Scad").StringValue)
eisen.MoveNext
Wend
eisen.Close
End If"
If I state ToDoWin.ImpUrg.AddRow(eisen.Field(“ch”).StringValue as Boolean instead of StringValue I get an error.
I’m posting a screenshot of what comes out when you retrieve data: basically, I do not know how to set the chekbox instead of having “false” written besides it
Store the Boolean value in a variable, then in your Write (and Read) SQL, use that variable.
ToDoWin.ImpUrg.AddRow(eisen.Field("ch").StringValue
Thank you but how do I physically get the eisen.Field(“ch”).StringValue above into a Boolean variable, say called check, and do it for every row that gets populated in the ListBox?
I’m sorry but I’m still very “green” in ListBoxes and SQLite
You haven’t started a transaction so there will be no transaction to commit.
DB.ExecuteSQL( bql, ToDoWin.ImpUrg.CellCheckBoxValueAt(i, 0), ToDoWin.ImpUrg.CellTextAt(i, 1), ToDoWin.ImpUrg.CellTextAt(i, 2))
bql doesn’t appear to be defined before you use it.
Also I would convert the state of the checkmark to an integer (0, 1) and store that.
myInt = if (ToDoWin.ImpUrg.CellCheckBoxValueAt(i, 0), 1, 0)
In your project, the line above do not autocomplete (here); I presume that eisen was not properly declared.
I wanted to change it to:
http://documentation.xojo.com/api/databases/databasecolumn.html#databasecolumn-booleanvalue
It’s that I did not show the whole statement, just the part that it’s supposed to handle a Boolean value and, consequently, put a tick in the checkbox instead of writing true next to it and not touching the checkbox itself
Also, RecordSet is deprecated with Xojo 2021r3.1, use RowSet.
Dim eisen As RecordSet // Use RowSet
RowSet possible values:
Look at the RowSet example in the Docs.
I understand. Unfortunately, I have hard times reading today (my old eye is tired today).
What I wanted to say is to store the values from the database into local variables and add that into a new ListBox’ Row.
something like
Var loc_ch As Boolean
Var loc_Comp As Text
Var loc_Scad As Text
Then read each Row and place the retur,ed values (Boolean and Text) into these local variables.
Then
ToDoWin.ImpUrg.AddRow loc_ch, loc_Comp, loc_Scad
using proper Xojo 2021r3.1 syntax (code above written in Firefox).
At debug time, you will be able to check what is in these variables so you’ll be sure you get the correct values from the db.
The example @ RowSet use Try…
The provided database (eisen.sqlite) hold 4 EMPTY tables.
I hope this helps.
