The ADO Object Model - The Other ADO Objects
(Page 3 of 9 )
The Field Object and the Fields Collection
A Field object contains details about a single field (or column) in a recordset. Such information includes the data type of the field, its width, and so on.
The Fields collection contains a reference to an ADO Field object for each field in a recordset, and there are two ways in which you can use this. The first is with existing recordsets, where you can examine each field, see its type, check its name, and so on. This is quite useful when you are unsure what exactly the recordset contains, and it can be useful in scripting when showing tables dynamically. Using the Fields collection allows you to find the name of each field. For example, in ASP script you could do this:
Response.Write "<TABLE>"
For Each objFld In objRec.Fields
Response.Write "<TR><TD>" & objFld.Name & "</TD></TR>"
Next
Response.Write "</TABLE>"
This creates an HTML table showing which fields are in the recordset objRec. In fact, using a similar method, you could easily create a simple script routine that accepts any recordset and builds an HTML table from it. You could use the Fields collection to supply the table header, showing the column names, and the recordset to fill in table details.
Another use of the Fields collection is for creating programmatic, or fabricated, recordsets. In the past, arrays often were used when it was necessary to store several related items. The trouble with arrays is that you must manage the array manually; you can’t add data to a new array element without first creating room in the array. Then came collections, to which you could add your own objects; but collections are also limited to objects and basic data types. So if you want to store some related items (records), but each item has different attributes (fields), you can use a fabricated recordset.
For example, imagine that you need to store details from a questionnaire. You could create a recordset like so:
rsNew.CursorLocation = adUseClient
rsNew.Fields.Append "Name", adVarChar, 50, adFldFixed
rsNew.Fields.Append "Age", adInteger, , adFldFixed
rsNew.Fields.Append "Question1", adBoolean, , adFldFixed
. . .
rsNew.Fields.Append "Comments", adVarChar, 255, adFldFixed
The good thing about this method is that you can save the contents of this disconnected recordset to a file, and you can use it as a parameter for functions in business objects (just like recordsets obtained from OLE DB Providers).
The Error Object and the Errors Collection An Error object contains details of a single error (or warning) returned from a data provider. Details include a description of the error, an error number, and the source object that raised the error. When a provider encounters an error or wishes to return information to the consumer, the details are placed in an Error object, and this is appended to the Errors collection.
The Errors collection holds all of the errors when a data provider generates an error or returns warnings in response to some failure. This is provided as a collection because a failure can generate more than one error. For example, the following statement generates two errors because X and Y are unknown columns:
objConn.Open "DSN=pubs"
Set objRec = objConn.Execute "SELECT X, Y FROM authors"
You could check the errors generated here by looping through the collection:
For Each objErr In conPubs.Errors
Print objErr.Description
Next
If you don’t have a predefined Connection object, you can use the ActiveConnection property of the Command or Recordset:
rsAuthors.Open "SELECT X, Y FROM authors", "DSN=pubs", _
adOpenStatic, adLockReadOnly, adCmdTable
For Each objErr In rsAuthors.ActiveConnection.Errors
Print objErr.Description
Next
As with the Fields collection, you could easily build a simple routine that centralizes error handling.
There is always an Errors collection, but it will be empty if no errors have occurred. Error objects remain in the collection until the next data access error occurs, when they are cleared and replaced by the new error details.
The Parameter Object and the Parameters Collection A Parameter object contains details of a single parameter for a Command object. Some of these details include the name, data type, direction, and value of the parameter. A parameter can be one of the following types:
- An input parameter, which supplies values to the statement represented by the Command object
- An output parameter, which supplies values from the statement represented by the Command object
- An input and output parameter
- A return value, which supplies the return value from the statement represented by the Command object
The usage of the various parameter types is discussed in more detail in Chapter 4.
The Parameters collection is unique to the Command object and is one way of allowing you to pass parameters into and out of stored queries and procedures. When using parameters, you have two options. First, you can ask the data provider to fetch the parameters from the data source, and the Parameters collection will be filled in automatically for you:
objCmd.Parameters.Refresh
The downside of this is that it requires a trip to the server, which may cause your program to perform poorly, especially if you do this often. However, it’s a great way of finding out what type and size the provider expects your parameters to be, and is very useful during development and debugging. You should note that not all OLE DB Providers (or ODBC Drivers) support the Refresh method.
The second option is to add the parameters to the collection manually, like so:
Set objParam = objCmd.CreateParameter ("ID", adInteger, _
adParamInput, 8, 147)
This creates an input parameter called ID, which is an integer (length 8 bytes) with a value of 147. Once created, the parameter can be appended to the Parameters collection:
objCmd.Parameters.Append objParam
Another good use of the Refresh method is, when first connecting to the data store, to cache the parameters details locally, perhaps in an array or in a user collection. This sort of approach allows you to write generic routines that process stored procedures.
The Property Object and the Properties Collection A Property object contains provider-specific information about an object. We know that objects have a fixed set of properties, but one of the fundamentals of OLE DB and ADO is that it can talk to a variety of data providers, and these providers often have different ways of working, or they support different properties. If all providers had to support a fixed functionality, then OLE DB and ADO wouldn’t really be very flexible; they would constitute the lowest common denominator of functionality. The solution is to provide a Property object for each provider-specific property, and a Properties collection to store all provider-specific properties.
For a Connection, the Properties collection contains a large amount of information about the facilities that the provider supports, such as the maximum number of columns in a SELECT statement, and what sort of outer join capabilities are supported. For a Recordset, there’s just as much information, ranging from the current locking level to the asynchronous capabilities supported by the provider.
You can examine or set the value of an individual property by just using its name to index into the collection. For example:
Print conPubs.Properties("Max Columns in SELECT")
To examine all the properties, you can use a simple loop:
For Each objProp In conPubs.Properties
Print objProp.Name
Next
You may well find that you never use this collection, unless you are writing an application that supports multiple data providers, in which case you may need to query the provider to examine what it supports.
 | This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today. Buy this book now! |
Next: The ADOX Object Model >>
More ASP.NET Articles
More By Apress Publishing
/ 15 
