Chapter 10 - Modifications/Corrections/Additions

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.