Performing Basic Tasks with ADS and ADO

This section describes some of the more common tasks that you can perform with ADO. These include connecting to a data dictionary, executing a query, using a parameterized query, retrieving and editing data, and executing a stored procedure.

Connecting to Data

You connect to a data dictionary or a directory in which free tables are located using a Connection object found in the ADODB namespace. At a minimum, you must provide the Connection object with sufficient information to locate your data and configure how the data should be accessed. This can be done either with the Parameters collection property or the ConnectionString property. Both of these properties accept name/value pairs using the parameters listed in Table 14-1. If you use the ConnectionString property, and use more than one name/value pair, separate them with semicolons.

Table 14-1: Parameters for Connecting with ADO

Parameter	Description
CharType	Set to the character set type for DBF files. Valid values are ADS_ANSI and ADS_OEM. The default value is ADS_ANSI.
Compression	Set to ALWAYS, INTERNET, NEVER, or empty. If left empty (the default), the ADS.INI file will control the compression setting. This parameter is not used by ALS.
Data Source	The path to your free tables or data dictionary. If you are using a data dictionary, you must include the data dictionary filename in this path. It is recommended that this path be a UNC path. Data Source is a required parameter.
DbfsUseNulls	Set to TRUE to return empty fields from DBF files as NULL values. If set to FALSE, empty fields are returned as empty data values. The default is FALSE.
EncryptionPassword	Set to an optional password to use for accessing encrypted free tables. If using less than a 20-letter password, a semicolon should be included directly after the password so the Advantage OLE DB Provider knows when the password ends. This parameter is ignored for data dictionary connections.
FilterOptions	Set to IGNORE_WHEN_COUNTING or RESPECT_WHEN_COUNTING. When set to IGNORE_WHEN_COUNTING, the RecordCount property of a Recordset may not accurately reflect the number of records in a result set. Set to RESPECT_WHEN_COUNTING for accurate record counts. Requesting accurate record counts can reduce performance significantly, and should be used only if accurate counts are needed. The default is IGNORE_WHEN_COUNTING.
IncrementUsercount	Set to TRUE to increment the user count when the connection is made, or FALSE to make a connection without incrementing the user count. The default is FALSE.
Initial Catalog	Optional name of a data dictionary if the data dictionary is not specified in the Data Source parameter.
LockMode	Set to ADS_PROPRIETARY_LOCKING or ADS_COMPATIBLE_LOCKING to define the locking mechanism used for DBF tables. Use ADS_COMPATIBLE_LOCKING when your connection must share data with non-ADS applications. The default is ADS_PROPRIETARY_LOCKING.
Password	When connecting to a data dictionary that requires logins, set to the user's password.
Provider	This required parameter must be set to either Advantage OLE DB Provider or Advantage.OLEDB.1.
SecurityMode	Set to ADS_CHECKRIGHTS to observe the user's network access rights before opening files, or ADS_IGNORERIGHTS to access files regardless of the user's network rights. The default is ADS_CHECKRIGHTS. This property applies only to free table connections.
ServerType	Set to the type of ADS server you want to connect to. Use ADS_LOCAL_SERVER, ADS_REMOTE_SERVER, or ADS_INTERNET_SERVER. To attempt to connect to two or more types, separate the server types using a vertical bar (|). This is demonstrated in the ConnectionString shown later in this chapter.
ShowDeleted	Set to TRUE to include deleted records in DBF files, or FALSE to suppress deleted records. The default is FALSE.
StoredProcedure Connection	Set to TRUE if connecting from within a stored procedure. When set to TRUE, the connection does not increment the user count. The default is FALSE.
TableType	Set to ADS_ADT, ADS_CDX, or ADS_NTX to define the default table type. The default is ADS_ADT. This parameter is ignored for data dictionary connections.
TrimTrailingSpaces	Set to TRUE to trim trailing spaces from character fields, or FALSE to preserve trailing spaces. The default is FALSE.
User ID	If connecting to a data dictionary that requires logins, set to the user's user name.

For any of the optional connection string parameters that you fail to provide, ADO will automatically insert the default parameters. Furthermore, instead of supplying a connection string with your connection information, you can set the connection string to the following pattern:

FILE NAME=path\filename.udl

where path is the physical or UNC path to a directory in which a file with the .udl file extension resides, and filename is the name of a UDL (universal data link) file. UDL files are INI-style files that contain ADO connection information. Under the most recent versions of Windows, UDL files are stored in

C:\Program Files\Common Files\System\Ole DB\Data Links

You do not even need to know how a UDL file is structured to create one. Simply create a new empty file in the preceding directory using the UDL file extension. Then, using the Windows Explorer, right-click this filename and select Properties. Use the displayed properties dialog box, shown in Figure 14-2, to configure the connection information. At runtime, when ADO processes the connection string containing FILE NAME=path\filename.udl, it will expand the connection string, populating it with the definitions located in the UDL file.

Figure 14-2: Setting the connection properties of an empty UDL file

Because the Connection object that is used by this project must be used by a number of subprocedures, the AdsConnection variable and several other variables that must be repeatedly referenced are declared global to the project. The following is this global declaration. Note also that the location of the data source location of the data dictionary is declared as a global constant.

'Require explicit variable declarations Option Explicit Dim AdsConnection As ADODB.Connection Dim AdsCommand As ADODB.Command Dim AdsRecordset As ADODB.Recordset Dim AdsParamQueryCommand As ADODB.Command Dim AdsParamQueryRecordset As ADODB.Recordset Dim AdsParameter As ADODB.Parameter Dim AdminConnection As ADODB.Connection Dim AdminCommand As ADODB.Command Const DataPath = "\\server\share\Program Files\Extended Systems" + _   "\Advantage\ADSBook\DemoDictionary.add"

This Connection, named AdsConnection, is created, configured, and opened from the Load event of the form, along with several other Command, Recordset, and Connection objects. The relevant portion of this subprocedure is shown in the following code:

Private Sub Form_Load()   On Error GoTo ErrorHandler   Set AdsConnection = New ADODB.Connection   Set AdsCommand = New ADODB.Command   Set AdsRecordset = New ADODB.Recordset   Set AdminConnection = New ADODB.Connection   Set AdminCommand = New ADODB.Command   'Setup the connection   AdsConnection.ConnectionString = _   "Provider=Advantage OLE DB Provider;" + _   "Data Source=" + DataPath + ";user ID=adsuser;" + _   "password=password;" + _   "ServerType=ADS_LOCAL_SERVER | ADS_REMOTE_SERVER;" + _   "FilterOptions=RESPECT_WHEN_COUNTING;TrimTrailingSpaces=True"   AdsConnection.Open   Set AdsCommand.ActiveConnection = AdsConnection   'Additional code not shown follows    Exit Sub ErrorHandler:   MsgBox "Error: " & Err.Number & vbCrLf & _   "Description: " & Err.Description   Exit Sub End Sub 

As you inspect this code, you will notice that all errors are handled by displaying the error code and message of the error. This type of error handler is present in every subprocedure in this Visual Basic project. In order to reduce redundancy in this chapter, the error handling block, as well as the subprocedure declaration, is omitted from the remaining subprocedure listings in this chapter.

Note

If you have difficulty connecting, it might be because you have other client applications, such as the Advantage Data Architect, connected using a local connection. Ensure that all clients on the same machine use the same type of connection.

Executing a Query

You execute a query that returns a result set by calling the Open procedure of a Recordset. This procedure has five optional parameters. The first is the command you want to execute. This can be either a Command object, the name of a table or stored procedure, or (as in the case in the following code segment) the actual text of the query. The second parameter is the connection over which the query will be executed.

The third parameter identities the type of cursor that you want returned, and the fourth specifies the type of record locking you want. The fifth and final parameter identifies what kind of command you pass in the first parameter. If you pass a table name in the first parameter, you can pass the value adCmdTable in this fifth parameter, and a SELECT * FROM query will be generated. If you pass the name of a stored procedure that takes no input parameters in the first parameter, an EXECUTE PROCEDURE statement is generated if adCmdStoredProc is given as the fifth parameter.

The following code demonstrates the execution of a query entered by the user into the TextBox named SELECTText. This subprocedure is associated with the Execute SELECT button (shown in Figure 14-1):

If AdsRecordset.State = adStateOpen Then   AdsRecordset.Close End If   AdsRecordset.Open SELECTText.Text, AdsConnection, adOpenDynamic, _   adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdsRecordset 

This code begins by verifying that the Recordset is not currently open, by checking its State property. Next, the query is executed and the returned records are assigned to the Recordset. Finally, the Recordset is assigned to the DataSource property of the DataGrid. The effects of executing this code are shown in Figure 14-3.

Figure 14-3: Records returned from a query are displayed in a data grid.

If you need to execute a query that does not return a Recordset, use a Command object. The use of a Command object to execute a query that does not return a Recordset is demonstrated later in this chapter.

Using a Parameterized Query

Parameterized queries are defined using a Command object. This command object can then be executed directly, so long as the query does not return a result set. If the parameterized query returns one or more records, you can execute it using the Open method of a Recordset, just as you can with a query that takes no parameters.

Before you can invoke a parameterized query, you must create one Parameter object for each of the query’s parameters, and then associate each Parameter with the Command holding the parameterized query.

The definition of a parameterized query, including the creation and configuration of a parameter, is shown in the following code segment. This code segment is part of the Load event for the form object, and was omitted from the code listing shown earlier (in the section “Connecting to Data”):

'Set up the parameterized query that will be reused Set AdsParamQueryCommand = New ADODB.Command Set AdsParamQueryRecordset = New ADODB.Recordset Set AdsParamQueryCommand.ActiveConnection = AdsConnection AdsParamQueryCommand.CommandText = _   "SELECT * FROM INVOICE WHERE [Customer ID] = ?" Set AdsParameter = AdsParamQueryCommand.CreateParameter AdsParamQueryCommand.Prepared = True AdsParamQueryCommand.NamedParameters = False AdsParameter.Type = adInteger AdsParamQueryCommand.Parameters.Append AdsParameter

Once a Parameter has been created, configured, and associated with the Command holding the parameterized query statement, there is only one more step necessary before the query can be executed. You must bind data to each parameter. This is shown in the following click event of the DoParamQuery button (the button labeled Show Invoices in Figure 14-1):

If IsNumeric(ParamText.Text) = False Then   MsgBox "Invalid customer number"   Exit Sub End If If AdsParamQueryRecordset.State = adStateOpen Then   AdsParamQueryRecordset.Close End If AdsParameter.Value = CInt(ParamText.Text) AdsParamQueryRecordset.Open AdsParamQueryCommand, , adOpenDynamic, _   adLockPessimistic, adCmdText If AdsParamQueryRecordset.BOF And _   AdsParamQueryRecordset.EOF Then   MsgBox "No invoices for customer ID"   Set DataGrid1.DataSource = Nothing Else   Set DataGrid1.DataSource = AdsParamQueryRecordset End If 

As you can see from this code, after verifying that a numeric value has been entered into the customer ID field, the entered data is assigned to the Value property of the parameter and the query is executed.

This example is actually a classic example of how parameterized queries are used. Specifically, the query text is defined only once, but can be executed repeatedly. And by changing only the value of the parameter, a different result set can be returned upon each execution.

Reading and Writing Data

You read data from fields of a Recordset by using the Recordset’s Fields property, which is a collection property. The Fields property takes a single parameter that identifies which field’s value you want to read. This value can either be an integer identifying the ordinal position of the field in the table’s structure (this value is zero-based) or it can be a string identifying the field’s name. The Value property of the identified field holds the field’s data.

Reading data from a Recordset is demonstrated in the following Click subprocedure associated with the Get Address button (shown in Figure 14-1):

Dim AdsGetCustCommand As ADODB.Command Dim AdsGetCustRecordset As ADODB.Recordset Dim AdsGetCustParameter As ADODB.Parameter If CustNoText.Text = "" Or Not IsNumeric(CustNoText.Text) Then   MsgBox "Please supply a valid customer ID number"   Exit Sub End If Set AdsGetCustCommand = New ADODB.Command Set AdsGetCustRecordset = New ADODB.Recordset Set AdsGetCustCommand.ActiveConnection = AdsConnection AdsGetCustCommand.CommandText = _   "SELECT * FROM CUSTOMER WHERE [Customer ID] = ?" Set AdsGetCustParameter = AdsGetCustCommand.CreateParameter AdsGetCustCommand.Prepared = True AdsGetCustCommand.NamedParameters = False AdsGetCustParameter.Type = adInteger AdsGetCustCommand.Parameters.Append AdsGetCustParameter AdsGetCustParameter.Value = CInt(CustNoText.Text) AdsGetCustRecordset.Open AdsGetCustCommand, , adOpenDynamic, _   adLockPessimistic, adCmdText If AdsGetCustRecordset.BOF And _   AdsGetCustRecordset.EOF Then   MsgBox "No records for customer ID"   Set DataGrid1.DataSource = Nothing Else   Set DataGrid1.DataSource = AdsGetCustRecordset   OldAddressText.Text = _   AdsGetCustRecordset.Fields("Address").Value End If

So long as your Recordset contains a dynamic (live) cursor, you can make changes to a Recordset by assigning data to one or more of the Recordset’s Fields Value properties, where you identify the field you are writing to using the same technique that you use to read from a field. After changing one or more fields, you call the Update method of the Recordset to write those changes to ADS. Alternatively, you can call the Recordset’s Update, passing to it either a field name/value pair or an array of field name/value pairs. This second approach writes one or more updated fields to ADS in a single command.

The following code demonstrates one way to update a Recordset. This code can be found for the click procedure associated with the button labeled Set New Address in Figure 14-1:

If CustNoText.Text = "" Or Not IsNumeric(CustNoText.Text) Then   MsgBox "Please supply a valid customer ID number"   Exit Sub End If If InStr(1, TableNameText.Text, ";", vbTextCompare) <> 0 Then   MsgBox "Customer ID may not contain a semicolon"   Exit Sub End If Dim AdsGetCustRecordset As ADODB.Recordset Set AdsGetCustRecordset = New ADODB.Recordset AdsGetCustRecordset.Open "SELECT Address FROM CUSTOMER " + _   "WHERE [Customer ID] = " + CustNoText.Text, AdsConnection, _   adOpenDynamic, adLockPessimistic, adCmdText If AdsGetCustRecordset.BOF And _   AdsGetCustRecordset.EOF Then   MsgBox "Customer ID not found" Else   AdsGetCustRecordset.Fields("Address").Value = _   NewAddressText.Text   AdsGetCustRecordset.Update End If MsgBox "Address for customer " + CustNoText.Text + " " + _   "has been updated" Exit Sub

Of course, a SQL UPDATE query can also be used to achieve a similar result.

Calling a Stored Procedure

Calling a stored procedure is no different than executing any other query. If your stored procedure does not require input parameters, you can define the query text using a Command object or by passing the call to EXECUTE PROCEDURE in the first parameter of a Recordset Open invocation. Alternatively, you simply pass the name of the stored procedure object in the CommandText, and set the CommandType property (of a Command object) or CommandType parameter (of the Resultset’s Open method) to adCmdStoredProc (this second technique is demonstrated in the following example). Typically, you use a Command object—executing it directly—when the stored procedure does not return records, and use the Open method of a Recordset when your stored procedure returns one or more records.

Invoking a stored procedure that takes one input parameter is demonstrated by the following code associated with the click event for the Show 10% of Invoices button (shown in Figure 14-1). The DelphiAEP stored procedure referenced in this code is the Delphi AEP (Advantage Extended Procedure) created in Chapter 7. If you did not create this AEP, but created one of the other AEPs described in that chapter, substitute the name of the stored procedure object in your data dictionary in the CommandText property of the Command object.

Dim AdsSPCommand As ADODB.Command Dim AdsSPRecordset As ADODB.Recordset Dim AdsSPParameter As ADODB.Parameter If ParamText.Text = "" Or Not IsNumeric(ParamText.Text) Then   MsgBox "Please supply a valid customer ID number"   Exit Sub End If Set AdsSPCommand = New ADODB.Command Set AdsSPRecordset = New ADODB.Recordset Set AdsSPCommand.ActiveConnection = AdsConnection AdsSPCommand.CommandText = "DelphiAEP" Set AdsSPParameter = AdsSPCommand.CreateParameter AdsSPCommand.Prepared = True AdsSPCommand.NamedParameters = False AdsSPParameter.Type = adInteger AdsSPCommand.Parameters.Append AdsSPParameter AdsSPParameter.Value = CInt(ParamText.Text) AdsSPRecordset.Open AdsSPCommand, , adOpenDynamic, _   adLockPessimistic, adCmdStoredProc Set DataGrid1.DataSource = AdsSPRecordset Exit Sub