This example demonstrates how the Item property accesses members of a collection. The example opens the Authors table of the Pubs database with a parameterized command.
The parameter in the command issued against the database is accessed from the Command object's Parameters collection by index and name. The fields of the returned Recordset are then accessed from that object's Fields collection by index and name.
Public Sub Main()
ItemX
End Sub
Public Sub ItemX()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim fld As ADODB.Field
Dim ix As Integer
Dim limit As Long
Dim Column(0 To 8) As Variant
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command
'Set the array with the Authors table field (column) names
Column(0) = "au_id"
Column(1) = "au_lname"
Column(2) = "au_fname"
Column(3) = "phone"
Column(4) = "address"
Column(5) = "city"
Column(6) = "state"
Column(7) = "zip"
Column(8) = "contract"
cmd.CommandText = "SELECT * FROM Authors WHERE state = ?"
Set prm = cmd.CreateParameter("ItemXparm", adChar, adParamInput, 2, "CA")
cmd.Parameters.Append prm
cnn.Open "DSN=Pubs;Provider=MSDASQL; uid=sa;pwd=;"
cmd.ActiveConnection = cnn
'Connection and CommandType are omitted because a Command object is
'provided.
rst.Open cmd, , adOpenStatic, adLockReadOnly
Debug.Print "The Parameters collection accessed by index..."
Set prm = cmd.Parameters.Item(0)
Debug.Print "Parameter name = '"; prm.name; "', value = '"; prm.Value; _
"'"
Debug.Print
Debug.Print "The Parameters collection accessed by name..."
Set prm = cmd.Parameters.Item("ItemXparm")
Debug.Print "Parameter name = '"; prm.name; "', value = '"; prm.Value; _
"'"
Debug.Print
Debug.Print "The Fields collection accessed by index..."
rst.MoveFirst
limit = rst.Fields.Count - 1
For ix = 0 To limit
Set fld = rst.Fields.Item(ix)
Debug.Print "Field "; ix; ": Name = '"; fld.name; _
"', Value = '"; fld.Value; "'"
Next ix
Debug.Print
Debug.Print "The Fields collection accessed by name..."
rst.MoveFirst
For ix = 0 To 8
Set fld = rst.Fields.Item(Column(ix))
Debug.Print "Field name = '"; fld.name; "', Value = '"; fld.Value; "'"
Next ix
rst.Close
cnn.Close
End Sub