Calling SQL Stored Procedure from ASP

4 Nov 2005 8:29 PM

Tincho

Hi friends, i want to call a SP from ASP (not dot net) that returns some many
rows.

The conn string is:

<%
    Set objCon = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objcon.connectionstring = "Provider=SQLOLEDB.1;User
ID=USER;Password=XXXX;Persist Security Info=True;Initial Catalog=opessa;Data
Source=SERVER01"
    iCursorType                = 1
    iLockType                = 3
    objCon.Open
    %>

Then i use the following sentence to call the SP from ASP

SQLtxt = "exec SPNAME '" & PARAM1 & "','" & PARAM2 & "','" & PARAM3 & "'"
objRS.open SQLTXT, objcon

And last, i try to print the results:

WHILE NOT ObjRS.EOF

           Response.Write "" & ObjRS("XXXXX").VALUE & "" &
ObjRS("XXXXX").VALUE & "" & ObjRS("XXXXX").VALUE & " Mb" & "" &
ObjRS("XXXXX").VALUE & " Mb" & "" & ObjRS("XXXXX").VALUE & " %" & ""

ObjRS.Movenext

WEND

ObjRS.CLOSE

But i receive the following error:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

Whats wrong??? Anyone can help me?

Thanks in advance.

4 Nov 2005 8:33 PM

Bob Barrows [MVP]

Tincho wrote:

> Hi friends, i want to call a SP from ASP (not dot net) that returns
> some many rows.

Here is my canned reply on this topic:
http://groups.google.com/groups?hl=en&lr=&c2coff=1&selm=OVlfw%235sDHA.1060%40TK2MSFTNGP12.phx.gbl

Bob barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

4 Nov 2005 8:41 PM

Tincho

I found the solution:
I forgot the command:
set nocount inside the stored_procedure.

Best regards!

Show quote

"Bob Barrows [MVP]" wrote:

> Tincho wrote:
> > Hi friends, i want to call a SP from ASP (not dot net) that returns
> > some many rows.
>
> Here is my canned reply on this topic:
> http://groups.google.com/groups?hl=en&lr=&c2coff=1&selm=OVlfw%235sDHA.1060%40TK2MSFTNGP12.phx.gbl
>
> Bob barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

4 Nov 2005 8:45 PM

Aaron Bertrand [SQL Server MVP]

Make sure the stored procedure has SET NOCOUNT ON.

Show quote

"Tincho" <Tin***@discussions.microsoft.com> wrote in message
news:D15145D5-A0CF-4BA3-B211-6532936B1E92@microsoft.com...
> Hi friends, i want to call a SP from ASP (not dot net) that returns some
> many
> rows.
>
> The conn string is:
>
> <%
>    Set objCon = CreateObject("ADODB.Connection")
>    Set objRS = CreateObject("ADODB.Recordset")
>    objcon.connectionstring = "Provider=SQLOLEDB.1;User
> ID=USER;Password=XXXX;Persist Security Info=True;Initial
> Catalog=opessa;Data
> Source=SERVER01"
>    iCursorType                = 1
>    iLockType                = 3
>    objCon.Open
>    %>
>
> Then i use the following sentence to call the SP from ASP
>
> SQLtxt = "exec SPNAME '" & PARAM1 & "','" & PARAM2 & "','" & PARAM3 & "'"
> objRS.open SQLTXT, objcon
>
> And last, i try to print the results:
>
> WHILE NOT ObjRS.EOF
>
>           Response.Write "" & ObjRS("XXXXX").VALUE & "" &
> ObjRS("XXXXX").VALUE & "" & ObjRS("XXXXX").VALUE & " Mb" & "" &
> ObjRS("XXXXX").VALUE & " Mb" & "" & ObjRS("XXXXX").VALUE & " %" & ""
>
> ObjRS.Movenext
>
> WEND
>
> ObjRS.CLOSE
>
> But i receive the following error:
>
> ADODB.Recordset error '800a0e78'
>
> Operation is not allowed when the object is closed.
>
> Whats wrong??? Anyone can help me?
>
> Thanks in advance.
>
>
>
>