Listbox / database

BRIAN_CUNNINGHAM · March 4, 2014, 1:05am

Hello everyone, I’m trying to write a 3 column listbox to a database but I’m not sure where to begin. The listbox will be used for job notes consisting of a checkbox column, a notes column and a date column, in that order. I’m not even sure how I should set up the table. Any help would be appreciated.

Vince_Francia · March 4, 2014, 2:28am

Hey Brian,

Have you checked out the Language Reference?
Search ‘Listbox’
Then depending on your database if you are using sqlite then search for sqlite.

There are som basic examples to get you started.

Jason_King · March 4, 2014, 2:32am

Look up the SQLite webinar, it’s a good place to start and it has an example project with a listbox to show how the two can be used together.

Emile_Schwarz · March 4, 2014, 10:59am

Brian,

you have two tasks here:

Create and populate a ListBox (docs for ListBox.)
CheckBox Column (ListBox CheckBox) docs: How to write / read a checkbox Cell
Notes Column: I do not know if this is possible to write many lines in a single cell. ListBox.Cell
Dates Column: store the SQLDate (that is 2014-03-04) in the last Column Date and SQLDate.
You start adding a Row in a loop (Add a Row and populate it.
Read the ListBox and save its contents into a DataBase ( docs for SQLite)
(read one Row - its three cells and store these values ina SQLite data base for example).
You read the Cells one after the other in a Row scanning loop

Then, you store the data in a Record and write it into the data base.
(of course, you have to create a table, etc. All is in the Docs, but you have to read them first).

If you have troubles or need more info, just ask here.

BRIAN_CUNNINGHAM · March 4, 2014, 1:24pm

Thank you for your advice. I have been searching the docs and Google but simply have not yet found my answer, if so, I have not recognized it.

I must have been far too vague. I have a working app currently with 6 windows, multiple listbox’s and a main window containing 105 various controls. This app uses a schema with multiple tables. until now, I have not had a need to save the data from a listbox, only to populate a listbox from my db for selection.

This listbox, when opened, adds a row inserting a checkbox in the left column, a shortdate in the right column and upon selection, the middle column becomes editable. The user will have the ability to delete notes as they see fit and upon closing, it should save the current contents of the listbox which raises another question, once I figure out how I’m writing this to the db , with such a list, do I delete all contents of the table and insert the new data for this job number, which will be the id, or is there a way to simply update?

I will continue to read.

Louis_D · March 4, 2014, 7:11pm

Brian, you must write code that reads the desired listbox cell by cell and then populates an appropriate SQL statement that you then execute against your database. For example, once a row is read and then written to database, you move to the next row in the listbox and run through the method again.

This approach will work with any number of columns and any number of rows in your listbox. There are various ways you can do this: I am assuming that your code will read the cells and pass the contents as parameters to the method that then writes the data to the database. Of course, your method will need to be tailored to fit the structure of your table.

Now, when additions and deletions are involved, I like to use an underlying array that includes a field that I usually call Deletion_flag, and an other that I usually call Dirty: 1 = new record, 2 = changed record, 0 is the default set when I read the data from the database. When a record is deleted from the listbox, I have code that sets the deletion_flag to true. I now have a nice array that I can use to insert new records, update changed records and delete the ones that are marked deletion_flag-= true. You may wonder why a Dirty and a Deletion_flag? Because I actually have a Deletion_flag in the database record, but not a Dirty field. The Dirty field is only required within the program to know which method to use, but the deletion_flag is a nice way to “soft delete” a record.

Now, you are already imagining how you can use the deletion_flag to display valid-only records, deleted-only records, provide an undelete feature, etc. and you are correct. You do not absolutely have to physically delete records from your database. All you need is the right set of methods to read the data you want in the right context.

BRIAN_CUNNINGHAM · March 4, 2014, 7:24pm

Thank you Louis. I’ve written code that allows me to save the job notes as well as the cellstate of the listbox-checkbox which, for a beginner like myself, is a modestly huge accomplishment. My concern now is that the only way I was able to do this is to create a new table programmatically for each job when I save notes for the first time. is there a way for me to do this while using one table for all job notes? it seems that the end user could end up with thousands of tables in the db doing it my way.

I will certainly explore your options for deletion/updating but for now I need a stable yet sensible way to record these notes.

BRIAN_CUNNINGHAM · March 4, 2014, 8:11pm

OK, Now I am populating my listbox with saved job notes and all is well with the exception of the checkboxes I was so proud of in my previous posting. I’m able to record the CellState but unable to figure out how to affect the CellState with this information upon retrieval. I’m currently getting a 1 or 0 next to the checkbox in that column. Any solutions for this?

Emile_Schwarz · March 5, 2014, 8:59am

Syntax for CellCheck:

aListBox.CellCheck( Row as Integer, Column as Integer ) = newBooleanValue

0 and 1 can be replaced by False / True and so you will be able to set correctly the CellCheck CheckBox.

At read time, use something like (pseudo code):

If value = 0 then aListBox.CellCheck( Row, Column) = False Else aListBox.CellCheck( Row, Column) = True End If

Nota: value is the name of the variable you use to get the CheckBox Column contents.

Simple doc for boolean at www.sqlite.org.

BRIAN_CUNNINGHAM · March 5, 2014, 1:17pm

Thanks Emile, That was the conclusion I came up with. here is what I did. I also got rid of the 0 and 1.

if rs<>Nil Then
While Not rs.EOF
NotesListbox.AddRow(rs.Field(“ok”).Value, _
rs.Field(“notes”).StringValue, _
rs.Field(“date”).StringValue)
NotesListbox.RowTag(NotesListbox.LastIndex)=rs.Field(“x”).IntegerValue
if rs.Field(“OK”).Value=1 Then
NotesListbox.CellCheck(NotesListbox.LastIndex,0)=True
end if
NotesListbox.Cell(NotesListbox.LastIndex,0)=""
rs.MoveNext
Wend
rs.Close
Convtblname=""
end if

Emile_Schwarz · March 6, 2014, 8:47am

Brian, your code looks good (for my eyes, one hour after Im awaken). Sorry, look far below, I saw something

On the other hand, you can use a better way for NotesListbox.LastIndex

What I usually do is to use a local variable to hold that value:

[code]Dim LocRow As Integer

NotesListbox.AddRow “bla blah”
LocRow = NotesListbox.LastIndex

// Then, below, I use LocRow:
NotesListbox.Cell(LocRow,0) = “”[/code]

Oh Oh ! I think there is something wrong here:

if rs.Field("OK").Value=1 Then NotesListbox.CellCheck(NotesListbox.LastIndex,0)=True end if NotesListbox.Cell(NotesListbox.LastIndex,0)=""

You store in CellCheck True and the line after, in the same Cell, you store a Nil string ? ("")

Did I really read that ?

BRIAN_CUNNINGHAM · March 6, 2014, 12:05pm

Good morning Emile, it’s early for me here to, 4 am, I’ll try to make sense. Yes you are correct, I put the string value of the rs.field(“ok”) in the first column so that the following two rs values will land in their respective columns, then I apply the value of the rs.field(“ok”) to the checkbox and remove the 1 or 0 which appears in the column next to the checkbox. I think this would have been a little less complicated had the checkbox been in the last column rather than the first. It is all working great as it is but if there is a way to streamline, I welcome the advice.

Thank you for the tip on the local variable. I can think of many places where I can reduce my code by doing that.

Simon_Berridge · March 6, 2014, 2:43pm

[quote=69435:@BRIAN CUNNINGHAM]Thanks Emile, That was the conclusion I came up with. here is what I did. I also got rid of the 0 and 1.

if rs<>Nil Then
While Not rs.EOF
NotesListbox.AddRow(rs.Field(“ok”).Value, _
rs.Field(“notes”).StringValue, _
rs.Field(“date”).StringValue)
NotesListbox.RowTag(NotesListbox.LastIndex)=rs.Field(“x”).IntegerValue
if rs.Field(“OK”).Value=1 Then
NotesListbox.CellCheck(NotesListbox.LastIndex,0)=True
end if
NotesListbox.Cell(NotesListbox.LastIndex,0)=""
rs.MoveNext
Wend
rs.Close
Convtblname=""
end if[/quote]
If you must populate your listbox this way then the more efficient way is:

if rs<>Nil Then While Not rs.EOF NotesListbox.AddRow("", rs.Field("notes").StringValue, rs.Field("date").StringValue) NotesListbox.RowTag(NotesListbox.LastIndex)=rs.Field("x").IntegerValue if rs.Field("OK").Value=1 Then NotesListbox.CellCheck(NotesListbox.LastIndex,0)=True end if rs.MoveNext Wend rs.Close Convtblname="" end if
Personally I would use this code:

if rs<>Nil Then While Not rs.EOF NotesListbox.AddRow if rs.Field("OK").Value=1 Then NotesListbox.CellCheck(NotesListbox.LastIndex,0)=True end if NotesListbox.Cell(NotesListbox.LastIndex,1)=rs.Field("notes").StringValue NotesListbox.Cell(NotesListbox.LastIndex,2)=rs.Field("date").StringValue NotesListbox.RowTag(NotesListbox.LastIndex)=rs.Field("x").IntegerValue rs.MoveNext Wend rs.Close Convtblname="" end if
I am assuming that the value in the database field “date” (referenced by rs.Field(“date”).StringValue) is not a database Date field. If it is a database date field then I would use the following code:

if rs<>Nil Then dim dt As new Date While Not rs.EOF NotesListbox.AddRow if rs.Field("OK").Value=1 Then NotesListbox.CellCheck(NotesListbox.LastIndex,0)=True end if NotesListbox.Cell(NotesListbox.LastIndex,1)=rs.Field("notes").StringValue dt.TotalSeconds = rs.Field("date").DateValue.TotalSeconds NotesListbox.Cell(NotesListbox.LastIndex,2)=dt.SQLDate NotesListbox.RowTag(NotesListbox.LastIndex)=rs.Field("x").IntegerValue rs.MoveNext Wend rs.Close Convtblname="" end if
Hope this helps.

BRIAN_CUNNINGHAM · March 6, 2014, 3:05pm

Hello Simon, the table is as follows:
sql=“CREATE TABLE “+tblname+”(x INTEGER PRIMARY KEY, ok INTEGER, notes TEXT, date TEXT)”

because I’ve typed ‘date’ as TEXT your 3rd option is not an option correct? it is a bit confusing because SQLite is very flexible on what data is stored in a column regardless of the assigned data type.

your 3rd option does resolve some date issues I’m having elsewhere in the app although it’s not critical in this instance.

thx

Emile_Schwarz · March 8, 2014, 8:38am

Brian,

I do not know if you see that, but CheckBow have THREE (3) states, no more two states like it was more than 10 years ago (I re-discovered that yesterday, trying to run an old project from 2003).

HTH.