DBKit

Introduction

DBKit is a module of classes and example projects designed to show and help you make it easier to build desktop and web applications that are front-ends to databases. Specifically, it makes it easy to connect a database table to the user interface controls on a layout. DBKit also handles a lot of the interface for you. For example, it will enable/disable the controls you provide for editing data. It will enable/disable buttons for saving, deleting, etc. It automatically confirms with the user when they attempt to delete a record or switch records after editing without saving first.

Once you set it up, adding controls for columns in your database is very simple. Most will require no code at all. They will just work.

Unlike most other example projects, this one both demonstrates DBKit and provides the source to the DBKit module itself that you can then use in your own projects.

DBKit is designed to be used with interfaces where there's a list of rows in a ListBox that the user can then select from to edit either in controls on the same layout or a separate layout. It can also be used in cases where no list of rows are displayed. See the DBKit-Desktop and DBKit-Web example projects for examples of the various ways in which it can be used.

Supported project types

It currently support desktop and web projects. Mobile project support will be coming in a later update.

Supported Databases

DBKit supports MySQL, PostgreSQL and SQLite. It comes with all the source so you could potentially add support yourself for other database engines.

Supported Xojo versions

DBKit requires 2023r2 or later.

DBKit version

DBKit is in beta. Read the release notes for details about specific releases.

Downloading DBKit

The current version of DBKit is available from Xojo's Project Chooser. In Xojo, choose File > New Project then click on Examples > Databases to find them.

Testing DBKit

Run the DBKit-Desktop or DBKit-Web projects. Both have three examples of DBKit in action:

Name	Description
Single	Displays a ListBox with customer records that when selected can be edited via various controls on the same layout.
Separate	Displays the ListBox by itself. Double-clicking on a row will then display a separate layout for editing. This example also shows how to display rows from two different tables on the same layout using two TableConnection instances (one for each table).
Row Only	Performs a query then displays the first row in the the EntryWindow/EntryPage layout without the need for a ListBox displayings rows for the user to choose from.

Because DBKit is new, make sure you test it well. If you run into any bugs or have feature requests, report them at https://issues.xojo.com and make sure to put DBKit in the title.

Installing DBKit

For Desktop projects, open the DBKit-Desktop example project. Click on the DBKit Module, choose Edit > Copy then go to your project and choose Edit > Paste.

For Web projects, open the DBKit-Web example project. Click on the DBKit Module, choose Edit > Copy then go to your project and choose Edit > Paste.

Important

Always copy DBKit from the DBKit-Desktop project for desktop projects and from the DBKit-Web project for web projects. They are identical but there's an issue in the Xojo IDE at the moment copying from on project type to another.

No Row locking

If you are going to be using DBKit with a database server and multiple, simultaneous users, be aware that this version of DBKit does not attempt to do any row locking. The user experience of requiring the user to deliberately indicate they wish to edit by clicking the Edit button is designed to help limit occurrences where two users accidentally editing the same row at the same time. For now, if this does happen, the version of the row saved by the last user to save will be the one stored.

Getting started

In most cases, there are a only a few things you'll need to know about DBKit to use it:

1. How to connect to your database.

2. How to configure DBKit.TableConnection class (which only requires a few method calls).

3. How to indicate which controls on your layout are to be used with DBKit.

It is highly recommended you work through the desktop or web tutorial first as it will teach you all of this. It will take about 20 minutes.

The rest of this page contains details you may want to know but certainly don't need to know.

The DBKit.TableConnection Class

This class is the heart of DBKit. To use it you will drag it to a layout then use its methods and properties.

Because of its design, you won't likely have to use many of DBKit.TableConnection's methods and properties as it will automatically interrogate your layout and connect the columns from the database table you specify to the your controls.

If you are connecting to your database in your App or Session Opening events and storing a reference to the connection in a property, DBKit.TableConnection instances on your layouts will automatically find that connection and use it. You can of course override this by assigning the Connection property yourself. If your layout has more than one TableConnection instance, any DBKit controls that do not have their Table and Column properties explicitly set, will be assigned to the first TableConnection instance (the leftmost in the tray area of the layout).

Note

Only the public properties, methods and events of DBKit.TableConnection are documented as they are the only ones you would directly use. The various private class members are not documented here though you can explore them by looking through the source code itself.

DBKit.TableConnection properties

Name	Description
Connection	The connection to the database.
CreateNewRow	Indicates that a new row should be created when the layout opens.
QueryRows	Assign this property the RowSet from the query you perform for the rows you wish to display.
Table	The name of the database table being used. This can be set in the Inspector.

DBKit.TableConnection methods

It's unlikely you will need to be familiar with any of these methods as nearly all of these methods are used internally by DBKit and are only public because they are called by other DBKit classes.

Name	Description
BindAllControls	Finds all the DBKit controls on the layout then connects them to their designated columns in the database.
BindDeleteButton	Makes the passed control the button to be automatically managed for deleting rows.
BindEntryControl	Binds the column passed to the control passed. If the control is named identically to the column, only the control needs to be passed.
BindListBoxColumn	Binds the database column passed to the Listbox column specified.
BindListBoxControl	Makes the passed control the that will display rows that can be selected for editing.
BindMoveToFirstRow	Binds the button passed to the TableConnection making it the button that will be enabled/disabled automatically depending on which row is being edited.
BindMoveToLastRow	Binds the button passed to the TableConnection making it the button that will be enabled/disabled automatically depending on which row is being edited.
BindMoveToNextRow	Binds the button passed to the TableConnection making it the button that will be enabled/disabled automatically depending on which row is being edited.
BindMoveToPreviousRow	Binds the button passed to the TableConnection making it the button that will be enabled/disabled automatically depending on which row is being edited.
BindNavigationButton	Binds the SegmentedButton passed to the TableConnection make it the button that will be enabled/disabled automatically depending on which row is being edited.
BindEditButton	Makes the passed control the button to be automatically managed for editing/saving changes to rows and saving new rows as well.
BindNewButton	Makes the passed control the button to be automatically managed for creating new rows.
BindSearchField	Makes the passed DBKit.SearchField the control that will be used by the user to search and display rows in the QueryRowsListBox control bound to the same TableConnection.
BindToolbar	Makes the passed DBKit.Toolbar the toolbar whose buttons for adding	updating and deleting rows will be automatically managed by DBKit.
BindUndoButton	Makes the passed control the button to be automatically managed for reverting/undoing changes to rows.
BoundQueryResultsListBox	The QueryResultsListBox that is bound to this TableConnection.
ConfirmDeleteRow	Deletes the current row after confirming first with the user.
CheckForRowChange	Call in events that change control data to allow DBKit.TableConnection to know if the row has changed or not.
ConfirmDeleteRow	Displays a dialog box asking the user to confirm they wish to delete the currently selected row.
Connected	Connects to the database passed returning True if the connection was successful and False if it was not.
EditRow	Allows the user to edit the record by enabling/making read-write the bound entry controls and changes the Edit button caption to Done.
HasBoundListBox	Returns True if a QueryRowsListBox is bound to this TableConnection and False if one is not.
LoadSelectedRow	Loads the selected row in the bound ListBox into the bound entry controls.
MoveToFirstRow	Makes the first row in the RowSet the current row. If BindMoveToFirstRow has been called, that button will disable/enable automatically.
MoveToLastRow	Makes the last row in the RowSet the current row. If BindMoveToLastRow has been called, that button will disable/enable automatically.
MoveToNextRow	Makes the next row in the RowSet the current row. If BindMoveToNextRow has been called, that button will disable/enable automatically.
MoveToPreviousRow	Makes the previous row in the RowSet the current row. If BindMoveToLastRow has been called, that button will disable/enable automatically.
NewRow	Sets all entry controls to their null values in preparation for creating a new row.
PrimaryKeyColumn	Returns the name of the database column that contains the table's primary keys.
RemoveSelectedRow	After confirming with the user, the selected row is removed/deleted.
TableColumnType	When passed a column name	it returns the column type as an integer.
UndoRowChanges	Reloads the controls with the values from the row when it was loaded, undoing any changes the user has made.

DBKit.TableConnection events

Name	Description
Closing	The layout (window, webpage) is closing.
ControlsStateChanged	Indicates when DBKit.TableConnection has enabled or disabled the entry controls.
LoadingRow	The row in the RowSet passed is being loaded. Return False to prevent it from loading.
Opening	The layout (window, webpage) is opening.
SavingRow	The row in the RowSet passed is being saved. Return False to prevent it from saving.

Notes

Desktop versus web projects

DBKit was created to be used nearly identically in desktop and web projects. The DBKit.TableConnection class is identical in for both desktop and web projects. You can copy it from one to the other. The included control subclasses, while nearly identical, are not cross-project compatible because their super classes are specific to the project type. Having said that, if you look at the way DBKit is used in the Desktop and Web example projects, you will see that the DBKit code is nearly identical. The only place where things are different is where they have to be, for example, on the web the WebFileUploader must be used to add photos.

Connecting to a database

To connect to a database, call DBKit.TableConnection's Connected function. For SQLite, pass it the database file. For MySQL and PostgreSQL, pass it a DBKit.TableConnection.DatabaseServer enumeration that indicates that the connection is MySQL or PostgreSQL followed by the IP address of the server, the port, the database name, the user and the password. If the connection succeeds, the Connected function returns True.

This code from the DBKit-Desktop example project connects to the SQLite database being passed to it:

app.db = New DBKit.TableConnection
If app.db.Connected(SpecialFolder.Resources.child("EddiesElectronics.sqlite")) Then
  Var w As New SingleWindow
Else
  System.Beep
  MessageBox("The database could not be reached due to an error.")
End If

Supported controls

DBKit includes a several subclasses of the existing controls. These subclasses are designed to handle the work of connecting a database column to that particular type of control. Most of them you will only use to designate a control as being a DBKit-enabled control. You do this by dragging a control from the Library to your layout, then clicking the Pencil icon next to the Super property in the Inspector and choosing the DBKit control class that appears.

Important

For all DBKit controls, if there is only one TableConnection instance on the layout, the control will act upon that instance's QueryRows RowSet. If there is more than one instance of TableConnection, providing a matching table name (and in many cases the Column as well) is required.

The following controls are supported for binding to database columns:

• CheckBox

• ComboBox

• DateTimePicker/DatePicker

• ImageViewer

• ImageUploader (web only)

• Label

• PopupMenu

• QueryRowsListBox

• RadioGroup

• Slider

• TextArea

• TextField

Note

For DesktopDateTimePicker and WebDatePicker the database column must be Text, and cannot be null. DesktopDateTimePicker TimeOnly mode is not currently supported.

DBKit.CheckBox

The standard Checkbox control subclassed to work with DBKit.

Property	Description
AutoPopulate	Populates the CheckBox with the unique values from the database column in its Column property.
AutoPopulateColumn	If AutoPopulate is True, this column will be used to populate the InitialValue property of the control. If this property is empty, the table in the Column property is used.
AutoPopulateTable	If AutoPopulate is True, this table will be used to populate the InitialValue property of the control. If this property is empty, the table in the Table property is used. Column, The database column from which to exchange data.
Table	The database table name from which to get the columns.
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires that the CheckBox be checked to save the row.

DBKit.ComboBox

The standard ComboBox control subclassed to work with DBKit.

Property	Description
AutoPopulate	Populates the ComboBox with the unique values from the database column in its Column property.
AutoPopulateColumn	If AutoPopulate is True, this column will be used to populate the InitialValue property of the control. If this property is empty, the table in the Column property is used.
AutoPopulateTable	If AutoPopulate is True, this table will be used to populate the InitialValue property of the control. If this property is empty, the table in the Table property is used. Column, The database column from which to exchange data.
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires that a value be entered or selected to save the row.
Table	The database table name from which to get the columns.

DBKit.DateTimePicker/DatePicker

The standard DesktopDateTimePicker or WebDatePicker control subclassed to work with DBKit.

Property	Description
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires that a value be entered or selected to save the row.
Table	The database table name from which to get the columns.

DBKit.DeleteButton

A Button control that will automatically delete the current row. It will also automatically enable and disable when appropriate.

Property	Description
Connector	The TableConnection instance to which the control is connected.
Table	The name of the table whose matching TableConnection holds the RowSet from which row will be deleted. This is required when there's more than one TableConnection instance on the layout.

DBKit.EditButton

A Button control that when pressed will make editable all DBKit controls on the layout that are bound to the same table as the button. It will then become a Done button that when pressed will commit the user changes. It will also automatically enable and disable when appropriate.

Property	Description
Connector	The TableConnection instance to which the control is connected.
Table	The name of the table whose matching TableConnection holds current row and DBKit controls will be made ready for editing. This is required when there's more than one TableConnection instance on the layout.

DBKit.ImageViewer

An Imageviewer subclass that can display a picture stored in the database.

Property	Description
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
CurrentImage	The image displayed in the ImageViewer.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires that an image be assigned to the control to save the row.
Table	The database table name from which to get the columns.

DBKit.ImageUploader

Used in web projects to upload images to be displayed in a DBKit.ImageViewer.

Property	Description
TargetImageViewer	The DBKit.ImageViewer control an uploaded image should be sent to once the upload is complete.

DBKit.Label

A Label subclass that can be bound to a column of a table.

Property	Description
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires that an image be assigned to the control to save the row.
Table	The database table name from which to get the columns.

Method	Description
UpdateText	Used to change the Text property so that DBKit will be aware and thus the Undo button (should you have one) will be able to undo the change and the Edit button (should you have one) will be able to save the change.

DBKit.MoveToFirstRowButton

A button that will move the current row to the first row and updates the DBKit controls on the layout to display that row.

Property	Description
Connector	The TableConnection instance to which the control is connected.
Table	The name of the table whose matching TableConnection holds the RowSet whose first row will become the current row. This is required when there's more than one TableConnection instance on the layout.

DBKit.MoveToLastRowButton

A button that will move the current row to the last row and updates the DBKit controls on the layout to display that row.

Property	Description
Connector	The TableConnection instance to which the control is connected.
Table	The name of the table whose matching TableConnection holds the RowSet whose last row will become the current row. This is required when there's more than one TableConnection instance on the layout.

DBKit.MoveToNextRowButton

A button that will move the current row to the next row and updates the DBKit controls on the layout to display that row.

Property	Description
Connector	The TableConnection instance to which the control is connected.
Table	The name of the table whose matching TableConnection holds the RowSet whose next row will become the current row. This is required when there's more than one TableConnection instance on the layout.

DBKit.MoveToPreviousRowButton

A button that will move the current row to the previous row and updates the DBKit controls on the layout to display that row.

Property	Description
Connector	The TableConnection instance to which the control is connected.
Table	The name of the table whose matching TableConnection holds the RowSet whose previous row will become the current row. This is required when there's more than one TableConnection instance on the layout.

DBKit.NavigationButton

A SegmentedButton control that can be configured to include First, Last, Next and Previous buttons. The buttons will navigate automatically and will enable/disable automatically.

While you can add your own segments to the control in the Layout Editor or at runtime, the built-in buttons you enable must be the first buttons in the control. In other words, if you enable Next and Previous for example, the first two buttons will act as the Next and Previous buttons.

Property	Description
Connector	The TableConnection instance to which the control is connected.
FirstRow	Set this to True in Inspector or in the Opening event to include a First button in the control.
LastRow	Set this to True in Inspector or in the Opening event to include a Last button in the control.
NextRow	Set this to True in Inspector or in the Opening event to include a Next button in the control.
PreviousRow	Set this to True in Inspector or in the Opening event to include a Previous button in the control.
Table	If the layout includes more than one TableConnection instance, designate the name of a table that matches the Table property of the TableConnection instance whose RowSet you wish the user to navigate.

DBKit.NewButton

A Button control that when pressed will make editable all DBKit controls on the layout that are bound to the same table as the button. It will then become a Done button that when pressed will commit the user changes. It will also automatically enable and disable when appropriate.

Property	Description
Connector	The TableConnection instance to which the control is connected.
Table	The name of the table into which the row will be inserted. This is required when there's more than one TableConnection instance on the layout.

DBKit.NumberField

A DBKit.TextField subclass that can be bound to a column of a table.

Property	Description
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DecimalPlaces	The number of decimal places allowed during data entry by the user.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires that the NumberField contains a value to save the row.
MaximumValue	The lowest acceptable value the NumberField can contain for the user to be able to save the row.
MinimumValue	The highest acceptable value the NumberField can contain for the user to be able to save the row.
Table	The database table name from which to get the columns.
ThousandsSeparator	If True	a the locale-savvy thousands separator will appear when appropriate.
Type	Controls both the formatting and entry of the numeric value. See NumberField.Types for details.

NumberField.types

Type	Description
Number	Any unformatted value.
Currency	Formats the value using appropriate currency symbol based on the locale of the device.
Custom	Uses the format assigned to the Format property.
Percentage	Formats the value as a percentage. When loading the value	1 is assumed to be 100%. Thus for example the user can enter 45.5 which will be stored in the database column as .455.

Methods	Description
Number	The numeric value of the NumberField returned as a Double.
NumericTypeFormat	Returns as a String the format of NumberField based upon its Type property.

DBKit.PopupMenu

A PopupMenu subclass that can be bound to a column of a table. It's contents can be created manually in the Layout Editor or can be created at runtime by being automatically populated via a column from a table in a database.

Property	Description
AutoPopulate	Populates the PopupMenu with the unique values from the database column in its Column property.
AutoPopulateColumn	If AutoPopulate is True, this column will be used to populate the InitialValue property of the control. If this property is empty, the table in the Column property is used.
AutoPopulateTable	If AutoPopulate is True, this table will be used to populate the InitialValue property of the control. If this property is empty, the table in the Table property is used. Column, The database column from which to exchange data.
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires a value be selected to save the row.
Table	The database table name from which to get the columns.

DBKit.QueryRowsListBox

A ListBox subclass that can display columns from a table in a database.

Property	Description
Columns	The database columns (comma-separated) from which to exchange data. To skip a ListBox column, add the comma without entering a column name. For example, FirstName,,LastName.
Connector	The TableConnection instance to which the control is connected.
QueryRows	The RowSet whose data will be displayed in the bound controls. This property is write-only.

Method	Description
BindDatabaseColumns	Used to connect database columns to columns of the ListBox if the Columns property is not populated.

DBKit.RadioGroup

A RadioGroup subclass that can be bound to a column of a table.

Note

Unlike PopupMenu, the AutoPopulate option will only populate the control from a table if the RadioGroup initially has a single row in it in the Layout Editor. Otherwise, the control in the Layout Editor would have to be empty which would make it invisible in the Layout Editor.

Property	Description
AutoPopulate	Populates the RadioGroup with the unique values from the database column in its Column property.
AutoPopulateColumn	If AutoPopulate is True, this column will be used to populate the InitialValue property of the control. If this property is empty, the table in the Column property is used.
AutoPopulateTable	If AutoPopulate is True, this table will be used to populate the InitialValue property of the control. If this property is empty, the table in the Table property is used. Column, The database column from which to exchange data.
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires that a value be selected to save the row.
Table	The database table name from which to get the columns.

DBKit.SearchField

A SearchField subclass that automatically handles searching and displaying the results in a DBKit.QueryRowsListBox.

The QueryRowsListBox is uses is the one bound to the same TableConnection as the SearchField. DBKit.SearchField will bind itself to the TableConnection on the layout whose Table property matches its Table property. If the Table property is left blank, SearchField will bind to the first TableConnection in the layout's tray. In other words, if you have a TableConnection and a QueryRowsListBox on the layout, adding a SearchField will search the table defined by the

Property	Description
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DisplayAllRowsByDefault	If True	when the SearchField has no search value	all rows will be displayed. The default for this property is True.
Table	The database table name that must match the Table property of a TableConnection instance on the layout in order to bind to it. If this is left blank	the SearchField will bind to the first TableConnection in the layout's tray.

DBKit.Slider

A Slider subclass that can be bound to a column of a table.

Property	Description
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires that a value greater than the minimum value be selected to save the row.
Table	The database table name from which to get the columns.

DBKit.TextArea

A TextArea subclass that can be bound to a column of a table.

Property	Description
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires that the TextArea contains at least a single character to save the row.
Table	The database table name from which to get the columns.

DBKit.TextField

A TextField subclass that can be bound to a column of a table.

Property	Description
Column	The database column from which to get the value.
Connector	The TableConnection instance to which the control is connected.
DisplayName	The name you wish displayed when DBKit needs to display it in a dialog box.
Mandatory	When checked requires that the TextField contains at least a single character to save the row.
Table	The database table name from which to get the columns.

DBKit.Toolbar

A Toolbar subclass that can provide and manage buttons to create a new row, delete the selected row, edit the selected row and undo changes to the selected row.

Property	Description
Connector	The TableConnection instance to which the control is connected.
DeleteButton	If True	a Delete button will be included in the Toolbar. This can be set in the Inspector or via code. Clicking the button will delete the selected row.
EditButton	If True	an Edit/Done button will be included in the Toolbar. This can be set in the Inspector or via code. Clicking the button will toggle the layout between read-only and read-write mode to allow editing by the user.
NewButton	If True	a New button will be included in the Toolbar. This can be set in the Inspector or via code. Clicking this button will empty the layout controls so that a new row can be entered.
Table	The database table name that must match the Table property of a TableConnection instance on the layout in order to bind to it. If this is left blank	the Toolbar will bind to the first TableConnection in the layout's tray.
UndoButton	If True	an Undo button will be included in the Toolbar. This can be set in the Inspector or via code. Clicking this button will reload the previously saved version of the row.

DBKit.UndoButton

A Button control that when pressed will reload the current row into any DBKit controls on the layout that are bound to the same table as the button. This has the effect of undoing any changes the user made. It will also automatically enable and disable when appropriate.

Property	Description
Connector	The TableConnection instance to which the control is connected.
Table	The name of the table whose matching TableConnection holds the RowSet whose current row will be reloaded undoing any changes the user has made. This is required when there's more than one TableConnection instance on the layout.

Binding data entry controls to database columns

Binding is, for the most part, automatic. You can either name your controls to match the names of the columns from the database table or put the column name for the control in its Column property in the Inspector. DBKit controls have a Table property as well. This is only required in cases where you are using two DBKit.TableConnections for two different tables on the same layout. In that case, all controls should designate both their Column and their Table names.

However, if you find a situation in which you need to bind controls manually, use BindAllControls to bind them all at once.

To bind a single control to a database column, use DBKit.TableConnection's BindEntryControl method. If you name your controls to match the names of the columns, you only need pass the control itself as the first parameter. If the control names do not match the column names, pass the column name as the second parameter: For example:

Me.BindEntryControl(LastName)

or

Me.BindEntryControl(LastName, "SurName")

Loading the selected row into the bound entry controls

When the user clicks on a row in your bound QueryRowsListBox, DBKit.TableConnection will first determine if the row already displayed is in the middle of being edited. If it is, DBKit.TableConnection will display a confirmation dialog box allowing the user to cancel the row change in order to continue editing the current row or lose those changes and switch to the new row. If the user decides to switch to the new row, it will load that row automatically.

While this is done automatically, if you need to load the row manually, in the ListBox.SelectionChanged event, call DBKit.TableConnection's LoadSelectedRow method:

TableConnection1.LoadSelectedRow

Intercepting the loading and saving of rows

DBKit.TableConnection has a LoadRowing event that is called before the row is loaded into bound entry controls. This is a good place to put code that needs to modify or view the row before it's loaded into the user interface. You can also stop the process of loading the row by returning False from this event. Make sure you return True to allow the row to be loaded. DBKit.TableConnection also has a SavingRow event that is called just before the row is saved to the database. This event gives you an opportunity to change values and to prevent the row from being saved by return False.

Loading related rows

If you have the need to show related rows in another table:

1. Add a second TableConnection object to the layout set its Table property to the table with the related rows.

2. Add a QueryRowsListBox to the layout, assigning its Table property the same table.

3. In the LoadingRow event of the primary TableConnection instance, you will get the primary key from the row being loaded (which is passed to the event), query for the related rows in the other table and assign the resulting RowSet to the QueryRowsListBox you just added:

For example, on a Layout displaying a customer and their invoices, the CustomersConnection.LoadingRow event would look like this:

'Load the invoices for this customer
Var ID As Integer = row.Column(Me.PrimaryKeyColumn).IntegerValue
Var rs As RowSet = Me.Connection.SelectSQL("SELECT * FROM " + InvoicesConnection.Table + " WHERE CustomerID = ?", ID.ToString)
Invoices.QueryRows = rs
Return True

Adding Edit, Undo, Delete and New buttons

You may wish to have buttons to allow the user to edit, undo changes, delete the record and/or make new records. You can use a DesktopButton or WebButton. To designate a button as being a Delete, New, Undo or Edit button:

1. Drag a button to the layout.

2. In the Inspector, click on the Pencil icon next to the Super property then select the appropriate button type:

Name	Description
DBKit.DeleteButton	Deletes the selected row. It will ask the user to confirm before deleting.
DBKit.EditButton	Makes all DBKit data entry controls on the layout ready for editing by the user.
DBKit.NewButton	Resets all DBKit data entry controls on the layout to their default state so they are ready for the user to make a new database entry.
DBKit.UndoButton	Reloads the entire row as it was originally into the data entry controls, undoing all changes the user has made.

While DBKit can bind these buttons automatically for you using the steps above, if you need to do it in code, you can do so. In all cases, pass the control as the only parameter:

'Indicate the buttons that handle Delete, New, Undo and Edit
Me.BindDeleteButton(DeleteButton)
Me.BindNewButton(NewButton)
Me.BindUndoButton(UndoButton)
Me.BindEditButton(EditButton)

Creating your own Edit button

If you set a button's Super property to the DBKit.EditButton class, the edit button function will be handled automatically. If you need to handle it manually, call the DBKit.TableConnection's EditRow method:

Try
  TableConnection1.EditRow
Catch error As DatabaseException
  System.Beep
  MessageBox("An error occurred while attempting to save the record.")
End Try

Creating your own Undo button

If you set a button's Super property to the DBKit.UndoButton class, the undo button function will be handled automatically. If you need to handle it manually, call the DBKit.TableConnection's UndoRowChanges method:

Try
  TableConnection1.UndoRowChanges
Catch error As DatabaseException
  System.Beep
  MessageBox("An error occurred while attempting undo the changes you made.")
End Try

Creating your own New button

If you set a button's Super property to the DBKit.NewButton class, the new button function will be handled automatically. If you need to handle it manually, call the DBKit.TableConnection's NewRow method:

Try
  TableConnection1.NewRow
  FirstName.SetFocus 'Set focus back to whichever control is first in the tab order
Catch error As DatabaseException
  System.Beep
  MessageBox("An error occurred while attempting create the new record.")
End Try

Creating your own Delete button

If you set a button's Super property to the DBKit.DeleteButton class, the delete button function will be handled automatically. If you need to handle it manually, call the DBKit.TableConnection's ConfirmDeleteRow method:

Try
  TableConnection1.ConfirmDeleteRow
Catch error As DatabaseException
  System.Beep
  MessageBox("An error occurred while attempting delete the record.")
End Try

Adding navigation buttons

You may wish to have buttons to allow the user to move to the first, previous, next and last rows in the RowSet.

You can add a single navigation button that handles moving between rows or separate buttons.

If you wish to have a single navigation button:

1. Drag a SegmentedButton contorl to the layout.

2. In the Inspector, click on the Pencil icon next to the Super property then select DBKit.NavigationButton.

For separate buttons, you can use a DesktopButton or WebButton. To designate a button as being a Delete, New, Undo or Edit button:

1. Drag a button to the layout.

2. In the Inspector, click on the Pencil icon next to the Super property then select the appropriate button type:

Name	Description
DBKit.MoveToFirstRow	Displays the first row in the RowSet.
DBKit.MoveToLastRow	Displays the last row in the RowSet.
DBKit.MoveToNextRow	Displays the next row in the RowSet.
DBKit.MoveToLastRow	Displays the last row in the RowSet.

In all cases, if the row has been edited but not saved, clicking one of these buttons will display the save confirmation dialog box.

These buttons will all auto-enable and auto-disable appropriately based upon which row is being displayed. For example, if the first row is being displayed the MoveToFirstRow and MoveToPreviousRow buttons will automatically disable.

While DBKit can bind these buttons automatically for you using the steps above, if you need to do it in code, you can do so. In all cases, pass the control as the only parameter:

'Indicate the buttons that handle First, Last, Next and Previous
Me.BindMoveToFirstRow(FirstButton)
Me.BindMoveToLastRow(LastButton)
Me.BindMoveToNextRow(NextButton)
Me.BindMoveToPreviousRow(PreviousButton)

Binding in this case will only handle automatically enabling/disabling the controls. Make sure in their Pressed events you are calling the appropriate DBKit.TableConnection method (MoveToNextRow for example) to make the button perform the desired action.

Creating your own navigation buttons

If you set a button's Super property to one of the DBKit navigation button classes (MoveToFirstButton, MoveToLastButton, MoveToNextButton, MoveToPreviousButton, or NavigationButton), the button's function will be handled automatically. If you need to handle it manually, call the appropriate DBKit.TableConnection method:

For example, to create your own button to move the current row to the next row, put the following code in its Pressed event:

Try
  TableConnection1.MoveToNextRow
  FirstName.SetFocus 'Set focus back to whichever control is first in the tab order
  Catch error As DatabaseException
  System.Beep
  MessageBox("An error occurred while attempting move to the next row.")
End Try

Using an ImageViewer control for adding/changing images

For desktop projects, the DBKit.ImageViewer will accept JPEG and PNG files dropped on to it. For web projects, use the DBKit.FileUploader control.

The example projects demonstrate these.

Testing Your Project

Testing a database application often involves making changes to the data. To facilitate this, if you are using SQLite, you can add a Copy Files Build Step to your project to put a copy of your database in the debug build when running from the IDE. If you've never done this before, checkout either DBKit Tutorial as they will have to do this as one of the steps.

Localizing DBKit

Any text displayed by DBKit that the end user is likely to see is localizable. You will find most of the localizable strings in DBKit.TableConnection > Localizable Strings. Others can be found in the controls themselves.

Note

Keep copies of your localized strings so that you can reinstall them when new versions of DBKit are released. Eventually we will provide an update that has these strings localized for you.

Save Changes confirmation dialog box

The localizable parts of the confirmation dialog that appears when the user attempts to exit a row they have edited before saving changes are:

Name	Description
ConfirmCancel	The Cancel button caption.
ConfirmContinue	The Continue button caption.
ConfirmExplanation	The explanatory message.
ConfirmMessage	The primary message.
ConfirmTitle	The dialog title.

Delete confirmation dialog box

The localizable parts of the confirmation dialog that appears when the user attempts to delete the current row are:

Name	Description
DeleteConfirmCancel	The Cancel button caption.
DeleteConfirmContinue	The Continue button caption.
DeleteConfirmExplanation	The explanatory message.
DeleteConfirmMessage	The primary message.
DeleteConfirmTitle	The dialog title.

Edit button caption

The Edit button's Edit and Done captions can be localized:

Name	Description
EditButtonDoneCaption	The caption that appears when the user clicks the Edit button. The default is Done.
EditButtonEditCaption	The initial caption that appears when the layout is not in editing mode. The default is Edit.

Mandatory field required message

To localize the message displayed when the user attempts to save the row but has left a mandatory control without a value, edit the TableConnection.ErrorMandatoryValueMissing constant.

NumberField out of range message

To localize the message displayed when the user attempts to leave a NumberField with a value that doesn't meet the minimum or maximum values, edit the NumberField.ErrorMinValue and ErrorMaxValue constants.

NumberField no decimals allowed message

Because the TextField control in a web app does not have a KeyDown event, numeric entry can't be checked as the user types. It is therefore checked by DBKit when the user attempts to exit the field. If they have entered a decimal value when the DecimalPlaces property is 0, a message will be displayed. To localize this message, update the NumberField/ErrorDecimalsNotAllowed constant.

NumberField message dialog box (web)

To localize the OK button that appears in the dialog box when any of the above NumberField messages are displayed, edit the NumberField.MessageDialogButtonCaption constant.

Bug reports and feature requests

If you find a bug in DBKit or have a feature request, use our Issues reporting system to give us the details. Make sure you prefix the title of your issue with DBKit.

Supporting more controls and platforms

To support controls that are not currently supported, the following DBKit.TableConnection methods must be updated:

• CurrentRowChanged

• EditRow

• SaveRow

• SetControlValue

Control subclasses must implement the DBKit.Control class interface and include TableName As String, ColumnName As String and Connector as DBKit.TableConnection properties. Examine the existing control subclasses in DBKit for details.

To support entirely new platforms, the following DBKit.TableConnection methods must be updated:

• Constructor

• DesktopControlForColumn/WebControlForColumn

• EditRow

• LoadSelectedRow

• NewRow

• NoRowSelected

• SaveRow

• SetButtonEnabledState

DBKit Release notes

Known issues

• Desktop: In the example after clicking the Edit button, tabbing from the SearchCustomers control to FirstName does not work. You can click on FirstName and then tab through the controls but once you table out of the DBKit.Desktop.DBTextFields, you can't tab back into them again. Tabbing between SearchCustomers and the Taxable CheckBox however, does work.

• A regular ListBox control separate from the QueryRowsListBox is needed.

• DBKit is not yet aware of _rowid in MySQL tables.

• Some DBKit properties available in the Inspector do not appear in the DBKit section of the Inspector.

Beta 10 (October 2nd, 2024)

• Removed the Version attribute on the TableConnection class as this could cause a crash when copying the class between projects. This bug has been resolved for 2024r4 but the attribute is unnecessary anyway since DBKit has a Version constant that is used to indicate the version number.

Beta 9 (May 7th, 2024)

Changes

• DBKit now supports using any column type for the primary key column (not just numbers as had been the case).

Resolved Issues

• Fixed a bug when using DBKit with PostgreSQL that caused the PrimaryKeyColumn function to fail if the table name included the name of the schema.

• DBKit.DatePicker (web) now stores null in the database column when there is no date selected.

• WebDialogs and WebContainers are now supported in addition to WebPages for web projects. Note that any setup you are going to do for a TableConnection on a WebDialog must be done in the Shown event rather than the Opening event.

• Fixed a bug that caused a NOE when uploading a picture to a DBKit.ImageViewer.

New Features

• DBKit.SearchField - Provides a Search UI that automatically works with your QueryRowsListBox without any additional code. If you leave the Table property blank, it will find the first TableConnection then bind with its QueryRowsListBox. Otherwise, it will look for a TableConnection on the layout whose Table property matches its own. If you provide columns for it to search it will use them. If you don't, it will use the columns of the QueryRowsListBox to which it is bound. String and Number type columns are supported. For String values, a begins with search is performed. For numeric values, the search is for an exact match. You can use >, < or ... for a range. For example, >10 finds rows use searched numeric column has a value greater than 10. Entering 10...20 will find rows whose searched numeric column has a value between 10 and 20. The Show All Rows By Default property if true will display all rows when the SearchField is blank.

• DBKit.Toolbar - New control that provides a toolbar with options for New, Delete, Edit and Undo buttons. No code needed. The buttons you wish displayed can be indicated via properties in the Inspector. (For web projects, you'll will need to add the new backicon.png file to your icons folder)

• DBKit controls now have a Mandatory property that can be set via code or in the Inspector. Mandatory controls must have a value for the record to be able to be saved. For Slider, the value must be greater than the minimum value. For CheckBox, the checkbox must be checked.

• DBKit.NumberField - Handles numeric entry. Choose the type (Currency, Number, Percentage and Custom) and it will handle the formatting and entry masking for you. It includes properties to indicate if you want a thousands separator and the number of decimals you wish to show. It's also locale-aware. Choose Custom if you plan to assign a format to the Format property. It also has MinimumValue and MaximumValue properties. It will only allow entry of values between these two numbers provided that they are not the same number. In other words, if MinimumValue and MaximumValue are both 0 then no range checking will be done.

• All controls that display database data now have a DisplayName property. If this property has a value, it will be used in any messages (except those displayed as a result of database errors) to the user such as when they have not filled in a mandatory field. If it does not have a value, the name of the control will be used.

Beta 8 (April 9th, 2024)

Changes

• DBKit.Connector has been renamed TableConnection (to be consistent with SerialConnection, URLConnection).

• DBKit.SearchResultsListBox no longer has an AddRowsFromRowSet method. Use the new QueryRows As RowSet property instead. This is for consistency with the new DBKit.TableConnection.QueryRows property.

• DBKit.SearchResultsListBox has been renamed QueryRowsListBox for consistency with other parts of the API. You may need to make a few name changes in your code to accommodate this.

• DBKit.TableConnection.PrimaryKeyColumn now caches the column name rather than querying the database every time.

Resolved Issues

• DeleteButton, EditButton, NewButton and UndoButton all now fire their Pressed events when pressed. (74844)

• Fixed a bug that caused the controls to not be bound to the correct table when the user had entered a schema name as part of the table name in the Table property of a DBKit control.

• Fixed a bug that caused rows to not save properly when using DBKit with PostgreSQL.

New Features

• New TableConnection.QueryRows As RowSet property that can be used to create layouts that show the current row of a RowSet without a QueryRowsListBox as part of the user interface. This property is write-only. Once you assign your RowSet to it, do NOT change your RowSet in any way as the TableConnection is using that RowSet to display rows in your user interface.

• New TableConnection.MoveToFirstRow, MoveToNextRow, MoveToPreviousRow, MoveToLastRow methods that can be used to move the current row of the QueryRows property for use when you're displaying rows directly from the TableConnection rather than via a QueryRowsListBox.

• New TableConnection.HasBoundListBox function that will tell you if the TableConnection has a QueryRowsListBox bound to it or not.

• New MoveToFirstRowButton, MoveToLastRowButton, MoveToPreviousRowButton, MoveToNextRowButton which can be dragged to the layout to automatically move the current row. These buttons enable and disable automatically.

• New NavigationButton As DesktopSegmentedButton/WebSegmentedButton. This provides a single control that can include navigation buttons (First, Previous, Next, Last).

• QueryRowsListBox now has a Table As String property. The purpose of this is to allow for multiple QueryRowsListBoxes to exist on the same layout each driven by its own TableConnection instance. In a case like this, the Table property is mandatory as it will bind to the TableConnection whose table matches its Table. This allows for displaying rows from related tables. See the EntryWindow/EntryPage layouts for an example of using two TableConnections (one to show the customer, the other two show the customer's invoices). The invoices are loaded in the CustomersConnection.LoadingRow event.

Beta 7 (February 27th, 2024)

• Calling BindControls in a DBKit.Connector Opening event is no longer necessary. If you used previous betas, you'll want to update your Connector instance opening events to remove the now unnecessary call to BindControls.

• Fixed the bug where a bound WebFileUploader button was always enabled even when a row was not being edited.

• When using DBKit with PostgreSQL, tables in schemas other than public are now supported. To designate the schema, prefix the table name assigned to the DBKit.Connector.Table property with the schema name. For example, if the schema name is "utilities" and the table name is "resources", then the Table property of the instance of the DBKit.Connector control on the layout should be assigned "utilities.resources".

• The DBKit.DeleteButton controls are now disabled when a new row is being added by clicking a DBKit.NewButton. (75002)

Beta 6 (August 24th, 2023)

• IMPORTANT: It turns out there are too many issues with trying to have a single DBKit module that includes all the DBKit controls for all project types. As a result, the version of DBKit in the Desktop example is only for desktop projects. The version in the web example is only for web projects. The core code in DBKit itself is still the same in both projects. This also means that you will have to reset the super property of any DBKit controls on your layouts. This is a one-time only change.

• IMPORTANT: DBKitConnector is now Public (instead of Global) and is now simply Connector. This means that when you replace a previous version of DBKit with this one, you will need to reset the Super of any DBKitConnector class instances to DBKit.Connector. This is a one time-only change.

• IMPORTANT: DBKit control subclasses are no longer in their own project type-specific modules (Desktop or Web). As a result, they are no longer namespaced as Desktop or Web. Thus you will need to reset the super on any DBKit controls you're using on your layouts. This is a one time-only change.

• Versions for the desktop and web are now separate because the Xojo IDE isn't really designed to have classes from different platforms in the same project file. Make sure to copy from the appropriate example project.

• Fixed a few controls whose DBKit properties didn't appear correctly in the Inspector.

• Edit/Done button now enables when making a new row for a table with no rows.

• If you attempt to use a DBKit.Connector class without first assigning a table name to the Table property, an exception is now raised.

Beta 5 (August 15th, 2023)

• When editing a row or adding a new row, the focus is now automatically set to the first TextField or TextArea in the tab order.

• Fixed a bug that caused the Edit button to be disabled after pressing the Undo button.

• DBKitSearchResultsListBox columns can now be bound via the Columns property in the DBKit section of the Inspector instead of having to do it in code with the BindDatabaseColumns method though that too still works.

• It is no longer necessary to set up the connection for a DBKitConnector on a layout if one has already been established and stored in an App class (desktop) or the Session (web) classes. DBKitConnector looks for the connection in those objects and will use it if it finds it. You can still create the connection in code if needed.

Beta 4 (July 20th, 2023)

• TableConnector has been renamed DBKitConnector.

• All new control subclasses that nearly eliminate all the code needed to set up database controls. It's highly recommended that you go through the tutorial again to get a good idea of how different this version is. The tutorial now only takes about 20 minutes compared to 30 with the previous beta as there's a LOT less code to enter.

• DBComboBox, DBPopupMenu and DBRadioGroup now all have an AutoPopulate As Boolean property that appears in the DBKit section of the Inspector. Setting this to true will auto populate the control with the unique values from the column. If you prefer the values to come from another column or another table and column, provide those in the AutoPopulateTable and AutoPopulateColumn properties.

• BindListBoxColumns now allows you to bind two columns (example: FirstName and LastName) to the same single column of a listbox. To do so, pass two columns names separated by a comma "firstname,lastname" as a column parameter.

Beta 3 (June 26th, 2023)

• Removed the transaction used while testing in the IDE since, at least for SQLite, we are copying the database each time your run from the IDE.

Beta 2 (June 26th, 2023)

• Fixed a bug with the locking of some of the TextFields in the example projects.

• Fixed a bug that resulted in an exception when a row had been modified and saved more than once.

• Replaced icons in example projects with nicer ones from Jérémie Leroy (thanks Jérémie!).

• Added TableConnector.ControlsStateChanged event that fires when the entry controls are enabled or disabled by TableController allowing the user to enable/disable other controls that are not bound to the database table.

• Fixed a bug that caused TableConnector running in a web project to think stored images had changed when they had not changed.

• Changed the behavior of returning False from the SavingRow event. It no longer reloads the original row.

• Fixed a bug that prevented the saved record from being unlocked.

• Changed the behavior of editing rows. Entry controls are now disabled or read-only (in the case of TextFields and TextAreas) by default. The user clicks the Edit button to begin editing. The Edit button's caption then changes to Done. This enables the controls or in the case of TextFields and TextAreas makes them read-write. When the user is done editing, they click the Done button. This avoids accidental edits and is in preparation for a future version of DBKit that will provide the option to allow editing of any specific record by one user at a time. In the example projects, the button is now named EditButton and has Edit as its default caption. The two possible captions (Edit and Done) are localizable strings.

• Because of the change to how rows are edited, use EditRow now instead of SaveRow (which is now private).

• The BindSaveButton method is now BindEditButton. When you update your projects to use DBKit 1b2, you'll get some compiler errors as a result in your TableConnector opening events. Changing BindSaveButton to BindEditButton will fix that.

• To improve clarity, the TableConnector.BindListControl method has been renamed BindListBoxControl. When you update to this version, you'll need to update any places where you call BindListControl. Thankfully, the compiler will point these out to you when you run your project.

Beta 1 (June 21st, 2023)

• Initial pre-release

See also

DBKit Desktop Tutorial, DBKit Web Tutorial