ADO Field Object Properties

[Previous] [Next]

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

ActualSize Property

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.

Attributes Property

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.

DataFormat Property

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.

DefinedSize Property

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.

Name Property

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 Queries

If 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.

NumericScale Property

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.

OriginalValue Property

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.

Precision Property

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.

Properties Collection

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.

Type Property

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.

UnderlyingValue 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.

Value Property

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.



Programming ADO
Programming MicrosoftВ® ADO.NET 2.0 Core Reference
ISBN: B002ECEFQM
EAN: N/A
Year: 2000
Pages: 131
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net