` | | | | ## Property descriptions

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

forsearch

RecordSet.BOF **BOF** As `Boolean` Returns `True` when the RecordSet is before the first row or the RecordSet is empty. This property is read-only. A new RecordSet is always at the first row (if any rows were returned). The only way to set the RecordSet to be before the first row is to use `RowSet`, which is not supported by all data sources. This moves the RecordSet pointer so that BOF is True: ``` xojo Dim rs As RecordSet rs = db.SQLSelect("SELECT * FROM data") If rs <> Nil Then rs.MovePrevious If rs.BOF Then MsgBox("BOF!") End If ```

forsearch

RecordSet.EOF **EOF** As `Boolean` `True` when the record pointer is at the end of the set of records. This property is read-only. The following method populates a `ListBox` with a \`RecordSet\`: ``` xojo Sub PopulateListBox(dataList As Listbox, rs As RecordSet) If rs Is Nil Then Return ' set up listbox state for population dataList.DeleteAllRows dataList.Columncount = rs.Fieldcount ' Add the DB columns as the heades 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 ```

forsearch

RecordSet.FieldCount **FieldCount** As `Integer` The number of fields in the RecordSet. This property is read-only. The following method populates a `ListBox` with a RecordSet. Is uses FieldCount to create the header for the ListBox with the names of the columns in the table: ``` xojo Sub PopulateListBox(dataList As Listbox, rs As RecordSet) If rs Is Nil Then Return ' set up listbox state for population 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 ``` ## Method descriptions

forsearch

RecordSet.Close **Close** Closes an open RecordSet. When the RecordSet goes out of scope, it is automatically closed. If you try to use a RecordSet after it has been closed, an `UnsupportedOperationException` is raised. The following method populates a `ListBox` with a RecordSet and closes the RecordSet when finished: ``` xojo Sub PopulateListBox(dataList As Listbox, rs As RecordSet) If rs Is Nil Then Return ' set up listbox state for population dataList.DeleteAllRows dataList.Columncount = rs.Fieldcount ' Add the DB columns as the heades 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 rs.Close End Sub ```

forsearch

RecordSet.ColumnType **ColumnType**(Index As `Integer`) As `Integer` Returns an integer value for the data type of the passed column. The *index* parameter is 0-based. The following gets the columntype of the *i*th column in an existing RecordSet. ``` xojo Dim colType As Integer Dim stringValue As String For i As Integer = 0 To rs.FieldCount - 1 colType = rs.ColumnType(i) ' ColumnType is 0-based If colType = 5 Or colType = 18 Then ' The column is a String stringValue = rs.IdxField(i + 1).StringValue ' IdxField is 1-based End If Next ```

forsearch

RecordSet.DeleteRecord **DeleteRecord** Deletes the current record in the RecordSet. ``` xojo ' Delete the current record in an existing RecordSet ' db is an existing database connection rs.DeleteRecord If db.Error Then MsgBox("DB Error: " + db.ErrorMessage) End If ```

forsearch

RecordSet.Edit **Edit** Call Edit prior to performing modifications to the current record. Be sure the SQL incudes the primary key column (or columns) so that you can update the table after making changes. Changes are only saved to the DB when Update is called. Set the string value of a column in a \`RecordSet\`: ``` xojo ' rs is a RecordSet with a string column called "ProductName": rs.Edit If Not db.Error Then rs.Field("ProductName").StringValue = "Generic Widgets" rs.Update If db.Error Then MsgBox("DB Error: " + db.ErrorMessage) End If Else MsgBox("DB Error: " + db.ErrorMessage) End If ```

forsearch

RecordSet.Field **Field**(Name As `String`) As `DatabaseColumn` Returns a `DatabaseColumn` containing information about the column (specified by *Name*) from the current position in the RecordSet. Get the string value of a column in a \`RecordSet\`: ``` xojo ' rs is a RecordSet with a string column called "ProductName" Dim productName As String productName = rs.Field("ProductName").StringValue ``` Set the string value of a column in a \`RecordSet\`: ``` xojo ' rs is a RecordSet with a string column called "ProductName": rs.Edit rs.Field("ProductName").StringValue = "Generic Widgets" rs.Update ```

forsearch

RecordSet.IdxField **IdxField**(Index As `Integer`) As `DatabaseColumn` Returns a `DatabaseColumn` containing information about the column (specified by *index*, which is 1-based) from the current position in the RecordSet. If the RecordSet has no rows, the returned DatabaseField only contains the Name of the column.

Note

The IdxField method is 1-based, not 0-based.

Get the string value of a column in a \`RecordSet\`: ``` xojo ' rs is a RecordSet; column 1 (the first column) is a string column Dim productName As String productName = rs.IdxField(1).StringValue ``` Changes the string value of a column in a \`RecordSet\`: ``` xojo ' rs is a RecordSet; column 1 (the first column) is a string column rs.Edit rs.IdxField(1).StringValue = "Generic Widgets" rs.Update ```

forsearch

RecordSet.MoveFirst **MoveFirst** Moves the record pointer to the first record in the RecordSet. Currently only supported by these databases: \* `SQLiteDatabase` \* `ODBCDatabase`, although not all ODBC drivers implement this This example moves the record pointer to the first record in the RecordSet. ``` xojo ' move to the first record rs.MoveFirst ```

forsearch

RecordSet.MoveLast **MoveLast** Moves the record pointer to the last record in the RecordSet. Currently only supported by these databases: \* `SQLiteDatabase` \* `ODBCDatabase`, although not all ODBC drivers implement this This example moves the record pointer to the first record in the RecordSet. ``` xojo ' move to the last record rs.MoveLast ```

forsearch

RecordSet.MoveNext **MoveNext** Moves to the next record/row in the RecordSet. After the last row, `RowSet` is True. The following method populates a `ListBox` with a RecordSet. It uses the Name and StringValue properties to obtain the fieldnames and values: ``` xojo Sub PopulateListBox(dataList As Listbox, rs As RecordSet) If rs Is Nil Then Return ' set up listbox state for population dataList.DeleteAllRows dataList.Columncount = rs.Fieldcount ' Add the DB columns as the heades 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 rs.Close End Sub ```

forsearch

RecordSet.MovePrevious **MovePrevious** Moves the record pointer to the previous record in the RecordSet. Currently only supported by these databases: \* `SQLiteDatabase` \* `ODBCDatabase`, although not all ODBC drivers implement this A RecordSet is populated in memory when you call the `SQLSelect` method. If you use MovePrevious to go back to prior records (including those that you may have changed using the `Edit` method), then you will get the values that were originally populated, not the changed values. This example moves the record pointer to the previous record in the RecordSet. ``` xojo ' move to the previous record/row rs.MovePrevious ```

forsearch

RecordSet.RecordCount **RecordCount** As `Integer` The number of records in the RecordSet. Currently only supported by these databases: \* `SQLiteDatabase` \* `PostgreSQLDatabase` \* `ODBCDatabase`, although not all ODBC drivers implement this For databases that do not support this function, RecordCount returns -1. The following method populates a `ListBox` with a RecordSet, first displaying the record count if it can: ``` xojo Sub PopulateListBox(dataList As Listbox, rs As RecordSet) If rs Is Nil Then Return ' set up listbox state for population dataList.DeleteAllRows dataList.Columncount = rs.Fieldcount ' Add the DB columns as the heades 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 rs.Close End Sub ```

forsearch

RecordSet.Update **Update** Call to update the RecordSet to reflect changes to the record the record pointer is pointing to. Set the string value of a column in a \`RecordSet\`: ``` xojo ' rs is a RecordSet with a string column called "ProductName": rs.Edit If Not db.Error Then rs.Field("ProductName").StringValue = "Generic Widgets" rs.Update If db.Error Then MsgBox("DB Error: " + db.ErrorMessage) End If Else MsgBox("DB Error: " + db.ErrorMessage) End If ``` ## 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 | ## Sample code This code gets the data from the Team table and displays it in a ListBox: ``` xojo ' 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: ``` xojo 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 ``` ## Compatibility All project types on all supported operating systems. ## See also `Object` parent class; `Database`, `DatabaseColumn`, `DatabaseRow`, `ODBCDatabase`, `PostgreSQLDatabase`, `SQLiteDatabase` classes.