Sending output from Stored Procedure via ASP

[BradEdwards]

I created the following (simple) Stored Procedure. The real one is a lot more detailed but I created this simple one to make sure that it worked

CREATE PROCEDURE spTest AS

Create Table #Date
(
Branch int,
)
INSERT INTO #Data (Branch)
(SELECT Branch
FROM tblSalesInc)

SELECT Branch from #Data

Now executing this sp from the Query Analyzer generates a list of Branches as would be expected. My problem is when I try and execute using ASP and dump the fields out to the web page it gives me the following error:

ADODB.Fields error '800a0cc1'
ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application

Here is my asp code:

<%
Dim rs, objconn

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
objConn.Open strConnect

rs.open objConn.execute(&quot;spTest&quot
response.write rs(&quot;Branch&quot
%>

Now if I change the stored Procedure by taking out the CREATE TABLE and just use SELECT Branch from tblSalesInc it works fine, but as soon as I create a temporary table and try to select records from it that's when I get the previous error. Any ideas would be helpful. I'm going to have to create some pretty involved stored procedures using temporary tables so I hope that I'm going to be able to do that.

 

[IonelBurtan]

You cannot return data in ASP from temporary tables in ADO on SQL server. ADO does not see the # tables.(as far as I know)

Use a permanent table instead.

Hope this helps, s-)

Blessed is he who in the name of justice and goodwill, sheperds the weak through the valley of darkness...

http://www20.brinkster.com/ionelb