Upgrading Remote Data Objects RDO

This section covers issues that are specific for the upgrade of RDO technology to ADO.

RDO with Data Binding

Applications that fall into this scenario have projects that reference the Microsoft Remote Data Objects. In them, at least one form contains the Remote Data Control for data binding and at least one control in the form uses data binding with Remote Data Control that uses the properties, DataSource and DataField .

After you upgrade an application like this, the Remote Data Control appears to be upgraded. However, the new control is read-only at run time, and you will not be able to navigate between the results. You can correct this by changing the Remote Data Control of the upgraded project to an ADODC control in the Microsoft.VisualBasic.Compatibility.Data library. However, this approach might require a significant amount of work. It is more efficient to replace the RDO technology with ADO technology in the Visual Basic 6.0 application before you upgrade the application. For more information on this approach see "Replacing the RDO Remote Data Control with ADO Data Control in Visual Basic 6.0" later in this chapter.

RDO without Data Binding

In this scenario, your application must contain in its project a reference to Microsoft Remote Data Objects. Additionally, it may not contain any RDO Data Controls; all of the data access is managed at run time.

With an application like this the upgrade of the RDO code is automatic, because the upgrade wizard creates a reference to the RDO library. Your application maintains the same RDO instructions in its code, which results in the same Visual Basic 6.0 functionality.

Replacing RDO with ADO in Visual Basic 6.0

Because RDO maps closely to ADO, it is generally a good idea to replace RDO with ADO before you upgrade your application. The following scenarios demonstrate how RDO is used to resolve data access issues and how you can achieve the same behavior with ADO. The examples assume that the application project has a reference to Microsoft ADO 2.0.

Establishing a Connection to a Database

To open a connection in RDO, you must supply a connection string with parameters. A connection is not required by RDO to create an rdoQuery object, but it is required to initially create an rdoResultset object. The following code example demonstrates opening a connection using RDO.

Dim WithEvents cn As rdoConnection Dim cnB As New rdoConnection

Const ConnectString = "uid=myname;pwd=mypw;driver={SQL Server};" & _ "server=myserver;database=pubs;dsn=''"

This connection string accesses a specific SQL Server and permits ODBC to open a connection without a Data Source Name (DSN). This example represents a typical ODBC connection string with all of the standard arguments.

To continue the example, consider the following form Load event code. The code establishes the type of cursor driver and the login timeout. By default, RDO uses the rdUseIfNeeded cursor type, which invokes server-side cursors on SQL Server. However, this default is overridden in the following example by specifying rdUseNone. The rdDriverNoPrompt flag means that the application generates an error if the user ID and password do not match.

Private Sub Form_Load()

Set cn = New rdoConnection With cn

.Connect = ConnectString .LoginTimeout = 10 .CursorDriver = rdUseNone .EstablishConnection rdDriverNoPrompt End With

Within the Load event, a second connection performs client-batch updates as shown in this example.

With cnB

.Connect = ConnectString .CursorDriver = rdUseClientBatch .EstablishConnection End With End Sub

The last event occurs when the connection operation completes. It handles any errors that occur when the connection is opened. With this event, you can test to see if the connection was established before attempting to perform any actions. For example, you can enable any buttons that rely on an open connection. This is demonstrated in the following code example.

Private Sub cn_Connect(ByVal ErrorOccurred As Boolean) If ErrorOccurred Then

MsgBox "Could not open connection", vbCritical

Else

RunOKFrame.Enabled = True End If End Sub

However, to establish a database connection in ADO, you must first create a set of ADO objects that are referenced from the ADODB object. These are used later to set specific properties that open connections and generate result sets. This is shown in the following code.

Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim cnB As New ADODB.Connection Dim Qy As New ADODB.Command

The next line creates a connection string, just like the one that was created in the previous RDO example. In both cases, the examples are using ODBCs "non-DSN" connection strategy to save time and to increase performance.

Const ConnectString= "uid=myname;pwd=mypw;driver={SQL Server};" & _ "server=myserver;database=pubs;dsn=''"

Now that the variables and connection string have been created, you can open an ADO connection to a database in the form Load event. This is demonstrated here.

Private Sub Form_Load() With cn

' Establish DSN-less connection .ConnectionString = ConnectString .ConnectionTimeout = l0 .Properties("Prompt") = adPromptNever ' This is the default prompting mode in ADO. .Open End With With cnB

.ConnectionString = ConnectString .CursorLocation = adUseClient .Open End With End Sub

Notice how this converted code is very similar to the RDO code except that the constants are prefaced with ad instead of rd. For example, the rdDriverNoPrompt was changed to adPromptNever. There is no need to specify the prompting behavior because ADO defaults to no prompt. If you do elect to change this, use the ADO Properties collection to establish the desired prompt behavior. In RDO, you can set the behavior using the OpenConnection argument. In ADO, you must set the Properties ("Prompt") property. Also, there is no need to specify a cursor driver if you do not want to use one (such as the RDO CursorDriver = rdUseNone) because ADO defaults to no cursor driver by default.

Running a Basic Query

In RDO, you can run queries using the method OpenResultset, which returns a Resultset that allows you to access all results. The following example shows how to return a Resultset based on a SQL statement. Notice that building a Resultset requires an open connection.

Private Sub RunButton_C1ick() Dim rs As rdoResultset

Set rs = cn.OpenResultsetC'select * from titles where title like '%h'")

' Perform operations on the Resultset obtained from the query here.

rs.Close End Sub

To execute a query with ADO, you must use the Open method of a recordset, using the database connection. The following event procedure is very similar to the previous RDO code example. However, in this case, you use the new ADO Open method that takes the SQL query and the ADO Connection object as arguments, instead of using the rdoConnection object's OpenResultset method. You can also opt to use the ADO Connection object's Execute method, just as you could in RDO, as long as it does not return a rowset.

Private Sub RunButton_Click() Dim rs As New ADODB.Recordset rs.Open "select * from titles where title like '%h'", cn Set MyMSHFlexGrid.Recordset = rs rs.Close End Sub

You can run this query and process its recordset asynchronously in ADO. When you specify the adFetchAsynch option on rs.Open, ADO causes the cursor provider to automatically populate the recordset in the background.

Displaying a Result Set in a MSHFlexGrid Control

The following code example uses the ShowData method of a custom ActiveX control to display data from a result set in an MSHFlexGrid control using RDO technology. The code sets up the grid based on the names in the rdoColumns property and initializes the grid, preparing it for the data. Notice the use of the OrdinalPosition property to index the resultset's rdoColumns property. The code uses the GetClipString method of the rdoResultset object to add the rows to the MSHFlexGrid control.

Public Function ShowData(Resultset As rdoResultset) As Variant Dim cl As rdoColumn Static GridSetup As Boolean Dim MaxL As Integer Dim rsl As rdoResultset Dim Rows As Variant On Error GoTo ShowDataEH Set rsl = Resultset If GridSetup = False Then FGrid1.Rows = 51

FGrid1.Cols = rsl.rdoColumns.Count FGrid1.Row = 0

For Each cl In rsl.rdoColumns

FGrid1.Col = cl.OrdinalPosition - 1 FGrid1 = cl.Name

If rsl.rdoColumns(cl.OrdinalPosition - 1).ChunkRequired Then MaxL = 1

Else

MaxL = rsl.rdoColumns(cl.OrdinalPosition - 1).Size + 4 End If

FGrid1.ColWidth(FGrid1.Col) = TextWidth(String(MaxL, "n")) Next cl

GridSetup = True End If

FGrid1.Rows = 1 'Clear Grid of data (except titles) FGrid1.Rows = 51 FGrid1.Row = 1 FGrid1.Col = 0

FGrid1.RowSel = FGrid1.Rows - 1 FGrid1.ColSel = FGrid1.Cols - 1 FGridi.Clip = rsl.GetClipString(50, , , "-")

ExitShowData:

FGrid1.RowSel = 1 FGrid1.ColSel = 0 Exit Function

ShowDataEH:

Select Case Err Case 40022:

FGrid1.Clear Resume ExitShowData Case 13

FGrid1.Text = "< >" Resume Next Case Else

MsgBox "Could not display data: " & Err & vbCrLf & Error$ Resume ' ExitShowData End Select End Function

By way of comparison, the following code example implements the ShowData method of a custom ActiveX control that is adapted from an RDO control, but it uses ADO. Note that the RDO GetClipString method is superseded in ADO by the GetString method. Because you then have to parse the resulting Variant array, the routine is noticeably slower.

Notice how you can no longer use the OrdinalPosition as an index on the Fields collection to pull out the column titles as you could in RDO. To resolve this issue, you can substitute a new integer counter to address the column being manipulated. Use the DefinedSize and ActualSize properties to find the TEXT and IMAGE data type fields that do not fit in a column. These new properties make it easier to determine the details of specific field values.

The following example adds code to handle BLOB types if they are encountered while working through the data columns.

Public Function ShowData(Resultset As Recordset) As Variant Dim cl As Field

Static GridSetup As Boolean

Dim MaxL As Integer

Dim Op As Integer

Dim rsl As Recordset

Dim rows As Variant

On Error GoTo ShowDataEH

Set rsl = Resultset

If GridSetup = False Then FGrid1.rows = 51 FGrid1.Cols = rsl.Fields.Count FGrid1.Row = 0 Op = 0

For Each cl In rsl.Fields FGrid1.Col = Op FGrid1 = cl.Name

If rsl.Fields(Op).DefinedSize > 255 Then MaxL = 1

Else

MaxL = rsl.Fields(Op).ActualSize + 4 End If

FGrid1.ColWidth(FGrid1.Col) = TextWidth(String(MaxL, "n")) Op = Op + 1 Next cl

GridSetup = True End If

FGrid1.rows = 1 FGrid1.rows = 51 FGrid1.Row = 1 FGrid1.Col = 0

FGrid1.RowSel = FGrid1.rows - 1 FGrid1.ColSel = FGrid1.Cols - 1 With FGrid1

' You can also use the ADO2 GetString method here in lieu of the ' following.

FGrid1.Clip = rsl.GetString(adClipString, 50, , , "-") End With

ExitShowData:

FGrid1.RowSel = 1 FGrid1.ColSel = 0 Exit Function

ShowDataEH:

Select Case Err Case 3021:

FGrid1.Clear Resume ExitShowData Case 13, Is < 0

rows(j, i) = "< >" Resume 'Next Case Else

MsgBox "Could not display data: " & Err & vbCrLf & Error$

Resume ' ExitShowData End Select End Function

For more information about converting RDO 2.0 to ADO 2.0, see "Visual Basic Concepts: Converting from RDO 2.0 to ADO 2.0" on MSDN.

Replacing the RDO Remote Data Control with ADO Data Control in Visual Basic 6.0

If you upgrade an application that contains Remote Data Control technology, it appears the controls are upgraded by the upgrade wizard; however, the new controls are read-only at run time. In this case, you will not be able to navigate between results. A better option is to replace the RDO technology with ADO technology in the Visual Basic 6.0 application before you upgrade it.

The following example illustrates the process that you can use to upgrade a Remote Data Control. The example contains a Remote Data Control named RDC1, and a TextBox control named OrderId.

In this example, RDC1 receives all of the orders that are stored in the Orders table, which is part of the Northwind Microsoft Access database that uses a DSN named Access. The control's properties are set as listed in Table 12.1.

Table 12.1: Remote Data Control Properties for the Upgrade Example

RemoteData Control properties Value

Table 12.1: Remote Data Control Properties for the Upgrade Example

RemoteData Control properties Value

Connect

DSN=Access

CursorDriver

0-rdUseIfNeeded

DataSourceName

MS Access Database

Password

<empty>

SQL

Select * from Orders

UserName

Admin

In the TextBox control, the DataSource property is set to RDC1 and the DataField property is set to OrderID.

To use an ADO data control, perform the following procedure.

► Using an ADO data control

1. Add the ADO reference to the project, as explained in the previous section, "Replacing the Data Control with ADO Data Control in Visual Basic 6.0."

2. Add an ADO data control to the form, and set its name with a representative name. In the current example, it is ADORDC1.

3. In the ConnectionString property of the ADO data control, set the Access DSN that is used in the RemoteData control. Figure 12.2 shows how to do this through the Property Pages.

Figure 12.2

Setting the ConnectionString of an ADO data control through the property page.

Figure 12.2

Setting the ConnectionString of an ADO data control through the property page.

4. Set the UserName property of the ADO data control to Admin.

5. In the RecordSource property of the ADO data control, set the query "select * from Orders".

6. Delete the RemoteData control.

7. Change the value of the DataSource property of the TextBox control to ADORDC1.

After you have completed this procedure, rebuild and retest the application to ensure everything functions correctly before you begin the upgrade process.

The next section discusses upgrading all DAO and RDO technology to ADO.

0 0

Post a comment