
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:
CONNECTING TO OTHER DATABASESAll 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".
This How-To shows the quickest and simplest way to get up and running using ADO, 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.
Retrieve data without the use of the ADO Data control in this How-To.
This How-To shows you how to update, insert, and delete records using ADO.
Most business applications require them; this How-To shows you how to perform a transaction in ADO.
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.
Some stored procedures have return values and output parameters. This How-To shows you what you need to know to handle parameterized stored procedures.
The ADO object model does not let you modify SQL Server objects. This How-To shows you how to get around it.
Saving many changes at once can increase performance. This How-To shows you how.
In this How-To you will learn how to best update a Recordset using ADO when connections and bandwidth are limited.
Move up to three-tier development in this How-To, where you learn how to build a middle-tier business object using ADO.
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.
This How-To shows you what to do when things do go wrong.
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:
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.
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.
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:
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.
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
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
Private Sub mnuNew_Click()
adodc.Recordset.AddNew
`so we can save that new record
mnuSave.Enabled = True
End Sub
Private Sub mnuSave_Click()
Save
End Sub
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
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.
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:
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 STRINGSIt 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.
Table 8.3. New objects and properties for frmAuthors.
OBJECT Property Value Form Caption Authors ListView Name listAuthors View 3 - lvwReport LabelEdit 1 - lvwManual
Table 8.4. Column Headers for listAuthors.
COLUMN WIDTH Name 2000 Address 2000 City 1440 State 500 Zip 700
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.
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.
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.
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
Table 8.6. Column headers for listAuthors.
COLUMN Width Name 2000 Address 2000 City 1440 State 500 Zip 700
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
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
Private Sub Form_Unload(Cancel As Integer)
mConn.Close
Set mConn = Nothing
End Sub
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
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
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
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
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
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.
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:
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.
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
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"
RefreshLists
End Sub
Private Sub Form_Unload(Cancel As Integer)
mConn.Close
Set mConn = Nothing
End Sub
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
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.
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.
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
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
Private Sub optAuthorTitle_Click()
RefreshList
End Sub
Private Sub optTitleAuthor_Click()
RefreshList
End Sub
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.
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:
A NOTE ON STORED PROCEDURES AND ADOKeep 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.
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
Table 8.10. Column Headers for listWorks.
COLUMN Width Name 2000 TITLE 2000
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
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
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.
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.
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
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
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.
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:
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.
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
Table 8.13. Column Headers for listAuthors.
COLUMN Width Last 1440 First 1440 Address 2000 City 1440 State 500 Zip 700
Option Explicit Private mConn As Connection
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
Private Sub Form_Unload(Cancel As Integer)
Unload frmDetails
Set frmDetails = Nothing
mConn.Close
Set mConn = Nothing
End Sub
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
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
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
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.
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:
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 ADDRESSESThe Remote Server parameter in the ConnectionString expects an http address for the server. On a local network the http address is simply http://myserver.
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.
Figure 8.11. The New Authors application uses a client-side cursor.
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
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.
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:
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.
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
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
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
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
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
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
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
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
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
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
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
Private Sub listAuthors_DblClick()
cmdEdit_Click
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
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 <