Class

RecordSet

Warning

This item was deprecated in version 2019r2. Please use RowSet as a replacement.

Description

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

Name

Type

Read-Only

Shared

BOF

Boolean

✓

EOF

Boolean

✓

FieldCount

Integer

✓

Property descriptions

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:

Dim rs As RecordSet
rs = db.SQLSelect("SELECT * FROM data")
If rs <> Nil Then
  rs.MovePrevious
  If rs.BOF Then MsgBox("BOF!")
End If

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:

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

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:

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

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:

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

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.

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

RecordSet.DeleteRecord

DeleteRecord

Deletes the current record in the RecordSet.

' 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

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:

' 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

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:

' 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:

' rs is a RecordSet with a string column called "ProductName":
rs.Edit
rs.Field("ProductName").StringValue = "Generic Widgets"
rs.Update

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:

' 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:

' rs is a RecordSet; column 1 (the first column) is a string column
rs.Edit
rs.IdxField(1).StringValue = "Generic Widgets"
rs.Update

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.

' move to the first record
rs.MoveFirst

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.

' move to the last record
rs.MoveLast

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:

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

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.

' move to the previous record/row
rs.MovePrevious

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:

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

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:

' 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:

' 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

Compatibility

All project types on all supported operating systems.