Site hosted by Angelfire.com: Build your free website today!

'; zhtm += '

' + pPage + ''; zhtm += ''; window.popUpWin.document.write(zhtm); window.popUpWin.document.close(); // Johnny Jackson 4/28/98 } //--> Visual Basic 6 Database How-To -- Ch 8 -- Using ActiveX Data Objects

www.Sir FreeDom.com.Ar

Visual Basic 6 Database How-To

- 8 -
Using ActiveX Data Objects

How Do I...

Universal Data Access, OLE DB, ActiveX Data Objects...what do they all mean? Recently developers have been hit with another new wave of technology terms. Microsoft has once again pushed forward the frontier of data access.

Universal Data Access, referred to as UDA from here forward, is Microsoft's term for the idea that a developer should be able to use one data access method for any data source he is querying. ODBC was a great step forward. For the first time, no matter what relational database the application needed to talk to, you only needed to learn one API. The problem with ODBC is that it was aimed directly at relational databases and other sources of data did not fit its model very well. Instead of trying to tack functionality on to ODBC so that it could handle other data sources as well as it did relational databases, Microsoft decided to do things right and start from scratch. They built OLE DB without having to make any compromises to the existing architecture.

OLE DB is a COM-based interface between data providers and client applica-tions. Data providers can be anything from relation databases to spread sheets to file systems. Like RDO was to the ODBC API, Microsoft knew it needed to create an easy-to-use object layer on top of OLE DB; thus ActiveX Data Objects were born.

ADO is the interface into OLE DB so that VB can reap the benefits of UDA. Got that? This chapter covers ADO from its simplest form, using the ADO Data control, to more complex forms, such as building three-tier applications using Microsoft Transaction Server.

This chapter makes a few assumptions:

4 You need access to a SQL Server with the sample database pubs installed on it.
4 A few of the samples require an ODBC datasource--pubs--set up to connect to that server and database. One of the first data providers Microsoft released was a data provider that sat on top of ODBC. Any database accessible to ODBC is accessible to ADO.
4 For How-To 8.12, you will need Microsoft Transaction Server. It is available from Microsoft as part of the NT Server Option Pack.

CONNECTING TO OTHER DATABASES

All the How-Tos in this chapter use a Microsoft SQL Server, but ADO is not constrained to one database vender or even just databases. The "Provider" parameter of the connection string indicates which OLE DB data provider should be used. ADO 2.0 ships with several data providers including Jet, Oracle, and Microsoft Directory Services. Connecting to a different data provider is as easy as changing the connection string. For example, a typical connection string for an Access database would be "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=mydb.mdb".

8.1 Browse a SQL Server Database with the ADO Data Control

This How-To shows the quickest and simplest way to get up and running using ADO, the ADO Data control.

8.2 Create and Delete Records in a SQL Server Database Using the ADO Data Control

With a little extra code you can insert and delete records using the ADO Data control. This How-To shows you how.

8.3 Retrieve Results from a SQL Server Using ActiveX Data Objects

Retrieve data without the use of the ADO Data control in this How-To.

8.4 Alter Data Using ActiveX Data Objects

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

8.5 Perform a Transaction Using ActiveX Data Objects

Most business applications require them; this How-To shows you how to perform a transaction in ADO.

8.6 Execute a SQL Server Stored Procedure Using ActiveX Data Objects

You can increase the performance of your ADO applications by using stored procedures. In this How-To you learn how to use SQL Server stored procedures with ADO.

8.7 Execute a Parameterized SQL Server Stored Procedure with ActiveX Data Objects

Some stored procedures have return values and output parameters. This How-To shows you what you need to know to handle parameterized stored procedures.

8.8 Create and Modify SQL Server Objects with ActiveX Data Objects

The ADO object model does not let you modify SQL Server objects. This How-To shows you how to get around it.

8.9 Execute Batch Updates with ActiveX Data Objects

Saving many changes at once can increase performance. This How-To shows you how.

8.10 Make Remote Updates to Data with ActiveX Data Objects

In this How-To you will learn how to best update a Recordset using ADO when connections and bandwidth are limited.

8.11 Build a Middle-Tier Business Object Using ActiveX Data Objects

Move up to three-tier development in this How-To, where you learn how to build a middle-tier business object using ADO.

8.12 Incorporate a Business Object into Microsoft Transaction Server

When scalability becomes a problem, MTS is your answer. This How-To shows you how to take a middle-tier business component and tune it for Microsoft Transaction Server.

8.13 Handle ActiveX Data Objects Errors

This How-To shows you what to do when things do go wrong.

8.1 How do I...

Browse a SQL Server database with the ADO Data control?

Problem

My data is on SQL Server database. What is an easy way to get to get the data using ADO?

Technique

Like the Visual Basic Data control and the RemoteData control, the ADO Data control gives you a "no code" solution for data access. The difference lies in the data access method. The Visual Basic Data control uses the Jet Engine, the RemoteData control uses RDO, and the ADO Data control uses Microsoft's newest data access methodology, ActiveX Data Objects.

The technique to use the ADO Data control is very similar to its predecessors:

: 1. Draw an ADO Data control on your form and set the ConnectionString and RecordSource properties.
: 2. Add controls to the form to display the columns in the record source and set their DataSource and DataField properties.

In just a couple of steps, and no lines of code, an application with the ability to browse and edit the database is born. All the data access is handled by the ADO Data control.

Steps

Load and run ADODC.vbp. Figure 8.1 shows the ADODC application in action. Change the ConnectionString property of the ADO Data Control on frmMain to match your user and password, and then run the application. You can use the application to browse and edit the Authors table in the pubs database.

Figure 8.1. The Authors form using an ADO Data control.

: 1. Create a new Standard EXE project, and change the Project Name to ADODC in the Project Properties dialog box. Rename the default form frmMain.
: 2. Using Project | Components, add the Microsoft ADO Data Control 6.0 to the project. Add an ADO Data Control to frmMain along with the other controls listed in Table 8.1. Arrange the controls to match Figure 8.1
and set all the properties according to Table 8.1. Change the ConnectionString property of the ADO Data Control to match your user and password.

Table 8.1. Objects and properties for frmMain.

OBJECT Property Value

Form Caption Authors

ADO Data control Name adodc

Caption Authors

Align 2 - vbAlignBottom

ConnectionString DSN=pubs;User Id=sa;Password=password

RecordSource authors

TextBox Name txtFirstName

Text ""

DataSource adodc

DataField au_fname

TextBox Name txtLastName

Text ""

DataSource adodc

DataField au_lname

TextBox Name txtAddress

Text ""

DataSource adodc

DataField address

TextBox Name txtCity

Text ""

DataSource adodc

DataField city

TextBox Name txtState

Text ""

DataSource adodc

DataField state

TextBox Name txtZip

Text ""

DataSource adodc

DataField zip

TextBox Name txtPhone

Text ""

DataSource adodc

DataField phone

CheckBox control Name chkContract

Caption Contract

DataSource adodc

DataField contract

Label Name lblFirstName

Caption First

Label Name lblLastName

Caption Last

Label Name lblAddress

Caption Address

Label Name lblCity

Caption City

Label Name lblState

Caption State

Label Name lblZip

Caption Zip Code

Label Name lblPhone

Caption Phone

How It Works

The ADO Data control does all the work in this application. Use the navigation buttons to move through the records and use the bound controls to edit and view the data.

Comments

This application is drag-and-drop programming at its best. With no code, a fully function application enables the user to edit and view a Recordset using ADO.

8.2 How do I...

Create and delete records in a SQL Server database using the ADO Data control?

Problem

Viewing and editing existing records is nice, but my users need to do more. How do I create and delete records using the ADO Data control?

TECHNIQUE

Creating an application to view and edit existing records is really easy and it requires no code at all. Unfortunately, the user's requirements are seldom that simple. The next obvious step is to grant the user the ability to add and delete records.

To add a record using the ADO Data control:

: 1. Use the AddNew method of the ADO Data control's Recordset property to create a new blank row in the row buffer.
: 2. Use the Update method of the ADO Data control's Recordset property to save the new row to the database.

The Recordset's Delete method is used to delete a record. However, the Delete method will not refresh the window with a valid record. You must move the ADO Data control to a new valid row. This How-To uses the convention of moving to the previous row when deleting.

Steps

Open ADODC2.vbp and change the ConnectionString property of the ADO Data Control on frmMain to match your user and password, then run. This is almost the same application from the first How-To. The changes are listed in the steps below.

: 1. Starting with the finished product of How-To 8.1, add the controls and menu items specified in Table 8.2. Use Figure 8.2 to help place the new controls.

Figure 8.2. The new and improved Authors form.

Table 8.2. New objects and properties for frmMain.

OBJECT Property Value

TextBox Name txtId

Text ""

DataSource adodc

DataField au_id

Label Name lblId

Caption Id

Menu item Name mnuFile

Caption &File

Menu item Name mnuNew

Caption &New

Indent 1

Menu item Name mnuSave

Caption &Save

Indent 1

Enabled False

Menu item Name mnuExit

Caption E&xit

Indent 1

Menu item Name mnuEdit

Caption &Edit

Menu item Name mnuDelete

Caption &Delete

Indent 1

: 2. Add Option Explicit to the declarations section of the form if it is not already there.
: 3. Add the following code to the form. This sub checks to see if a new record has been added; if it has been added, the sub attempts to save the new record and disable the Save menu selection.

Private Sub Save()
    `if we need to save then save it
    If adodc.Recordset.EditMode = adEditAdd Then
        On Error GoTo SaveFailure:
        adodc.Recordset.Update
        On Error GoTo 0
        `don't need to save so disable that menu
        mnuSave.Enabled = False
    End If
SaveDone:
    Exit Sub
SaveFailure:
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume SaveDone
End Sub

: 4. Adding the code adds the new empty row to the row buffer.

Private Sub mnuNew_Click()
    adodc.Recordset.AddNew
    `so we can save that new record
    mnuSave.Enabled = True
End Sub

: 5. Create the mnuSave_Click event procedure. It just calls the Save sub.

Private Sub mnuSave_Click()
    Save
End Sub

: 6. If the user tries to exit after adding a new row, check to see if she wants to save it in the mnuExit_Click event procedure.

Private Sub mnuExit_Click()
    If adodc.Recordset.EditMode = adEditAdd Then
        If MsgBox("Do you want to save?", vbYesNo) = vbYes Then
            Save
        End If
    End If
    Unload Me
End Sub

: 7. Finally, add the code to delete a row.

Private Sub mnuDelete_Click()
    On Error GoTo DeleteFailure:
    adodc.Recordset.Delete
    `current row is now invalid so move back one
    adodc.Recordset.MovePrevious
    `if we are before the beginning go to the first
    If adodc.Recordset.BOF Then
        adodc.Recordset.MoveFirst
    End If
DeleteDone:
    Exit Sub
DeleteFailure:
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume DeleteDone
End Sub

How It Works

Like the previous How-To, this one relies on the ADO Data control to do much of the work. You can use the AddNew, Update, and Delete methods of the Recordset object to supplement the ADO Data control's basic functionality to build a simple data manipulation application.

Comments

The ADO Data control is nice for quick and easy applications, but this How-To shows that you quickly have to move up to manipulating ADO programmatically to get any advanced features. The rest of this chapter's How-To's focus on using ADO's objects directly without the ADO Data control.

8.3 How do I...

Retrieve results from a SQL Server using ActiveX Data Objects?

Problem

I want to get at my data without using a bound control. How do I retrieve results from SQL Server using ActiveX Data Objects?

Technique

The ADO Connection and Recordset objects provide direct access to data in ADO. If you have programmed in either DAO or RDO, the ADO objects will seem strangely familiar. Most DAO and RDO objects have counterparts in ADO, but one with significant difference.

The biggest difference between ADO and its predecessors is the flat nature of the ADO object model. RDO also has an rdoRecordset object, which is the child of an rdoConnection, which is the child of an rdoEnvironment, which is the child of the rdoEngine. RDO and DAO are very hierarchical; to get to a recordset, you must also have all the parent objects. ADO does not require the overhead of the hierarchy. To retrieve results from a data source, all you need is a connection and a recordset.

The steps to get data from SQL Server are listed below:

: 1. Create and open a connection.
: 2. Create and open a recordset with the connection as a parameter.
: 3. Close the recordset.
: 4. Close the connection.

Steps

Open the ListAuthors.vbp project. Before running the project you will have to change the username (User Id=), password (Password=), and server name (Location=) parameters of the connection string. The connection is opened in the Form_Load event of frmAuthors. The Authors form, shown in Figure 8.3, shows a list of authors and their addresses.

Figure 8.3. The Authors form shows a list of authors.

A NOTE ON CONNECTION STRINGS

It is often hard to remember the exact syntax for connection strings, but the ADO Data control has an excellent wizard for building and testing connection strings. Simply add the ADO Data control to your project, right-click on the control and select ADODC properties, then click the Build button next to the Use Connection String option. When you're finished creating and testing the connection string, cut and paste it from the text box and remove the ADO Data control from your project.

: 1. Create a new Standard EXE project, add a reference to Microsoft ActiveX Data Objects 2.0 Library, and add the Microsoft Windows Common Controls 6.0 component. Change the Project Name to ListAuthors and the Name of the default form to frmAuthors, and save the project.
: 2. Add the object and set the properties according to Table 8.3.

Table 8.3. New objects and properties for frmAuthors.

OBJECT Property Value

Form Caption Authors

ListView Name listAuthors

View 3 - lvwReport

LabelEdit 1 - lvwManual

: 3. Add the columns listed in Table 8.4 to the ListView control using the Column Headers property page.

Table 8.4. Column Headers for listAuthors.

COLUMN WIDTH

Name 2000

Address 2000

City 1440

State 500

Zip 700

: 4. Add the following procedure. The Form_Load event/property opens the Connection object, opens the Recordset object, and fills the ListView control with the names and address from the Authors table. Remember to change the connection string for the Connection object's Open method.

Private Sub Form_Load()
    Dim cn As Connection
    Dim rs As Recordset
    Dim NewItem As ListItem
    `open the connection
    Set cn = New Connection
    cn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password;" _
        + "Location=WINEMILLER;Database=pubs"
    `could have also just specified an ODBC DSN like below
    `cn.Open "DSN=pubs"
    `now open the recordset
    Set rs = New Recordset
    rs.Open "authors", cn, adOpenForwardOnly, adLockReadOnly
    Do Until rs.EOF
        Set NewItem = listAuthors.ListItems.Add(, rs("au_id"), _
            rs("au_lname") & ", " & rs("au_fname"))
        NewItem.SubItems(1) = rs("address")
        NewItem.SubItems(2) = rs("city")
        NewItem.SubItems(3) = rs("state")
        NewItem.SubItems(4) = rs("zip")
        rs.MoveNext
    Loop
    `close and clean up
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

HOW IT WORKS

The code in Form_Load is typical for an ADO data retrieval operation.

: 1. Instance the Connection object.
: 2. Call the Connection object's Open method.
: 3. Instance the Recordset object.
: 4. Call the Recordset object's Open method, using the Connection object as a parameter.
: 5. Move through the Recordset until the EOF method returns a True.
: 6. Call the Recordset object's Close method.
: 7. Call the Connection object's Close method.
: 8. Set the Recordset and Connection objects to Nothing.

Opening connections is an expensive operation, so in most applications it is far better to keep a connection as a module or global level variable. The rules change when Microsoft Transaction Server is added to the pot; How-To 8.12 explains why you should open and close a connection in each call when using Microsoft Transaction Server.

Comments

In most applications you will probably want to keep a Connection object at the global or module level. This will save you from creating the object each time an operation is performed.

8.4 How do I...

Alter data using ActiveX Data Objects?

Problem

Now that I know how to get to my data using ADO, I want to change it. How do I alter data using ActiveX Data Objects?

Technique

ADO provides two basic means for altering data: the Connection object's Execute method and the Recordset object. Using the Execute method, you can use SQL queries and commands such as UPDATE, INSERT, and DELETE. The Recordset object exposes corresponding methods with its Update, AddNew, and Delete methods.

How-To 8.3 uses a forward-only, read-only cursor because it only needs to display data. This How-To uses keyset cursors with optimistic locking. This type of cursor allows updates and deletes to the recordset.

Steps

Open the ListAuthors2.vbp. As with How-To 8.3, you will need to update the connection string used to open the connection in the Form_Load before you can run it. This project is typical of many applications where you select from a list of objects and the objects' properties are displayed in a separate part of the window. There you can delete, edit, and add new records.

: 1. Create a new Standard EXE, and change the Project Name to ListAuthors2. Change the default form name to frmAuthors and save the project.
: 2. Add a reference to the Microsoft ActiveX Data Objects 2.0 Library and add the Microsoft Windows Common Controls 6.0.
: 3. Using Table 8.5, add the required objects to the form and set their properties. Use Figure 8.4 as a guide to lay out the position of the controls.

Figure 8.4. The Authors form.

Table 8.5. Objects and properties for frmAuthors.

OBJECT Property Value

Form Caption Authors

CommandButton Name cmdNew

Caption New

CommandButton Name cmdDelete

Caption Delete

CommandButton Name cmdUpdate

Caption Update

CheckBox Name chkExecute

Caption Use Execute

ListView Name listAuthors

View 3 - lvwReport

LabelEdit 1 - lvwManual

TextBox Name txtId

Text ""

TextBox Name txtFirstName

Text ""

TextBox Name txtLastName

Text ""

TextBox Name txtAddress

Text ""

TextBox Name txtCity

Text ""

TextBox Name txtState

Text ""

TextBox Name txtZip

Text ""

TextBox Name txtPhone

Text ""

CheckBox Name chkContract

Caption Contract

Label Name lblId

Caption Id

Label Name lblFirstName

Caption First

Label Name lblLastName

Caption Last

Label Name lblAddress

Caption Address

Label Name lblCity

Caption City

Label Name lblState

Caption State

Label Name lblZip

Caption Zip Code

Label Name lblPhone

Caption Phone

: 4. Add the columns listed in Table 8.6 to the ListView control using the Column Headers property page.

Table 8.6. Column headers for listAuthors.

COLUMN Width

Name 2000

Address 2000

City 1440

State 500

Zip 700

: 5. Add following code to the declarations section of frmAuthors.

Option Explicit
Private mConn As Connection
`has something changed
Private mbNeedSave As Boolean
`are we working with a new record
Private mbNewRecord As Boolean
`keep track of the current record
Private msCurrentRecord As String

: 6. The Form_Load event looks much like the one from How-To 8.3. This
version does not close the connection because it will be used later. Add
a Form_Load event with the following code. Remember to change the
con-nection string to match your setup.

Private Sub Form_Load()
    Dim rs As Recordset
    Dim NewItem As ListItem
    `open the connection
    Set mConn = New Connection
    mConn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" _
        + "Location=WINEMILLER;Database=pubs"
    `could have also just specified an ODBC DSN like below
    `mConnOpen "DSN=pubs"
    `now open the recordset
    Set rs = New Recordset
    rs.Open "authors", mConn, adOpenForwardOnly, adLockReadOnly
    Do Until rs.EOF
        Set NewItem = listAuthors.ListItems.Add(, rs("au_id"), _
            rs("au_lname") & ", " & rs("au_fname"))
        NewItem.SubItems(1) = rs("address")
        NewItem.SubItems(2) = rs("city")
        NewItem.SubItems(3) = rs("state")
        NewItem.SubItems(4) = rs("zip")
        rs.MoveNext
    Loop
    `close and clean up
    rs.Close
    Set rs = Nothing
    `set the first item
    listAuthors_ItemClick listAuthors.ListItems(1)
End Sub

: 7. Now add the Form_Unload event. This event closes the mConn and sets the object to Nothing.

Private Sub Form_Unload(Cancel As Integer)
    mConn.Close
    Set mConn = Nothing
End Sub

: 8. When the data fields change, the mbNeedSave flag is set and the cmdUpdate button is enabled. Add the following code to capture when
a change happens.

Private Sub RecordChanged()
    mbNeedSave = True
    cmdUpdate.Enabled = True
End Sub
Private Sub chkContract_Click()
    RecordChanged
End Sub
Private Sub txtAddress_Change()
    RecordChanged
End Sub
Private Sub txtCity_Change()
    RecordChanged
End Sub
Private Sub txtFirstName_Change()
    RecordChanged
End Sub
Private Sub txtId_Change()
    RecordChanged
End Sub
Private Sub txtLastName_Change()
    RecordChanged
End Sub
Private Sub txtPhone_Change()
    RecordChanged
End Sub
Private Sub txtState_Change()
    RecordChanged
End Sub
Private Sub txtZip_Change()
    RecordChanged
End Sub

: 9. The New button clears the data and sets the mbNewRecord flag. Add the cmdNew_Click event.

Private Sub cmdNew_Click()
    `clear screen
    txtId.Text = ""
    txtFirstName.Text = ""
    txtLastName.Text = ""
    txtAddress.Text = ""
    txtCity.Text = ""
    txtState.Text = ""
    txtZip.Text = ""
    txtPhone.Text = ""
    chkContract.Value = vbChecked
    `set flags
    mbNewRecord = True
    mbNeedSave = True
    `nothing to delete
    cmdDelete.Enabled = False
    `no record selected
    Set listAuthors.SelectedItem = Nothing
    `start the user off in the right place
    txtId.SetFocus
End Sub

: 10. Add the code to handle the update. The Update function does most of the hard work in the application. Based on the value of the chkExecute checkbox, it either uses the Connection object's Execute method, or the Recordset object's AddNew and Update methods.

Private Sub cmdUpdate_Click()
    UpdateRecord
End Sub
Private Function UpdateRecord() As Boolean
    Dim sCmd As String
    Dim rs As Recordset
    If mbNewRecord Then
        `try to insert
        If chkExecute.Value = vbChecked Then
            `use the execute method of the connection
            sCmd = "insert into authors " _
                "(au_id,au_fname,au_lname,address" _
                + ",city,state,zip,phone,contract)"
            sCmd = sCmd + " values ("
            sCmd = sCmd + "`" + txtId.Text + "`"
            sCmd = sCmd + ",'" + txtFirstName.Text + "`"
            sCmd = sCmd + ",'" + txtLastName.Text + "`"
            sCmd = sCmd + ",'" + txtAddress.Text + "`"
            sCmd = sCmd + ",'" + txtCity.Text + "`"
            sCmd = sCmd + ",'" + txtState.Text + "`"
            sCmd = sCmd + ",'" + txtZip.Text + "`"
            sCmd = sCmd + ",'" + txtPhone.Text + "`"
            sCmd = sCmd + "," & IIf(chkContract.Value = vbChecked _
               1, 0)
            sCmd = sCmd + ")"
            On Error GoTo UpdateFailed:
            mConn.Execute sCmd
            On Error GoTo 0
        Else
            `use a Recordset Object to add it
            Set rs = New Recordset
            On Error GoTo UpdateFailed
            rs.Open "select * from authors where au_id = `" 
                + txtId.Text + "`", mConn, adOpenKeyset, _
                adLockOptimistic
            rs.AddNew
            rs!au_id = txtId.Text
            rs!au_fname = txtFirstName.Text
            rs!au_lname = txtLastName.Text
            rs!address = txtAddress.Text
            rs!city = txtCity.Text
            rs!State = txtState.Text
            rs!zip = txtZip.Text
            rs!phone = txtPhone.Text
            rs!contract = (chkContract.Value = vbChecked)
            rs.Update
            On Error GoTo 0
            rs.Close
            Set rs = Nothing
        End If
        `no longer dealing with a new record
        mbNewRecord = False
        `add the new item to the list
        Dim NewItem As ListItem
        Set NewItem = listAuthors.ListItems.Add(, txtId.Text, _
            txtLastName.Text & ", " & txtFirstName.Text)
        NewItem.SubItems(1) = txtAddress.Text
        NewItem.SubItems(2) = txtCity.Text
        NewItem.SubItems(3) = txtState.Text
        NewItem.SubItems(4) = txtZip.Text
        Set listAuthors.SelectedItem = NewItem
    Else
        `try to update
        If chkExecute.Value = vbChecked Then
            `use the execute method of the connection
            sCmd = "update authors"
            sCmd = sCmd + " set "
            sCmd = sCmd + "au_id = `" + txtId.Text + "`"
            sCmd = sCmd + ",au_fname = `" 
            sCmd = sCmd + txtFirstName.Text + "`"
            sCmd = sCmd + ",au_lname = `" + txtLastName.Text + "`"
            sCmd = sCmd + ",address = `" + txtAddress.Text + "`"
            sCmd = sCmd + ",city = `" + txtCity.Text + "`"
            sCmd = sCmd + ",state = `" + txtState.Text + "`"
            sCmd = sCmd + ",zip = `" + txtZip.Text + "`"
            sCmd = sCmd + ",phone = `" + txtPhone.Text + "`"
            sCmd = sCmd + ",contract = " & _
                IIf(chkContract.Value = vbChecked, 1, 0)
            sCmd = sCmd + " where au_id = `" "`"
            sCmd = sCmd + msCurrentRecord + "`"
            On Error GoTo UpdateFailed:
            mConn.Execute sCmd
            On Error GoTo 0
        Else
            `use a Recordset Object to make the changes
            Set rs = New Recordset
            On Error GoTo UpdateFailed
            rs.Open "select * from authors where au_id = `" _
                + msCurrentRecord + "`", mConn, adOpenKeyset _
                , adLockOptimistic
            `only update the primary key if it's changed
            `ADO acts like it's been updated even if the new
            `value is the same as the old so only set if it's
            `really changed
            If rs("au_id") <> txtId.Text Then
                rs!au_id = txtId.Text
            End If
            rs!au_fname = txtFirstName.Text
            rs!au_lname = txtLastName.Text
            rs!address = txtAddress.Text
            rs!city = txtCity.Text
            rs!State = txtState.Text
            rs!zip = txtZip.Text
            rs!phone = txtPhone.Text
            rs!contract = (chkContract.Value = vbChecked)
            rs.Update
            On Error GoTo 0
            rs.Close
            Set rs = Nothing
        End If
        `update the item in the list
        Dim OldItem As ListItem
        Set OldItem = listAuthors.ListItems.Item(msCurrentRecord)
        OldItem.Key = txtId.Text
        OldItem.Text = txtLastName.Text & ", " & txtFirstName.Text
        OldItem.SubItems(1) = txtAddress.Text
        OldItem.SubItems(2) = txtCity.Text
        OldItem.SubItems(3) = txtState.Text
        OldItem.SubItems(4) = txtZip.Text
    End If
    `no longer need save
    mbNeedSave = False
    cmdUpdate.Enabled = False
    cmdDelete.Enabled = True
    UpdateRecord = True
UpdateComplete:
    Exit Function
UpdateFailed:
    ShowADOError
    GoTo UpdateComplete
End Function

: 11. Create the cmdDelete_Click event. Like the Update function used previously, the cmdDelete_Click event uses the Execute or Delete methods based on the value of the chkExecute checkbox.

Private Sub cmdDelete_Click()
    If chkExecute.Value = vbChecked Then
        Dim sCmd As String
        sCmd = "delete from authors where au_id = `" _
            + msCurrentRecord + "`"
        On Error GoTo DeleteFailed
        mConn.Execute sCmd
        On Error GoTo 0
    Else
        Dim rs As Recordset
        `now open the recordset
        Set rs = New Recordset
        On Error GoTo DeleteFailed
        rs.Open "select * from authors where au_id = `" _
            + msCurrentRecord + "`", mConn, adOpenKeyset _
            adLockOptimistic
        Do Until rs.EOF
            rs.Delete
            rs.MoveNext
        Loop
        On Error GoTo 0
    End If
    `remove the item from the list
    listAuthors.ListItems.Remove msCurrentRecord
    mbNeedSave = False
    cmdUpdate.Enabled = False
    listAuthors_ItemClick listAuthors.SelectedItem
DeleteComplete:
    Exit Sub
DeleteFailed:
    ShowADOError
    GoTo DeleteComplete
End Sub

: 12. As an item is selected in the listAuthors control, the listAuthors_ItemClick event checks to see if the current record
needs to be saved, and then it refreshes the detail controls.

Private Sub listAuthors_ItemClick(ByVal Item As ComctlLib.ListItem)
    Dim rs As Recordset
    Set rs = New Recordset
    If mbNeedSave Then
        If Not UpdateRecord() Then
            Set listAuthors.SelectedItem = _
                listAuthors.ListItems.Item(msCurrentRecord)
            Exit Sub
        End If
    End If
    `now open the recordset
    Set rs = New Recordset
    rs.Open "select * from authors where au_id = `" + Item.Key + _
       "`" 
        , mConn, adOpenForwardOnly, adLockReadOnly
    Do Until rs.EOF
        `update the listview in case it's changed
        Item.Text = rs("au_lname") & ", " & rs("au_fname")
        Item.SubItems(1) = rs("address")
        Item.SubItems(2) = rs("city")
        Item.SubItems(3) = rs("state")
        Item.SubItems(4) = rs("zip")
        `fill the edit controls
        txtId.Text = rs("au_id")
        txtFirstName.Text = rs("au_fname")
        txtLastName.Text = rs("au_lname")
        txtAddress.Text = rs("address")
        txtCity.Text = rs("city")
        txtState.Text = rs("state")
        txtZip.Text = rs("zip")
        txtPhone.Text = rs("phone")
        chkContract.Value = IIf(rs("contract"), vbChecked _
           vbUnchecked)
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    mbNeedSave = False
    cmdUpdate.Enabled = False
    cmdDelete.Enabled = True
    msCurrentRecord = txtId.Text
End Sub

: 13. Finally, add the sub that displays the errors reported by ADO.

Private Sub ShowADOError()
    `spin through the errors collection and
    `display the constructed error message
    Dim ADOError As Error
    Dim sError As String
    For Each ADOError In mConn.Errors
        sError = sError + ADOError.Number & " - " & _
            ADOError.Description _
            + vbCrLf
    Next ADOError
    MsgBox sError
End Sub

How It Works

The ListAuthors2 project shows two ways of altering data using ADO. Based on the Use Execute option, it uses the Execute method to send SQL statements, or it uses the AddNew, Delete, and Update methods of the Recordset object.

Comments

The changes to the database in How-To 8.4 were very simple; insert a record, delete a record, and update a record. Often, multiple changes must occur at the same time, and if one fails, none of the remaining changes should commit. How-To 8.5 shows you how to wrap up your changes into a transaction where all the changes succeed or fail together.

Many operations in ADO can be performed multiple ways. In most cases the performance difference is negligible, unless the operation is performed in a loop. Generally, deciding which method to use is a matter of preference and a matter of which method can get the job done in the easiest manner possible for the developer. However, many companies today are moving to three-tier applications. In a three-tier application, an application developer manipulates the database through objects instead of executing SQL statements. Using the object model to perform data manipulation might help you get acclimated to using objects instead of SQL.

8.5 How do I...

Perform a transaction using ActiveX Data Objects?

Problem

Now I know how to make changes to data, but several things must change together. If one fails, they should all fail. How do I perform a transaction using ActiveX Data Objects?

Technique

A transaction ensures that all changes within the transaction either succeed or fail together. The classic example of an application that requires transactions is a financial application in which money can be transferred from one account to another. Without transactions, if the debit is successful, but the credit is not, the customer has lost money. If the debit fails and the credit is successful, the institution loses money. Neither one is good if the company wants to stay in business for a long time.

In ADO, the BeginTrans, CommitTrans, and RollbackTrans methods of the Connection object provide the means to do transactions. The steps to perform a transaction are outlined below:

: 1. Call BeginTrans.
: 2. Make all changes on the same connection where the BeginTrans was called. Check each change to make sure it is successful.
: 3. If all changes were successful, call CommitTrans. If any change failed, call RollbackTrans.

Steps

Open the Transaction.vbp. You will need to update the connection string in the Form_Load event. Change the username (User Id=), password (Password=), and server name (Location=) parameters. Figure 8.5 shows the Transfer Funds application. Two lists are displayed; select accounts in the To and From lists, enter an amount, and press the Transfer button to move money from one account to another.

Figure 8.5. The Transfer Funds application.

: 1. Use SQL Enterprise Manager or ISQL/w to run the Accounts.sql script in your pubs database. This script creates a new table, Accounts, and test data for this How-To.
: 2. Create a new Standard EXE. Change the Project Name to Transaction and rename the default form frmMain.
: 3. Add a reference to the Microsoft ActiveX Data Objects 2.0 Library. Add the Microsoft Windows Common Controls 6.0 and the Microsoft Masked Edit Control 6.0.
: 4. Use Table 8.7 to add the objects and set the properties for frmMain.

Table 8.7. Objects and properties for frmMain.

OBJECT Property Value

Form Caption Transfer Funds

ListView Name listFrom

View 3 - lvwReport

LabelEdit 1 - lvwManual

ListView Name listTo

View 3 - lvwReport

LabelEdit 1 - lvwManual

Label Name lblAmount

Caption Amount

MaskEdBox Name maskedAmount

Mask ####.##

CommandButton Name cmdTransfer

Caption Transfer

: 5. For each of the ListViews, add a column for Customer and Balance using the Column Headers tab of the ListView properties dialog box.
: 6. Add the following code to the Declarations section of frmMain.

Option Explicit
Private mConn As Connection

: 7. The Form_Load and Form_Unload events open and close the connection. Change the connection string of the Open method to connect to your server.

Private Sub Form_Load()
    `open the connection
    Set mConn = New Connection
    mConn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" _
        + ";Location=WINEMILLER;Database=pubs"
    RefreshLists
End Sub
Private Sub Form_Unload(Cancel As Integer)
    mConn.Close
    Set mConn = Nothing
End Sub

: 8. Add the RefreshLists procedure. This procedure fills the two ListView controls with the list of account holders and their balances.

Private Sub RefreshLists()
    `refresh the lists with acount holders and balances
    Dim NewItem As ListItem
    Dim rs As Recordset
    Set rs = New Recordset
    listFrom.ListItems.Clear
    listTo.ListItems.Clear
    rs.Open "Accounts", mConn, adOpenForwardOnly, adLockReadOnly
    Do Until rs.EOF
        Set NewItem = listFrom.ListItems.Add(, "k" & rs("AccountId") _
            , rs("Name"))
        NewItem.SubItems(1) = Format(rs("Balance"), "$0.00")
        Set NewItem = listTo.ListItems.Add(, "k" _
            & rs("AccountId"), rs("Name"))
        NewItem.SubItems(1) = Format(rs("Balance"), "$0.00")
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub

: 9. Finally, add the cmdTransfer_Click event.

Private Sub cmdTransfer_Click()
    Dim lRowsAffected As Long
    Dim sError As String
    Dim sCmd As String
    Dim rs As Recordset
    If vbYes = MsgBox("Transfer " _
        & Format(Val(maskedAmount.Text), "$0.00") _
        & " from " & listFrom.SelectedItem.Text _
        & " to " & listTo.SelectedItem.Text & ".", vbYesNo) Then
        mConn.BeginTrans
        On Error GoTo TransferFailure
        `use the Connection's execute method
        sCmd = "update Accounts"
        sCmd = sCmd + " set Balance = Balance - " 
        sCmd = sCmd + maskedAmount.Text
        `only do the update if the from account has enough money
        sCmd = sCmd + " where balance >= " & maskedAmount.Text
        sCmd = sCmd + " and AccountId = " _
            & Right(listFrom.SelectedItem.Key _
            , Len(listFrom.SelectedItem.Key) - 1)
        mConn.Execute sCmd, lRowsAffected
        If lRowsAffected = 0 Then
            sError = "Insufficient funds."
            GoTo TransferFailure
        End If
        `or use the Recordset's methods
        Set rs = New Recordset
        rs.Open "select * from Accounts where AccountId = " _
            & Right(listTo.SelectedItem.Key _
            , Len(listTo.SelectedItem.Key) - 1), mConn, _
            adOpenDynamic
                , adLockPessimistic
        rs!Balance = rs("Balance") + Val(maskedAmount.Text)
        rs.Update
        `ok so far, commit it
        mConn.CommitTrans
        rs.Close
    End If
TransferDone:
    On Error GoTo 0
    Set rs = Nothing
    RefreshLists
    Exit Sub
TransferFailure:
    `something bad happened so rollback the transaction
    mConn.RollbackTrans
    Dim ADOError As Error
    For Each ADOError In mConn.Errors
        sError = sError & ADOError.Number & " - " & _
             ADOError.Description + vbCrLf
    Next ADOError
    MsgBox sError
End Sub

How It Works

The beginning of the cmdTransfer_Click event calls the BeginTrans method. If an error occurs or the originating account has insufficient funds, the transaction is rolled back. Two methods of data manipulation are used during the transaction: the Connection object's Execute method and the Recordset object's Update method. There are no restrictions on mixing and matching data manipulation methods during a transaction, as long as they are all done on the same connection. If all the updates are successful, the transaction is committed and the display is refreshed.

Comments

Transactions are an indispensable tool for the database application developer. However when you begin to use transactions, you add a new level of complexity to your application. One of the biggest problems with transactions is that they easily lead to deadlocks.

A deadlock occurs when two connections try to make changes to resources the other holds. There are a couple of strategies you can use to reduce the chance of a deadlock.

: 1. Reduce the time of the transaction. Move all string manipulation and other processing outside of the transaction. In this How-To, reduce the chances of a deadlock by moving the code that assembles the UPDATE so that it is before the BeginTrans.
: 2. Do transactions in the same order if possible. If all transactions start by changing the Orders table and then the Inventory table, there will never be a situation in which one transaction holds the Orders table, but wants the Inventory table, and another transaction holds the Inventory table, but wants the Orders table.

8.6 How do I...

Execute a SQL Server stored procedure using ActiveX Data Objects?

Problem

My company uses stored procedures to increase performance and to encapsulate objects in the database. How do I execute a SQL Server?

Technique

Stored Procedures in ADO are very straight forward--if there are no parameters. Simply change the Options parameter of the Recordset object's Open method to adCmdStoredProc. This tells ADO the Source argument is a stored procedure. If you leave off the Options parameter, ADO will still work correctly, but it will have to do a little extra processing to figure out with what it is dealing. For the best performance, always use the Options parameter to tell ADO what type of command it is performing.

This How-To also introduces a new technique for creating a Recordset. As with most things in ADO, there are a couple of ways to tackle the problem of creating a Recordset. The Connection object's Execute method returns a Recordset if one is generated by the CommandText parameter. The Recordset returned from the Execute method is the same as a manually created Recordset that was specified as forward-only and read-only.

Steps

Open the StoredProcedure.vbp. Change the connection string in the Form_Load event of frmMain and run the application. The Stored Procedures application shown in Figure 8.6 displays two possible lists, based on which option is selected. If Title Author is selected, the book titles become the parents in the TreeView; otherwise, the authors are the parents.

Figure 8.6. The Stored Procedures application.

: 1. Use SQL Enterprise Manager or ISQL/w to run the Stored Procedures.sql script in your pubs database. This script creates two new stored procedures in the pubs database: GetAuthorTitleList and GetTitleAuthorList.
: 2. Create a new Standard EXE. Change the Project Name to StoredProcedure and rename the default form to frmMain.
: 3. Add a reference to the Microsoft ActiveX Data Objects 2.0 Library and add the Microsoft Windows Common Controls 6.0.
: 4. Use Table 8.8 to add the objects and set the properties for frmMain.

Table 8.8. Objects and properties for frmMain.

OBJECT Property Value

Form Caption Stored Procedure

TreeView Name treeResults

OptionButton Name optTitleAuthor

Caption Title Author

OptionButton Name optAuthorTitle

Caption Author Title

: 5. Like the other How-To's in this chapter, the declarations section, the Form_Load event, and the Form_Unload event handle the Connection object.

Option Explicit
Private mConn As Connection
Private Sub Form_Load()
    `open the connection
    Set mConn = New Connection
    mConn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password"
        + ";Location=WINEMILLER;Database=pubs"
    RefreshList
End Sub
Private Sub Form_Unload(Cancel As Integer)
    mConn.Close
    Set mConn = Nothing
End Sub

: 6. Now add the code to call RefreshList when the OptionButtons change.

Private Sub optAuthorTitle_Click()
    RefreshList
End Sub
Private Sub optTitleAuthor_Click()
    RefreshList
End Sub

: 7. Finally add the RefreshList procedure.

Private Sub RefreshList()
    Dim rs As Recordset
    Dim NewNode As Node
    treeResults.Nodes.Clear
    If optTitleAuthor.Value = True Then
        `sort by titles
        Dim sLastTitle As String
        `use the Execute method to generate the Recordset
        `works the same as adOpenForwardOnly, adLockReadOnly
        Set rs = mConn.Execute("GetTitleAuthorList", , _
             adCmdStoredProc)
        Do Until rs.EOF
            If sLastTitle <> rs("title") Then
                `need a new parent
                sLastTitle = rs("title")
                Set NewNode = treeResults.Nodes.Add(, , _
                    rs("title") , rs("title"))
                NewNode.Expanded = True
            End If
            `add the child
            treeResults.Nodes.Add sLastTitle, tvwChild _
                , sLastTitle + rs("au_lname") & ", " & _
                rs("au_fname") _
                , rs("au_lname") & ", " & rs("au_fname")
            rs.MoveNext
        Loop
    Else
        `sort by authors
        Dim sLastAuthor As String
        Set rs = New Recordset
        rs.Open "GetAuthorTitleList", mConn, adOpenForwardOnly _
            , adLockReadOnly, adCmdStoredProc
        Do Until rs.EOF
            If sLastAuthor <> rs("au_lname") & ", " & _
                rs("au_fname") Then
                `need a new parent
                sLastAuthor = rs("au_lname") & ", " & _
                    rs("au_fname")
                Set NewNode = treeResults.Nodes.Add(, , _
                    sLastAuthor , sLastAuthor)
                NewNode.Expanded = True
            End If
            `add the child
            treeResults.Nodes.Add sLastAuthor, tvwChild, _
                sLastAuthor + rs("title"), rs("title")
            rs.MoveNext
        Loop
    End If
End Sub

HOW IT WORKS

The RefreshList procedure opens a Recordset from a stored procedure almost exactly like a normal SELECT. The only difference is the adCmdStoredProc parameter. This parameter tells ADO the Source parameter is a stored procedure and tells ADO to do what it needs to do for stored procedures. Additionally RefreshList shows an alternative way to create a Recordset by using the Execute method.

Comments

Of course this is the simple case. In most applications, stored procedures without any parameters are few and far between. How-To 8.7 addresses the problem of dealing with stored procedures with input and output parameters.

8.7 How do I...

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

Problem

Some of the stored procedures I use have parameters and return values. How do I execute a parameterized SQL Server Stored Procedure with ActiveX Data Objects?

Technique

Parameterized, SQL Server-stored procedures are enabled through the use of ADO's Command and Parameter objects. The steps for executing a parameterized SQL Server stored procedure are outlined in the following numbered list:

: 1. Create a Command object and set its ActiveConnection, CommandText, and CommandType properties.
: 2. For each input parameter, output parameter, or return value, use the Command object's CreateParameter method to create and populate a Parameter object.
: 3. Append the new Parameter objects to the Command object's Parameters collection.
: 4. Call the Command object's Execute method. If you are expecting a Recordset object from the stored procedure, the Execute method
returns a Recordset object.
: 5. After this, the Recordset is completely fetched out or closed. The return value and output parameters are available.

A NOTE ON STORED PROCEDURES AND ADO

Keep in mind that Command and Parameter objects do not need to be used for stored procedures with no output parameters or return values. You can open a Recordset with the adCmdText Option parameter and build the stored procedure call yourself in Transact SQL. This saves the overhead of creating Command and Parameter objects.

Steps

Open the ParameterStoredProcedure.vbp, change the connection string in the Form_Load event, and run. The Royalty List application, shown in Figure 8.7, displays a list of royalties. Selecting a royalty displays the list of authors and titles to which that royalty was paid.

Figure 8.7. The Royalty List application.

: 1. Use SQL Enterprise Manager or ISQL/w to run the AuthorTitleByRoyalty.sql script in your pubs database. This script creates
a new stored procedure in the pubs database, AuthorTitleByRoyalty.
: 2. Create a new Standard EXE. Change the Project Name to ParameterStoredProcedure and rename the default form to frmMain.
: 3. Add a reference to the Microsoft ActiveX Data Objects 2.0 Library and add the Microsoft Windows Common Controls 6.0.
: 4. Use Table 8.9 to add the objects and set the properties for frmMain.

Table 8.9. Objects and properties for frmMain.

OBJECT Property Value

Form Caption Royalty List

ListBox Name lstRoyalty

Label Name lblRoyalty

Caption Royalty

Label Name lblWorks

Caption Authors and Titles

ListView Name listWorks

View 3 - lvwReport

LabelEdit 1 - lvwManual

: 5. Add the columns listed in Table 8.10 to the ListView control using the Column Headers property page.

Table 8.10. Column Headers for listWorks.

COLUMN Width

Name 2000

TITLE 2000

: 6. Add the Declarations section, Form_Load event, and Form_Unload event to handle the Connection object. Remember to change the connection string in the Form_Load event for your server, user, and password.

Option Explicit
Private mConn As Connection
Private Sub Form_Load()
    `open the connection
    Set mConn = New Connection
    mConn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" _
        + ";Location=WINEMILLER;Database=pubs"
    FillRoyalty
End Sub
Private Sub Form_Unload(Cancel As Integer)
    mConn.Close
    Set mConn = Nothing
End Sub

: 7. Add the FillRoyalty procedure. This procedure fills the ListBox control with all the possible royalty values.

Private Sub FillRoyalty()
    `fill the list with the royalty values
    Dim rs As Recordset
    lstRoyalty.Clear
    Set rs = mConn.Execute("select distinct royaltyper from" _
        "titleauthor" , , adCmdText)
    Do Until rs.EOF
        lstRoyalty.AddItem rs("royaltyper")
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub

: 8. Finally add the lstRoyalty_Click event to display the list of authors and titles.

Private Sub lstRoyalty_Click()
    `display a list of authors and titles at the selected royalty 
    `level
    Dim rs As Recordset
    Dim cmd As Command
    Dim param As adodb.Parameter
    Dim NewItem As ListItem
    Set cmd = New Command
    cmd.ActiveConnection = mConn
    cmd.CommandText = "AuthorTitleByRoyalty"
    cmd.CommandType = adCmdStoredProc
    `now build the parameter list
    `The stored procedure returns a true or false if there were 
    `results
    Set param = cmd.CreateParameter("Return", adBoolean _
        , adParamReturnValue, , 0)
    cmd.Parameters.Append param
    `The input parameter
    Set param = cmd.CreateParameter("percentage", adInteger _
        , adParamInput, , Val(lstRoyalty.Text))
    cmd.Parameters.Append param
    `The output parameter, the number of rows as reported by 
    `@@ROWCOUNT
    Set param = cmd.CreateParameter("numrows", adInteger, _
        adParamOutput)
    cmd.Parameters.Append param
    `clear the list
    listWorks.ListItems.Clear
    `cmd execute generates the Recordset for us then it's
    `business as usual
    Set rs = cmd.Execute
    Do Until rs.EOF
        Set ListItem = listWorks.ListItems.Add(, , _
           rs("au_lname") & ", " & rs("au_fname"))
        ListItem.SubItems(1) = rs("title")
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set NewItem = Nothing
    `use the return value and output parameter to display a 
    `message
    If cmd("Return") = True Then
        MsgBox "This stored procedure returned " _
            & cmd("numrows") _
            & " rows as reported by @@ROWCOUNT"
    Else
        MsgBox "No rows were found."
    End If
End Sub

How It Works

The AuthorTitleByRoyalty stored procedure takes one input parameter and sends back one output parameter and a return value. After setting up the Command object, the lstRoyalty_Click event specifies all the parameters of the stored procedure. When the Recordset is completely fetched and closed, the return value and output parameter are available through the Command object. Those values are then used to display a message to the user.

Comments

Stored procedures can greatly enhance the performance of most SQL Server operations in addition to providing you with a way to encapsulate and hide data. SQL server checks the syntax and precompiles stored procedures so those steps are eliminated when the stored procedure is called instead of straight Transact SQL. For operations that take minutes or more to return, the overhead for syntax checking and compiling is relatively small, but on smaller operations that return quickly and are called often the savings are substantial.

8.8 How do I...

Create and modify SQL Server objects with ActiveX Data Objects?

Problem

I need to do data definition through ADO. How do I create and modify SQL Server objects with ActiveX Data Objects?

Technique

Unlike DAO, ADO does not have any way through the object model to modify SQL Server objects. There is no Tables collection to which you can add; however, that does not prevent you from sending Transact SQL using the Connection object's Execute method and adCmdText in the Options Parameter.

Using Transact SQL, you can create, alter, and drop tables, devices, stored procedures, or anything you can do with SQL Enterprise manager. Appendix A has a full summary of SQL syntax including all the commands necessary to create and modify tables.

Steps

Open and run the AlterObjects.vbp. Remember to change the connection string in the Form_Load event. Figure 8.8 shows the Alter Objects application. The three buttons create, alter, and drop a sample table in the pubs database.

Figure 8.8. The Alter Objects application.

: 1. Create a new Standard EXE. Change the Project Name to AlterObjects, rename the default form to frmMain, and add a reference to the Microsoft ActiveX Data Objects 2.0.
: 2. Use Table 8.11 to add the objects and set the properties for frmMain.

Table 8.11. Objects and properties for frmMain.

OBJECT Property Value

Form Caption Alter Objects

CommandButton Name cmdCreate

Caption Create

CommandButton Name cmdAlter

Caption Alter

CommandButton Name cmdDrop

Caption Drop

: 3. Add the Declarations section, the Form_Load event, and the Form_Unload event to handle the Connection object.

Option Explicit
Private mConn As Connection
Private Sub Form_Load()
    `open the connection
    Set mConn = New Connection
    mConn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" 
        + ";Location=WINEMILLER;Database=pubs"
End Sub
Private Sub Form_Unload(Cancel As Integer)
    mConn.Close
    Set mConn = Nothing
End Sub

: 4. Finally, add the code behind the buttons.

Private Sub cmdAlter_Click()
    `alter the sample table
    Dim sCmd As String
    sCmd = "alter table HowToSample808 add MoreStuff CHAR(40) "
    sCmd = sCmd + "NULL"
    mConn.Execute sCmd, , adCmdText
End Sub
Private Sub cmdCreate_Click()
    `create the sample table
    Dim sCmd As String
    sCmd = "create table HowToSample808 (SampleId INTEGER NOT "
    sCmd = sCmd + "NULL"
    sCmd = sCmd + ", Stuff CHAR(40) NOT NULL)"
    mConn.Execute sCmd, , adCmdText
End Sub
Private Sub cmdDrop_Click()
    `drop the sample table
    Dim sCmd As String
    sCmd = "drop table HowToSample808"
    mConn.Execute sCmd, , adCmdText
End Sub

How It Works

Each of the CommandButton Click events assemble the Transact SQL commands for the database modifications and send them using the Connection object's Execute method. By using adCmdText for the Options parameter, any command the server understands can be sent to the server for processing.

Comments

Using the Execute method to do this opens a whole new realm of possibilities. Sometimes object models can be restrictive, as the designer does not foresee every use that might come about. Microsoft has left the door open so you can talk directly to the database, and you can use any command the database understands.

This power does not come without risks. Many companies might not want application developers with direct access to the database. Fortunately, SQL Server does have strong support for security, and most applications will not log in as the system administrator as this How-To does.

8.9 How do I...

Execute batch updates with ActiveX Data Objects?

Problem

I want to make many changes to a recordset and apply all the changes at once. How do I execute batch updates with ActiveX Data Objects?

Technique

The Recordset object's UpdateBatch method applies multiple changes at once. There are three steps to performing a batch update with ADO:

: 1. Open a recordset with the LockType parameter set to adLockBatchOptimistic.
: 2. Move through the recordset making all the changes required.
: 3. Call the Recordset object's UpdateBatch method.

Steps

Load the BatchUpdate.vbp, change the connection string in the Form_Load event to match your setup, and run the application. The Authors application, displayed in Figure 8.9, shows a list of authors and their addresses. Double-clicking on an author presents the Edit Author dialog box, shown in Figure 8.10. After all changes are complete, click the Apply Changes button to perform the batch update.

Figure 8.9. The Authors application enables batch updates.

Figure 8.10. You can change names and address information using the Edit Authors dialog box.

: 1. Create a new Standard EXE. Change the Project Name to BatchUpdate, rename the default form to frmMain, and add a reference to the Microsoft ActiveX Data Objects 2.0.
: 2. Using the Components dialog box, add Microsoft Windows Common Controls 6.0.
: 3. Use Table 8.12 to add the objects and set the properties for frmMain.

Table 8.12. Objects and properties for frmMain.

OBJECT Property Value

Form Caption Authors

ListView Name listAuthors

View 3 - lvwReport

LabelEdit 1 - lvwManual

CommandButton Name cmdApply

Caption Apply Changes

: 4. Use Table 8.13 to add the columns for listAuthors.

Table 8.13. Column Headers for listAuthors.

COLUMN Width

Last 1440

First 1440

Address 2000

City 1440

State 500

Zip 700

: 5. In the Declarations section of frmMain, set Option Explicit and add the declaration for mConn.

Option Explicit
Private mConn As Connection

: 6. In the Form_Load event, the connection is opened and the ListView control is filled with the author table data. Remember to change the connection string on mConn's Open method to match your configuration.

Private Sub Form_Load()
    Dim rs As Recordset
    Dim NewItem As ListItem
    `open the connection
    Set mConn = New Connection
    mConn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" 
        + ";Location=WINEMILLER;Database=pubs"
    `fill the list
    Set rs = mConn.Execute("authors", , adCmdTable)
    Do Until rs.EOF
        Set NewItem = listAuthors.ListItems.Add(, rs("au_id") _
            , rs("au_lname"))
        NewItem.SubItems(1) = rs("au_fname")
        NewItem.SubItems(2) = rs("address")
        NewItem.SubItems(3) = rs("city")
        NewItem.SubItems(4) = rs("state")
        NewItem.SubItems(5) = rs("zip")
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub

: 7. The Form_Unload event closes the connection and unloads the frmDetails.

Private Sub Form_Unload(Cancel As Integer)
    Unload frmDetails
    Set frmDetails = Nothing
    mConn.Close
    Set mConn = Nothing
End Sub

: 8. In the listAuthors_DblClick event, show the Edit Authors dialog box with all the author information. If the user clicks OK, update listAuthors.

Private Sub listAuthors_DblClick()
    `fill the detail screen
    frmDetails.txtLastName.Text = listAuthors.SelectedItem.Text
    frmDetails.txtFirstName.Text = listAuthors.SelectedItem.SubItems(1)
    frmDetails.txtAddress.Text = listAuthors.SelectedItem.SubItems(2)
    frmDetails.txtCity.Text = listAuthors.SelectedItem.SubItems(3)
    frmDetails.txtState.Text = listAuthors.SelectedItem.SubItems(4)
    frmDetails.txtZip.Text = listAuthors.SelectedItem.SubItems(5)
    frmDetails.OK = False
    frmDetails.Show vbModal
    If frmDetails.OK = True Then
        `user hit OK, update the list
        listAuthors.SelectedItem.Text = frmDetails.txtLastName.Text
        listAuthors.SelectedItem.SubItems(1) = frmDetails.txtFirstName.Text
        listAuthors.SelectedItem.SubItems(2) = frmDetails.txtAddress.Text
        listAuthors.SelectedItem.SubItems(3) = frmDetails.txtCity.Text
        listAuthors.SelectedItem.SubItems(4) = frmDetails.txtState.Text
        listAuthors.SelectedItem.SubItems(5) = frmDetails.txtZip.Text
    End If
End Sub

: 9. Add the code to apply the changes.

Private Sub cmdApply_Click()
    Dim rs As Recordset
    Set rs = New Recordset
    `to do a batch update be sure to open with adLockBatchOptimistic
    rs.Open "authors", mConn, adOpenKeyset, _
        adLockBatchOptimistic _
        , adCmdTable
    Do Until rs.EOF
        rs("au_lname") = listAuthors.ListItems((rs("au_id"))).Text
        rs("au_fname") = listAuthors.ListItems((rs("au_id"))).SubItems(1)
        rs("address") = listAuthors.ListItems((rs("au_id"))).SubItems(2)
        rs("city") = listAuthors.ListItems((rs("au_id"))).SubItems(3)
        rs("state") = listAuthors.ListItems((rs("au_id"))).SubItems(4)
        rs("zip") = listAuthors.ListItems((rs("au_id"))).SubItems(5)
        rs.MoveNext
    Loop
    `update batch commits all the changes
    rs.UpdateBatch
    rs.Close
    Set rs = Nothing
End Sub

: 10. Add a new form and name it frmDetails. Use Table 8.14 to add the objects and set properties for frmDetails.

Table 8.14. Objects and properties for frmDetails.

OBJECT Property Value

Form Caption Edit Authors

TextBox Name txtFirstName

Text ""

TextBox Name txtLastName

Text ""

TextBox Name txtAddress

Text ""

TextBox Name txtCity

Text ""

TextBox Name txtState

Text ""

TextBox Name txtZip

Text ""

Label Name lblFirstName

Caption First

Label Name lblLastName

Caption Last

Label Name lblAddress

Caption Address

Label Name lblCity

Caption City

Label Name lblState

Caption State

Label Name lblZip

Caption Zip Code

CommandButton Name cmdOK

Caption OK

CommandButton Name cmdCancel

Caption Cancel

: 11. Finally, add the code for frmDetails.

Option Explicit
Public OK As Boolean
Private Sub cmdCancel_Click()
    Hide
End Sub
Private Sub cmdOK_Click()
    OK = True
    Hide
End Sub

How It Works

The Edit Details dialog box enables the user to change the names and addresses of the authors, but does not change the database. All changes are collected till the Apply Changes button is pressed. The Recordset object is opened again, this time with adLockBatchOptimistic in the LockType parameter. Next, the application makes all the changes and calls the UpdateBatch method to apply all the changes.

Comments

Batch updates are often a way to squeeze more performance from a database operation. There is inherent overhead each time an update is performed. By using a batch update, that overhead cost is paid only once instead of at each separate update.

8.10 How do I...

Make remote updates to data with ActiveX Data Objects?

Problem

My application will be running over the Web, where server connections and bandwidth are expensive. I want to minimize the server connections and bandwidth requirements. How do I make remote updates to data with ActiveX Data Objects?

Technique

ADO was designed with the Web in mind, and by using client side cursors, RDS enables an application to retrieve data, modify the data, and update the server using only one round trip. There are several steps required to enable this:

: 1. Open the connection using a remote provider. The connection string below uses the MS Remote provider to open an ODBC datasource.

Dim conn As Connection
Set conn = New Connection
conn.Open "Provider=MS Remote;Remote Server=http://www.myserver.com" _
    + ";Remote Provider=MSDASQL;DSN=pubs"

A NOTE ON HTTP ADDRESSES

The Remote Server parameter in the ConnectionString expects an http address for the server. On a local network the http address is simply http://myserver.

: 2. Set the Recordset object's CursorLocation property to adUseClient.
: 3. Open the Recordset using adOpenStatic for the CursorType parameter and adLockBatchOptimistic for the LockType parameter.
: 4. After all the changes are collected, create a new Connection object using the same ConnectionString property that is used in the first Connection.
: 5. Create a new Recordset object. Open it using the original Recordset as the Source parameter and the new Connection object as the ActiveConnection parameter.
: 6. Call the second Recordset object's UpdateBatch method.

STEPS

Open the ADODC.vbp, change the connection string in the Form_Load event to match your server, user, and password. The form, shown in Figure 8.11, is visually the same as the form in How-To 8.1, except this form uses a client-side cursor.

This How-To is a modification of How-To 8.1. If you have completed How-To 8.1, you can start from where it left off, or you can use the completed How-To 8.1 project from the CD.

: 1. Clear the ConnectionString and RecordSource properties of the ADO Data control.

Figure 8.11. The New Authors application uses a client-side cursor.

: 2. Add the following code to frmMain to load the RecordSet in Form_Load event and update it in the Form_Unload event.

Private Sub Form_Load()
    Dim conn As Connection
    Dim rs As Recordset
    `open the connection
    Set conn = New Connection
    conn.Open "Provider=MS Remote" _
        + ";Remote Server=http://winemiller" _
        + ";Remote Provider=MSDASQL;DSN=pubs"
    `fill the list
    Set rs = New Recordset
    rs.CursorLocation = adUseClient
    rs.Open "authors", conn, adOpenStatic _
        , adLockBatchOptimistic, adCmdTable
    Set adodc.Recordset = rs
End Sub
Private Sub Form_Unload(Cancel As Integer)
    Dim rs As Recordset
    Dim conn As Connection
    `open the connection
    Set conn = New Connection
    Set rs = New Recordset
    On Error GoTo UpdateFailure
    conn.Open "Provider=MS Remote; _
       + "Remote Server=http://winemiller" _
       + ";Remote Provider=MSDASQL;DSN=pubs"
    rs.Open adodc.Recordset, conn
    rs.UpdateBatch
    On Error GoTo 0
UpdateDone:
    Exit Sub
UpdateFailure:
    ShowADOError conn
    GoTo UpdateDone
End Sub

: 3. Add the ShowADOError procedure to display any errors that occur during the update.

Private Sub ShowADOError(pConn As Connection)
    `spin through the errors collection and
    `display the constructed error message
    Dim ADOError As Error
    Dim sError As String
    For Each ADOError In pConn.Errors
        sError = sError & ADOError.Number & " - " 
        sError = ADOError.Description + vbCrLf
    Next ADOError
    MsgBox sError
End Sub

How It Works

First, in the Form_Load event, a client-side recordset is opened and assigned to the ADO Data control's Recordset property. The user can make changes to all the records, moving back and forth through the entire recordset. When the form unloads, another Recordset object is created with the changes from the first. The UpdateBatch method commits the changes to the database.

Comments

Remote updates work great for single user applications, but open the window for concurrency problems if there is more than one user. If multiple users are running applications that perform remote updates, the second user (and any subsequent users) will get an error if he tries to update a record that has been changed since he first opened the recordset.

8.11 How do I...

Build a middle-tier business object using ActiveX Data Objects?

Problem

Our company has decided to move our applications to a three-tier architecture. How do I build a middle-tier business object using ActiveX Data Objects?

Technique

Remote updates work well where the application is small and concurrency is not a big issue; however, as applications and number of users grow, a new paradigm for application development becomes attractive. Three-tier applications typically move the data access and business rules from the client back to the server. This enables thin clients, sometimes just a browser, and easy deployment when it comes time to make changes.

Any data access method can be used to build middle-tier components, but ADO in particular is a good choice because of its ability to query almost anything and its light footprint.

When designing middle-tier objects there are several questions that must be answered:

4 What objects will be exposed? Some companies choose to build an object for each physical database table, whereas others expose logical objects where several physical database tables are combined into one logical entity.
4 What functions should those objects perform? Deletes, updates, and inserts are the first functions that come to mind, but that might not be appropriate in all circumstances. Imagine a medical application where a delete is not allowed, but a strikeout is.
4 Is there a standard to which you should adhere? Many companies have guidelines about the type of interface a middle-tier component should have. If you are just starting your migration to three-tier, this might be where you should start. Defining this standard up-front will save time in the long run as other developers begin to use and build the middle-tier components.
4 Do you plan to use Microsoft Transaction Server? Transaction Server is not necessary to deploy three-tier applications, but provides many benefits to applications that use it. The middle-tier component in this How-To is written with Transaction Server in mind, but does not require MTS. You will see why some of the unusual techniques used in this sample make sense in the next How-To.

Steps

Open the ThreeTier.vbg project group. Change the connection string in the Class_Initialize event of cAuthor to match your user, server, and password. Run the application. The UITier application, shown in Figure 8.12, is very similar to some of the other How-To's in this chapter. But, there is one important difference: there is no data access code in the application. All the data access is done through the MiddleTier.dll.

Figure 8.12. The UITier application uses a middle-tier component for data access.

: 1. Create a new ActiveX DLL. Change the project name to MiddleTier. Rename the default class from Class1 to cAuthor.
: 2. Add a reference to Microsoft ActiveX Data Objects 2.0 Library.
: 3. Add the following code for the Declarations section, the Class_Initialize event, and the Class_Terminate events. Remember to change the ConnectionString parameter on mConn's Open method to your configuration.

Option Explicit
Private mConn As Connection
Private Sub Class_Initialize()
    Set mConn = New Connection
    mConn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" _
        + ";Location=WINEMILLER;Database=pubs"
End Sub
Private Sub Class_Terminate()
    mConn.Close
    Set mConn = Nothing
End Sub

: 4. Add the method to handle retrieving a list.

Public Function GetList(Optional psWhere As String) As Object
    `return a recordset to the client as object so she doesn't
    `even need to have a reference to ADO to use this object
    Dim sCmd As String
    Dim rs As Recordset
    sCmd = "select * from authors"
    `if she wanted a restricted list give it to her
    If Len(psWhere) > 0 Then
        sCmd = sCmd + " where " + psWhere
    End If
    Set rs = New Recordset
    rs.CursorLocation = adUseClient
    rs.Open sCmd, mConn, adOpenForwardOnly, adLockReadOnly _
        , adCmdText
    Set GetList = rs
End Function

: 5. Add the DeleteAuthor method. Given the author's ID, it constructs a delete statement and executes the delete. Notice that the DeleteAuthor method does not return a True or False indicating success or failure. Instead, if there is a problem, it raises an error. The reason for this is explained in How-To 8.13.

Public Sub DeleteAuthor(psau_id As String)
    `build delete string
    Dim sCmd As String
    sCmd = "delete authors"
    sCmd = sCmd + " where au_id = `" + psau_id + "`"
    `use execute to do the delete
    On Error GoTo DeleteError:
    mConn.Execute sCmd
    Exit Sub
DeleteError:
    Err.Raise vbObjectError, , "Error deleting"
End Sub

: 6. The UpdateAuthor method is next. Like the DeleteAuthor method, it also indicates failure through a raised error instead of a return value. By now you may have noticed something unusual about this class: there are no properties except the connection; everything is passed as a parameter! Each time an application calls a method or sets a property, one round trip must be made across the network, and that is assuming early binding. When a DLL is in process on the same machine, that hit is seldom noticeable, but in an environment where your middle-tier is running on a server that might not even be in the same country, the performance hit is definitely noticeable. This is perhaps the most important lesson to learn when building middle-tier components: They are not objects for holding data, rather they are objects for doing work.

Public Sub UpdateAuthor(psau_id As String, psau_lname As String _
    , psau_fname As String, psphone As String _
    , psaddress As String _
    , pscity As String, psstate As String, pszip As String _
    , pbcontract As Boolean)
    `build udpate string
    Dim sCmd As String
    sCmd = "update authors "
    sCmd = sCmd + " set"
    sCmd = sCmd + " au_lname = `" + psau_lname + "`"
    sCmd = sCmd + ",au_fname = `" + psau_fname + "`"
    sCmd = sCmd + ",phone = `" + psphone + "`"
    sCmd = sCmd + ",address = `" + psaddress + "`"
    sCmd = sCmd + ",city = `" + pscity + "`"
    sCmd = sCmd + ",state = `" + psstate + "`"
    sCmd = sCmd + ",zip = `" + pszip + "`"
    sCmd = sCmd + ",contract = " & IIf(pbcontract, 1, 0)
    sCmd = sCmd + " where au_id = `" + psau_id + "`"
    `use execute to do the update
    On Error GoTo UpdateError
    mConn.Execute sCmd
    Exit Sub
UpdateError:
    Err.Raise vbObjectError, , "Error updating"
End Sub

: 7. Finally, add the method to insert a new author.

Public Sub NewAuthor(psau_id As String, psau_lname As String _
    , psau_fname As String, psphone As String _
    , psaddress As String _
    , pscity As String, psstate As String, pszip As String _
    , pbcontract As Boolean)
    `build insest string
    Dim sCmd As String
    sCmd = "insert authors (au_id, au_lname, au_fname , "
    sCmd = sCmd + "phone , address"
    sCmd = sCmd + ", city, state, zip, contract)"
    sCmd = sCmd + " values "
    sCmd = sCmd + "(`" + psau_id + "`"
    sCmd = sCmd + ",'" + psau_lname + "`"
    sCmd = sCmd + ",'" + psau_fname + "`"
    sCmd = sCmd + ",'" + psphone + "`"
    sCmd = sCmd + ",'" + psaddress + "`"
    sCmd = sCmd + ",'" + pscity + "`"
    sCmd = sCmd + ",'" + psstate + "`"
    sCmd = sCmd + ",'" + pszip + "`"
    sCmd = sCmd + "," & IIf(pbcontract, 1, 0)
    sCmd = sCmd + ")"
    `use execute to do the insert
    On Error GoTo InsertError
    mConn.Execute sCmd
    Exit Sub
InsertError:
    Err.Raise vbObjectError, , "Error inserting"
End Sub

: 8. Now it is time to create the user interface level of the application. Using File | Add Project, add a new Standard EXE to the project group. Change the project name to UITier, change the default form name to frmMain, and save. When you are prompted to save the project group file, save it as ThreeTier.vbg.
: 9. Using the Project | References menu item, add references to Microsoft ActiveX Data Objects Recordset 2.0 Library and the MiddleTier project.
: 10. Using Table 8.15, add the objects for frmMain.

Table 8.15. Objects and properties for frmMain.

OBJECT Property Value

Form Caption Authors

ListView Name listAuthors

View 3 - lvwReport

LabelEdit 1 - lvwManual

CommandButton Name cmdNew

Caption New

CommandButton Name cmdDelete

Caption Delete

CommandButton Name cmdEdit

Caption Edit

CommandButton Name cmdExit

Caption Exit

: 11. Table 8.16 lists the columns and widths for the listAuthors ListView control.

Table 8.16. Column Headers for listAuthors.

COLUMN Width

Last 1000

First 1000

Address 2000

City 1440

State 500

Zip 700

Phone 1440

Contract 700

: 12. Add the Declarations section, the Form_Load event, and the Form_Unload event. This code takes care of creating and destroying the mAuthors object and filling the ListView control.

Option Explicit
Private mAuthors As cAuthor
Private Sub Form_Load()
    `fill the list with all the authors
    Dim rs As Recordset
    Dim NewItem As ListItem
    Set mAuthors = New cAuthor
    Set rs = mAuthors.GetList()
    Do Until rs.EOF
        Set NewItem = listAuthors.ListItems.Add(, rs("au_id"), _
            rs("au_lname"))
        NewItem.SubItems(1) = rs("au_fname")
        NewItem.SubItems(2) = rs("address")
        NewItem.SubItems(3) = rs("city")
        NewItem.SubItems(4) = rs("state")
        NewItem.SubItems(5) = rs("zip")
        NewItem.SubItems(6) = rs("phone")
        NewItem.SubItems(7) = rs("contract")
        rs.MoveNext
    Loop
End Sub
Private Sub Form_Unload(Cancel As Integer)
    Set mAuthors = Nothing
    Unload frmDetails
    Set frmDetails = Nothing
End Sub

: 13. Now, add the following code for the cmdDelete_Click event. The application tells the middle-tier component to delete the selected author. If it is successful, then it updates the list; otherwise it displays the trapped error.

Private Sub cmdDelete_Click()
    `delete the current author
    On Error GoTo DeleteError
    mAuthors.DeleteAuthor listAuthors.SelectedItem.Key
    listAuthors.ListItems.Remove listAuthors.SelectedItem.Key
    Exit Sub
DeleteError:
    MsgBox Err.Number + " - " + Err.Description
    Exit Sub
End Sub

: 14. The cmdEdit_Click event displays the Edit Authors dialog box, and if the user clicks OK, the event attempts to update the selected author's record through the middle-tier component. Like the cmdDelete_Click event, if the update is successful, the UI is updated; otherwise the trapped error is displayed.

Private Sub cmdEdit_Click()
    With frmDetails
        `fill the detail screen
        .txtId.Text = listAuthors.SelectedItem.Key
        .txtId.Locked = True
        .txtId.BackColor = vbButtonFace
        .txtLastName.Text = listAuthors.SelectedItem.Text
        .txtFirstName.Text = listAuthors.SelectedItem.SubItems(1)
        .txtAddress.Text = listAuthors.SelectedItem.SubItems(2)
        .txtCity.Text = listAuthors.SelectedItem.SubItems(3)
        .txtState.Text = listAuthors.SelectedItem.SubItems(4)
        .txtZip.Text = listAuthors.SelectedItem.SubItems(5)
        .txtPhone.Text = listAuthors.SelectedItem.SubItems(6)
        .chkContract.Value = _
            IIf(listAuthors.SelectedItem.SubItems(7) = "True" _
            , vbChecked, vbUnchecked)
        `show the edit dialog
        .OK = False
        .Caption = "Edit Author"
        .Show vbModal
        If .OK = True Then
            `user hit OK, update the database
            On Error GoTo EditError
            mAuthors.UpdateAuthor .txtId.Text, .txtLastName.Text _
                , .txtFirstName.Text, .txtPhone.Text, _
                 .txtAddress.Text _
                , .txtCity.Text, .txtState.Text, .txtZip.Text _
                , .chkContract.Value = vbChecked
            On Error GoTo 0
            `update successfull change ui
            listAuthors.SelectedItem.Text = .txtLastName.Text
            listAuthors.SelectedItem.SubItems(1) = _
                .txtFirstName.Text
            listAuthors.SelectedItem.SubItems(2) = _
                .txtAddress.Text
            listAuthors.SelectedItem.SubItems(3) = .txtCity.Text
            listAuthors.SelectedItem.SubItems(4) = .txtState.Text
            listAuthors.SelectedItem.SubItems(5) = .txtZip.Text
            listAuthors.SelectedItem.SubItems(6) = .txtPhone.Text
            listAuthors.SelectedItem.SubItems(7) = _
                (.chkContract.Value = vbChecked)
        End If
    End With `frmDetails
    Exit Sub
EditError:
    MsgBox Err.Number + " - " + Err.Description
    Exit Sub
End Sub

: 15. Add the cmdNew_Click event. It looks very similar to the cmdEdit_Click event, except that the txtId field is not locked when creating a new record.

Private Sub cmdNew_Click()
    With frmDetails
        `fill the detail screen
        .txtId.Text = ""
        .txtId.Locked = False
        .txtId.BackColor = vbWindowBackground
        .txtLastName.Text = ""
        .txtFirstName.Text = ""
        .txtAddress.Text = ""
        .txtCity.Text = ""
        .txtState.Text = ""
        .txtZip.Text = ""
        .txtPhone.Text = ""
        .chkContract.Value = vbUnchecked
        `show new dialog
        .OK = False
        .Caption = "New Author"
        .Show vbModal
        If .OK = True Then
            `user hit OK, update the database
            On Error GoTo NewError
            mAuthors.NewAuthor .txtId.Text, .txtLastName.Text _
                , .txtFirstName.Text, .txtPhone.Text, _
                .txtAddress.Text _
                , .txtCity.Text, .txtState.Text, .txtZip.Text _
                , .chkContract.Value = vbChecked
            On Error GoTo 0
            `update successfull change ui
            Dim NewItem As ListItem
            Set NewItem = listAuthors.ListItems.Add( _
                .txtId.Text 
                , .txtLastName.Text)
            NewItem.SubItems(1) = .txtFirstName.Text
            NewItem.SubItems(2) = .txtAddress.Text
            NewItem.SubItems(3) = .txtCity.Text
            NewItem.SubItems(4) = .txtState.Text
            NewItem.SubItems(5) = .txtZip.Text
            NewItem.SubItems(6) = .txtPhone.Text
            NewItem.SubItems(7) = (.chkContract.Value = vbChecked)
        End If
    End With `frmDetails
    Exit Sub
NewError:
    MsgBox Err.Number + " - " + Err.Description
    Exit Sub
End Sub

: 16. Round out frmMain by adding a couple of miscellaneous procedures to activate the cmdEdit_Click event when the listAuthors control is double-clicked and unload the form when cmdExit is clicked.

Private Sub listAuthors_DblClick()
    cmdEdit_Click
End Sub
Private Sub cmdExit_Click()
    Unload Me
End Sub

: 17. Next add a new form to the project. Change its name to frmDetails. Use Table 8.17 and Figure 8.13 to add the objects for frmDetails.

Figure 8.13. The Details dialog box is used to edit and create authors.

Table 8.17. Objects and properties for frmDetails.

OBJECT Property Value

TextBox Name txtId

Text ""

TextBox Name txtFirstName

Text ""

TextBox Name txtLastName

Text ""

TextBox Name txtAddress

Text ""

TextBox Name txtCity

Text ""

TextBox Name txtState

Text ""

TextBox Name txtZip

Text ""

TextBox Name txtPhone

Text ""

CheckBox Name chkContract

Caption Contract

Label Name lblId

Caption Id

Label Name lblFirstName

Caption First

Label Name lblLastName

Caption Last

Label Name lblAddress

Caption Address

Label Name lblCity

Caption City

Label Name lblState

Caption State

Label Name lblZip

Caption Zip Code

Label Name lblPhone

Caption Phone

: 18. Finally, add the following code to frmDetails so that the application can tell if the user clicked the OK button.

Option Explicit
Public OK As Boolean
Private Sub cmdCancel_Click()
    Hide
End Sub
Private Sub cmdOK_Click()
    OK = True
    Hide
End Sub

How It Works

The UITier application collects and displays data, but passes off all the work to the MiddleTier.dll. The UITier application could be replaced with DHTML, and no changes would be needed for the data access. Likewise, the underlying table structure could change, business rules could be added and deleted, and the UITier application would not have to change.

Comments

This How-To created a middle-tier component that would do its job of hiding the underlying data access and any business rules from the application, but scalability would be questionable. For each object created there would be a new database connection, and for each application using the object, there would be a separate instance of the object. The next logical step for any three-tier application is to try and reap the benefits of using Microsoft Transaction Server.

8.12 How do I...

Incorporate a business object into Microsoft Transaction Server?

Problem

I have a three-tier application in which I am experiencing scalability problems. How do I incorporate a business object into Microsoft Transaction Server?

Technique

MTS can greatly enhance the scalability of middle-tier components. There are two benefits to using MTS that most impact middle-tier components: object reuse and connection pools.

If MTS knows that an object has completed all the work that needs to be done and it is stateless, MTS will reuse that object. So, many applications using the cAuthor class from How-To 8.11 could all be using the same instance of that class. This saves time on the creation and destruction of objects, and it saves memory because only a few objects need to be instantiated instead of one for each client.

WHAT DO YOU MEAN BY STATELESS?

An object is said to be stateless if there are no properties that must stay the same between calls. If the cAuthor class from How-To 8.11 kept the author's name and other properties from one call to the next, it would be statefull. MTS could not reuse that object for different clients because each client would need the instance of cAuthor with their values intact.

The second benefit is the connection pools. MTS keeps a pool of database connections open. When an MTS object needs a new connection, MTS gives the object one of the connections that is already open. Many objects share the same connection without knowledge of this fact.

There are only a couple of things that an object needs to do to enjoy these benefits:

4 Tell MTS when the object is complete and if it was successful. Keep MTS informed, and MTS can reuse the object in addition to providing transactions. MTS provides an ObjectContext; use the SetAbort or SetComplete methods of the ObjectContext to indicate that object is complete but unsuccessful, or complete and successful.
4 Open a new connection on each call instead of holding them open. This enables MTS to grant connections and share them between objects.

Steps

This How-To uses the complete How-To 8.11 project and modifies it to take advantage of MTS. If you have completed How-To 8.11, you can use your finished project as a starting point, or you can copy the completed How-To 8.11 project from the CD and start from there.

: 1. Open the MiddleTier.vbp and a reference to Microsoft Transaction Server Type Library.
: 2. Change the NewAuthor procedure to tell MTS when it is done and to open the connection each time it's called. The changes are listed below in bold.

Public Sub NewAuthor(psau_id As String, psau_lname As String _
    , psau_fname As String, psphone As String _
    , psaddress As String _
    , pscity As String, psstate As String, pszip As String _
    , pbcontract As Boolean)
    Dim Conn As Connection
    Set Conn = New Connection
    Conn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" _
        + ";Location=WINEMILLER;Database=pubs"
    `build insest string
    Dim sCmd As String
    sCmd = "insert authors (au_id, au_lname, au_fname , "
    sCmd = sCmd + "phone , address"
    sCmd = sCmd + ", city, state, zip, contract)"
    sCmd = sCmd + " values "
    sCmd = sCmd + "(`" + psau_id + "`"
    sCmd = sCmd + ",'" + psau_lname + "`"
    sCmd = sCmd + ",'" + psau_fname + "`"
    sCmd = sCmd + ",'" + psphone + "`"
    sCmd = sCmd + ",'" + psaddress + "`"
    sCmd = sCmd + ",'" + pscity + "`"
    sCmd = sCmd + ",'" + psstate + "`"
    sCmd = sCmd + ",'" + pszip + "`"
    sCmd = sCmd + "," & IIf(pbcontract, 1, 0)
    sCmd = sCmd + ")"
    `use execute to do the insert
    On Error GoTo InsertError
    Conn.Execute sCmd
    GetObjectContext().SetComplete
    Exit Sub
InsertError:
    GetObjectContext().SetAbort
    Err.Raise vbObjectError, , "Error inserting"
End Sub

: 3. Next, change the UpdateAuthor procedure the same way. Again, all the changes are listed in bold.

Public Sub UpdateAuthor(psau_id As String, psau_lname As String _
    , psau_fname As String, psphone As String _
    , psaddress As String _
    , pscity As String, psstate As String, pszip As String _
    , pbcontract As Boolean)
    Dim Conn As Connection
    Set Conn = New Connection
    Conn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" _
        + ";Location=WINEMILLER;Database=pubs"
    `build udpate string
    Dim sCmd As String
    sCmd = "update authors "
    sCmd = sCmd + " set"
    sCmd = sCmd + " au_lname = `" + psau_lname + "`"
    sCmd = sCmd + ",au_fname = `" + psau_fname + "`"
    sCmd = sCmd + ",phone = `" + psphone + "`"
    sCmd = sCmd + ",address = `" + psaddress + "`"
    sCmd = sCmd + ",city = `" + pscity + "`"
    sCmd = sCmd + ",state = `" + psstate + "`"
    sCmd = sCmd + ",zip = `" + pszip + "`"
    sCmd = sCmd + ",contract = " & IIf(pbcontract, 1, 0)
    sCmd = sCmd + " where au_id = `" + psau_id + "`"
    `use execute to do the update
    On Error GoTo UpdateError
    Conn.Execute sCmd
    GetObjectContext().SetComplete
    Exit Sub
UpdateError:
    GetObjectContext().SetAbort
    Err.Raise vbObjectError, , "Error updating"
End Sub

: 4. Now change the DeleteAuthor method.

Public Sub DeleteAuthor(psau_id As String)
    Dim Conn As Connection
    Set Conn = New Connection
    Conn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" _
        + ";Location=WINEMILLER;Database=pubs"
    `build delete string
    Dim sCmd As String
    sCmd = "delete authors"
    sCmd = sCmd + " where au_id = `" + psau_id + "`"
    `use execute to do the delete
    On Error GoTo DeleteError:
    Conn.Execute sCmd
    GetObjectContext().SetComplete
    Exit Sub
DeleteError:
    GetObjectContext().SetAbort
    Err.Raise vbObjectError, , "Error deleting"
End Sub

: 5. Change the GetList method to include the new code.

Public Function GetList(Optional psWhere As String) As Object
    Dim Conn As Connection
    Set Conn = New Connection
    Conn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" _
        + ";Location=WINEMILLER;Database=pubs"
    `return a record set to the client as object so he doesn't
    `even need to have a reference to ADO to use this object
    Dim sCmd As String
    Dim rs As Recordset
    sCmd = "select * from authors"
    `if they wanted a restricted list give it to them
    If Len(psWhere) > 0 Then
        sCmd = sCmd + " where " + psWhere
    End If
    Set rs = New Recordset
    rs.CursorLocation = adUseClient
    On Error GoTo GetListError:
    rs.Open sCmd, Conn, adOpenForwardOnly, adLockReadOnly, _
        adCmdText
    Set GetList = rs
    GetObjectContext().SetComplete
    Exit Function
GetListError:
    GetObjectContext().SetAbort
    Err.Raise vbObjectError, , "Error getting list"
End Function

: 6. Finally, remove the mConn variable from the Declarations section and delete the Class_Initialize and Class_Terminate events. Compile the new DLL and fix any errors.
: 7. Using Microsoft Management Console, create a new MTS package and add the MiddleTier.dll to the package so that your package and objects appear like Figure 8.14.

Figure 8.14. The MiddleTier.cAuthors component configured in MTS.

: 8. If you are running MTS on a separate machine, use dcomcnfg.exe to tell the local machine to create the MiddleTier.cAuthor component on the MTS server. Load the UITier.vbp project and run it. Without any changes to the user interface tier, the application now runs under MTS.

How It Works

Like How-To 8.11, the UI does very little work, and the MiddleTier.dll does the data access. However, now the middle-tier is optimized for MTS. By using the SetAbort and SetComplete methods of the ObjectContext, MTS knows when the object is completed and stateless so it can be reused. MTS can also hand out connections as needed because the middle-tier opens a connection for each call. MTS can immediately reuse those connections after the call is complete.

Comments

The MiddleTier.dll is now a highly scalable middle-tier component. Because this particular application would probably spend only a little time within the middle-tier's code, compared to the user's time typing and browsing, hundreds of clients could be serviced using probably only a small number of actual objects.

The only thing lacking in the cAuthor class is some really nice error reporting; "Error getting list" does not really help much. How-To 8.13 shows you how to handle ADO errors.

8.13 How do I...

Handle ActiveX Data Objects errors?

Problem

To make my application more robust and easier to debug, I need to deal with and report to the user ADO errors. How do I handle ActiveX Data Objects errors?

TECHNIQUE

ADO reports errors through the Connection object's Errors collection. The values from the last Error object in the Error's collection will also be repeated in Visual Basic's Err object. Typical error handling looks something like the code below. Before calling on an ADO object's methods or properties, use On Error Goto. Depending on the level of the application, you can then either raise an error or display a message to the user. This code below comes from a middle-tier component and raises an error for the UI tier to display.

`open the connection
On Error GoTo InitializeError
mConn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password;" _
    + "Location=WINEMILLER;Database=pubs"
Exit Sub
InitializeError:
Err.Raise aeInitializeError, , FormatError(mConn _
    , "An error occurred while making the database connection.")

Steps

This How-To uses the complete How-To 8.11 project and modifies it to better deal with and report ADO errors. If you have completed How-To 8.11, you can use your finished project as a starting point, or you can copy the completed How-To 8.11 project from the CD and start from there.

: 1. Open the MiddleTier.vbp and add the following code to the cAuthor class.

Private Function FormatError(pConn
 As Connection, psAdditionalMessage _
    As String) As String
`start it with any message passed in
    Dim Error As Error
    Dim sTemp As String
    If Len(psAdditionalMessage) > 0 Then
        sTemp = psAdditionalMessage + vbCrLf
    End If
    `spin through the errors collection and add in all those 
    `errors
    For Each Error In pConn.Errors
        sTemp = sTemp + Error.Source + " reported " _
            & Error.Number _
            & " - " + Error.Description + vbCrLf
    Next Error
    FormatError = sTemp
End Function

: 2. Add the following enumeration to the Declarations section of cAuthor. By providing meaningful error codes, a UI tier developer could decide how critical the error is.

Public Enum cAuthorErrors
    aeInsertError = (vbObjectError + 1)
    aeUpdateError = (vbObjectError + 2)
    aeDeleteError = (vbObjectError + 3)
    aeFillError = (vbObjectError + 4)
    aeInitializeError = (vbObjectError + 5)
End Enum

: 3. Add error handling to the Class_Initialize procedure. Changes are highlighted in bold.

Private Sub Class_Initialize()
    Set mConn = New Connection
    `open the connection
    On Error GoTo InitializeError
    mConn.Open "Provider=SQLOLEDB.1;User ID=sa;Password=password" _
        + ";Location=WINEMILLER;Database=pubs"
    Exit Sub
InitializeError:
    Err.Raise aeInitializeError, , FormatError(mConn _
        , "An error occurred while making the database _
           connection.")
End Sub

: 4. Make similar changes to the GetList, UpdateAuthor, DeleteAuthor, and NewAuthor procedures. Again changes are highlighted in bold. In addition, each function also has a chance to introduce an error. Those are also highlighted in bold. You can use those errors to see the results of the error handling.

Public Sub NewAuthor(psau_id As String, psau_lname As String _
    , psau_fname As String, psphone As String _
    , psaddress As String _
    , pscity As String, psstate As String, pszip As String _
    , pbcontract As Boolean)
    `build insest string
    Dim sCmd As String
    sCmd = "insert sauthors (au_id, au_lname, au_fname , "
    sCmd = sCmd + "phone , address"
    sCmd = sCmd + ", city, state, zip, contract)"
    sCmd = sCmd + " values "
    sCmd = sCmd + "(`" + psau_id + "`"
    sCmd = sCmd + ",'" + psau_lname + "`"
    sCmd = sCmd + ",'" + psau_fname + "`"
    sCmd = sCmd + ",'" + psphone + "`"
    sCmd = sCmd + ",'" + psaddress + "`"
    sCmd = sCmd + ",'" + pscity + "`"
    sCmd = sCmd + ",'" + psstate + "`"
    sCmd = sCmd + ",'" + pszip + "`"
    sCmd = sCmd + "," & IIf(pbcontract, 1, 0)
    sCmd = sCmd + ")"
    `use execute to do the insert
    On Error GoTo InsertError
    mConn.Execute sCmd
    Exit Sub
InsertError:
    Err.Raise aeInsertError, , FormatError(mConn _
        , "An error occured while inserting the author.")
End Sub
Public Sub UpdateAuthor(psau_id As String, psau_lname As String _
    , psau_fname As String, psphone As String _
    , psaddress As String _
    , pscity As String, psstate As String, pszip As String _
    , pbcontract As Boolean)
    `build udpate string
    Dim sCmd As String
    sCmd = "udpate authors "
    sCmd = sCmd + " set"
    sCmd = sCmd + " au_lname = `" + psau_lname + "`"
    sCmd = sCmd + ",au_fname = `" + psau_fname + "`"
    sCmd = sCmd + ",phone = `" + psphone + "`"
    sCmd = sCmd + ",address = `" + psaddress + "`"
    sCmd = sCmd + ",city = `" + pscity + "`"
    sCmd = sCmd + ",state = `" + psstate + "`"
    sCmd = sCmd + ",zip = `" + pszip + "`"
    sCmd = sCmd + ",contract = " & IIf(pbcontract, 1, 0)
    sCmd = sCmd + " where au_id = `" + psau_id + "`"
    `use execute to do the update
    On Error GoTo UpdateError
    mConn.Execute sCmd
    Exit Sub
UpdateError:
    Err.Raise aeUpdateError, , FormatError(mConn _
        , "An error occured while updating the author.")
End Sub
Public Sub DeleteAuthor(psau_id As String)
    `build delete string
    Dim sCmd As String
    sCmd = "delete authorsx"
    sCmd = sCmd + " where au_id = `" + psau_id + "`"
    `use execute to do the delete
    On Error GoTo DeleteError:
    mConn.Execute sCmd
    Exit Sub
DeleteError:
    Err.Raise aeDeleteError, , FormatError(mConn _
        , "An error occured while deleting the author.")
End Sub
Public Function GetList(Optional psWhere As String) As Object
    `return a record set to the client as object so they don't
    `even need to have a reference to ADO to use this object
    Dim sCmd As String
    Dim rs As Recordset
    sCmd = "zselect * from authors"
    `if they wanted a restricted list give it to them
    If Len(psWhere) > 0 Then
        sCmd = sCmd + " where " + psWhere
    End If
    Set rs = New Recordset
    rs.CursorLocation = adUseClient
    On Error GoTo GetListError
    rs.Open sCmd, mConn, adOpenForwardOnly, adLockReadOnly, _
        adCmdText
    Set GetList = rs
    Exit Function
GetListError:
    Err.Raise aeFillError, , FormatError(mConn _
        , "An error occured while getting the list of authors.")

End Function

How It Works

The On Error Goto statements trap the ADO errors. Each procedure then uses the FormatError function to raise another error that a top-level component can display to the user. Figure 8.15 shows a sample of the error message generated by FormatError.

Figure 8.15. The formatted error message generated by FormatError.

Comments

Consistent and thorough error handling is a must for creating robust applications and components. Often, error handling is the last thing that is added to an application. By taking the time up front to develop a consistent and required approach to error handling, it will become second nature and save you time in the long run as your code starts to debug itself instead of being debugged by your customers.

*OBJECT*	*Property*	*Value*
Form	Caption	Authors
ADO Data control	Name	adodc
	Caption	Authors
	Align	2 - vbAlignBottom
	ConnectionString	DSN=pubs;User Id=sa;Password=password
	RecordSource	authors
TextBox	Name	txtFirstName
	Text	""
	DataSource	adodc
	DataField	au_fname
TextBox	Name	txtLastName
	Text	""
	DataSource	adodc
	DataField	au_lname
TextBox	Name	txtAddress
	Text	""
	DataSource	adodc
	DataField	address
TextBox	Name	txtCity
	Text	""
	DataSource	adodc
	DataField	city
TextBox	Name	txtState
	Text	""
	DataSource	adodc
	DataField	state
TextBox	Name	txtZip
	Text	""
	DataSource	adodc
	DataField	zip
TextBox	Name	txtPhone
	Text	""
	DataSource	adodc
	DataField	phone
CheckBox control	Name	chkContract
	Caption	Contract
	DataSource	adodc
	DataField	contract
Label	Name	lblFirstName
	Caption	First
Label	Name	lblLastName
	Caption	Last
Label	Name	lblAddress
	Caption	Address
Label	Name	lblCity
	Caption	City
Label	Name	lblState
	Caption	State
Label	Name	lblZip
	Caption	Zip Code
Label	Name	lblPhone
	Caption	Phone

*COLUMN*	*WIDTH*
Name	2000
Address	2000
City	1440
State	500
Zip	700

www.Sir FreeDom.com.Ar

Visual Basic 6 Database How-To

- 8 - Using ActiveX Data Objects

8.1 Browse a SQL Server Database with the ADO Data Control

8.2 Create and Delete Records in a SQL Server Database Using the ADO Data Control

8.3 Retrieve Results from a SQL Server Using ActiveX Data Objects

8.4 Alter Data Using ActiveX Data Objects

8.5 Perform a Transaction Using ActiveX Data Objects

8.6 Execute a SQL Server Stored Procedure Using ActiveX Data Objects

8.7 Execute a Parameterized SQL Server Stored Procedure with ActiveX Data Objects

8.8 Create and Modify SQL Server Objects with ActiveX Data Objects

8.9 Execute Batch Updates with ActiveX Data Objects

8.10 Make Remote Updates to Data with ActiveX Data Objects

8.11 Build a Middle-Tier Business Object Using ActiveX Data Objects

8.12 Incorporate a Business Object into Microsoft Transaction Server

8.13 Handle ActiveX Data Objects Errors

8.1 How do I...

Browse a SQL Server database with the ADO Data control?

Table 8.1. Objects and properties for frmMain.

8.2 How do I...

Create and delete records in a SQL Server database using the ADO Data control?

Table 8.2. New objects and properties for frmMain.

8.3 How do I...

Retrieve results from a SQL Server using ActiveX Data Objects?

Table 8.3. New objects and properties for frmAuthors.

Table 8.4. Column Headers for listAuthors.

8.4 How do I...

Alter data using ActiveX Data Objects?

Table 8.5. Objects and properties for frmAuthors.

Table 8.6. Column headers for listAuthors.

8.5 How do I...

Perform a transaction using ActiveX Data Objects?

Table 8.7. Objects and properties for frmMain.

8.6 How do I...

Execute a SQL Server stored procedure using ActiveX Data Objects?

Table 8.8. Objects and properties for frmMain.

8.7 How do I...

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

Table 8.9. Objects and properties for frmMain.

Table 8.10. Column Headers for listWorks.

8.8 How do I...

Create and modify SQL Server objects with ActiveX Data Objects?

Table 8.11. Objects and properties for frmMain.

8.9 How do I...

Execute batch updates with ActiveX Data Objects?

Table 8.12. Objects and properties for frmMain.

Table 8.13. Column Headers for listAuthors.

Table 8.14. Objects and properties for frmDetails.

8.10 How do I...

Make remote updates to data with ActiveX Data Objects?

8.11 How do I...

Build a middle-tier business object using ActiveX Data Objects?

Table 8.15. Objects and properties for frmMain.

Table 8.16. Column Headers for listAuthors.

Table 8.17. Objects and properties for frmDetails.

8.12 How do I...

Incorporate a business object into Microsoft Transaction Server?

8.13 How do I...

Handle ActiveX Data Objects errors?

- 8 -
Using ActiveX Data Objects