Managing Transactions
ADO can manage transactions for you—it does so at the Connection level, assuming that the data provider supports transactions. If your provider supports transactions, the Connection.Properties("Transaction DDL") is present. If the provider does not support transactions, you'll trip a trappable error if you try to reference this property. I guess that's a sure sign it's not supported.
So, let's assume your provider supports transactions. Now you can start a new transaction with the BeginTrans Connection object method, commit a transaction with CommitTrans, and roll back transactions with RollbackTrans. After you call the BeginTrans method, the provider will no longer instantaneously commit any changes you make until you call CommitTrans or RollbackTrans to end the transaction.
Just remember the scope of transactions. If you execute several queries on a Connection, all of the operations are under the same transaction umbrella. If you (or ADO) open an additional connection, its transaction scope is unaffected by other transactions on other connections.
For providers that support nested transactions, calling the BeginTrans method within an open transaction starts a new, nested transaction. The return value indicates the level of nesting: a return value of 1 indicates you have opened a top-level transaction (that is, the transaction is not nested within another transaction), 2 indicates that you have opened a second-level transaction (a transaction nested within a top-level transaction); and so forth. Calling CommitTrans or RollbackTrans affects only the most recently opened transaction; you must close or roll back the current transaction before you can resolve any higher-level transactions.
Depending on the Connection object's Attributes property, calling either the CommitTrans or RollbackTrans methods may automatically start a new transaction. If the Attributes property is set to adXactCommitRetaining, the provider automatically starts a new transaction after a CommitTrans call. If the Attributes property is set to adXactAbortRetaining, the provider automatically starts a new transaction after a RollbackTrans call.
Connection Object Events
ADO 2.0 implemented a comprehensive set of events. This moved ADO ahead in the race, when compared to RDO, which implemented the first (albeit limited) set of data access events. Unfortunately, these event handlers were redefined in ADO 2.1—that's the bad news if you were using the ADO 2.0 events. The good news is that they were not revised again in ADO 2.5. Basically, the new event handlers pass back an ADO 2.1 format Recordset (and the same format was used in ADO 2.5). This broke the event handlers in existing ADO 2.0 applications, which expected 2.0-format Recordsets. To deal with this problem, you have to recode your existing ADO 2.0 event handlers after having set your Project Reference to ADO 2.1 or 2.5. This should not involve more than a cut-and-paste operation.
| Tip | When debugging event handlers, remember that Visual Basic has a tendency to "lose" events. That is, they might fire when your code is stopped at a breakpoint or dialog box (such as a MsgBox). However, if this happens, the events can be tossed. Bug? Bug. |
In some cases, however, just cutting and pasting the event handlers into the new prototype statements is not an option. Suppose your code uses the ADODC, the Data Form Wizard, or any of the Visual Database Tools, such as the Data Environment Designer, and you set your project reference to ADO 2.1 or 2.5. In this case, you have to take more drastic corrective action because all of these tools have been specifically written to support (just) ADO 2.0 Recordset objects. To fix this problem, edit your source code, and wherever your event handler (as generated by the Data Environment Designer, for example) plugs in its event handler prototype, modify the code to reference the ADO 2.0 Recordset—specifically. For example, notice how I replaced the ADODB.Recordset with ADODB.Recordset20 in the following event-handler prototype statement.
Private Sub Connection1_WillExecute(Source As String, _ CursorType As ADODB.CursorTypeEnum, _ LockType As ADODB.LockTypeEnum, Options As Long, _ adStatus As ADODB.EventStatusEnum, _ ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset20, _ ByVal pConnection As ADODB.Connection) End Sub
The ADO 2.1 and 2.5 type libraries include definitions for both the older and newer Recordset objects. However, the newer Recordset 2.1 properties, methods, and options won't be available on the 2.0 version of the Recordset.
When you want to expose ADO object event handlers, you must declare the objects using the WithEvents operand. For example, the following code instructs Visual Basic to include any ADODB Connection events amongst the other objects and events in the code pane.
Let's walk through a code sample that illustrates the use of several of these events:
Option Explicit Dim WithEvents cn As ADODB.Connection Dim er As ADODB.Error Dim strMsg As String
This first section sets up the cn (Connection) object using the WithEvents syntax to expose the event handlers.
Private Sub Form_Load() Set cn = New Connection cn.Open "dsn=localserver", "admin", "pw", adAsyncConnect End Sub
The Form_Load event sets up the Connection object and tells ADO to begin opening it. We drop through to the end of the sub so that the initial form is painted. The Command button is still disabled. This means that if we don't enable it later, we won't be able to execute the query.
Private Sub cn_ConnectComplete(ByVal pError As ADODB.Error, _ adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) If adStatus = adStatusOK Then cmdTestPrint.Enabled = True MsgBox strMsg, vbInformation, "Connect Complete" Else MsgBox "Could not connect." & pError.Description End If strMsg = "" End Sub
The connection operation finishes and fires the ConnectComplete event. The code tests to determine whether the connection succeeded. If it did (adStatus = adStatusOK), the Command button control is enabled;. otherwise, a MsgBox dialog is displayed and nothing is done.
Private Sub cmdTestPrint_Click() cn.Execute "Execute TestPrint 'This is a test message'" , , adCmdText End Sub
We're ready to test the stored procedure. In this case, it executes a couple of TSQL Print statements. These will end up in the Connection object's Errors collection. We could have executed this statement asynchronously, but there was no need to do so.
Private Sub cn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection) DumpErrors MsgBox strMsg, vbInformation, "Execute Complete" strMsg = "" End Sub
When the query finishes, the ExecuteComplete event fires, as it does for all operations on this Connection (pConnection). Note that a Command object is exposed here even though we didn't create one explicitly. There's a pointer to a Recordset here too, whose State property shows the query "adStateExecuting".
Private Sub cn_InfoMessage(ByVal pError As ADODB.Error, _ adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) strMsg = "Info message: " & pError.Description DumpErrors End Sub
During the course of opening the Connection object, the InfoMessage event fired to tell us that we switched the default database to "Biblio"—but it did so twice. Why? BHOM.[6] At that point it would/should not be opening another connection.
The following code simply dumps the errors collection into a single string we can use to debug the operation—not a string we can show users.
Sub DumpErrors() For Each er In cn.Errors strMsg = strMsg & "Errors:" & er.Description & vbCrLf Next er End Sub
Do Events Make Much Sense in the Middle Tier?
Some developers have asked about the wisdom and practicality of implementing asynchronous operations in the middle tier. Sure, you can. The middle-tier component event handler can notify your application via DCOM if you have a mind to, but this approach implies maintaining state in the middle tier—something that's not a "best practice". So, you can do it, but try not to. It will make your application more complicated than it really needs to be, and it opens the door to random crashes.
The InfoMessage Event
Some providers, such as SQL Server, return "informational" messages when certain operations occur on the server. For example, when the current database changes, SQL Server reports back that this operation succeeded by sending the provider a message. The provider discards the message unless there is an InfoMessage event handler to deal with it. In most cases these messages are unimportant.
Error Handling
Trying to figure out what went wrong when opening a connection can be a challenge. Most of the errors seem to be grouped under three error numbers, with varying descriptions. Yes, that means that you'll have to parse the strings for the salient information and respond accordingly. Best practices dictates that you do not simply pass on these messages to end-users. They are the least likely people to be able to solve the problem.
- Error.Number-2147467259 (0×80004005) is fired for connection-related issues. This includes wrong protocol, missing (or misspelled) server, network permissions issues—basically anything that prevents ADO from creating a link to the remote server. The description often (but not always) has useful information for the developer about what went wrong.
- Error.Number-2147217843 (0×80040E4D) is fired for logon issues. However, you might have the "right" UserID but the wrong server and receive these errors if the server you specify is a working server.
- Error.Number-2147221020 (0×800404) is fired when the syntax of the ConnectionString is incorrect. This is fairly rare and should not occur after the application is debugged. If it occurs with a compiled application, it probably means that the DSN or UDL is contaminated.
| Tip | Don't make the user debug your program. Trap the errors and figure out what went wrong yourself. |
The Error.Description returned is prefixed with the source "stack" of the error. Apparently, each layer that handles the error adds an additional bracketed expression to the Description string, so that when you get your error message, it looks something like this:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'Fred'.
| Tip | If you get ConnectionWrite(GetOverlappedResult) errors, switch to TCP/IP (away from named pipes). |
[6]BHOM: an old army term. "Beats the hell out of me" or "dunno."
Team-Fly |
EAN: 2147483647
Pages: 106