PRB: Transactions and ADO Parameters Cause Connections to Hang

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a Stored Procedure that has return and input parameters. For example:

   create procedure proctest(@in text) as return 1

2. Create a new Standard EXE Visual Basic project.
3. Set a reference to the ActiveX Data Objects Library 2.x.
4. Place two command buttons on Form1, the default form.
5. Paste the following code into the code for Form1:

   Option Explicit
   
    Dim Con1 As New ADODB.Connection
    Dim Con2 As New ADODB.Connection
    Dim Cmd1 As New ADODB.Command
    Dim Cmd2 As New ADODB.Command
    Dim ServerName As String
    Dim UserID As String
    Dim Password As String
    Dim ConString As String
   
    Private Sub Command1_Click()
   
    On Error Resume Next
   
    ' Restore error handling.
    On Error GoTo 0
   
    ' Specify properties of the command object.
    Set Cmd1.ActiveConnection = Con1
    Cmd1.CommandType = adCmdStoredProc
    Cmd1.CommandText = "proctest"
   
    ' Begin Transaction
    Con1.BeginTrans
    Cmd1.Parameters.Refresh
   
    End Sub
   
    Private Sub Command2_Click()
   
    On Error Resume Next
   
    ' Specify properties of the command object.
    Set Cmd2.ActiveConnection = Con2
    Cmd2.CommandType = adCmdStoredProc
    Cmd2.CommandText = "{? = call proctest(?)}"
   
    ' Begin Transaction.
    Con2.BeginTrans
    Cmd2.Parameters.Refresh
   
    End Sub
   
    Private Sub Form_Load()
   
    ServerName = "Govind3"
    UserID = "sa"
    Password = ""
   
    ConString = "Driver={SQL Server};Server=" & ServerName
    ConString = ConString  & ";Database=Pubs;"
    ConString = ConString  & "UID="  & UserID
    ConString = ConString  & ";PWD=" & Password  & ";DSN='';"
   
    Con1.Open ConString
    Con2.Open ConString
   
    End Sub
   
    Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As
    Integer)
   
    Con1.Close
    Con2.Close
    Set Cmd1 = Nothing
    Set Cmd2 = Nothing
    Set Con1 = Nothing
    Set Con2 = Nothing
   
    End Sub
   

6. Modify the values assigned to the ServerName, UserID, and Password variables in the Form_Load event to suit the connection needs of your SQL Server 6.5 environment.
7. Run the application. Click the Command1 button. Then click the Command2 button. The application hangs indefinitely until it is terminated through the Task Manager window.