
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.
This How-To introduces the most basic of operations on a database server: browsing the results of a query with 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.
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.
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.
This How-To shows you how to insert, update, and delete records using RDO.
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.
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.
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:
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.
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
Table 7.2. Menu controls for FMain.
CAPTION Name &File mnuFile ----E&xit mnuFileExit
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.
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.
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:
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 TABLEThe 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.
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
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
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
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
Private Sub AddRecord()
` add it 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
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.
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.
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
Option Explicit Private mcon As rdoConnection
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
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.
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 CURSORSDon'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.
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
Option Explicit
Private mcon As rdoConnection
Private Sub Form_Load() On Error GoTo ProcError OpenConnection GetStates 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 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
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
Private Sub OpenConnection()
Dim sConnect As String ` default using a configured DSN Set mcon = rdoEnvironments(0).OpenConnection("pubs") End Sub
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.
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:
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.
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
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
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
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
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
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
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
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
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
Private Sub Terminate() ` clean up mrsPublishers.Close mcon.Close End Sub
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
Private Sub NewRow() ` create a new row ` save current data SaveRow ClearControls mlRowState = RowStateNew End Sub
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
Private Sub DeleteRow() ` delete the current row mrsPublishers.Delete MovePrevious End Sub
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
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
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
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
Private Sub ClearControls() ` clear existing values from controls txtID = "" txtName = "" txtCity = "" cboState = "" txtCountry = "" End Sub
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 DATABASEThe 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.
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 PROCEDURESTransact-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.
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.
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.
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.
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
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
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.
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:
DEVELOPERAs 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.
Figure 7.7. The Command has been aborted message.
Figure 7.8. The operation canceled message.
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
Dim rerr As rdoError
Dim sMsg As String
For Each rerr In rdoErrors
sMsg = rerr.Number & ": " & rerr.Description & vbCrLf
Next
MsgBox sMsg
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.
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.
© Copyright, Macmillan Computer Publishing. All rights reserved.