dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Changing an SP causes a Recordset object error.

  1. #1
    Keith Guest

    Changing an SP causes a Recordset object error.


    I have an SQL7 database and a VB6 app, linked via ADO 2.5 - one particular
    stored procedure builds a temp table then returns said table as a recordset.
    I have made changes to the sp (the main one being that it now uses two cursors
    to collate and append more data to the temp table).

    If I call the sp from Query Analyzer it works fine, but calling it from the
    VB app (which uses a command and recordset object) gives Error 3707:

    "cannot change the Active Connection properties of a RecordSet object which
    has a Command object as its source"

    Is short, what I am asking is how can changing the way a stored procedure
    works affect the active connection of a VB recordset object?

  2. #2
    Rune Bivrin Guest

    Re: Changing an SP causes a Recordset object error.


    My guess is ADO gets confused by the rowcounts being generated by the statements
    prior to the actual SELECT that returns data. Try SET NOCOUNT ON as the first
    statement of your SP.

    Rune Bivrin


    "Keith" <nospam@nospam.com> wrote:
    >
    >I have an SQL7 database and a VB6 app, linked via ADO 2.5 - one particular
    >stored procedure builds a temp table then returns said table as a recordset.
    >I have made changes to the sp (the main one being that it now uses two cursors
    >to collate and append more data to the temp table).
    >
    >If I call the sp from Query Analyzer it works fine, but calling it from

    the
    >VB app (which uses a command and recordset object) gives Error 3707:
    >
    >"cannot change the Active Connection properties of a RecordSet object which
    >has a Command object as its source"
    >
    >Is short, what I am asking is how can changing the way a stored procedure
    >works affect the active connection of a VB recordset object?



  3. #3
    Keith Guest

    Re: Changing an SP causes a Recordset object error.


    That hasn't worked unfortunately.

    I'm currently looking at rewriting it without cursors though so it may be
    ok.

    Thanks anyway!!

    "Rune Bivrin" <rune@bivrin.com> wrote:
    >
    >My guess is ADO gets confused by the rowcounts being generated by the statements
    >prior to the actual SELECT that returns data. Try SET NOCOUNT ON as the

    first
    >statement of your SP.
    >
    >Rune Bivrin
    >
    >
    >"Keith" <nospam@nospam.com> wrote:
    >>
    >>I have an SQL7 database and a VB6 app, linked via ADO 2.5 - one particular
    >>stored procedure builds a temp table then returns said table as a recordset.
    >>I have made changes to the sp (the main one being that it now uses two

    cursors
    >>to collate and append more data to the temp table).
    >>
    >>If I call the sp from Query Analyzer it works fine, but calling it from

    >the
    >>VB app (which uses a command and recordset object) gives Error 3707:
    >>
    >>"cannot change the Active Connection properties of a RecordSet object which
    >>has a Command object as its source"
    >>
    >>Is short, what I am asking is how can changing the way a stored procedure
    >>works affect the active connection of a VB recordset object?

    >



  4. #4
    Glenn Block Guest

    Re: Changing an SP causes a Recordset object error.

    This is probably happening because by adding the cursors, you have created
    more recordsets that are being returned to ADO. I encountered this before,
    it usually happens when if you are using disconnected recordsets. If you
    are, when you set the active connection to nothing, you will get this error.
    Try using .nextrecordset on the recordset object. You may have to call it
    twice, one for each cursor. Eventually you will find a recordset that is
    your recordset. You also need to check the recordset.state property to see
    if the recordset is open or not. Anyway, once you have the recordset that
    you want, then you can close the connection. The reason this happens, is
    because when you use a client side recordset, the stored proc automatically
    returns default recordsets for each recordset within the sp in order to
    facilitate better performance. You should not get this behavior if you use a
    server side recordset.

    Let me know how it works for you

    --Glenn


    "Keith" <nospam@nospam.com> wrote in message
    news:3e636408$1@tnews.web.devx.com...
    >
    > I have an SQL7 database and a VB6 app, linked via ADO 2.5 - one particular
    > stored procedure builds a temp table then returns said table as a

    recordset.
    > I have made changes to the sp (the main one being that it now uses two

    cursors
    > to collate and append more data to the temp table).
    >
    > If I call the sp from Query Analyzer it works fine, but calling it from

    the
    > VB app (which uses a command and recordset object) gives Error 3707:
    >
    > "cannot change the Active Connection properties of a RecordSet object

    which
    > has a Command object as its source"
    >
    > Is short, what I am asking is how can changing the way a stored procedure
    > works affect the active connection of a VB recordset object?




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center