Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Question Unanswered: Error accessing stored procedure containing a temp table from VB

    I am getting an error trying to generate a disconnected recordset from a SQL 2000 database. The application is in VB 6. I am using an ADO Command object.

    This code works fine for most of my storer procedures. 2 procedures make use of temporary tables. The results set being passed top VB is a select from the temp table.

    When I use the temp table I get an error that states:
    "Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."

    If I check the recordcount of rstResults, in the immediate window, after the .open method I get an error stating that the object is not open.

    Again this is only for the stored procedure that Selects from a temp table.

    Here is the code:

    On Error GoTo ErrHandler

    Dim rstResults As ADODB.Recordset
    Dim conSQL As ADODB.Connection
    Dim comFetch As ADODB.Command

    Set conSQL = New Connection
    conSQL.Open gstrAdminConn

    '-- Associate the Command object to stored procedure --
    Set comFetch = New ADODB.Command

    With comFetch
    Set .ActiveConnection = conSQL
    .CommandType = adCmdStoredProc
    .CommandText = "FetchChildren"
    .Parameters("@PageCode") = strPageCode
    .Execute
    End With

    Set rstResults = New ADODB.Recordset
    With rstResults

    '-- Open a disconnected recordset off of the command --
    .CursorLocation = adUseClient
    .Open comFetch, , adOpenKeyset, adLockBatchOptimistic

    '-- Dissociate command from connection --
    Set comFetch.ActiveConnection = Nothing
    Set comFetch = Nothing

    '-- Dissociate recordset from connection --
    'Set .ActiveConnection = Nothing

    End With

    Set GetChildren = rstResults

    Exit Function

    ErrHandler:
    err.Raise vbObjectError + 1001, "basPages.GetChildren", Error$

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    try using a

    "set nocount on" at the begining of your procedure.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Dec 2003
    Posts
    7

    Question It Worked but Why

    Thanks. The SET NOCOUNT ON worked.

    Why was that causing the error?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •