Assuming you have a property “DB” of tye “SQLiteDatabase” in App and the database is connected, then you can create your table “IdeaDrawer” with this statement:
App.DB.ExecuteSQL("CREATE TABLE IF NOT EXISTS IdeaDrawer (ID NOT NULL PRIMARY KEY AUTOINCREMENT, GUID TEXT, ListBoxIdeaDrawer TEXT, IdeaDrawerDescription TEXT);")
GUID is meant to store a unique user generated ID. There are several ways to generate such a unique ID (I am using the UUIDMBS from the MBS plugins). There are several improvements in using a GUID instead of the ID (e.g. creating new records, merging two databases etc.)
Create a method “Listbox_IdeaDrawer” to populate the listbox.
Var rs As RowSet
Var LB As DesktopListBox
LB = IdeaDrawer // the name of yout listbox
LB.RemoveAllRows // clear the listbox before populating with data
Try
rs = App.DB.SelectSQL("SELECT GUID, ListBoxIdeaDrawer, IdeaDrawerDescription FROM IdeaDrawer")
Catch e As DatabaseException
MessageBox("Error loading data: " + e.Message)
Return
End Try
If rs <> Nil Then
// hide ListBox for faster loading
LB.visible = False
For i As Integer = 1 To rs.RowCount
LB.AddRow(rs.Column("ListBoxIdeaDrawer").StringValue )
LB.CellTooltipAt(LB.LastAddedRowIndex, 1 ) = rs.Column("IdeaDrawerDescription").StringValue
LB.RowTagAt(LB.LastAddedRowIndex) = rs.Column("GUID").StringValue
rs.MoveToNextRow
Next
// show ListBox again
LB.visible = True
// close Rowset
rs.Close
End If
Create a property “mCurrentGUID” of type “String” in your window, to store the current selected GUID. Create an eventhandler “DoublePressed” in your listbox to do a database request and to populate the fields.
// stop, if nothing is selected
If Me.SelectedRowIndex = -1 Then Return
// store GUID in mCurrentGUID an then perform database select
mCurrentGUID = Me.RowTagAt(Me.SelectedRowIndex).StringValue
// ---------------------------------------------------------------------
// search and retrieve data
// ---------------------------------------------------------------------
Var rs As RowSet
Try
rs = App.DB.SelectSQL("SELECT * FROM IdeaDrawer WHERE GUID = ?", mCurrentGUID)
Catch e As DatabaseException
MessageBox("Error loading data: " + e.Message)
Return
End Try
If rs <> Nil Then
tf_IdeaName.Text = rs.Column("ListBoxIdeaDrawer").StringValue
ta_IdeaDescription.Text = rs.Column("IdeaDrawerDescription").StringValue
End If
// close RowSet
rs.Close
Finaly you can put this in the “Pressed” event of your “Update” button:
// check if mCurrentGUID is not empty
If mCurrentGUID = "" Then Return
// we have a mCurrentGUID, so now update database record
Try
App.DB.ExecuteSQL("UPDATE IdeaDrawer SET ListBoxIdeaDrawer = ?, IdeaDrawerDescription = ? WHERE GUID = ?", tf_IdeaName.Text, ta_IdeaDescription.Text, mCurrentGUID)
Catch e As DatabaseException
MessageBox( "Error updating IdeaDrawer: " + e.Message )
Return
End Try
// reload listbox to show changes
Listbox_IdeaDrawer