Visual Basic 6 Database How-To

Previous chapterNext chapterContents


- 7 -
SQL Server Databases and Remote Data Objects


If you're building large, high-volume database applications for many users, working with a database server such as Oracle, Sybase, or Microsoft SQL Server is probably in your future. This chapter focuses on Microsoft SQL Server. Although the techniques can often be applied to other server databases, each database can have its own particular feature set and SQL dialect.

Unless you've been away on a trip to another planet for the past couple of years, you've undoubtedly noticed that various new technologies have been introduced for accessing server databases. Developers now have a choice of using the Data control, the RemoteData control (RDC), Data Access Objects (DAO), ODBC Direct Data Access Objects (still DAO), Remote Data Objects (RDO), the open database connectivity (ODBC) API, the VBSQL control and the native server API, or Microsoft's newest entries into remote server connectivity: OLEDB, and ADO. Each of these technologies can be a suitable candidate for any given task.

This is the new kid on the block:

That's a wide array of choices--at times you might feel as if you've joined the technology-of-the-month club--but for most applications, RDO is an excellent option. RDO is now in its second release and is a proven technology. It offers the simplicity of a DAO-like object model but performance equal to the ODBC API or VBSQL. For those techniques available only with the ODBC API, RDO also exposes the necessary handles so you can go directly to the API when you need it.

Along with all the new data access technologies comes a plethora of SQL Server specific and general concepts and buzzwords. Rather than dealing with database files, SQL Server gives you database devices, dump devices, databases, transaction logs, and so on. Instead of a simple user and group list for security, you have logins, users, and groups that can be set up using standard, integrated, or mixed security models. The list goes on to concepts like n-tier design, the use of transaction servers and object brokers, Web connectivity technologies, and more. All this goes far beyond the scope of this chapter, and many excellent books have been written that address some or all of these tools and technologies. Fortunately, SQL Server also comes with excellent documentation.


NOTE Consider yourself lucky if you have a seasoned dba (database administrator) on staff to help handle the details of creating devices, databases, users, and so forth for you on the server. A good dba can save you days or weeks of work. If you don't have a veteran dba available, dive into the SQL Server documentation. There's a lot to cover, but you will learn it in time. Rome wasn't built in a day, and your client/server application won't be either.

This chapter assumes that you have already successfully installed Microsoft SQL Server 6.5, the pubs sample database, Visual Basic 6.0 Enterprise Edition, and the SQL Server client utilities. You will need to have the authority necessary to create a database on your server or access to a dba who can do it for you. You will also need to be able to execute SQL statements on the server using either SQL Enterprise Manager or ISQL/w. Consult the SQL Server documentation for more information on the installation and use of these utilities.

7.1 Browse a SQL Server Database by Using the RemoteData Control

This How-To introduces the most basic of operations on a database server: browsing the results of a query with the RemoteData control.

7.2 Add and Delete Records in a SQL Server Database by Using the RemoteData Control

In this How-To, you learn to use the RemoteData control to perform inserts and deletes on the remote SQL Server database.

7.3 Connect to a SQL Server Database by Using Remote Data Objects

Before you can do anything with remote server data, you must establish a connection to the server. This How-To shows you how to open an RDO Connection.

7.Browse Records in a SQL Server Database by Using Remote Data Objects

The RemoteData control, like the Data control, has its limitations. In this How-To, you learn to use Remote Data Objects to read SQL Server data.

7.5 Add, Update, and Delete Records in a SQL Server Database by Using Remote Data Objects

This How-To shows you how to insert, update, and delete records using RDO.

7.6 Execute a SQL Server Stored Procedure by Using Remote Data Objects

Most server databases rely heavily on stored procedures for database operations. This How-To teaches you to execute a simple, stored procedure that returns a result set.

7.7 Execute a Parameterized SQL Server Stored Procedure with Remote Data Objects

SQL Server stored procedures, like Visual Basic procedures, can have input and output parameters and return values. This How-To shows you how to execute a stored procedure with parameters.

7.8 Handle Remote Data Objects Errors

Despite your best efforts, things can go wrong on the remote database. In this How-To, you learn to trap and handle errors delivered to RDO by SQL Server.

7.1 How do I...

Browse a SQL Server database by using the RemoteData control?

Problem

My data is on a SQL Server database. How do I access this data using the RemoteData control?

Technique

Accessing data from a SQL Server database need not be complex. With the RemoteData control (RDC), you can build a simple form based on SQL Server data in minutes. Building forms with the RDC is the ultimate in visual design--no code whatsoever is required.

Building a form with the RDC requires only a few simple steps:

1. Create an ODBC data source for your database.

2. Draw a RemoteData control on your form and set the DataSourceName and SQL properties.

3. Add controls to the form for the columns you need to display and set the DataSource and DataField properties.

That's all there is to it. The RDC handles everything else for you in conjunction with the ODBC drivers. Like the Data control, the RemoteData control handles updating the tables as well as providing navigation buttons for the rows in the query.


NOTE This example and the rest of the examples in this chapter require that the pubs sample database be installed on your SQL Server and that an ODBC data source named pubs has been configured to connect to your server and the pubs sample database. See Chapter 6, "Connecting to an ODBC Server," for more information on configuring ODBC data sources.

Steps

Open and run project HT701.vbp. You can browse and update the rows returned by the query using the form shown in Figure 7.1.

Figure 7.1. The Publishers form.


NOTE Depending on the configuration of your SQL Server and network, you might or might not be prompted to provide a user name and password when connecting to the server. All of these examples were created using integrated security, which uses your network logon to validate your connection to the database server. SQL Server integrated security is available only if the server is a member of a Windows NT domain.
1. Create a new Standard EXE project, add the Microsoft RemoteData Control 6.0 to the toolbox, and save the project as HT701.vbp. Change the name of Form1 to FMain and create the objects and properties shown in Table 7.1.

Table 7.1. Objects and properties for FMain.

OBJECT Property Value
Form Caption Publishers
RemoteData Control Name rdc
Caption Publishers
Align 2 - vbAlignBottom
DataSourceName pubs
SQL SELECT pub_id, pub_name, city, state,country FROM publishers
Label Name lbl
Caption ID
Index 0
TextBox Name txtID
DataSource rdc
DataField pub_id
Label Name lbl
Caption Name
Index 1
TextBox Name txtName
DataSource rdc
DataField pub_name
Label Name lbl
Caption City
Index 2
TextBox Name txtCity
DataSource rdc
DataField city
Label Name lbl
Caption State
Index 3
TextBox Name txtState
DataSource rdc
DataField state
Label Name lbl
Caption Country
Index 4
TextBox Name txtCountry
DataSource rdc
DataField country

2. Using the menu editor, create the menu shown in Table 7.2.

Table 7.2. Menu controls for FMain.

CAPTION Name
&File mnuFile
----E&xit mnuFileExit

3. Create the declarations section of the form:

Option Explicit


NOTE You can and should set up Visual Basic to always use Option Explicit by checking the box marked Require Variable Declaration on the Editor tab of the Options dialog box.
4. Create the mnuFileExit_Click event procedure:

Private Sub mnuFileExit_Click()
  Unload Me
End Sub

How It Works

The RDC handles all the work for you in this application. When the application starts, the control opens a connection to the SQL Server, submits the query, and presents the results on the form. The navigation buttons provided by the control enable you to navigate among the rows returned by the query.

Comments

This simple application exemplifies the visual part of Visual Basic. The single line of executable code unloads the form and ends the program. Everything else required to enable live editing of data in the database is designed using visual tools.

7.2 How do I...

Add and delete records in a SQL Server database by using the RemoteData control?

Problem

My users need to be able to add and delete rows in my SQL Server tables as well as view and update existing rows. How do I add and delete rows using the RemoteData control?

Technique

Writing just a small amount of code enables you to implement the ability to insert and delete rows with the RemoteData control. The RemoteData control's Resultset object provides the AddNew and Delete methods. You can implement both with just a few lines of code and a user interface mechanism to invoke the procedures.

Inserting a row is a two-step process:

1. Call the AddNew method of the RemoteData control's Resultset to set the row buffer to a blank new row.

2. Call the Update method of the Resultset to insert the row into the table.

Deleting a row requires only a single call to the Delete method of the Resultset; but after the delete has been performed, the current row will be undefined, so you need to add code to move to a valid row. This example uses the Microsoft Access convention of moving to the previous row, but you could just as easily move to the next row.

Steps

Open and run project HT702.vbp. This is the same as the project created for How-To 7.1, with code and controls added to support inserts and deletes. You can browse, update, insert, and delete rows in the Publishers table using the RemoteData control's navigation buttons and the commands on the Data menu, as shown in Figure 7.2.

Figure 7.2. The Publishers form's New, Delete, and Save functions.


INSERTING AND DELETING ROWS IN THE PUBLISHERS TABLE

The Publishers table in the sample database has a rather unusual rule for the pub_id column:

(pub_id = `1756' or (pub_id = `1622' or
(pub_id = `0877' or (pub_id = `0736' or (pub_id = `1389')))) or
 (pub_id like `99[0-9][0-9]'))

This rule requires that the pub_id value be one of the five specific values shown (1756, 1622, 0877, 0736, or 1389) or that it be a four-digit number between 9900 and 9999. Don't be surprised if you see strange-looking rules like this appearing from time to time. This sort of thing is occasionally necessary to maintain compliance with legacy code, data, or both.

If you add rows to the table while working with the sample application, you need to make sure the pub_id column meets this rule. In a production application, you would probably want to add code to automatically generate a valid value for this column.
Additionally, there are other tables in the database that contain foreign key references to the Publishers table, so you might not be able to delete some of the existing rows. A complete application based on the pubs database would need to implement methods of dealing with these foreign key references. Alternately, you could write a delete trigger for the Publishers table that would delete any related rows in related tables.


1. Create a new Standard EXE project, add the Microsoft RemoteData Control 2.0 to the toolbox, and save the project as HT702.vbp.

2. FMain.frm is the same form developed for How-To 7.1. You can add
the existing file from the previous How-To, or refer to Table 7.1 to add the RemoteData control, labels, text boxes, and refer to Table 7.2 to create the File menu controls.

3. Use the Menu Editor to add the menu controls for the Data menu as shown in Table 7.3.

Table 7.3. Specifications for the Data menu.

CAPTION Name Shortcut Key
&Data mnuData
----&Save mnuDataSave Ctrl+S
----- mnuDataBar
----&New mnuDataNew Ctrl+N
----&Delete mnuDataDelete

4. Add Option Explicit to the declarations section.

5. Create the Form_Unload event procedure. This procedure sets up an error handler and calls the SaveRecord procedure described in step 10.

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo ProcError
  SaveRecord
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
6. Create the mnuFileExit_Click event procedure. This code is identical to the code in How-To 7.1.

Private Sub mnuFileExit_Click()
On Error Resume Next
  Unload Me
  If Err.Number <> 0 Then
    MsgBox Err.Number & vbCrLf & Err.Description
  End If
End Sub
7. Add code for the Data menu controls. The New, Delete, and Save commands call the corresponding AddRecord, DeleteRecord, and SaveRecord procedures described in steps 8, 9, and 10.

Private Sub mnuDataNew_Click()
On Error GoTo ProcError
  AddRecord
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuDataDelete_Click()
On Error GoTo ProcError
  DeleteRecord
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuDataSave_Click()
On Error GoTo ProcError
  SaveRecord
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
8. Create the AddRecord procedure. This procedure calls the AddNew method of the RemoteData control's Resultset to create the new record. The new row is inserted into the table later by the SaveRecord procedure.

Private Sub AddRecord()

  ` add it
  rdc.Resultset.AddNew
  txtID.SetFocus
End Sub
9. Add the DeleteRecord procedure. This procedure uses the Delete method of the RemoteData control's Resultset to remove the row from the table, then repositions the row pointer to a valid row in the table. The code first moves to the previous row, then checks for the beginning of the Resultset and, if necessary, moves to the first row.

Private Sub DeleteRecord()

  ` delete the row
  rdc.Resultset.Delete
  ` back up one row
  rdc.Resultset.MovePrevious
  ` check for beginning of set
  If rdc.Resultset.BOF Then
    rdc.Resultset.MoveFirst
  End If
End Sub
10. The SaveRecord procedure uses the EditMode property of the RemoteData control's Resultset to determine how to save the current row using a Select Case block. If the row has not been changed, no action is necessary. If an existing row has been changed, the UpdateRow method is used to commit the changes. New rows are added to the table by calling the Update method of the Resultset.

Private Sub SaveRecord()

  Select Case rdc.Resultset.EditMode
    Case rdEditNone
      ` clean record, do nothing
    Case rdEditInProgress
      ` the control handles regular edits
      rdc.UpdateRow
    Case rdEditAdd
      ` use the Update method of the
      ` resultset
      rdc.Resultset.Update
  End Select
End Sub

How It Works

This is a basic bound control application. The only thing that differentiates the techniques from an application based on the Data control is the use of the RemoteData control and a few minor variations in the code syntax. The control handles most of the work of displaying and updating data, with a little help from a few lines of code.

Comments

The RemoteData control is a convenient, but limiting, method of working with remote server databases. In the following How-To's, you'll learn to use Remote Data Objects to connect to and manage data in SQL Server databases.

7.3 How do I...

Connect to a SQL Server database by using Remote Data Objects?

Problem

My application needs to connect to a SQL Server database without using the RemoteData control. How can I create a connection with Remote Data Objects?

Technique

If an ODBC data source has been configured, you can establish a connection to a SQL Server database with as little as a single line of code using the OpenConnection method of the rdoEnvironment object. By adding an extra line of code or two, you can also create a so-called "DSN-less" connection with no preconfigured ODBC data source name.

Using a preconfigured ODBC data source adds one additional required step in configuring the workstation but also enables you to share the connection among multiple applications. If you embed the connection information in the source code for the application, you eliminate one step in setting up the workstation.

The following example demonstrates both methods. Additionally, RDO 2.0 enables you to create an rdoConnection object without an explicit physical connection to a remote database. After you have assigned the necessary properties to the rdoConnection object, use the EstablishConnection method to open the connection.

Steps

Open project HT703.vbp. Before running this project using a DSN-less connection, you will need to change the values used in creating the connect string in the OpenConnection routine to reflect the correct user name (UID=), password (PWD=), and SQL Server machine name (SERVER=). If you check the box marked Use pubs DSN, the application will open a connection using an ODBC data source named pubs (which you should have already created using the 32-bit ODBC control panel applet). If the check box is cleared, a DSN-less connection will be created. The connection form is shown in Figure 7.3.

1. Create a new Standard EXE project, add a reference to Microsoft Remote Data Object 2.0, and save the project as HT703.vbp.

2. Change the name of Form1 to FMain and add the objects and properties shown in Table 7.4.

Figure 7.3. The RDO Connect form.

Table 7.4. Objects and properties for FMain.

OBJECT Property Value
CheckBox Name chkUseDSN
Caption Use pubs DSN
CommandButton Name cmdConnect
Caption Connect

3. Add the following code to the declarations section. The module level variable mcon is used later by the OpenConnection routine.

Option Explicit
Private mcon As rdoConnection
4. Create the cmdConnect_Click event procedure. This routine sets up an error handler, calls OpenConnection, and displays a message indicating the success or failure of the connection.

Private Sub cmdConnect_Click()
On Error GoTo ProcError

  If OpenConnection() Then
    MsgBox "Connection Opened"
  Else
    MsgBox "Connection Failed"
  End If
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
5. Create the OpenConnection function. This function uses the value of the check box to determine whether it should use the pubs DSN or create a DSN-less connection; it then calls the OpenConnection method of the default rdoEnvironment object to establish the connection to the remote server. The function returns true if the connection was successfully established.

Private Function OpenConnection() As Boolean
On Error GoTo ProcError
  Dim sConnect As String
  If chkUseDSN = vbChecked Then
    ` use pubs DSN
    Set mcon = rdoEnvironments(0).OpenConnection("pubs")
  Else
    ` use DSN-less connection
    sConnect = _
        "UID=sa;" & _
        "PWD=MyPassword;" & _
        "DATABASE=pubs;" & _
        "SERVER=MyServer;" & _
        "DRIVER={SQL SERVER};" & _
        "DSN='';"
    Set mcon = rdoEnvironments(0).OpenConnection( _
        "", rdDriverNoPrompt, False, sConnect, rdAsyncEnable)
  End If
  OpenConnection = True
ProcExit:
  Exit Function
ProcError:
  OpenConnection = False
  Resume ProcExit
End Function

How It Works

A single line of code is all that's required to connect to a remote server. The OpenConnection method (or alternately the EstablishConnection method) connects you to the SQL Server database. You can then use the connection to execute SQL statements and create other RDO objects.

Comments

If you're familiar with the DAO object model, the rdoConnection object is the rough equivalent of the Database object. Many of the properties and methods are similar. In fact, you'll find the entire Remote Data Objects hierarchy very similar to the Data Access Objects hierarchy. The similarities in the two models make it easier to not only learn to use RDO but also to convert existing code from DAO to RDO.

7.4 How do I...

Browse records in a SQL Server database by using Remote Data Objects?

Problem

How can I use Remote Data Objects to browse rows in a SQL Server database?

Technique

Remote Data Objects (RDO) provide the rdoResultset object --similar to the DAO Recordset object--that you can use to capture and browse the results of a SELECT query. If you've programmed with DAO, you'll find the rdoResultset familiar. The various Move methods work the same as the methods of the Recordset, and you still use AddNew, Edit, and Update to make changes to the data in a row. Many of the properties are also the same as those of the DAO Recordset. In fact, much of the code written for rdoResultsets is so similar that you could change the declaration and the Set statement and use your existing DAO code.

Despite the code similarities, there are differences in the techniques used to access SQL Server data with rdoResultsets:


A NOTE ON CURSORS

Don't be confused by all the new terminology. A cursor is essentially just a set of pointers to rows in a resultset that enables you to browse the results.


You create a resultset by using the OpenResultset method of an rdoConnection. You typically will provide a SQL statement to the method that specifies the rows to return. You can additionally specify the type of cursor, which by default is forward-only, and the type of locking which by default is read-only.

After you have created the resultset, you can browse the rows using the various navigation methods: MoveFirst, MovePrevious, MoveNext, MoveLast, and Move. Like the DAO Recordset, the rdoResultset object provides the BOF and EOF properties that you can use to test for the ends of the resultset.


NOTE SQL Server queries can return more than one set of results. Use the MoreResults method to test for additional resultsets from the query.

Values for individual columns in the rows retrieved are obtained from the rdoColumns collection. This is the default collection for a resultset. You can use the same syntax styles for columns in a resultset that you use for fields in a DAO recordset:

Depending on the query, the type of cursor, and the type of locking, the data might be read-only or might be updatable.

Steps

Before you can use this example, you need to install the States table in the pubs sample database. Using SQL Enterprise Manager or I-SQL/W, load the States.sql script, select the pubs database, and run the script. The script creates and populates the States table with a list of the 50 states in the U.S. as well as the District of Columbia and Puerto Rico. After you have installed this table, you can run the sample application.


NOTE You can run this script against any database where you need this table.

Open and run project HT704.vbp. You can use the form shown in Figure 7.4 to browse and update the rows in the sample table from the pubs database. This project makes a simple extension to the project used for How-To 7.2. The text box used for the state column in the Publishers table has been replaced with a combo box that contains a list of state postal codes, using a query and
an rdoResultset object. This is a commonly implemented user interface convenience. Unless people work all day every day with a data entry application, few will remember the postal codes for all the states, so the combo box enables the user to pick a value from a list on the form.

Figure 7.4. The modified Publishers form.

1. Create a new Standard EXE project, add a reference to Microsoft Remote Data Object 2.0, and save the project as HT704.vbp. Rename the default Form1 to FMain, save it as FMain.frm, and add the object and properties shown in Table 7.5. The menu controls for this example are identical to those in How-To 7.2.

Table 7.5. Objects and properties for FMain.

OBJECT Property Value
Form Caption Publishers
RemoteData Control Name rdc
Caption Publishers
Align 2 - vbAlignBottom
DataSourceName pubs
SQL SELECT pub_id, pub_name, city, state, country FROM publishers
Label Name lbl
Caption ID
Index 0
TextBox Name txtID
DataSource rdc
DataField pub_id
Label Name lbl
Caption Name
Index 1
TextBox Name txtName
DataSource rdc
DataField pub_name
Label Name lbl
Caption City
Index 2
TextBox Name txtCity
DataSource rdc
DataField city
Label Name lbl
Caption State
Index 3
ComboBox Name cboState
DataSource rdc
DataField state
Label Name lbl
Caption Country
Index 4
TextBox Name txtCountry
DataSource rdc
DataField country

2. Add the following code to the declarations section. The rdoConnection object is used later to populate the state combo box.

Option Explicit

Private mcon As rdoConnection
3. Create the Form_Load event procedure. This procedure calls the OpenConnection procedure to connect to the remote server and then calls GetStates to populate the combo box.

Private Sub Form_Load()
On Error GoTo ProcError
  OpenConnection
  GetStates
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
4. Add the following procedures to handle the click events for the menu controls. These procedures are identical to those developed in How-To 7.2.

Private Sub mnuDataDelete_Click()
On Error GoTo ProcError
  DeleteRecord
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuDataNew_Click()
On Error GoTo ProcError
  AddRecord
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuDataSave_Click()
On Error GoTo ProcError
  SaveRecord
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuFileExit_Click()
On Error Resume Next
  Unload Me
  If Err.Number <> 0 Then
    MsgBox Err.Number & vbCrLf & Err.Description
  End If
End Sub
5. Create the AddRecord, NewRecord, and SaveRecord procedures. These procedures are also identical to those in How-To 7.2.

Private Sub AddRecord()

  rdc.Resultset.AddNew
  txtID.SetFocus
End Sub
Private Sub DeleteRecord()
  ` delete the row
  rdc.Resultset.Delete
  ` back up one row
  rdc.Resultset.MovePrevious
  ` check for beginning of set
  If rdc.Resultset.BOF Then
    rdc.Resultset.MoveFirst
  End If
End Sub
Private Sub SaveRecord()
  Select Case rdc.Resultset.EditMode
    Case rdEditNone
      ` clean record, do nothing
    Case rdEditInProgress
      ` the control handles regular edits
      rdc.UpdateRow
    Case rdEditAdd
      ` use the Update method of the
      ` resultset
      rdc.Resultset.Update
  End Select
End Sub
6. Create the OpenConnection procedure. This is based on How-To 7.3 and uses the pubs ODBC DSN to create the connection to the SQL Server.

Private Sub OpenConnection()

  Dim sConnect As String
  ` default using a configured DSN
  Set mcon = rdoEnvironments(0).OpenConnection("pubs")
End Sub
7. Create the GetStates subroutine. This code uses an rdoResultset to populate the cboState combo box. It creates a forward-only, read-only cursor based on the States table. Because only one pass through the results is required, a forward-only cursor is sufficient. The rdExecDirect option bypasses ODBC's normal step of creating a prepared statement and directly executes the query on the SQL Server.

Private Sub GetStates()
` populate the states combo box
  Dim sSQL As String
  Dim rsStates As rdoResultset
  sSQL = "SELECT StateCode FROM States"
  ` we only need one pass through this data,
  ` and will only need to do it once, so we
  ` use a read only, forward only cursor and
  ` the exec direct option
  Set rsStates = mcon.OpenResultset(sSQL, _
      rdOpenForwardOnly, rdConcurReadOnly, rdExecDirect)
  ` populate the combo box
  Do While Not rsStates.EOF
    cboState.AddItem rsStates!StateCode
    rsStates.MoveNext
  Loop
  ` clean up
  rsStates.Close
End Sub

How It Works

The primary object you will use in the Remote Data Objects hierarchy is the rdoResultset object. The rdoResultset object enables you to submit a SQL query (over an rdoConnection object) and process the results. Depending on the type of query, you might also be able to add, delete, and update the rows in the resultset.

In this example, the resultset in the GetStates procedure is used only to populate the combo box and avoid the use of a data bound ComboBox control.

Comments

Many of the objects in the Remote Data Objects hierarchy have events in addition to properties and methods. You can use these events by declaring the objects using the WithEvents keyword and write event procedures for these events. For example, the rdoResultset object provides the RowStatusChanged event that you can use to trigger code that should run if a row is updated or deleted.

7.5 How do I...

Add, update, and delete records in a SQL Server database by using Remote Data Objects?

Problem

Bound controls use a database connection for each Data control; large applications based on bound controls would use too many connections. How can I create an unbound form based on SQL Server data using Remote Data Objects?

Technique

Building unbound applications with Remote Data Objects is no different from building unbound applications using any other data access approach, including Data Access Objects or standard Visual Basic file I/O techniques. Here are the steps:

1. Retrieve the data from the data source; in this example you will use an rdoResultset to manage the data.

2. Display the values in the controls on the form.

3. Update the tables in the database when the user moves to a new row, unloads the form, or explicitly requests that the data be saved.

If you've already read Chapter 2, "Accessing a Database with Data Access Objects," you will be familiar with these techniques. The only thing that has changed is the object model used to manage the data. In the previous How-To, you were introduced to using an rdoResultset to retrieve data from a SQL Server database. In this How-To, you will extend the use of the rdoResultset to inserting, updating, and deleting rows.

How-To 7.4 used a forward-only, read-only cursor--the default for rdoResultset objects created using the OpenResultset method of an rdoConnection object. Because you will now be making changes to the data as well as providing the capability to navigate both forward and backward through the rows, you will need a more flexible type of cursor. Using a keyset-type cursor enables you to add, update, or delete rows with minimal coding complexity and lower resource requirements than a dynamic cursor.


NOTE If you did not install the States table in How-To 7.4, do so now by running the States.sql SQL script, using SQL Enterprise Manager or I-SQL/W. This script creates and populates a table of state names and postal codes for U.S. states.

Steps

Open and run project HT705.vbp. Use the form shown in Figure 7.5 to browse, add, update, or delete rows in the Publishers table. In this example, the RemoteData control has been replaced with Visual Basic code. Toolbar buttons created with a Toolbar control replace the navigation buttons provided by the RemoteData control. With the commands on the Data menu, you can create new rows, save changes to an existing row, or delete the current row.

Figure 7.5. The unbound Publishers form.

1. Create a new Standard EXE project, add a reference to Microsoft Remote Data Object 2.0, and save the project as HT705.vbp. Rename Form1 to FMain, save it as FMain.frm, and add the objects and properties shown in Table 7.6.

Table 7.6. Objects and properties for FMain.

OBJECT Property Value
Form Caption Publishers
Toolbar Name tb
Label Name lbl
Caption ID
Index 0
TextBox Name txtID
Label Name lbl
Caption Name
Index 1
TextBox Name txtName
Label Name lbl
Caption City
Index 2
TextBox Name txtCity
Label Name lbl
Caption State
Index 3
ComboBox Name cboState
Label Name lbl
Caption Country
Index 4
TextBox Name txtCountry

2. Use the Menu Editor to create the menu controls shown in Table 7.7.

Table 7.7. Menu controls for FMain.

CAPTION Name Shortcut Key
&File mnuFile
----E&xit mnuFileExit
&Data mnuData
----&Save mnuDataSave Ctrl+S
----- mnuDataBar
----&New mnuDataNew Ctrl+N
----&Delete mnuDataDelete

3. Add the following code to the declarations section. The rdoConnection and rdoResultset objects are used by the form to manage the data in the sample database. The enumeration and the mlRowState variable are used to track the current state of the data on the form.

Option Explicit
` connection
Private mcon As rdoConnection
` resultset
Private mrsPublishers As rdoResultset
` record state
Private Enum RowState
  RowStateClean = 1
  RowStateDirty = 2
  RowStateNew = 3
End Enum
Private mlRowState As RowState
4. Create the Form_Load and Form_Unload event procedures. In the Load event, the form sets up the toolbar, opens the resultset for the Publishers table upon which the form is based, and populates the cboState combo box. In the unload event, the form saves any changes and closes and releases the object variables created in the declarations section. The Initialize, GetStates, and SetupToolbar procedures are described in steps 9, 11, and 20.

Private Sub Form_Load()
On Error GoTo ProcError
  SetupToolbar
  Initialize
  GetStates
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub Form_Unload(Cancel As Integer)
` save before exiting
On Error GoTo ProcError
  ` save the current row
  SaveRow
  ` close down resultset and connection
  Terminate
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
5. Create the tb_ButtonClick event procedure. This procedure calls procedures to navigate among the rows in the resultset.

Private Sub tb_ButtonClick(ByVal Button As ComctlLib.Button)
` toolbar handles navigation
  Select Case Button.Key
    Case "First"
      MoveFirst
    Case "Previous"
      MovePrevious
    Case "Next"
      MoveNext
    Case "Last"
      MoveLast
  End Select
End Sub
6. Add the following event procedures to track the edit state of the data on the form. Each procedure changes the module level mlRowState variable to RowStateDirty if the current value is RowStateClean. This enables later code to determine whether current changes should be sent to the SQL Server as an update or an insert.

Private Sub cboState_Click()
` mark row dirty
  If mlRowState = RowStateClean Then
    mlRowState = RowStateDirty
  End If
End Sub
Private Sub cboState_Change()
` mark row dirty
  If mlRowState = RowStateClean Then
    mlRowState = RowStateDirty
  End If
End Sub
Private Sub txtCity_Change()
` mark row dirty
  If mlRowState = RowStateClean Then
    mlRowState = RowStateDirty
  End If
End Sub
Private Sub txtCountry_Change()
` mark row dirty
  If mlRowState = RowStateClean Then
    mlRowState = RowStateDirty
  End If
End Sub
Private Sub txtID_Change()
` mark row dirty
  If mlRowState = RowStateClean Then
    mlRowState = RowStateDirty
  End If
End Sub
Private Sub txtName_Change()
` mark row dirty
  If mlRowState = RowStateClean Then
    mlRowState = RowStateDirty
  End If
End Sub
7. Create the mnuFileExit_Click event procedure. This procedure unloads the form, ending the application.

Private Sub mnuFileExit_Click()
On Error Resume Next
  Unload Me
  If Err.Number <> 0 Then
    MsgBox Err.Number & vbCrLf & Err.Description
  End If
End Sub
8. Add the following event procedures for the commands on the Data menu. Each menu command event procedure calls a corresponding subroutine in the form module to perform the requested operation. The NewRow, SaveRow, and DeleteRow procedures are described in steps 12, 13, and 14.

Private Sub mnuDataNew_Click()
On Error GoTo ProcError
  NewRow
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuDataSave_Click()
On Error GoTo ProcError
  SaveRow
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
Private Sub mnuDataDelete_Click()
On Error GoTo ProcError
  DeleteRow
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
9. Create the Initialize procedure. This subroutine is called from the Form_Load event procedure. It opens a connection to the SQL Server database, creates a resultset using the same query used in the previous RemoteData control examples, and then calls the ColumnsToControls procedure to display the first row in the resultset on the form. A keyset cursor with optimistic locking based on row values is used so that the resultset will be updatable.

Private Sub Initialize()
` connect and open the publishers resultset
  Dim sConnect As String
  Dim sSQL As String
  ` default using a configured DSN
  Set mcon = rdoEnvironments(0).OpenConnection( _
      "pubs")
  ` initialize the publishers resultset
  ` create the SQL statement
  sSQL = "SELECT pub_id, pub_name, city, state, country " & _
      "FROM publishers"
  ` use a keyset cursor
  Set mrsPublishers = _
      mcon.OpenResultset(sSQL, rdOpenKeyset, rdConcurValues)
  ` display the first row
  ColumnsToControls
  ` mark it clean
  mlRowState = RowStateClean
End Sub
10. The Terminate procedure, called from the Form_Unload event, performs a cleanup operation by closing the module level rdoConnection and rdoResultset objects:

Private Sub Terminate()
` clean up
  mrsPublishers.Close
  mcon.Close
End Sub
11. The GetStates procedure is the same code used in How-To 7.4:

Private Sub GetStates()
` populate the states combo box
  Dim sSQL As String
  Dim rsStates As rdoResultset
  sSQL = "SELECT StateCode FROM States ORDER BY StateCode"
  ` we only need one pass through this data,
  ` and will only need to do it once, so we
  ` use a read only, forward only cursor and
  ` the exec direct option
  Set rsStates = mcon.OpenResultset(sSQL, _
      rdOpenForwardOnly, rdConcurReadOnly, rdExecDirect)
  ` populate the combo box
  Do While Not rsStates.EOF
    cboState.AddItem rsStates!StateCode
    rsStates.MoveNext
  Loop
  ` clean up
  rsStates.Close
End Sub
12. Create the NewRow procedure. This code saves the current data, clears the controls, and sets the module level mlRowState state variable to RowStateNew, in preparation for the creation of a new row in the table.

Private Sub NewRow()
` create a new row
  ` save current data
  SaveRow
  ClearControls
  mlRowState = RowStateNew
End Sub
13. Create the SaveRow subroutine. The code uses either the Edit or AddNew method of the rdoResultset object to update an existing row or insert a new row, based on the value of the mlRowState module level state flag.

Private Sub SaveRow()
` save current data
  Select Case mlRowState
    Case RowStateDirty
      mrsPublishers.Edit
      ControlsToColumns
      mrsPublishers.Update
      mlRowState = RowStateClean
    Case RowStateNew
      mrsPublishers.AddNew
      ControlsToColumns
      mrsPublishers.Update
      mlRowState = RowStateClean
      mrsPublishers.Move 0
    Case Else
      ` nothing to do
  End Select
End Sub
14. The DeleteRow procedure uses the Delete method of the mrsPublishers rdoResultset object to delete the current row; the procedure then calls MovePrevious to back up to the previous row:

Private Sub DeleteRow()
` delete the current row
  mrsPublishers.Delete
  MovePrevious
End Sub
15. Create the MoveFirst and MoveLast subroutines. Each routine saves the current row, performs the appropriate move operation, displays the new row, and marks it as being clean.

Private Sub MoveFirst()
` goto first row
  SaveRow
  mrsPublishers.MoveFirst
  ColumnsToControls
  mlRowState = RowStateClean
End Sub
Private Sub MoveLast()
` goto last row
  SaveRow
  mrsPublishers.MoveLast
  ColumnsToControls
  mlRowState = RowStateClean
End Sub
16. Create the MovePrevious and MoveNext procedures. These are similar to the MoveFirst and MoveLast routines, but with code added to test for BOF and EOF conditions so that a valid row will always be displayed by the form.

Private Sub MovePrevious()
` goto previous row
  SaveRow
  mrsPublishers.MovePrevious
  If mrsPublishers.BOF Then
    mrsPublishers.MoveFirst
  End If
  ColumnsToControls
  mlRowState = RowStateClean
End Sub
Private Sub MoveNext()
` MoveNext w/ EOF handling
  SaveRow
  mrsPublishers.MoveNext
  If mrsPublishers.EOF Then
    mrsPublishers.MoveLast
  End If
  ColumnsToControls
  mlRowState = RowStateClean
End Sub
17. Create the ColumnsToControls routine. This procedure copies the values from the current row in the resultset to the controls on the form. In order to eliminate the possibility of writing Null values to the Text property of the text boxes, a zero-length string is appended to the value of the column.

Private Sub ColumnsToControls()
` load the current row in mrsPublishers to the controls
  txtID = mrsPublishers!pub_id & ""
  txtName = mrsPublishers!pub_name & ""
  txtCity = mrsPublishers!city & ""
  cboState = mrsPublishers!state & ""
  txtCountry = mrsPublishers!country & ""
End Sub
18. Create the ControlsToColumns procedure. This procedure compares the values on the form to the values in the columns and--if they have changed--copies the new values to the columns.

Private Sub ControlsToColumns()
` copy controls to current row
  Dim sID As String
  Dim sName As String
  Dim sCity As String
  Dim sState As String
  Dim sCountry As String
  ` get the values
  sID = txtID
  sName = txtName
  sCity = txtCity
  sState = cboState
  sCountry = txtCountry
  ` copy to columns only if changed
  With mrsPublishers
    If !pub_id <> sID Then
      !pub_id = sID
    End If
    If !pub_name <> sName Then
      !pub_name = sName
    End If
    If !city <> sCity Then
      !city = sCity
    End If
    If !state <> sState Then
      !state = sState
    End If
    If !country <> sCountry Then
      !country = sCountry
    End If
  End With
End Sub
19. The ClearControls subroutine writes zero-length strings to each of the controls and is called when creating a new row:

Private Sub ClearControls()
` clear existing values from controls
  txtID = ""
  txtName = ""
  txtCity = ""
  cboState = ""
  txtCountry = ""
End Sub
20. Enter the following code as the SetupToolbar subroutine. This procedure is called at startup and adds the four navigation buttons to the Toolbar control.

Private Sub SetupToolbar()
` setup toolbar buttons
  With tb.Buttons
    .Add , "First", "First"
    .Add , "Previous", "Previous"
    .Add , "Next", "Next"
    .Add , "Last", "Last"
  End With
End Sub


NOTE In a production application, you should set up more pleasing buttons with appropriate images in an associated ImageList control.

How It Works

At startup, the form connects to the SQL Server, submits a query, and captures the results of the query in the mrsPublishers rdoResultset object. Code attached to the menu controls and toolbar buttons provides the capability for navigation, inserts, updates, and deletes using the methods of the rdoResultset object.


NOTE You can easily implement an undo command by reloading the current row from the rdoResultset into the controls with a call to the ColumnsToControls procedure. A column-level undo command could be implemented by restoring only a single column value from the rdoResultset object.

Comments

In Chapter 2, "Accessing a Database with Data Access Objects," a more robust approach was implemented using a class module to encapsulate all the data access code. That approach would work equally well with this example. For the sake of simplicity, the more direct approach of placing the data access code directly in the form module was used. Although this code might be somewhat simpler to understand, over the lifetime of an application, it might prove more difficult to maintain because of the tight coupling of the data access code with the user interface code.


QUERYING THE DATABASE

The SQL statement used to provide the source of the data for this form is somewhat simplistic for a server database. Although the pubs sample database contains only a handful of rows, many tables will have hundreds, thousands, or even millions of rows of data. If you issue a SELECT query with no WHERE clause against such a large table in a production application, you will at best produce an application with terrible performance and will quite possibly be summoned to the office of your database administrator and be given a painful lesson in how to submit a proper query. Submitting queries that return only the data you need is always good advice for any database application but is even more critical if you are sending the query to a busy database server.


7.6 How do I...

Execute a SQL Server stored procedure by using Remote Data Objects?

Problem

Many of the queries I need to run are returned as the results of stored procedures on the server. How can I execute a stored procedure and capture the resultset with Remote Data Objects?

Technique

A SQL Server stored procedure is a precompiled set of Transact-SQL statements that are executed using a single statement. Because the statements have been precompiled and the queries have already been optimized, SQL Server can often execute stored procedures much more efficiently than ad hoc SQL statements submitted for processing. Many large database applications are built almost entirely using stored procedures to retrieve and edit rows. Additionally, stored procedures can provide an added measure of security by allowing access to tables and columns that would otherwise be unavailable. A user only needs permission to execute a stored procedure, regardless of the permissions on the underlying tables and columns.

Stored procedures can range from simple SELECT queries that return results to complex Transact-SQL procedures that take input and output parameters and return values and multiple resultsets. Although the details of writing stored procedures on the server is beyond the scope of this chapter, it is not difficult to capture and use the results of stored procedures.


NOTE Stored procedures always return read-only results.

Depending on the nature of the procedure, you might execute the procedure directly using the Execute method of an rdoConnection object, or you might capture results of a stored procedure by using an rdoQuery or rdoResultset object.


THE TROUBLE WITH STORED PROCEDURES

Transact-SQL, from a programming perspective, can be a difficult language to work with. SQL was designed to manage data and database objects, and the commands and system stored procedures are often cryptic. Debugging tools are limited, and in many cases you need to understand the inner workings of a procedure before you can use it. This can make it difficult to create procedures that act as "black boxes" with well-defined interfaces. However, the advantages in data processing efficiency offered by stored procedures often far outweigh these disadvantages, especially for heavily used, large database applications.


Steps

Use SQL Enterprise Manager or I-SQL/W to create the spStates stored procedure listed in step 2; then open and run project HT706.vbp. This example uses a form that is visually identical to the form shown in Figure 7.5 in the preceding How-To. Only two lines of code have been changed, but a minor change of this nature has the potential to provide a significant performance improvement in a production application.

1. Create a new Standard EXE project, add a reference to Microsoft Remote Data Object 2.0, and save the project as HT706.vbp. The single form for the application, FMain is identical to the FMain form used in HT705.vbp, with the exception of the change shown in step 3.

2. Using SQL Enterprise Manager or I-SQL/W, select the pubs database and execute the following SQL Script to create the spStates stored procedure:

CREATE PROCEDURE spStates AS
  SELECT StateCode FROM States ORDER BY StateCode
Go
GRANT EXECUTE ON spStates TO public
Go


NOTE You will need to have already installed the States table using the States.sql script. The script shown above can be found in spStates.sql.
3. Replace the GetStates procedure with the following code. Two changes were made to the original procedure in HT705.vbp. The sSQL string variable and the assignment of the SQL statement were removed, and the name parameter of the OpenResultset method was replaced with the spStates stored procedure. The balance of the project is identical.

Private Sub GetStates()
` populate the states combo box
  Dim rsStates As rdoResultset
  ` we only need one pass through this data,
  ` and will only need to do it once, so we
  ` use a read only, forward only cursor and
  ` the exec direct option
  Set rsStates = mcon.OpenResultset("spStates", _
      rdOpenForwardOnly, rdConcurReadOnly, rdExecDirect)
  ` populate the combo box
  Do While Not rsStates.EOF
    cboState.AddItem rsStates!StateCode
    rsStates.MoveNext
  Loop
  ` clean up
  rsStates.Close
End Sub

How It Works

The original query was a SELECT statement submitted directly to the server for processing:

SELECT StateCode FROM States ORDER BY StateCode

This was replaced with a stored procedure that executes the same query. Because stored procedures have already been compiled and optimized, a few steps have been saved on the SQL Server each time this query has been submitted. Only a minor change is required for the GetStates procedure to take advantage of the stored procedure because the procedure was already designed to use a read-only resultset.

Comments

There might be no difference in the steps the SQL server takes to process the rows for this query. If you execute the original SQL statement directly in SQL Enterprise Manager and then execute the stored procedure (both with the Show Query Plan option turned on), you will see that the plan is identical for both queries. However, you are still saving the SQL Server the time required to compile and optimize the query.

For this type of data, you should design your application to run the query once (either at start time or the first time it is needed) and then cache the data locally for future use. On a table as stable as this one, you could consider permanently retaining a local copy and not querying the server at all. However, most database applications will need to run this type of query routinely. Often many small queries are run to populate lookup tables of coded values and other types of selection lists for combo boxes, list boxes, and so on. This type of data probably does not change often, but might not be stable enough to hardwire the values into the source code.

If you routinely submit a number of these queries in your application, a good solution might be to design a single stored procedure on the server that returns several or possibly all of these queries at once; then cache the results locally after the first execution. When you submit a query or stored procedure that returns multiple resultsets, you use the MoreResults method of the rdoResultset object to determine whether there are additional resultsets to process. You can also use the GetRows method to quickly and easily populate an array with the results of a query for temporary local storage.

7.7 How do I...

Execute a parameterized SQL Server stored procedure with Remote Data Objects?

Problem

I need to capture the return value of a stored procedure that takes parameters. How can I do this with Remote Data Objects?

Technique

SQL Server stored procedures, like Visual Basic procedures, can take input and output parameters and can return values. To capture these values, you need to use a different technique from that used in How-To 7.6. The rdoQuery object provides the rdoParameters collection to manage the parameters of a stored procedure.

The question mark character is the placeholder for parameters in a SQL Server query. If a procedure takes parameters, you put question marks in the SQL statement where the parameters would be entered if you were submitting the query interactively. The following SQL statement would create a parameter query based on a CustLast column:

SELECT CustID, CustFirst, CustLast FROM Customers WHERE CustLast = ?

To supply this parameter when you execute the query, you need to create an

rdoQuery object using the CreateQuery method:

` cn is a connection object defined elsewhere
` qry is an object variable declared as rdoQuery
` sSQL is a string variable
sSQL = "SELECT CustID, CustFirst, CustLast FROM Customers WHERE CustID = ?
Set qry = cn.CreateQuery("",sSQL)

This query will now have a single parameter in its rdoParameters collection.

You can assign a value to the parameter and execute the query:

` the parameters collection is zero-based
qry(0) = 12
` rs is an rdoResultset object
Set rs = qry.Execute

Stored procedures can also take parameters, but to capture output parameters or return values, you need to use the OBDC call syntax. Here's the beginning of the sp_addgroup procedure from the master database:

create procedure sp_addgroup
@grpname varchar(30)

In addition to the grpname input parameter, sp_addgroup also returns a value indicating the success or failure of the procedure. With the ODBC call syntax, this query would be created as

{? = call sp_addgroup (?) }

The question mark at the beginning of the statement acts as a placeholder for
the return value, and the question mark at the end acts as a placeholder for the grpname parameter. Normally, the ODBC driver can determine whether the parameters are input or output, but you can also supply the direction explicitly by using the Direction property of the rdoParameter object:

qry(0).Direction = rdDirectionInput

Along with input and output parameters, you can also use rdParamReturnValue to specify that a parameter is a stored procedure return value.

Steps

The sample project for this How-To is a SQL Server Change Password dialog box. Open and run project HT707.vbp to display the form shown in Figure 7.6. The dialog box uses the sp_password system stored procedure (in the master database) to change the password for the current user.

Figure 7.6. The Change Password dialog box.


NOTE If you are the sa, you can use sp_password to change the password for any user--with or without the existing password. This is a standard security mechanism. Administrators can change, but not read, existing passwords. Windows NT Server domain administrators and Access database administrators have the same capability. This dialog box does not provide that capability. If you have sa authority and need to change the password of another user, execute sp_password using SQL Enterprise Manager or I-SQL/w.
1. Create a new Standard EXE project and save it as HT707.vbp. Change the name of Form1 to FMain and save it as FMain.frm, and then create the objects and properties shown in Table 7.8.

Table 7.8. Objects and properties for FMain.

OBJECT Property Value
Form Caption Change Password
Border Style 3 - Fixed Dialog
Label Name lbl
Caption Old Password
Index 0
TextBox Name txtOld
Label Name lbl
Caption New Password
Index 1
TextBox Name txtNew
Label Name lbl
Caption Confirm New Password
Index 2
TextBox Name txtNewConfirm
CommandButton Name cmd
Caption OK
Default True
Index 0
CommandButton Name cmd
Caption Cancel
Cancel True
Index 1

2. Add Option Explicit to the declarations section of the form module, and then create the cmd_Click event procedure. This procedure calls the ChangePassword procedure if OK was clicked or unloads the form if Cancel was clicked.

Private Sub cmd_Click(Index As Integer)
On Error GoTo ProcError
  Select Case cmd(Index).Caption
    Case "OK"
      ChangePassword
    Case "Cancel"
      Unload Me
  End Select
ProcExit:
  Exit Sub
ProcError:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume ProcExit
End Sub
3. Add the following code as the ChangePassword function. This procedure uses the values entered in the text boxes to supply the parameters for the sp_password system stored procedure. The procedure first verifies that the user entered the same value in the New Password and Confirm New Password text boxes; it then opens a connection to the SQL Server.

4. The pubs DSN is used to open the connection, but any valid connection to the server will work because the procedure name is fully qualified in the master database. After the connection has been established, the procedure creates an rdoQuery object, sets the direction of the parameters, and assigns the values of the two input parameters. Finally, the query is executed using the Execute method of the rdoQuery object, and the return value is captured using the rdoParameters collection. The sp_password returns 0 if the password was successfully changed, and this value is tested to display an appropriate message based on the return value of the stored procedure.

Private Sub ChangePassword()
` change the current user's password
` using the sp_password system stored procedure
  Dim con As rdoConnection
  Dim sSQL As String
  Dim qry As rdoQuery
  Dim sOld As String
  Dim sNew As String
  Dim sNewConfirm As String
  sOld = txtOld
  sNew = txtNew
  sNewConfirm = txtNewConfirm
  If sNew <> sNewConfirm Then
    ` mismatch, inform, clear values and exit
    MsgBox "New passwords do not match."
    txtNew = ""
    txtNewConfirm = ""
    txtNew.SetFocus
    Exit Sub
  End If
  Set con = _
      rdoEnvironments(0).OpenConnection _
      ("pubs")
  ` use the ODBC call syntax to capture the return value
  ` this is needed to know if the change succeeded
  sSQL = "{? = call master.dbo.sp_password (?,?) }"
  ` create the query object
  Set qry = con.CreateQuery("", sSQL)
  ` set direction for param 0
  qry(0).Direction = rdParamReturnValue
  qry(1).Direction = rdParamInput
  qry(2).Direction = rdParamInput
  ` this is equivalent to using
  ` qry.rdoParameters(1)
  qry(1) = sOld
  qry(2) = sNew
  ` run it
  qry.Execute
  ` sp_password returns 0 if successful
  ` the return parameters is always #0
  If qry(0) = 0 Then
    MsgBox "Password changed."
    Unload Me
  Else
    MsgBox "Unable to change password."
  End If
End Sub

How It Works

Only the ODBC call syntax can be used if you need to capture the output or return parameters from a stored procedure. By using this syntax in the SQL statement, you can create an rdoQuery object with an rdoParameters collection and use the parameters to supply input values and capture output and return values.

This procedure could also be run by directly providing the old and new password as part of the SQL statement and run using the Execute method of an rdoConnection object, but you would not be able to determine whether the procedure succeeded or failed because the return value would be unavailable.

Comments

This simple Change Password dialog box could easily be plugged into any SQL Server database application by changing the code used to open the connection to the server or by supplying a valid connection using a public property of the form.

7.8 How do I...

Handle Remote Data Objects errors?

Problem

I know that there will be runtime errors generated in my application and that I must trap and handle these errors to make the application robust and reliable. How do I handle errors generated by Remote Data Objects?

Technique

As with any Visual Basic application, you must trap and handle the errors that are generated at runtime, or the Visual Basic runtime DLL will invoke its own default error handler--which simply displays a message and terminates your application. Error-handling techniques for Remote Data Objects applications are similar to the techniques used for handling any other Visual Basic runtime errors, with one subtle but important variation: More than one error can be generated by a single statement in Remote Data Objects.

To enable you to deal with this possibility, Remote Data Objects provides the rdoErrors collection. This is a collection of rdoError objects you can examine the same way you examine the Visual Basic Err object. You can check the Number, Source, and Description properties. An rdoError object also provides the SQLRetCode and SQLState properties for you to examine.

Each of these properties might or might not provide useful information. SQL Server error messages can often be cryptic at best, and although a Visual Basic runtime error is generated, you get only the first error in the rdoErrors collection. You will normally need to iterate the rdoErrors collection to find the true nature of the problem because the first error is often a generic message such as Command has been aborted.--a true but generally useless piece of information for debugging without having the underlying cause of the problem.

The other significant difference in handling errors generated by Remote Data Objects is that you need to be prepared to deal with a much wider array of possible problems, ranging from typical Visual Basic errors like Invalid Use of Null (a common error in database applications) to the sudden death of the SQL Server at the other end of the connection. Along with the normal array of possible data manipulation problems (invalid foreign key values, data missing from required columns, and so on), most large SQL Server databases will have a wide variety of often complex business rules that are enforced. If rules are enforced using SQL Server triggers or stored procedures, the error numbers and messages generated will be dependent on the developer that created the Transact-SQL code. If that developer was conscientious in developing the Transact-SQL code, you will receive descriptive and informative messages. If you are developing your own Transact-SQL code, provide as much information as you reasonably can and use a sensible convention for generating error numbers.


NOTE You generate errors in Transact-SQL by using the RAISERROR statement.

Although there are standard techniques for trapping and examining errors, the means you use to handle the errors is entirely up to you and dependent on the context of the error. If a rule or some type of constraint is violated, you might be able to provide a message to the user with information about the problem and possible solutions. For more severe errors, such as a lost connection to the database server over the network, you might have little choice but to gracefully terminate the application, informing the user of the problem in the process.

There are several avenues available for you to deal with possible problems:

Your Responsibilities as a Database Application


DEVELOPER

As the developer of a database application, you have two responsibilities that can sometimes be at odds with each other. You need to provide an easy-to-use, high-performance application for the user. Users are mainly concerned with getting their work done and don't care much about the intricacies of SQL Server rules, foreign key constraints, and other problems. At the same time, you need to make sure that only valid data is stored in the database because the data can be worse than useless if it is not reliable.
Most database developers tend to err on the side of caution where data validation is concerned, often at the expense of end user productivity. How strictly you need to enforce your rules depends on the nature of the application. If you are working with a financial application where precision is paramount, you might need to be more severe in your enforcement of business rules. However, it is often the case that developers enforce rules that can potentially be ignored--at least temporarily--for the sake of the productivity of the end user.

Only you can determine how strict you need to be, but you should try to make an effort to sterilize the data only if it's truly necessary. In most cases, it's wishful thinking to believe that you can build an application where no bad data can be entered, so attempts to do so only hurt user productivity. If you are faced with a decision about data validation and rule enforcement, make the decision in the context of the overall goal of the application rather than looking only at the details of table or column level rules.


Steps

There is no specific project that demonstrates handling errors from Remote Data Objects. You are probably already well versed with basic Visual Basic error handling techniques, so rather than provide a contrived example, the following steps examine some of the problems you might encounter in the existing examples from How-To 7.2 and How-To 7.7. If you need a review of the essentials of Visual Basic error handling, see How-To 2.8, which covers the basic techniques of runtime error handling.

In How-To 7.2, a bound control application was developed that enables you to browse, update, insert, and delete rows in the Publishers table of the pubs sample database. There are two known problems involved in working with the Publishers table that were described in the chapter. First, a rather strange check constraint is enforced on the pub_id column, and second, there are foreign keys in other tables that reference the pub_id column. Inserts, updates, and deletes can all generate violations of these rules, and the basic error handlers provided in the existing example do not provide any information about the true nature of the problem.

1. Open and run HT702.vbp. The first publisher displayed should be New Moon Books. Change the existing ID value from 0736 to 0737 and attempt to move to the next row using the navigation buttons provided by the RemoteData control. The message shown in Figure 7.7 will be displayed. Following this message will be another, shown in Figure 7.8.

Figure 7.7. The Command has been aborted message.

Figure 7.8. The operation canceled message.

2. Insert the following code as the rdc_Error event procedure. This procedure iterates the rdoErrors collection with a For...Each loop and builds a message using all the available errors. The message generated reveals the true nature of the problem: The column level check constraint on the pub_id column has been violated.

Private Sub rdc_Error( _
  ByVal Number As Long, _
  Description As String, _
  ByVal Scode As Long, _
  ByVal Source As String, _
  ByVal HelpFile As String, _
  ByVal HelpContext As Long, _
  CancelDisplay As Boolean)
Dim sMsg As String
  Dim rerr As rdoError
  For Each rerr In rdoErrors
    sMsg = _
      rerr.Number & ": " & rerr.Description & vbCrLf
  Next
  MsgBox sMsg
End Sub
3. In the mnuDataSave_Click event, replace the simple MsgBox statement in the error handler with the following code. This is the same approach used in the rdc_Error event procedure to find out what the real problem is with the error. The original message Command has been aborted--although true--is useless without the additional information provided by the rdoErrors collection.

  Dim rerr As rdoError
  Dim sMsg As String
  For Each rerr In rdoErrors
    sMsg = rerr.Number & ": " & rerr.Description & vbCrLf
  Next
  MsgBox sMsg
4. Run the project again, but this time attempt to delete New Moon Books. The now familiar Command has been aborted message appears. Again the message is correct but useless. Replace the MsgBox statement in the error handler with the same code used in the two previous steps. If you run the project and again attempt the delete, the rdoErrors collection will now reveal that the underlying problem is the violation of a foreign key constraint. With this information available, you can deal with the problem by either cancelling the delete and informing the user or by deleting the associated rows in the foreign table.

5. Sometimes, despite your best efforts, the messages you receive from SQL Server just aren't going to provide any useful information. In How-To 7.7, you created a form that calls the system stored procedure sp_password. In SQL Enterprise Manager or I-SQL/w, you can run this stored procedure (which resides in the master database) from any database on the server and change your password. That's how system stored procedures are supposed to work and why they're called system stored procedures--they apply to the entire SQL Server. Based on this information, replace the line of code in the ChangePassword subroutine with the following line. At first glance you might expect this to work, but it doesn't. The rather bizarre error message shown in Figure 7.9 is displayed instead of a notification that the password was changed.

sSQL = "{? = call sp_password (?,?) }"

Figure 7.9. A not-so-helpful error message.


NOTE A copy of the application with the incorrect code is available in project HT707R.vbp.
6. The trouble is that a fully qualified name for the procedure in the form database.owner.object is required. Unfortunately, the rdoErrors collection does not provide any further information. If you place a breakpoint in the procedure or set the Break on all Errors option in Visual Basic, you can examine the rdoErrors collection in the immediate window. The only error available is the one displayed, and it certainly doesn't indicate the true nature of the problem. A search of the Microsoft Technical Support Knowledge Base on the Microsoft Web site using the keywords rdoquery error returned (among others) the following article. This article indicates that you need to provide the full path to the stored procedure if you use the technique shown in How-To 7.7 (as well as an alternative method).

HOWTO: Call SQL Server System Stored Procedures from RDO
Last reviewed: April 18, 1997
Article ID: Q166211 The information in this article applies to:
*Microsoft Visual Basic Enterprise Edition for Windows, version 5.0


NOTE If you aren't already familiar with the Microsoft Knowledge Base, you should visit the Web site and learn to use it. The Knowledge Base (or just KB) provides a wealth of technical information on all Microsoft products and should be your first source of information if you encounter a problem. You can query the KB online at http://www.microsoft.com/kb/.

How It Works

Standard Visual Basic error-handling techniques, in conjunction with the rdoErrors collection, enable you to trap and examine runtime errors returned by Remote Data Objects. Armed with the information provided by the rdoErrors collection, you can build applications that trap and handle runtime errors effectively.

Comments

The information provided by the rdoErrors collection is often helpful, but not always--as demonstrated in the steps above. In order to be productive as a programmer, you'll need to be able to query the resources available in the help files, the Knowledge Base, and other sources to find the solutions to your problems. Finally, don't underestimate the power of your own intuition in solving problems and working out bugs in your application. As you gain experience in programming with Visual Basic and Remote Data Objects, you will eventually learn to anticipate problems and can often solve them with a simple educated guess.


Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.