Returning records from SP 
Author Message
 Returning records from SP

I have a problem returning a recordset from a Sp to my ASP page.

The Sp uses cursors and temporary tables. Ive set the dboption SELECT
INTO/BULK COPY to true and I use SET NOCOUNT ON in my Sp.

There is only one SELECT statement at the end of the SP so only one
resultset should be returned to my ado cmd object.

When I run the procedure in the Query analyzer there is no problem, but when
I use it in VB6 or via ASP the following error occurs:

ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

I dont close the object - perhaps ADO does that automatically ?

I use the command object in my code. The ASP code looks like this:

Dim cCon
Dim objCmd
Dim Rst
Dim sCon
Dim aRs

Set objCmd = Server.CreateObject("ADODB.Command")
Set Rst = Server.CreateObject("ADODB.Recordset")
Set cCon = Server.CreateObject("ADODB.Connection")

sCon="Provider=SQLOLEDB;User ID=sa;Initial Catalog=TRM2;Data Source=fx-srv5"

cCon.ConnectionString = sCon

cCon.CursorLocation = adUseClient

cCon.Open objCmd.ActiveConnection = cCon

objCmd.CommandTimeout = 1000
objCmd.CommandText = "sp_ReportGetEmpAcc"
objCmd.CommandType = adCmdStoredProc

adParamInput, ,162)

Set Rst = objCmd.Execute

aRs = Rst.GetRows

Response.Write ubound(aRs)

Set objCmd = Nothing

Ive been trying different approches but nothing seems to be working.....so
PLEASE help me

TIA

Kasper



Fri, 19 Dec 2003 19:22:07 GMT  
 Returning records from SP
Hello kb

I think this statement is not correct, because the connection is not opened
by this statement, hence the error

cCon.Open objCmd.ActiveConnection = cCon

It should be

cCon.Open sCon
objCmd.ActiveConnection = cCon

HTH
Sukesh


Quote:
> I have a problem returning a recordset from a Sp to my ASP page.

> The Sp uses cursors and temporary tables. Ive set the dboption SELECT
> INTO/BULK COPY to true and I use SET NOCOUNT ON in my Sp.

> There is only one SELECT statement at the end of the SP so only one
> resultset should be returned to my ado cmd object.

> When I run the procedure in the Query analyzer there is no problem, but
when
> I use it in VB6 or via ASP the following error occurs:

> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed.

> I dont close the object - perhaps ADO does that automatically ?

> I use the command object in my code. The ASP code looks like this:

> Dim cCon
> Dim objCmd
> Dim Rst
> Dim sCon
> Dim aRs

> Set objCmd = Server.CreateObject("ADODB.Command")
> Set Rst = Server.CreateObject("ADODB.Recordset")
> Set cCon = Server.CreateObject("ADODB.Connection")

> sCon="Provider=SQLOLEDB;User ID=sa;Initial Catalog=TRM2;Data
Source=fx-srv5"

> cCon.ConnectionString = sCon

> cCon.CursorLocation = adUseClient

> cCon.Open objCmd.ActiveConnection = cCon

> objCmd.CommandTimeout = 1000
> objCmd.CommandText = "sp_ReportGetEmpAcc"
> objCmd.CommandType = adCmdStoredProc

> adParamInput, ,162)

> Set Rst = objCmd.Execute

> aRs = Rst.GetRows

> Response.Write ubound(aRs)

> Set objCmd = Nothing

> Ive been trying different approches but nothing seems to be working.....so
> PLEASE help me

> TIA

> Kasper



Sat, 20 Dec 2003 00:49:40 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. ODBC Drive returns records using MDAC 2.0 but not MDAC 2.1 SP 2

2. pass parameter to SQL SP from VB and return recordset

3. SP w/ADO not returning recordset

4. Return RS from Oracle SP

5. Returning SP from SQL with ADO

6. Returning Values from SP with VB.NET

7. Testing Return Status of SQL SP using VB

8. Return status of SP

9. ASP Return Value From SP

10. Returning values from SP with VB.NET

11. Not returning rs from sp!

12. Return value from SP

 

 
Powered by phpBB® Forum Software