RowSet

Class (inherits from Object)

New in 2019r2

A RowSet provides a way to access a group of Database rows (or records) from a query. Use the methods and properties of the RowSet class to navigate among this set of rows and view, edit, remove, and save changes to individual rows.

Properties

AfterLastRow

BeforeFirstRow

LastColumnIndex

Methods

Close	EditRow	RemoveRow
Column	MoveToFirstRow	RowCount
ColumnAt	MoveToLastRow	SaveRow
ColumnCount	MoveToNextRow
ColumnType	MoveToPreviousRow

Notes

If you are creating an application as a front-end in a multi-user environment, keep in mind the following. The EditRow method will attempt to lock the current row to other users. If a user tries to access a locked row, a DatabaseException is raised. You can then check its Error and Message properties for details.

A RowSet is closed when it goes out of scope.

If you try to use a RowSet after it has been closed, an UnsupportedOperationException is raised.

The MoveToNextRow method unlocks the current row if you previously locked it with the EditRow method. Previously locked rows are also unlocked by the Close method of the class and the Close method of the Database class.

Record Navigation

Iterator

You can iterate through a RowSet using For Each and a DatabaseRow. This lets you get at the column values of the row but does not let you make any changes to the row (if you do then a DatabaseException is raised).

// db is a valid connection to a SQLite database
Var rows As RowSet
Var postalCode As String = "12345"
Try
rows = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=?", postalCode)
If rows <> Nil Then
For Each row As DatabaseRow In rows
ListBox1.AddRow(row.Column("Name").StringValue)
Next
rows.Close
End If
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try

Move Methods

Not all data sources support all four record navigation methods, MoveToFirstRow, MoveToNextRow, MoveToPreviousRow, MoveToLastRow. The limitations of some of the data sources are presented in the following table.

Data Source	Limitation
SQLiteDatabase	Supports all row navigation methods
Oracle	Supports all row navigation methods
ODBC	Supports all row navigation methods
mySQL	Supports only MoveToNextRow method
MS SQL Server	Supports only MoveToNextRow method
PostgreSQL	Supports only MoveToNextRow method

Examples

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

// mDB is a previously connected database

Var sql As String
sql = "SELECT * FROM Team"

Var results As RowSet
Try
results = mDB.SelectSQL(sql)
Catch error As DatabaseException
MsgBox("DB Error: " + error.Message)
Return
End Try

If results <> Nil Then
For Each row As DatabaseRow In results
DataList.AddRow(row.ColumnAt(0).StringValue, row.ColumnAt(1).StringValue, _
row.ColumnAt(2).StringValue, row.ColumnAt(3).StringValue)
Next
results.Close
End If

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

// mDB is a previously connected database

Var sql As String
sql = "SELECT * FROM Team"

Var results As RowSet
Try
results = mDB.SelectSQL(sql)
Catch error As DatabaseException
MsgBox("DB Error: " + error.Message)
Return
End Try

If results <> Nil Then
While Not results.AfterLastRow
DataList.AddRow(results.ColumnAt(0).StringValue, results.ColumnAt(1).StringValue, _
results.ColumnAt(2).StringValue, results.ColumnAt(3).StringValue)

results.MoveToNextRow
Wend
results.Close
End If

The following method populates a ListBox with a RowSet. It uses the Name and StringValue properties to obtain the column names and values:

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

// remove all rows
dataList.RemoveAllRows

// Add the DB columns as the headers for the ListBox
dataList.ColumnCount = rs.ColumnCount
dataList.ColumnAt(-1).WidthExpression = "100"
For i As Integer = 0 To rs.LastColumnIndex
dataList.Heading(i) = rs.ColumnAt(i).Name
Next

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

For i As Integer = 0 To rs.LastColumnIndex
dataList.CellValueAt(dataList.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
Next

rs.MoveToNextRow
Wend
End Sub