A RecordSet provides a way to access a group of Database records (or rows) from a query. Use the methods and properties of the RecordSet class to navigate among this set of records and view, edit, delete, and update individual records.
If you are creating an application as a front-end in a multi-user environment, keep in mind the following. The Edit method will attempt to lock the current record to other users. If a user tries to access a locked record, the Database class Error property will return True.
A RecordSet is closed when it goes out of scope.
If you try to use a RecordSet after it has been closed, an UnsupportedOperationException is raised.
The MoveNext method unlocks the current record if you previously locked it with the Edit method. Previously locked records are also unlocked by the Close method of the class and the Close method of the Database class.
Not all data sources support all four record navigation methods, MoveFirst, MoveNext, MovePrevious, MoveLast. The limitations of some of the data sources are presented in the following table.
|SQLiteDatabase||Supports all record navigation methods|
|Oracle||Supports all record navigation methods|
|ODBC||Supports all record navigation methods|
|mySQL||Supports only MoveNext method|
|MS SQL Server||Supports only MoveNext method|
|PostgreSQL||Supports only MoveNext method|
This code gets the data from the Team table and displays it in a ListBox:
Dim sql As String
sql = "SELECT * FROM Team"
Dim data As RecordSet
data = mDB.SQLSelect(sql)
If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
If data <> Nil Then
While Not data.EOF
DataList.AddRow(data.IdxField(1).StringValue, data.IdxField(2).StringValue, _
The following method populates a ListBox with a RecordSet. It uses the Name and StringValue properties to obtain the fieldnames and values:
If rs Is Nil Then Return
// set up listbox state for population
dataList.Columncount = rs.Fieldcount
// Add the DB columns as the headers for the ListBox
dataList.ColumnCount = rs.FieldCount
dataList.Column(-1).WidthExpression = "100"
For i As Integer = 0 To rs.FieldCount - 1
dataList.Heading(i) = rs.IdxField(i + 1).Name
// Add the data from the table
While Not rs.EOF
For i As Integer = 0 To rs.FieldCount-1
dataList.Cell(dataList.LastIndex, i) = rs.IdxField(i + 1).StringValue