Expanding the Connection Object
The Connection object is responsible for more than just the initialization of communication between the server and the application. It is also used to manage transactions, submit SQL statements directly to a provider, and set provider-specific attributes.
Let's look at an example of using several of the advanced features of the Connection object to update our database. Typically, when working with the ADO, you might assume that to update a record in a table we would open a Recordset, use AddNew to add a new record, update the fields in the Recordset and then Update the Recordset. This is how we did it in the previous chapter:
...
Set rsAddMessage = Server.CreateObject("ADODB.Recordset")
rsAddMessage.Open "Message", Conn, adOpenKeyset, adLockBatchOptimistic
rsAddMessage.AddNew
rsAddMessage.Fields("FromMsg") = strFrom
rsAddMessage.Fields("Email") = strEmail
rsAddMessage.Fields("Subject") = strSubject
rsAddMessage.Fields("Body") = strBody
rsAddMessage.Fields("WhenMsg") = CStr(Now())
rsAddMessage.Fields("MsgLevel") = intNewMsgLevel
rsAddMessage.Fields("PrevRef") = lngPrevRef
rsAddMessage.Fields("ThreadPos") = intNewThreadPos
rsAddMessage.UpdateBatch
rsAddMessage.Close
Set rsAddMessage = Nothing
...
However, consider the number of steps involved, as well as the potential server traffic issues that could arise, if the UpdateBatch method were not supported. In our example, we applied all of the required changes as a single transaction, using the UpdateBatch parameter, and thus reduced the amount of interaction that had to occur between the server and the application. However, your ADO access provider must support this facility in order for it to be of value.
Now, let's take a look at a piece of code that does the same thing, using a different tactic:
...
SQLQuery = "INSERT INTO Forum.dbo.Message " _
& "( FromMsg, Email, Subject, Body, WhenMsg, MsgLevel, " _
& " PrevRef, TopRef ) " _
& " VALUES ( '" & strFrom & cDlm & strEmail & cDlm & strSubject _
& cDlm & strBody & cDlm & CStr(Now()) & "', " & intNewMsgLevel
& ", " & lngPrevRef & ", " & intNewThreadPos & " )"
oConn.Execute SQLQuery, lRecs
...
Here we build an INSERT statement to apply the data stored in a number of variables to the records. The SQL Statement is syntactically compatible with Transaction SQL (TSQL), the native SQL language of SQL Server.
The SQL statement is then submitted to the provider using the Execute method of the Connection object. Notice that an lRecs variable is supplied as a parameter to the Execute method call. This variable will contain a count of the number of records affected by this statement after it is submitted. It can be used to test for the successful completion of the statement.
One of the advantages of this approach is that it simply requires fewer steps. More importantly, it provides us with the ability to supply batches of SQL to the Server. In the above example, we use a single INSERT statement. Consider, however, the need for multiple inserts or updates—or perhaps additional data validation that might occur during, or as a part of, the submission.