In Example 10-1, the ActiveConnection property setting for the Command object should have been set with the following code:
Set conn.ActiveConnection = cn
It isn't an error to not use the "Set" keyword, but what happens is that the Connection object's connection string is accessed and used to create a new Connection object when the connection is assigned without the Set keyword. This leads to two connections being created instead of one.
This information also applies to setting the ActiveConnection property within Examples 10-12 and 10-14.
To ensure that the ADO objects used in Example 10-9 and elsewhere participate in MTS transactions, the objects must be created using the CreateInstance method on the ObjectContext object. For instance, the first part of Example 10-9 would be modified as follows:
Public Sub addNewTitle()
'Declare object variables for the database connection and recordset
Dim cn As Connection
Dim cmnd As Command
Dim cmnd2 As Command
' create ASP Response object
Dim objContext As ObjectContext
Dim objRequest As Request
Set objContext = GetObjectContext()
' instantiate ADO objects
Set cn = objContext.CreateInstance("ADODB.Connection")
Set cmnd = objContext.CreateInstance("ADODB.Command")
Set cmnd2 = objContext.CreateInstance("ADODB.Command")
' create ASP objects
Set objRequest = objContext.Item("Request")
On Error GoTo HandleError
' connect to database
cn.ConnectionString = "driver={SQL Server};server=FLAME;uid=sa;pwd=;database=pubs"
cn.Open
' set connection
Set cmnd.ActiveConnection = cn
Set cmnd2.ActiveConnection = cn
Also in Example 10-14, add the ADO Recordset object to the Session object collection only if the ADO components have been modified to be both threaded. See the bonus article Storing a Recordset in the Session Object to see how to do this.
In addition, the Recordset object does need to be a disconnected recordset. Do this by setting the Recordset's CursorLocation property to adUseClient, and setting the Recordset's ActiveConnection to Nothing. Once this has happened the Connection object can be closed:
rsAuthors.CursorLocation = adUseClient
...
Set rsAuthors.ActiveConnection = Nothing
cn.Close
Using a disconnected ADO Recordset releases the database connection, but in such a way that the Recordset contents can be accessed and even modified by the component client. Then, a connection can be re-established with the database if any updates need to be made.
Another approach to persist the Recordset is to use the Save and Open methods to store a recordset to file:
rsAuthors.Save "c:\temp\somefile.adtg"
...
Set rsAuthors = Nothing
...
rsAuthors.Open "c:\temp\somefile.adtg"
Set rsAuthors.ActiveConnection = cn
With the released version of SQL Server 7.0, in Example 10-11 there is also a foreign key relationship between titles and roysched. The Stored procedure sp_delete_titles_1 has been modified to account for this:
CREATE PROCEDURE [sp_delete_titles_1]
(@title_id_1 tid)
AS
BEGIN
DELETE [pubs].[dbo].[titleauthor]
WHERE
( [title_id] = @title_id_1)
DELETE [pubs].[dbo].[roysched]
WHERE
( [title_id] = @title_id_1)
DELETE [pubs].[dbo].[titles]
WHERE
( [title_id] = @title_id_1)
END
The downloadable example SQL contains the correct code.
In the Further Reading section, remove the following URL:
For a description of the PUBS database tables, see http://premium.microsoft.com/msdn/library/sdkdoc/sql/tsqlref/src/append_b_8885.htm.