Returned recordset is closed when executing stored procedure with Insert Into statement 
Author Message
 Returned recordset is closed when executing stored procedure with Insert Into statement

Hi all,

i have a problem when I use the Insert Into table statement in a stored
procedure, for example:

Stored Procedure:

    create table TempTable

    INSERT INTO TempTable
        Execute('StoredProc')

    Select * From TempTable Where Naam = 'Johnson'

    drop table TempTable

This will execute fine when I use the Query analyzer but when I use Visual
Basic the returned recordset is closed (as sone I use the INSERT INTO
statement).

Can anyone help me ?

Thanks !!

Greetings Koert



Tue, 29 Jun 2004 18:43:54 GMT  
 Returned recordset is closed when executing stored procedure with Insert Into statement
Hi

Quote:
>drop table TempTable

This will drop your table from database once it is executed by command
object/ Connection object. Then You will not get any Recordset from
this.
It is same like your executing some ddl or update or insert statements.

Harinatha Reddy Gorla
        Software Engineer
Smart Software Technologies,
Hyderabad, India

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 29 Jun 2004 19:18:42 GMT  
 Returned recordset is closed when executing stored procedure with Insert Into statement
Hi,

What you said seems to be working fine in VB.  Can I have some more
information ?

Regards,

Anand.


Quote:
> Hi all,

> i have a problem when I use the Insert Into table statement in a stored
> procedure, for example:

> Stored Procedure:

>     create table TempTable

>     INSERT INTO TempTable
>         Execute('StoredProc')

>     Select * From TempTable Where Naam = 'Johnson'

>     drop table TempTable

> This will execute fine when I use the Query analyzer but when I use Visual
> Basic the returned recordset is closed (as sone I use the INSERT INTO
> statement).

> Can anyone help me ?

> Thanks !!

> Greetings Koert



Tue, 29 Jun 2004 20:55:48 GMT  
 Returned recordset is closed when executing stored procedure with Insert Into statement
Here is the outcome of this thread..

Harinatha Reddy Gorla
        Software Engineer
Smart Software Technologies,
Hyderabad, India

Quote:
----- Original Message -----


Sent: Friday, January 11, 2002 6:39 PM
Subject: Re: Returned recordset is closed when executing stored

procedure with Insert Into statement

> Hi,

> I think you're right that the stored proc is returning two recordsets.
> NextRecordset is not working (provider doesn't support returning two
> recordsets). So probably i have to call the stored procedures twice.
One
> that selects the records and places them in a temptable, and one that
does
> the next operation on these records and deletes the temptable. It is
not the
> nicest solution but when it works it works...

> Thanks for the support !!!

> greetings Koert Heinen.

> ----- Original Message -----


> Sent: Friday, January 11, 2002 1:55 PM
> Subject: Re: Returned recordset is closed when executing stored
procedure
> with Insert Into statement

> > Please let me know when you are done..

> > ----- Original Message -----


> > Sent: Friday, January 11, 2002 6:08 PM
> > Subject: Re: Returned recordset is closed when executing stored
procedure
> > with Insert Into statement

> > > Here is the stored procedure Test2:

> > > CREATE Procedure test2
> > > /*Get all the employees*/

> > > As

> > > SELECT ALL Medewerker.Voornaam + ' ' + Medewerker.Tussenvoegsel +
' ' +
> > > Medewerker.Achternaam as Naam, Medewerker.MedewerkerID  as n FROM
> > Medewerker
> > > WHERE
> > > EXISTS(SELECT * FROM Ervaring WHERE Ervaring.MedewerkerID =
> > > Medewerker.MedewerkerID AND Ervaring.CompetentieID <> '')
> > > AND Medewerker.Tussenvoegsel is not null

> > > So this is a simple stored procedure that selects some employees
> > > (=Medewerker in Dutch). You get for example the following records:

> > > Name:        ID:
> > > John           13
> > > Ted             2
> > > Phil             34

> > > I already tried it without closing the recordset but when I debug
I see
> > that
> > > oRs is already closed after the Set oRs = ... statement.

> > > With kind regards Koert

> > > ----- Original Message -----


> > > Sent: Friday, January 11, 2002 1:21 PM
> > > Subject: Re: Returned recordset is closed when executing stored
> procedure
> > > with Insert Into statement

> > > > HI
> > > > >Set oRs = oCmd.Execute
> > > >   >oRs.Close
> > > > I think there is no need to close recordset here..

> > > > >     INSERT INTO
> > > > >     TempTable
> > > > >         EXECute('test2')
> > > > What is there in Test2 procedure.??

> > > > ----- Original Message -----


> > > > Sent: Friday, January 11, 2002 5:44 PM
> > > > Subject: Re: Returned recordset is closed when executing stored
> > procedure
> > > > with Insert Into statement

> > > > > Hi,

> > > > > here is the simple Visual Basic program and the Stored
procedure
> that
> > I
> > > > use.
> > > > > The stored procedure runs well in the Query Analyzer. The
stored
> > > procedure
> > > > > is also executed (the records are inserted into the table) in
the VB
> > > > program
> > > > > but the returned recordset is closed. The VB program runs
without
> > errors
> > > > > when I use another stored procedure without the INSERT INTO
> statement.

> > > > > * Stored procedure:

> > > > >     CREATE PROCEDURE test1

> > > > >     AS

> > > > >     INSERT INTO
> > > > >     TempTable
> > > > >         EXECute('test2')

> > > > >     SELECT * from TempTable WHERE  ID = 31

> > > > >     GO

> > > > > * The TempTable looks like this:

> > > > >     TempTable (Naam varchar(30), ID integer)

> > > > > * Sample of Visual Basic program:

> > > > >   Set oCmd.ActiveConnection = oConn
> > > > >   Set oRs = New ADODB.Recordset

> > > > >   oCmd.CommandText = "test1"
> > > > >   oCmd.CommandType = adCmdStoredProc

> > > > >   Set oRs = oCmd.Execute
> > > > >   oRs.Close

> > > > >   do while not oRs.EOF
> > > > >    'Implementation
> > > > >   loop

> > > > > The following error I receive from VB by checkking oRs.EOF:

> > > > >     Operation not allowed when the object is closed

> > > > > I hope this is enough information for you,

> > > > > thanks,

> > > > > with kind regards,

> > > > > Koert Heinen

> > > > > ----- Original Message -----


> > > > > Sent: Friday, January 11, 2002 12:47 PM
> > > > > Subject: Re: Returned recordset is closed when executing
stored
> > > procedure
> > > > > with Insert Into statement

> > > > > > Hi
> > > > > > Please send your Stored procure ..
> > > > > > I think I need to have a look on that.

> > > > > > ----- Original Message -----


> > > > > > Sent: Friday, January 11, 2002 5:07 PM
> > > > > > Subject: Re: Returned recordset is closed when executing
stored
> > > > procedure
> > > > > > with Insert Into statement

> > > > > > > Thanks for the fast response,

> > > > > > > I understand that but when I leave the drop statement, the
> > recordset
> > > > is
> > > > > > > still closed. It seems that the INSERT statement closes
the
> > > recordset.
> > > > > Do
> > > > > > > you know how this is possible ?

> > > > > > > greetings

> > > > > > > ----- Original Message -----
> > > > > > > From: Harinatha Reddy Gorla

> > > > > > > Newsgroups: microsoft.public.vb.database.ado
> > > > > > > Sent: Friday, January 11, 2002 12:18 PM
> > > > > > > Subject: Re: Returned recordset is closed when executing
stored
> > > > > procedure
> > > > > > > with Insert Into statement

> > > > > > > > Hi

> > > > > > > > >drop table TempTable
> > > > > > > > This will drop your table from database once it is
executed by
> > > > command
> > > > > > > > object/ Connection object. Then You will not get any
Recordset
> > > from
> > > > > > > > this.
> > > > > > > > It is same like your executing some ddl or update or
insert
> > > > > statements.

> > > > > > > > Harinatha Reddy Gorla
> > > > > > > > Software Engineer
> > > > > > > > Smart Software Technologies,
> > > > > > > > Hyderabad, India

> > > > > > > > *** Sent via Developersdex http://www.developersdex.com
***
> > > > > > > > Don't just participate in USENET...get rewarded for it!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 29 Jun 2004 21:14:34 GMT  
 Returned recordset is closed when executing stored procedure with Insert Into statement
Hi
If your provider doesn't support NextRecordset, Modify your stored
procedure.
use
SET NOCOUNT ON

Harinatha Reddy Gorla
        Software Engineer
Smart Software Technologies,
Hyderabad, India

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 29 Jun 2004 21:34:37 GMT  
 Returned recordset is closed when executing stored procedure with Insert Into statement
I have the solution:

The stored proc returns two recordsets. With NextRecordset I can enter the
recordsets.

regards Koert.


Quote:
> Hi,

> What you said seems to be working fine in VB.  Can I have some more
> information ?

> Regards,

> Anand.



> > Hi all,

> > i have a problem when I use the Insert Into table statement in a stored
> > procedure, for example:

> > Stored Procedure:

> >     create table TempTable

> >     INSERT INTO TempTable
> >         Execute('StoredProc')

> >     Select * From TempTable Where Naam = 'Johnson'

> >     drop table TempTable

> > This will execute fine when I use the Query analyzer but when I use
Visual
> > Basic the returned recordset is closed (as sone I use the INSERT INTO
> > statement).

> > Can anyone help me ?

> > Thanks !!

> > Greetings Koert



Tue, 29 Jun 2004 21:40:54 GMT  
 Returned recordset is closed when executing stored procedure with Insert Into statement
Hi,

If you are using OLEDB provider, then it interprets each
SQL statement as a result. So you will get multiple
resultsets. You can use NextRecordset to reach you real
result or you can use SET NOCOUNT ON at the beginning of
SP and SET NOCOUNT OFF at the very ent.
SET NOCOUNT ON will eliminate all resultsets different
from SELECT

Val

Quote:
>-----Original Message-----
>I have the solution:

>The stored proc returns two recordsets. With

NextRecordset I can enter the
Quote:
>recordsets.

>regards Koert.



>> Hi,

>> What you said seems to be working fine in VB.  Can I
have some more
>> information ?

>> Regards,

>> Anand.



>> > Hi all,

>> > i have a problem when I use the Insert Into table

statement in a stored

- Show quoted text -

Quote:
>> > procedure, for example:

>> > Stored Procedure:

>> >     create table TempTable

>> >     INSERT INTO TempTable
>> >         Execute('StoredProc')

>> >     Select * From TempTable Where Naam = 'Johnson'

>> >     drop table TempTable

>> > This will execute fine when I use the Query analyzer
but when I use
>Visual
>> > Basic the returned recordset is closed (as sone I use
the INSERT INTO
>> > statement).

>> > Can anyone help me ?

>> > Thanks !!

>> > Greetings Koert

>.



Wed, 30 Jun 2004 00:21:22 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Returning keyset/dynamic recordsets when executing parameterized queries against Stored Procedures

2. Returning keyset/dynamic recordsets when executing parameterized queries against Stored Procedures

3. Returning keyset/dynamic recordsets when executing parameterized queries against Stored Procedures

4. Closed Recordset returned from Stored procedure

5. VB6/ADO2.6: Executing stored procedure returns immediately when using cursors

6. executing SELECT statement with return value from other stored procedure

7. Stored Procedure - Insert & Return Unique ID

8. Editing Recordset Error / Executing Stored Procedures

9. ado recordsets from stored procedures, stored procedures have input parameters

10. Return recordset from Oracle Stored Procedure to VB.

11. Sql server stored procedures not returning a recordset...?

12. Recordsets not returned from stored procedures

 

 
Powered by phpBB® Forum Software