errors executing stored procedures through ADO??


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: errors executing stored procedures through ADO??

  1. #1
    dev X Guest

    errors executing stored procedures through ADO??


    I am executing a stored procedure on a connection object in order to pass
    back a adodb.recordset object. This works fine for simple select statements
    but when a stored procedure does an insert and then a select the stored procedure
    exits after the insert statement. I can check this as the insert affects
    21 records and the select only retrieves 2 records. I used the connection.execute(
    "exec sp_XX", iRows) to retrieve the rows affected, which was 21 and not
    2. The recordset set returned is a closed recordset which would be consistant
    with an insert statement but not a select. However, the stored procedure
    works as it should if I call the stored procedure from query analyser..??
    any clues what is wrong?? ( I am using vb6 -> sp3, sql server 7 -> sp1 and
    ADO 2.1 )

  2. #2
    Daniel Reber Guest

    Re: errors executing stored procedures through ADO??


    "dev X" <stephen.hawkins@northern-electric.co.uk> wrote:
    >
    >I am executing a stored procedure on a connection object in order to pass
    >back a adodb.recordset object. This works fine for simple select statements
    >but when a stored procedure does an insert and then a select the stored

    procedure
    >exits after the insert statement. I can check this as the insert affects
    >21 records and the select only retrieves 2 records. I used the connection.execute(
    >"exec sp_XX", iRows) to retrieve the rows affected, which was 21 and not
    >2. The recordset set returned is a closed recordset which would be consistant
    >with an insert statement but not a select. However, the stored procedure
    >works as it should if I call the stored procedure from query analyser..??
    >any clues what is wrong?? ( I am using vb6 -> sp3, sql server 7 -> sp1 and
    >ADO 2.1 )


    If you are doing the INSERT and SELECT in the same sp, then insert 'SET NOCOUNT
    ON' before you do the insert then 'SET NOCOUNT OFF' before you do the select
    statement. Hope this helps

    Daniel Reber, MCP


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