VBA Code problem with recordset using ADO from SQL Stored Proc

[MeSoCrazy]

Hello,

This is my first time trying to create a recordset from a SQL Server Stored Procedure. I expect one record returned with about fifteen columns. I will then take the columns and populate a form's textbox with the value or a null.

When I get to the IF loop I get the following error:

Runtime error '3704' Operation not allowed when the object is closed.

Here is the code: (the rstTeamDates has been set as a New ADODB.Recordset at the beginnning of the Sub) and cnnASPIRE is a connection string in the Form Load.

    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = cnnASPIRE
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.sp_CombinedEventActualCycleTime"
        .Parameters("@RTEventID") = EventProfileID
        Set rstTeamDates = .Execute
    End With
    

    If rstTeamDates.RecordCount > 0 Then     'This is where I get the error when stepping through the code...
        If IsDate(rstTeamDates(0)) = True Then
            SummaryActualSoldNot = rstTeamDates("SoldNotificationReceivedDate")
        Else
            SummaryActualSoldNot = Null
        End If
        If IsDate(rstTeamDates("RequirementsCompleteDate")) = True Then
            SummaryActualReqCompl = rstTeamDates("RequirementsCompleteDate")
        Else
            SummaryActualReqCompl = Null
        End If
        If IsDate(rstTeamDates("GroupSetupCompleteDate")) = True Then
            SummaryActualGSU = rstTeamDates("GroupSetupCompleteDate")
        Else
            SummaryActualGSU = Null
        End If
        If IsDate(rstTeamDates("EnrollmentReceivedDate")) = True Then
            SummaryActualEnrollRec = rstTeamDates("EnrollmentReceivedDate")
        Else
            SummaryActualEnrollRec = Null
        End If
        If IsDate(rstTeamDates("EnrollmentLoadedDate")) = True Then
            SummaryActualEnrollLoaded = rstTeamDates("EnrollmentLoadedDate")
        Else
            SummaryActualEnrollLoaded = Null
        End If
        If IsDate(rstTeamDates("SPAASetupCompleteDate")) = True Then
            SummaryActualSPAACompl = rstTeamDates("SPAASetupCompleteDate")
        Else
            SummaryActualSPAACompl = Null
        End If
        If IsDate(rstTeamDates("IDCardMeteredDate")) = True Then
            SummaryActualIDCMetered = rstTeamDates("IDCardMeteredDate")
        Else
            SummaryActualIDCMetered = Null
        End If
        If IsDate(rstTeamDates("PlanLoadCompleteDate")) = True Then
            SummaryActualPLNCompl = rstTeamDates("PlanLoadCompleteDate")
        Else
            SummaryActualPLNCompl = Null
        End If
        If IsDate(rstTeamDates("ProductBuildCompleteDate")) = True Then
            SummaryActualPBDCompl = rstTeamDates("ProductBuildCompleteDate")
        Else
            SummaryActualPBDCompl = Null
        End If
        If IsDate(rstTeamDates("GroupBilledDate")) = True Then
            SummaryActualBillReleased = rstTeamDates("GroupBilledDate")
        Else
            SummaryActualBillReleased = Null
        End If
        If IsDate(rstTeamDates("CertsIssuedDate")) = True Then
            SummaryActualCertSPD = rstTeamDates("CertsIssuedDate")
        Else
            SummaryActualCertSPD = Null
        End If
        rstTeamDates.Close
    Else
        SummaryActualSoldNot = Null
        SummaryActualReqCompl = Null
        SummaryActualGSU = Null
        SummaryActualEnrollRec = Null
        SummaryActualEnrollLoaded = Null
        SummaryActualSPAACompl = Null
        SummaryActualIDCMetered = Null
        SummaryActualPLNCompl = Null
        SummaryActualPBDCompl = Null
        SummaryActualBillReleased = Null
        SummaryActualCertSPD = Null
        rstTeamDates.Close
    End If

If I run the Stored Procedure from an Access Pass Through query (exec sp_CombinedEventActualCycleTime 5713) In my code the EventProfileID is equal to 5713 as I step through the code.

Please show me the error of my ways!

Thanks, Troy

 

[jrbarnett]

If you run this with SQL profiler in the background, does it show the execution of your stored procedure?

If you run your SP through management studio with its parameter, using the same credentials as your cnnASPIRE connection for authentication, does it work?

John

 

[MeSoCrazy]

Hello,

Thanks for the response. I work for a company and unfortunately I am not in the IT department, but work for a business group so I do not have Administrator rights to the SQL server nor my own laptop. I will try to get with IT tomorrow to see if I can get someone who has the rights, willingness to help me run SQL Profiler.

I have setup a pass-through query on my Access DB which uses the same connection string that my cnnASPIRE represents. This connection string also works for the rest of my strSQL strings when I am doing various SELECT, UPDATE and DELETE statements in other parts of the code. The pass-through is simply:

exec sp_CombinedEventActualCycleTime 5713

In the pass through query it returns one record as expected.

When I step through my code the EventProfileID definitely equals 5713. This should return one record of fifteen columns and all of the columns returns dates. Currently fourteen of the fifteen columns are filled with dates and one is blank.

I am not able to adjust the Stored Procedure without IT, but I noticed a post on another thread where he mentioned that he was getting the same error and added this to his SP:

"SET NOCOUNT ON" after the "AS" at the top of the SP

and code started creating the recordset properly. This line is not in the SP on the SQL Server. I am not sure what this line added to the SP does can someone explain?

I will also get IT to run the SQL Profiler while the code is executed on Monday.

 

[jrbarnett]

The SET NOCOUNT ON line tells SQL Server not to display the Rows affected messages after execution of a statement.

John

 

[PWise]

try adding this

   With cmd    
    .ActiveConnection = cnnASPIRE
    .CommandType = adCmdStoredProc
    .CommandText  "dbo.sp_CombinedEventActualCycleTime"         .Parameters("@RTEventID") = EventProfileID        
     Set rstTeamDates = .Execute    
End With       
[COLOR=red]
do while rstTeamDates.state =0
   set rstTeamDates=   rstTeamDates.nextrecordset

loop
[/color]
If rstTeamDates.RecordCount > 0 Then     'This is where I get the error when stepping through the code...

 

[MeSoCrazy]

Thanks All,

It was a connection string problem. The string we use seems to work fine with SELECTS, INSERTS, UPDATE and other statements.. but wasn't working for the Stored Procedure. Our connection had in the string "Data Source = <Server Name>" once I changed the string to be "Server = ,Server Name." it worked just fine.

Again, thanks!