Item Property Example (VB)

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.

'BeginItemVB
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
'EndItemVB

Item Property Example (VB)

See Also