Programming the Data Source Component

[Previous] [Next]

Now that you know what the DSC was designed to do, let's discuss how you accomplish some common programming tasks with it. The following sections describe how to perform tasks using the DSC with the other Office Web Components. These sections do not discuss tasks that involve using hierarchical data access pages. For more information on that subject, see the Access 2000 documentation.

Setting Connection Information

The first step in using the DSC is to configure its connection information. You can do this either at design time by using the container's property editor or at runtime by using code. You already saw how to do this in Chapters 3 and 4, but here is a quick recap of the process.

The following code, taken from the DSCSample.htm file in the Chap05 folder on the companion CD, shows how to set the DSC's connection information:

 ' Set the DSC's ConnectionString property ' Note: Change this line if you move the MDB file or want to connect to ' a different MDB file sDBPath = "..\Data\Northwind.mdb" DSC.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=" & _                         sDBPath 

This code simply sets the DSC's ConnectionString property to a valid connection string—in this case, using the Microsoft Jet OLE DB provider and a local MDB file. To connect to a SQL Server data source using integrated security, the code would look like this:

 DSC.ConnectionString = "PROVIDER=sqloledb;DATA SOURCE=SQLserverName;" & _                        "INITIAL CATALOG=DatabaseName;" & _                        "INTEGRATED SECURITY=SSPI" 

These two connection strings use a direct (also called two-tier) connection. To use RDS, which will remote your data requests over HTTP to a web server, you can use your normal connection string and set the UseRemoteProvider property to True:

 DSC.ConnectionString = "PROVIDER=sqloledb;DATA SOURCE=SQLserverName;" & _                        "INITIAL CATALOG=DatabaseName;" & _                        "INTEGRATED SECURITY=SSPI" DSC.UseRemoteProvider = True 

Note that the DSC will not immediately connect to the data source when you set the ConnectionString property. Instead, it will wait until your code or a bound control requests data from the data source. It will also attempt to connect if your code requests the DSC's Connection property, which returns an opened ADO Connection object. (More on the Connection property later.) This delayed connection scheme allows you to adjust the ConnectionString property in a <script> block before any controls have asked for data.

Adding a New RecordsetDef

The DSC has a RecordsetDefs collection of RecordsetDef objects, so named because they represent definitions of ADO Recordset objects that the DSC can materialize at runtime. Any RecordsetDef object defined in the DSC's RecordsetDefs collection is a valid data member that a data-bound control can request. (If you need a refresher on data sources and data members, see Chapter 2.) When using the DSC to retrieve data for another Office Web Component, you must add a RecordsetDef object to the DSC to define the table, view, stored procedure, or command text that the DSC should use to get the data.

A page designed in Access 2000's Data Access Page Designer already has a number of RecordsetDef objects defined in the page's DSC. However, you can add more at runtime and use them as data members for bound controls. The following code, also taken from the DSCSample.htm file in the Chap05 folder on the companion CD, shows how to add a RecordsetDef object at runtime:

 Set rsdef = DSC.RecordsetDefs.AddNew("SELECT Country, " & _     "Shippers.CompanyName AS Shipper, " & _     "Count(*) AS Shipments, " & _     "Sum(Quantity) AS Quantity " & _     "FROM Invoices " & _     "WHERE OrderDate Between #1/1/98# " & _     "and #12/31/98# " & _     "GROUP BY Country, Shippers.CompanyName", _     DSC.Constants.dscCommandText, "ChartData") 

This example adds a new RecordsetDef object using a SQL statement for the first parameter to the AddNew method. You can also use the name of a table, view, stored query, or stored procedure in this first parameter. The second parameter tells the DSC what kind of expression you used in the first parameter, and the valid values you can pass for this argument are listed in the DscRowsourceTypeEnum enumeration. The last parameter is the name you want to assign the new RecordsetDef object; if not specified here, the DSC will assign it a new unique name. The AddNew method returns the new RecordsetDef object, so if you want to perform a task with it right away, you can set a variable to the return value and further modify the new object.

Adding Calculated Fields

Occasionally, you will need to use a calculated field in your solution. Usually, you will want to use a calculated field expression in the SQL statement for your RecordsetDef object, using the database engine's capabilities to calculate a new field value for each returned row. However, you cannot always do this, either because the database engine does not support calculated columns or because you want to use a different expression service to resolve the calculation.

Adding a RecordsetDef Object Based on XML Data

Chapter 4 described how to load the PivotTable component with XML data (in a specific format) returned from a URL. When you set the PivotTable control's ConnectionString and CommandText properties, the control creates a DSC in memory and adds a RecordsetDef object programmatically, just as you saw in the previous code snippet. To add a RecordsetDef based on XML returned from a URL, use the following code:

 DSC.RecordsetDefs.AddNew sURL, DSC.Constants.dscCommandFile 

This should look a lot like the example in Chapter 4 because the PivotTable control acts as a front for the DSC. Note that you pass the constant dscCommandFile as the second argument. This tells the DSC the first argument is a URL that will return XML data in the specific format that ADO Recordset objects know how to load. For an example of this technique, see the sample file LoadingXML.htm from the companion CD.

The DSC exposes the Data Shape Provider's ability to create calculated fields in your Recordset object. The Data Shape Provider evaluates the calculation expression at the client using the Jet expression service. The Jet expression service exposes all the "safe" VBA runtime functions, allowing you to build some complex expressions.

The following code, taken from the DSCSample.htm file, shows how to add a calculated field to a RecordsetDef object:

 ' Add a calculated field to the RecordsetDef object rsdef.PageFields.Add "[Quantity]/[Shipments]", _     DSC.Constants.dscCalculated, "AvgQtyPerShipment" 

To add a calculated field, use the Add method of the PageFields collection. Every RecordsetDef object has a PageFields collection that contains definitions for all the fields in the RecordsetDef object. To add a new calculated field to this collection, specify the calculation expression as the first parameter, the dscCalculated constant as the second parameter, and the name for your new calculated field as the last parameter.

The previous calculation expression is quite trivial. You would probably just include it in your SQL statement, letting the database engine perform the calculation. However, the Jet expression service and VBA runtime functions allow you to use more complicated expressions than most database engines allow. For example, you can use all the VBA string manipulation functions—such as InStr, Left, Mid, Right, and Format—to massage text values in your database. Another example is using the Left function to extract an alpha-grouping value to group names by their first letter.

Although the Jet expression service in Access allows you to reference the Microsoft VBA functions you defined in the current database, they are not available to the data shape engine's expression service. The OLE DB provider for Jet databases does not permit use of your custom functions, even in stored queries.

Which VBA Functions Are Not Considered Safe?

VBA exposes a few runtime functions that are not considered safe and therefore are not available in the Jet expression service for calculated fields. For example, VBA exposes a function named Kill that deletes files on your hard drive. If this function were permitted, a malicious developer could use it in a field expression and delete critical files on the client's machine. For this reason, none of the VBA functions that access the hard drive or interact with the system settings are allowed in calculated field expressions.

WARNING

---

Although you might be tempted, do not use calculated fields with the PivotTable component. When I started playing with client-side calculated fields in the DSC, I tried using the Left function to create a new column with only the first letter of a customer's last name. I thought I could use this technique with the PivotTable component to perform an alpha-grouping of customer data. I tried running the page, and it immediately caused a general protection fault and disappeared.

The PivotTable control does not support using client-side calculated fields in this version of the Office Web Components; however, it will support them in future releases. For the Office 2000 release, you should include expressions in your SQL statements to create calculated fields for the PivotTable control. For example, the task I just described could also be performed using the Left function in Transact SQL or in Jet SQL.

Adding Server Filters

When you add a RecordsetDef object, you can simply use the name of a table or view, instead of a full SQL statement. If you do so, you can use the ServerFilter property of the RecordsetDef object to specify a WHERE clause that the DSC will include in the command text sent to the data source. This limits the amount of data returned from the data source, which is much faster than retrieving all data and then filtering locally.

To set the ServerFilter property, use the following code (from DSCSample.htm):

 Set rsdef = DSC.RecordsetDefs.AddNew("Invoices", _     DSC.Constants.dscView, "ChartData2")      rsdef.ServerFilter = "OrderDate Between #1/1/98# and #1/31/98#" 

The DSC will use the value of the ServerFilter property as a WHERE clause when fetching data from the table or view. If you use command text as the source for your RecordsetDef object, add the WHERE clause as part of your command text.

Using Parameters

The DSC also supports using parameterized commands and using parameters with stored procedures. Each RecordsetDef object has a ParameterValues collection. The collection holds a set of ParameterValue objects, one for each parameter in the source command.

When code or a bound control asks the DSC for the results of a RecordsetDef object, the DSC checks whether any parameter values (represented by ParameterValue objects) are needed for the command. If values are needed and the Value properties of the ParameterValue objects are not set, the DSC will display a parameter prompt dialog box that allows the user to specify parameter values. Typically, a custom solution displays its own user interface for collecting these parameter values and then programmatically sets the Value properties of the ParameterValue objects.

The following code shows how to add a RecordsetDef object based on a stored procedure and how to add parameter values for execution:

 Set rsdef = DSC.RecordsetDefs.AddNew("Employee Sales by Country", _     DSC.Constants.dscProcedure) rsdef.ParameterValues.Add "[Beginning Date]", "1/1/98" rsdef.ParameterValues.Add "[Ending Date]", "1/31/98" 

This example adds a RecordsetDef object that uses the stored procedure Employee Sales by Country as its source. This stored procedure has two parameters, and the code adds ParameterValue objects for each. This code was written to use the Jet database engine version of the Northwind database, so the parameter names have square brackets around them. However, if you are using SQL Server, you must omit the square brackets and use an at sign (@) in front of each parameter name.

Getting Recordset Objects from the Data Source Component

Once you add a RecordsetDef object to the DSC, you can retrieve the resulting ADO Recordset object in code by using the DSC's Execute method. The first time your code or a bound control requests a certain RecordsetDef object, the DSC will execute the source command to get the ADO Recordset object. Successive requests for the same RecordsetDef object will return the ADO Recordset object that the DSC already retrieved—meaning multiple controls bound to the same RecordsetDef object will display the same data and will have the same current row.

Having multiple controls bound to the same RecordsetDef object has an interesting effect when you set a local filter on the object. For example, if you bind the Chart component to a RecordsetDef object in the DSC, you can use the DSC's Execute method to retrieve a reference to the source Recordset, and you can then apply a local filter. The Recordset object notifies all controls to which it is bound that its contents have changed and that they should refresh themselves. The Chart component, therefore, updates itself so that it shows the current data.

The following code, taken from the DSCSample.htm file on the companion CD, shows how to accomplish this:

 Sub btnFilter_onClick()     ' Local variables     Dim rs             ' Recordset object     Dim asCountries    ' String array of country names     Dim sFilter        ' Filter expression     Dim ct             ' Counter          ' Get the Recordset object the chart is bound to     Set rs = DSC.Execute(ChartSpace1.DataMember)          ' Get the set of country names to filter on     asCountries = Split(txtCountries.value,",")          ' Build the filter expression     On Error Resume Next     sFilter = "Country='" & Trim(asCountries(0)) & "'"     If err.Number = 0 Then         For ct = 1 To Ubound(asCountries)             sFilter = sFilter & " OR Country='" & _                 Trim(asCountries(ct)) & "'"         Next 'ct     Else         sFilter = ""     End If              ' Now set the filter expression     ' The chart will automatically update     rs.Filter = sFilter End Sub 'btnfilter_onClick() 

This example uses the DataMember property of the Chart control to retrieve the name of the RecordsetDef object to which the Chart control is bound. It uses the DSC's Execute method to obtain a reference to the same Recordset object the Chart control is using to load the chart. It then builds a filter expression based on the country names typed into the text box and sets the Recordset object's Filter property to this filter expression.

Executing Ad Hoc Statements and Manipulating the Connection

The DSC exposes a property named Connection at its top-level interface. This property returns the ADO Connection object the DSC is currently using to communicate with the database. You can use this ADO Connection object reference to execute ad hoc statements that access the data source, such as a stored procedure call to retrieve certain lookup values or to ensure that the version of the database schema is what the client solution expects. You can also use the properties and methods of the ADO Connection object to perform tasks such as beginning and ending a transaction, determining or setting the database isolation level, or setting a new command timeout value.

For example, to retrieve the version of the SQL Server database engine to which your DSC is connected, you can use the following code:

 Set rsVerInfo = DSC.Connection.Execute("Select @@Version") 

The ADO Connection object's Execute method takes a command to execute and returns a forward-only, read-only Recordset with the results of the command. You can use this method to execute stored procedures in the data source or to execute any command text accepted by the data source.

Catching Data Access Errors

The DSC raises an important event related to data access errors. If the DSC encounters a data access error while connecting or fetching data for a bound control, it will raise its DataError event, passing a DSCEventInfo object to the event handler. This object has a property named Error, which returns the ADO Error object that the DSC obtains when the data access error occurs. To catch this error and display your own message, use the following code:

 Sub MSODSC_DataError(einfo)     MsgBox "Data Error!" & vbCrLf & einfo.Error.Description, vbCritical End Sub 

Make sure you set the DSC's DisplayAlerts property to False to suppress the message boxes the DSC itself displays when it encounters an error.

You can obviously perform other tasks in this event handler besides displaying the error. For example, you might use this event to catch conditions in which a certain data source is no longer available and to automatically switch to a backup data source. The ADO Error object exposes the data source's native error number to help you detect certain types of errors from which you might try to recover.