Bookmarking and Moving through Records with ADO - Accessing data fields and rows in a recordset
(Page 2 of 4 )
The recordset returned by a query is the result of the query as shown in the next picture. You may access the fields by using the recordset's Fields collection and going through each of them as shown in the code. For accessing rows you may depend on the fact that the recordset opens with the current record with Absolute Position = 1 . Now you can use MoveNext to go the second record, and so on forward until you reach the last record. You will end the process when you reach the EOF. For all these positions you will collect the fields as discussed earlier.
The fields of the recordset object
The following code shows how you access the fields collection using an index. The picture that follows shows some of the values of the field (synonymous with a column). After you open a recordset and run this code, you will be accessing the first row. If you want to get the fields for, say, Absolute Position=2 , just insert a rst.MoveNext after you open the recordset and before you go through the For...Next block.
Private Sub Command9_Click()
Dim strsql As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With
Debug.Print rst.Fields.Count
strfld = ""
For i = 0 To rst.Fields.Count - 1
strfld = strfld + "Item.Value: " & rst.Fields.Item(i).Value & vbCrLf
strfld = strfld + "Item.Type: " & rst.Fields.Item(i).Type & vbCrLf
strfld = strfld + "Item.Name: " & rst.Fields.Item(i).Name & vbCrLf
strfld = strfld + "Item.Definedsize: " & rst.Fields.Item(i).
DefinedSize & vbCrLf
strfld = strfld + "-----------------------------------" & vbCrLf
Next
Text10.SetFocus
Text10.Text = strfld
rst.Close
cnn.Close
End Sub
The rows of the recordset
You will implement the above code from Absolute Position = 1 up to EOF using the following code in a DO LOOP.
Private Sub Command12_Click()
Dim strsql As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With
strrows = ""
Do While rst.EOF = False
strrows = strrows + rst.Fields.Item(1) & " | " & rst.Fields.Item(2)
& vbCrLf
strrows = strrows + "---------------------------" & vbCrLf
Text10.SetFocus
Text10.Text = strrows
rst.MoveNext
Loop
End Sub
In the above code, the various items of the fields collection (here only two items were collected) are accessed inside a Do Loop and at the end of the record you move the pointer to the next record using a MoveNext command. Again for display, you will gather them in a string and dump the string to a text box as shown in the following picture.
Next: Moving through the Recordset and Bookmarking >>
More ASP.NET Articles
More By Jayaram Krishnaswamy
/ 8 
