Xojo Documentation Xojo Documentation

Class (inherits from Object)

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.

Properties

BOF EOF FieldCount

Methods

Close Field MoveNext ColumnType IdxField MovePrevious DeleteRecord MoveFirst RecordCount Edit MoveLast Update

Notes

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.

Record Navigation

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.

Data Source	Limitation
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

Examples

This code gets the data from the Team table and displays it in a ListBox:

// mDB is a previously connected database

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)
Return
End If

If data <> Nil Then
While Not data.EOF
DataList.AddRow(data.IdxField(1).StringValue, data.IdxField(2).StringValue, _
data.IdxField(3).StringValue, data.IdxField(4).StringValue)

data.MoveNext
Wend
data.Close
End If

The following method populates a ListBox with a RecordSet. It uses the Name and StringValue properties to obtain the fieldnames and values:

Sub PopulateListBox(dataList As Listbox, rs As RecordSet)
If rs Is Nil Then Return

// remove all rows
dataList.DeleteAllRows

// 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
Next

// Add the data from the table
While Not rs.EOF
dataList.AddRow("")

For i As Integer = 0 To rs.FieldCount-1
dataList.Cell(dataList.LastIndex, i) = rs.IdxField(i + 1).StringValue
Next

rs.MoveNext
Wend
End Sub

See Also

Database Class, DatabaseField, DatabaseRecord, ODBCDatabase, OracleDatabase, PostgreSQLDatabase, SQLiteDatabase classes.