SQL Server SP error with Disconnected Recordset

[Axoliien]

I have been using a piece of code for a very long time now and have not had a problem with it, but now for some reason I am getting an error that says I cannot set the activeconnection of a recordset with a command as its source. I have been doing this for months with no problem, and still can today until I change between stored procedures! What on earth is the problem?

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command

With cnn
    .ConnectionString = "myConnectionStringHere"
    .Open
End With

With cmd
    Set .ActiveConnection = cnn
    .CommandType = adCmdStoredProc
    .CommandText = "upGEMP_AgencyList"
    .Parameters.Append .CreateParameter("ItemID", adInteger, adparamInput, 4, ItemID)
End With

With rst
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    Set .Source = cmd
    .Open
    Set .ActiveConnection = Nothing 'Errors here
End With

If Not cnn Is Nothing Then
    If cnn.State <> adStateClosed Then cnn.Close
    Set cnn = Nothing
End If

If Not cmd Is Nothing Then Set cmd = Nothing

If Not rst Is Nothing Then
    If rst.State <> adStateClosed Then rst.Close
    Set rst = Nothing
End If

I have been using this code with the following parameterized query and it works fine.

CREATE PROCEDURE [dbo].[upGEMP_EditMonetaryCatsRS]
    @ItemID INT
AS
    SET NOCOUNT ON
    SELECT
        M.CategoryID,
        M.Descr,
        M.IsIncomeCategory,
        M.IsAssistanceCategory,
        M.Inactive, Stamped
    FROM dbo.GEMP_MonetaryCats M
    WHERE (M.CategoryID = @ItemID)
GO

If I use it with the next parameterized query, I get the error. I have tested it in Query Analyzer and receive recordsets back for my variations.

CREATE PROCEDURE [dbo].[upGEMP_MonetaryCatList]
    @MonetaryType CHAR(1) = 'A',
    @ItemID INTEGER = NULL
AS
    SET NOCOUNT ON
    IF NOT (@ItemID IS NULL)
    BEGIN
        SELECT
            M.CategoryID,
            M.Descr,
            M.IsIncomeCategory,
            M.IsAssistanceCategory,
            M.Inactive, Stamped
        FROM dbo.GEMP_MonetaryCats M
        WHERE (M.CategoryID = @ItemID)
    END
    ELSE IF @MonetaryType = 'I'
    BEGIN
        SELECT
            M.CategoryID AS MnthlyIncID,
            M.Descr AS IncomeDescr,
            CONVERT(BIT,'0') AS Assigned
        FROM GEMP_MonetaryCats M
        WHERE
        (
            M.Inactive = 0 AND
            M.IsIncomeCategory = 1
        )
    ORDER BY M.IncomeDescr ASC
    END
    ELSE IF @MonetaryType = 'S'
    BEGIN
        SELECT
            M.CategoryID AS AsstID,
            M.Descr AS AsstDescr,
            CONVERT(BIT,'0') AS Assigned
        FROM GEMP_MonetaryCats M
       WHERE
        (
            M.Inactive = 0 AND
            M.IsAssistanceCategory = 1
        )
        ORDER BY AsstDescr
    END
    ELSE IF @MonetaryType = 'A'
    BEGIN
        SELECT
            M.CategoryID,
            M.Descr,
            CAST((CASE WHEN IsIncomeCategory=1 THEN 'Yes' ELSE 'No' END) AS VARCHAR(3)) AS Income,
            CAST((CASE WHEN IsAssistanceCategory=1 THEN 'Yes' ELSE 'No' END) AS VARCHAR(3)) AS Asst,
            CAST((CASE WHEN Inactive=0 THEN 'Yes' ELSE 'No' END) AS VARCHAR(3)) AS Active
        FROM  dbo.GEMP_MonetaryCats M
        ORDER BY M.Descr
    END
GO

Any idea why?

 

[sodakotahusker]

try removing the set from in front of assigning your active connection?

 

[Axoliien]

Unfortunately, the ActiveConnection requires an object reference, which uses the set command.

 

[PHV]

Have you tried the 2nd parametized query with correct number of parameters of correct type ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Axoliien]

Yes, I have tried it with no parameters, with 1 parameter of each, and with 2 parameters of the correct types. I even have tried type casting to ensure correct types whenever passing to the parameter instantiations. It's still a no-go, though I cannot find any reason why. When I take the SP apart, each part works, but when I put it back together it just won't run.

 

[cmmrfrds]

This code shows 1 parameter being set. Please show the code with both parameters being set.

With cmd
Set .ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "upGEMP_AgencyList"
.Parameters.Append .CreateParameter("ItemID", adInteger, adparamInput, 4, ItemID)

Show how you are setting the 1st parameter since the list is positional.