Let's take a closer look at the properties of the Field object. This next table offers a preliminary description of each property.
Field Object Properties | ||
---|---|---|
Property Name | Data Type | Description |
ActualSize | Long | Returns the actual size of a field's value |
Attributes | Long | Describes characteristics of the field |
DataFormat | Object | Can be used to format your data |
DefinedSize | Long | Returns the defined size for a field |
Name | String | Contains the name of the field |
NumericScale | Byte | Indicates the numeric scale for numeric data |
OriginalValue | Variant | Contains the original value for the field |
Precision | Byte | Indicates the precision for numeric data |
Properties | Collection of Property objects | Collection of dynamic properties |
Type | Byte | Returns the data type for the field |
UnderlyingValue | Variant | Indicates the most recently retrieved value from the database for the field |
Value | Variant | Contains the current value for the field |
The ActualSize property tells you how much data actually exists in your field. This property is read-only and is primarily intended for variable length strings or binary data. For example, if you define an Address field to hold up to 64 characters, you can check the ActualSize property to find out how much data actually exists in the field without having to examine the contents of the Value property. The ActualSize property is also helpful when dealing with long variable and binary data types.
As of ADO 2.1, it is possible for the ActualSize property to be larger than the DefinedSize property. If you're using a Unicode data type, the DefinedSize property will return the number of Unicode characters that the field can hold. The ActualSize property will return the length of that string in bytes. Thus, the ActualSize property value is twice the number of Unicode characters.
The Attributes property contains metadata about the field, such as whether it's updatable or nullable. The property is a bitmask and is the sum of one or more values from FieldAttributeEnum, as shown in the following table.
FieldAttributeEnum Values | ||
---|---|---|
Constant | Value | Description |
adFldMayDefer | 2 | The data is retrieved separately from the other fields in your Recordset. This behavior is common for long text or binary fields with server-side Recordsets that support scrolling. |
adFldUpdatable | 4 | The field is updatable. |
adFldUnknownUpdatable | 8 | The provider cannot determine if the data is updatable. |
adFldFixed | 16 | The field contains fixed-length data such as an integer. |
adFldIsNullable | 32 | The field accepts Null values. |
adFldMayBeNull | 64 | The field can contain Null values. |
adFldLong | 128 | The field contains long string or binary data. You can use the AppendChunk and GetChunk methods. |
adFldRowID | 256 | The field contains a row identifier. |
adFldRowVersion | 512 | The field contains some kind of time or date stamp used to track updates. |
adFldCacheDeferred | 4096 | The provider caches field values, and subsequent reads are done from the cache. |
adFldIsChapter | 8192 | The Field corresponds to a chapter in a hierarchical Recordset. |
adFldNegativeScale | 16384 | The field has a negative value for its numeric scale. (For example, 12,000 with a numeric scale of -3 would be stored as 12.) |
adFldKeyColumn | 32768 | The field is part of the primary key. |
adFldIsRowURL | 65536 | For fields exposed by the Record object; the value of the field corresponds to the location of the Record. |
adFldIsDefaultStream | 131072 | For fields exposed by the Record object; the field contains the default stream of data for the Record. |
adFldIsCollection | 262144 | For fields exposed by the Record object; the field contains a collection of data. |
You can use the DataFormat property on a field to control how the data is retrieved or displayed. This feature is fairly well explained in the Visual Basic documentation. The DataFormat property is also exposed on most Visual Basic controls that you can bind to a field, such as a TextBox. The property is basically a pointer to a DataFormat object, which extends the functionality available in the Visual Basic Format function.
Possibly the best use of this feature is to display True, False, and Null values. In order to use the DataFormat property on a Field object, you must reference the Microsoft Data Formatting Object Library (MSStdFmt.dll) and use the StdDataFormat object. The following code uses a StdDataFormat object on a bit field in the Products table of the Northwind database:
Dim fmtDiscontinued As StdFormat.StdDataFormat strSQL = "SELECT * FROM Products" rsProducts.Open strSQL, cnNorthwind, adOpenStatic Set fmtDiscontinued = New StdFormat.StdDataFormat fmtDiscontinued.Type = fmtBoolean fmtDiscontinued.TrueValue = "Discontinued" fmtDiscontinued.FalseValue = "Currently Produced" Set rsProducts!Discontinued.DataFormat = fmtDiscontinued |
Checking the Value property of the Discontinued field will now return "Currently Produced" or "Discontinued" instead of True/False or 0/1.
The DefinedSize property contains the defined size for a field. This property is a long data type and is read-only. Generally, this information is helpful when you're using a variable-length string or binary field. The ADO documentation implies that the property returns the size of the field in bytes.
As of ADO 2.1, the DefinedSize property returns the number of characters a variable length string field supports rather than the number of bytes. Thus, both ANSI (single-byte) and Unicode (double-byte) string fields that accept the same number of their respective characters have the same value for the DefinedSize property. This behavior is different from that of the related property, ActualSize. With that property, the returned value is the number of bytes in the string, rather than the number of characters.
The Name property contains the name of the Field object in string format. Generally, this property will refer to the name of the column in the table that you're querying. With most OLE DB providers, you can use an AS clause to choose a different name if you don't want to use the column name.
Using the Name Property with Join QueriesIf you're using a Recordset based on a join query, the behavior of the Name property will depend on how the OLE DB provider or ODBC driver returns data about the columns in the results of your query. Using ADO 2.5 and the OLE DB Provider For SQL Server as a test, I submitted a join query that retrieved data from the Customers and Orders tables, basing the relationship on the CustomerID field:
strSQL = "SELECT * FROM Customers, Orders WHERE " & _ "Customers.CustomerID = Orders.CustomerID"I found that as I examined the Name property of each Field object, there were two fields with the same name—CustomerID. Keep in mind that you'll see the same behavior with the SQL Server query tools. I then used the Jet 4.0 OLE DB Provider, and the two fields came back with different values for the Name property—Customers.CustomerID and Orders.CustomerID. Compare this to using RDO with SQL Server, which returned two rdoColumn objects of different names—CustomerID and CustomerID1.
If you're going to use Recordsets based on a join, test to see whether your query returns multiple Field objects with the same value for the Name property. If that's the case and it poses a problem, your best bet is to use AS clauses in your query to ensure unique column names in the results.
Used for numeric data, the NumericScale property returns the scale of the numeric values for the field. This property contains a byte value and indicates the number of digits to the right of the decimal point that you can store in the field. For example, if you used a numeric data type in your database to store monetary units and did not store fractions of a cent, then NumericScale would be 2.
It's possible for a database to use a negative value for a numeric scale, though I'm not aware of any databases or providers that implement this functionality. There is a value in FieldAttributeEnum called adFldNegativeScale that would (we hope) be used to indicate that a field has a negative value for its numeric scale. This could come in handy for large numbers with limited precision. For example, if you wanted to keep track of the past lottery jackpots and decrease the amount of data to store, you could store 5 with a NumericScale of -6 to represent $5 million.
When you're editing the Recordset, the OriginalValue property contains the data for the Value property of the Field object before it was edited. This property is read-only and returns a Variant.
If you're using a LockType of adLockOptimistic or adLockPessimistic, the OriginalValue property contains the value for the field prior to editing. If you call CancelUpdate while editing the current record, the Value property on each field will be set to the OriginalValue property. Once you modify the value of a field and successfully update that record with the Update method on the Recordset object, the OriginalValue property on the Field is set to the newly submitted value.
In batch optimistic update mode, using CancelUpdate to cancel changes to a record while that record's EditMode is adEditInProgress resets the Value property on each Field to the OriginalValue property. Because calling the Update method in this mode caches only the pending changes in the Recordset rather than actually modifying the data in your database, the OriginalValue property is not updated until you call UpdateBatch. After you've called the Update method, canceling the pending changes in a record by calling CancelBatch will reset the Value property on the Field objects to the OriginalValue property.
We'll talk more about this property in Chapter 10, when we examine how the ADO Cursor Engine submits changes to your database.
The Precision property returns the maximum number of digits that the field can store, including digits to the right of the decimal point. This property is read-only and contains a byte value.
Like most ADO objects, the Field object exposes a Properties collection. The contents of the collection will depend on the OLE DB provider, CursorLocation, CursorType, and LockType. We'll discuss many of the dynamic properties specific to the ADO Cursor Engine in Chapters 10 and 12 of this book.
The Type property on a Field object contains a byte value from DataTypeEnum. For more information on the contents of DataTypeEnum, please see the ADO documentation in the Microsoft Data Access Components (MDAC) SDK and Platform SDK. This property is read-only. See the discussion of creatable Recordsets in the section "Append Method" for more information on how to use the Type property.
You can use the UnderlyingValue property to check the current field's value in your database. This property is not available for read-only Recordsets. With some OLE DB providers, this property will contain live data when using a server-side Recordset. If you're using a client-side Recordset, this data is not live and can be retrieved by using the Resync method. The UnderlyingValue property can also be updated by a failed optimistic update attempt on a client-side Recordset if you're using the Update Resync dynamic property with the adResyncConflicts constant.
The UnderlyingValue property is read-only and contains a Variant. We'll discuss this property in more depth when we discuss conflict detection in Chapter 12.
The data you retrieved from your query is stored in the Value property. This property is the default property on the Field object, so you generally do not need to explicitly call the Value property when you want to examine the contents of a field. The Value property contains a Variant and is read/write only if your Recordset supports updates.
ADO supports setting and returning long string and binary data with the Value property.