RDS DataControl Object

[Previous] [Next]

The RDS DataControl object is similar to the data controls included with Microsoft Visual Basic except that it's not a visible control. It's simply an object, just like the ADO Connection and Recordset objects.

The DataControl object's most compelling feature is that it can greatly simplify the process of querying a database on an internal network and sending the results, in the form of a Recordset object, to a client application anywhere on the Internet.

The DataControl object was designed primarily for use on web pages. Microsoft Internet Explorer supports binding controls to the DataControl. You can use the DataControl object on a web page or in a client application written in Visual Basic, Microsoft Visual C++, and other languages that support COM objects, but you can't bind controls directly to the DataControl in the forms packages of these languages.

RDS DataControl Object Properties

Let's take a closer look at the properties exposed by the DataControl object, as described in the following table.

Properties of the RDS DataControl Object
Property Name	Data Type	Description
Connect	String	Specifies the string the object uses to connect to a database.
DataSpace	String	Ignore; exists for backward compatibility. We won't discuss this property.
ExecuteOptions	Integer	Controls whether the query is run asynchronously.
FetchOptions	Integer	Controls whether the results of a query are fetched asynchronously.
FilterColumn	String	Specifies the name of the field in the recordset on which you want to apply a filter.
FilterCriterion	String	Specifies the filter criterion (<, >, =, and so on).
FilterValue	String	Specifies the value you want to use in the filter.
Handler	String	Indicates the name of the custom handler to use when connecting.
InternetTimeout	Long	Specifies the time, in milliseconds, that RDS should wait before timing out.
ReadyState	Long	Indicates the current state of the query.
Recordset	Recordset object	Contains the results of a query.
Server	String	Specifies the name of the IIS server and communications protocol to use.
SortColumn	String	Specifies the name of the field on which you choose to sort your data.
SortDirection	Boolean	Specifies the direction to use for the sort.
SourceRecordset	Recordset object	Allows you to specify the Recordset whose changes you want to submit to the database.
SQL	String	Specifies the query string you want to submit to your database.
URL	String	Specifies the URL for an ASP page that will return a Recordset.

Connect property

The Connect property on the RDS DataControl object is similar to the ConnectionString property on the ADO Connection object. To use this property, supply the connection string you want to use to connect to your database. You can use the Connect property in conjunction with an RDS handler, which we'll discuss later in the chapter.

Keep in mind that the Connect property tells the RDS server components how to connect to your database. If you want to describe how the DataControl will connect to a machine running IIS, use the Server property.

ExecuteOptions property

The ExecuteOptions property accepts an integer to specify whether the query should be submitted synchronously or asynchronously. You can use either value from ADCExecuteOptionEnum, listed in the following table.

ADCExecuteOptionEnum Values
Constant	Value	Description
adcExecSync	1	Submits a query synchronously
adcExecAsync	2	Default; submits a query asynchronously

FetchOptions property

The FetchOptions property accepts an integer that controls how the results of a query are fetched. You can use a value from ADCFetchOptionEnum, listed in this next table.

ADCFetchOptionEnum Values
Constant	Value	Description
adcFetchUpFront	1	Fetches all records in a recordset before returning control to your code.
adcFetchBackground	2	Fetches the records, beyond the initial set specified by the Recordset's CacheSize property, in the background. After the initial set of records has been fetched, control is returned to your application. If you attempt to access a record that hasn't been fetched, your code will be blocked until that record is fetched. This behavior is similar to that of ADO's adAsyncFetch constant in the ExecuteOptionEnum enumeration.
adcFetchAsync	3	Default; fetches the records, beyond the initial set specified by the Recordset's CacheSize property, in the background. After the initial set of records has been fetched, control is returned to your application. If you attempt to access a record that hasn't been fetched, your code will not be blocked. This behavior is similar to that of ADO's adAsyncFetchNonBlocking constant in the ExecuteOptionEnum enumeration.

FilterColumn, FilterCriterion, and FilterValue properties

You can use these three properties together to apply a string-based filter to the results of a query. Each property accepts a string and can be used as shown here:

 DataControl.FilterColumn = "City" DataControl.FilterCriterion = "=" DataControl.FilterValue = "Seattle" 

The FilterCriterion property accepts =, >, <, >=, <=, and <>. It does not accept LIKE. You cannot use wildcards with the FilterValue property.

NOTE

---

If you change the value of the FilterColumn, FilterCriterion, or FilterValue property, the changes won't be applied to the Recordset returned by the DataControl object until you call the DataControl's Reset method.

If you want to clear the current filter, set the FilterColumn or FilterCriterion property to an empty string and then call the Reset method with a value of False in the fRefilter parameter.

Handler property

The Handler property accepts a string to specify the name of the RDS handler to use to enforce security on a server. This property defaults to an empty string.

InternetTimeout property

The InternetTimeout property accepts a long integer value to control the number of milliseconds that RDS will wait before timing out an attempt to connect to an IIS server. The property defaults to 300,000 milliseconds (5 minutes). The InternetTimeout property affects only connection attempts using HTTP or HTTPS.

ReadyState property

The ReadyState property on the RDS DataControl object is similar to the State property on the ADO Connection and Recordset objects. You can use the ReadyState property to determine the current state of a query. The ReadyState property returns a long integer value from ADCReadyStateEnum, as described in the following table.

ADCReadyStateEnum Values
Constant	Value	Description
adcReadyStateLoaded	2	The current query is still executing.
adcReadyStateInteractive	3	RDS has fetched the initial set of records from a query, but there are more records to fetch.
adcReadyStateComplete	4	The query has completed, and all records have been fetched.

The DataControl object's onReadyStateChange event will fire when the value of the ReadyState property changes.

Recordset and SourceRecordset properties

The Recordset property on the DataControl object contains the results of your query. This property is read-only:

 Set MyRecordsetObject = DataControl.Recordset 

If you've been working with a separate Recordset and want to use it with the DataControl, use the SourceRecordset property, which is write-only:

 Set DataControl.SourceRecordset = MyRecordsetObject 

Server property

The Server property controls which server the DataControl object will connect to in order to interface with the RDS components that run in IIS on that machine. The setting for this property also controls the protocol (such as HTTP or DCOM) that RDS will use to communicate with its server-side components:

 'Connect to internal server through DCOM. DataControl.Server = "MyWebServer" 'Connect to internal server through HTTP. DataControl.Server = "http://MyWebServer" 'Connect over the Internet through HTTP. DataControl.Server = "http://www.microsoft.com" 

NOTE

---

The Server property does not specify the server where your database is located. That information should be provided in the Connect property (described earlier in this section).

SortColumn and SortDirection properties

To sort the results of a query based on a particular field, set the SortColumn property to the name of that field. The SortDirection property accepts a Boolean value to specify whether RDS should sort the data in ascending or descending order. By default, this property is set to True, which indicates that RDS should sort the data in ascending order. A setting of False specifies a sort in descending order.

NOTE

---

If you change the value of the SortColumn and SortDirection properties, you must call the DataControl object's Reset method before they will affect the Recordset returned by the DataControl.

SQL property

The SQL property contains the query that RDS will submit to your database. This property accepts a string and is similar to the CommandText property of the Command object and the Source property of the Recordset object.

URL property

The URL property is a string that can be set to a valid URL. Typically, the URL property is set to a URL for an ASP page that returns a recordset in its Response object. The URL property was added in RDS 2.5, allowing you to use the DataControl with XML data.

RDS DataControl Object Methods

Now let's examine the methods of the DataControl object, which are shown in the following table.

Methods of the RDS DataControl Object
Method Name	Description
Cancel	Cancels an asynchronous operation
CancelUpdate	Cancels the pending changes for a record
CreateRecordset	Creates a new, empty Recordset object with the structure you request
Refresh	Connects to an IIS server, executes a query, and retrieves the results
Reset	Resets the sort or filter on a Recordset object
SubmitChanges	Submits the pending changes in a Recordset object to your database

Cancel method

Use the Cancel method to cancel an asynchronous action on the DataControl object. If you call the Cancel method while RDS is still executing a query, the DataControl will return without generating an error. The onReadyStateChange event will fire, the ReadyState property will return adcReadyStateComplete, and the Recordset object will contain a null pointer.

You can also call the Cancel method to terminate the asynchronous fetching of results for a query. Using the Cancel method in this fashion still won't generate an error. The onReadyStateChange event will fire, the ReadyState property will return adcReadyStateComplete, and the Recordset property will return a valid Recordset object containing the records that RDS fetched before you called the Cancel method.

CancelUpdate method

Use the CancelUpdate method to cancel pending changes in the Recordset.

When you call the CancelUpdate method, all pending changes in the Recordset are discarded. The Recordset will then contain the data retrieved the last time you called the DataControl object's Refresh method.

CreateRecordset method

You can use the CreateRecordset method to generate a new, empty Recordset. CreateRecordset accepts a single parameter:

• varColumnInfos This parameter holds a Variant array that contains information about the structure of the Recordset that you want to create.

You can generate this parameter in one of two ways. The first technique is easier to follow but requires more code. The second is easier to write but can be a bit confusing until you understand the structure of the parameter. Let's take a look at the first technique:

 'Create the fixed-length Variant arrays. Dim aColumns(2), c0(3), c1(3), c2(3) 'The c0 array represents the first (or 0th) column. 'The initial entry in the array corresponds to ' the column's name and accepts a string. c0(0) = "CustomerID" 'The second entry corresponds to the column's data type. 'CreateRecordset requires that this information be stored in an ' integer. ADO uses a long integer for its data types. If you're using ' the ADO constants rather than the explicit value, you'll need to use ' CInt. c0(1) = CInt(adInteger) 'The third entry corresponds to the column's size. If you're using data ' types that handle various sizes (char, varchar, binary, varbinary), ' specify the size here. Otherwise, use -1. c0(2) = -1 'The fourth and final entry controls whether the column accepts ' null values. c0(3) = False 'Generate the arrays for the other two columns. c1(0) = "CustomerName" c1(1) = CInt(adVarChar) c1(2) = 64 c1(3) = False c2(0) = "BalanceDue" c2(1) = CInt(adCurrency) c2(2) = -1 c2(3) = False 'Store each column array as an element in the aColumns array. aColumns(0) = c0 aColumns(1) = c1 aColumns(2) = c2 'Now call CreateRecordset. Set Recordset = DataControl.CreateRecordset(aColumns) 

Once you have a handle on the structure of the varColumnInfos parameter, you can use the second technique, which takes advantage of dynamic arrays and the Array function of Visual Basic for Applications (VBA). With the second technique, you use the Array function to build and populate dynamic Variant arrays—rather than the static Variant arrays used in the first technique.

 'Create the structures as dynamic Variant arrays. Dim aColumns(), c0(), c1(), c2() 'Create each column array using the Array function, specifying ' the column's name, data type, size, and whether it accepts null ' values. c0 = Array("CustomerID", CInt(adInteger), -1, False) c1 = Array("CustomerName", CInt(adVarChar), 64, False) c2 = Array("BalDue", CInt(adCurrency), -1, False) 'Create the aColumns array, supplying each of the column arrays. aColumns = Array(c0, c1, c2) Set Recordset = DataControl.CreateRecordset(aColumns) 

This feature is similar to ADO's creatable Recordsets that allow you to populate the Fields collection using the Fields.Append method, which is discussed in Chapter 4.

Refresh method

Call the Refresh method on the DataControl object when you want to refresh the information you retrieved from a query. The Refresh method will create a connection to an IIS server, open a connection to your database, and submit a query. The Refresh method takes no parameters.

Reset method

Use the Reset method when you want the changes you've made to the DataControl object's sorting and filtering properties to take effect. The Reset method accepts a single parameter:

• fRefilter This optional parameter defaults to 1. A value of 1 indicates that you want the changes to the filtering and sorting information to apply to the currently filtered data. This parameter can help create compound filters.

For example, let's say you want to view only customers whose names begin with the letter "D." You can't use wildcards, but you could use a compound filter by setting the filter properties twice, as shown here:

 Dim rdsDC As RDS.DataControl  With rdsDC .FilterColumn = "CompanyName" .FilterCriterion = ">=" .FilterValue = "D" .Reset False .FilterColumn = "CompanyName" .FilterCriterion = "<" .FilterValue = "E" .Reset True End With 

This code uses the fRefilter parameter in the calls to the Reset method. Initially, you simply want to show the records whose CompanyName field contains a value greater than or equal to "D". When you call the Reset method, you use False as the value for the fRefilter parameter because you want to remove any previous filters before applying this one. Then you want to show the records whose CompanyName field contains a value less than "E". But you want the records to satisfy the previous filter criteria as well, so you use a value of True for the fRefilter parameter.

If you want to clear the current filter, set the FilterColumn or FilterCriterion property to an empty string and then call the Reset method with a value of False for the fRefilter parameter.

SubmitChanges method

The DataControl object's SubmitChanges method takes no parameters and is similar to the UpdateBatch method on the Recordset object. You use it to submit the changes pending in the Recordset to your database.

If you have changes pending in records that aren't visible because of the current filter setting, those pending changes will not be submitted. The changes that are submitted are bundled in a transaction. If any of the update attempts fail, the entire transaction is rolled back and none of the changes will be committed to your database.

RDS DataControl Object Events

Finally, let's look at the two events that the DataControl object exposes, shown in the following table.

RDS DataControl Events
Event Name	Description
onError	An error has occurred.
onReadyStateChange	The value of the ReadyState property has changed.

onError event

The onError event fires when the DataControl object encounters an error. You can use the onError event to handle errors generated by the DataControl. This event supports four parameters:

• SCode An integer value that indicates the status code of the error.
• Description A string that describes the error.
• Source A string that contains the query or command that generated the error.
• CancelDisplay A Boolean value that you can set to control whether the error will display a dialog box. This parameter is False by default, meaning a dialog box will be displayed.

onReadyStateChange event

The onReadyStateChange event fires when the value of the DataControl object's ReadyState property changes. This event uses no parameters. You can use this event to determine when the results of your query have been fetched, as shown here:

 Private Sub rdsDC_onReadyStateChange() 'Test the ReadyState property to see whether the operation has ' completed. If rdsDC.ReadyState = adcReadyStateComplete Then 'Retrieve the Recordset. Set rs = rdsDC.Recordset 'Test to see whether an uninitialized Recordset was returned. If Not rs Is Nothing Then Set gridResults.DataSource = rs gridResults.Visible = True Else MsgBox "No Recordset was returned" End If End If End Sub