COLLECTION:  Recordset::Fields Collection

The Fields Collection is a collection of all of the Field objects associated with a specific Recordset object.

If you reference by name a Field object that does not exist, a new Field object with that name will be appended automatically to the Fields Collection. The Status property for this newly appended Field will be assigned a FieldStatusEnum value of adFieldPendingInsert. Further, if allowed by your provider, the Field will be created in the data source the next time you call the Update method.

A Record object can also have a Fields Collection.

The Fields Collection has two properties and six methods.
Count Property
The Count property returns a long value that is the number of items in the collection. The counting starts at zero. You can use this value to loop throught the collection by iterating from zero to the value of Count minus one.
Code (VBScript):
intCountNumber = objRecordset.Fields.Count
You can also use the VB/VBScript For Each ... Next statement.
Code (VBScript):
For Each objField In objRecordset.Fields
   ' place code here to manipulate each item in collection

Item Property
The Item property is used to return a specific member of the fields Collection. The Index parameter is a variant. It can be the named item or the position (ordinal) number.
Code (VBScript):
MyProperty = objRecordset.Fields.Item(5)
MyProperty = objRecordset.Fields(5)
MyProperty = objRecordset.Fields("Transaction DDL")
Append Method
The Append method is used to add (append) a Field object to the Fields Collection.

Using this method, you can both append and assign a value to the object at the same time. This is useful, because the Value property must first be set and an Update must have occured, before you can set any other properties. There are three data types for the Field object that cannot be appended to the Fields Collection. If you try to use adArray, adChapter, or adEmpty, an error will occur.

syntax: Fields.Append Name, Type, DefineSize, Attrib, FieldValue

This method has five optional parameters.

The optional Name is the unique name of the new Field object being appended to the collection.

The optional Type is one of the DataTypeEnum constants that defines the data type of the new Field.
DataTypeEnum Constants
Constant Value Description
adArray 0x2000 Combine with another data type to indicate that the other data type is an array
adBigInt 20 8-byte signed integer
adBinary 128 Binary
adBoolean 11 True or false Boolean
adBSTR 8 Null-terminated character string
adChapter 136 4-byte chapter value for a child recordset
adChar 129 String
adCurrency 6 Currency format
adDate 7 Number of days since 12/30/1899
adDBDate 133 YYYYMMDD date format
adDBTime 134 HHMMSS time format
adDBTimeStamp 135 YYYYMMDDHHMMSS date/time format
adDecimal 14 Number with fixed precision and scale
adDouble 5 Double precision floating-point
adEmpty 0 no value
adError 10 32-bit error code
adFileTime 64 Number of 100-nanosecond intervals since 1/1/1601
adGUID 72 Globally unique identifier
adIDispatch 9 Currently not supported by ADO
adInteger 3 4-byte signed integer
adIUnknown 13 Currently not supported by ADO
adLongVarBinary 205 Long binary value
adLongVarChar 201 Long string value
adLongVarWChar 203 Long Null-terminates string value
adNumeric 131 Number with fixed precision and scale
adPropVariant 138 PROPVARIANT automation
adSingle 4 Single-precision floating-point value
adSmallInt 2 2-byte signed integer
adTinyInt 16 1-byte signed integer
adUnsignedBigInt 21 8-byte unsigned integer
adUnsignedInt 19 4-byte unsigned integer
adUnsignedSmallInt 18 2-byte unsigned integer
adUnsignedTinyInt 17 1-byte unsigned integer
adUserDefined 132 User-defined variable
adVarBinary 204 Binary value
adVarChar 200 String
adVariant 12 Automation variant
adVarNumeric 139 Variable width exact numeric with signed scale
adVarWChar 202 Null-terminated Unicode character string
adWChar 130 Null-terminated Unicode character string

The optional DefinedSize parameter is a long value that is the size in bytes or characters of the new Field. When DefinedSize exceeds 255 bytes, the field is treated as having variable length columns.

The optional Attrib parameter is one of the FieldAttributeEnum constants that specify the attributes of the new Field.

FieldAttributeEnum Constants
Constant Value Description
adFldCacheDeferred 0x1000 Provider caches values and reads from cache
adFldFixed 0x10 Fixed-length data
adFldIsChapter 0x2000 Chapter value with specified child recordset
adFldIsCollection 0x40000 Collection of resources
adFldIsDefaultStream 0x20000 Contains default stream
adFldIsNullable 0x20 Accepts null values
adFldIsRowURL 0x10000 Contains URL to resource in data source
adFldKeyColumn 0x8000 Primary key or part of primary key
adFldLong 0x80 Long binary field and can use AppendChunk and GetChunk methods
adFldMayBeNull 0x40 Can read null values
adFldMayDefer 0x2 Values are not retrieved with whole record
adFldNegativeScale 0x4000 Can support negative scale values
adFldRowID 0x100 Contains a row identifier used only to ID the row
adFldRowVersion 0x200 Uses time/date to track updates
adFldUnknownUpdatable 0x8 Provider cannot determine if you can write to field
adFldUnspecified -1 Does not specify attributes
adFldUpdatable 0x4 Can write to field

The optional FieldValue parameter is a variant that is the value for the new Field. If this parameter is not provided, it will be set to null when the new Field is appended.

Code (VBScript):
objRecordset.Fields.Append "Age", adInteger
Delete Method
The Delete method designates that a specified Field object is to be deleted from the Fields Collection. You must call the Update method of the Fields Collection to make the deletion.

This method has one parameter.
Syntax: Fields.Delete Index

The Index parameter is either the name property or the ordinal position (index) in the collection of the Field object.

Code (VBScript):
objRecordset.Fields.Delete 3
Refresh Method
Although the Refresh method is used to update objects in a collection, this method has no effect on the Fields Collection of the Recordset object.

Copyright 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information