Running an SQL stored procedure with ADO

[Jeremy]

Hi, I am having a problem running an sql stored procedure with
ADO/ASP. If I hard code a select statement, the code works, but when
I try to use a stored procedure it bombs. Here is my code:

THIS WORKS:

sql="SELECT PartNumber FROM Scrap WHERE DateOpened BETWEEN '20030601',
'20030605'"

'Note: I already did my connection object above
set rs=Server.Creat eObject("ADODB. Recordset")
rs.Open sql,conn

THIS DOES NOT:

'my stored procedure is LC_Top50_ByCost
sql="LC_Top50_B yCost 'DB', '20030601', '20030605'"

set rs=Server.Creat eObject("ADODB. Recordset")
rs.Open sql,conn

HERE IS THE ERROR:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/MyWeb/T10_Cost.asp, line 202

Line 202 is the first line that I try to access the data in the
recordset.

Any help would be wonderful, thank you

-Jeremy

[Ray at]

Have you created and opened "conn" and not closed it prior to executing that
code?

BTW, you may want to use:

Set rs = conn.Execute(sq l)

and drop the CreateObject("a dodb.recordset" ) method.

Ray at work

"Jeremy" <ok********@hot mail.com> wrote in message
news:3a******** *************** ***@posting.goo gle.com...

Hi, I am having a problem running an sql stored procedure with
ADO/ASP. If I hard code a select statement, the code works, but when
I try to use a stored procedure it bombs. Here is my code:

THIS WORKS:

sql="SELECT PartNumber FROM Scrap WHERE DateOpened BETWEEN '20030601',
'20030605'"

'Note: I already did my connection object above
set rs=Server.Creat eObject("ADODB. Recordset")
rs.Open sql,conn

THIS DOES NOT:

'my stored procedure is LC_Top50_ByCost
sql="LC_Top50_B yCost 'DB', '20030601', '20030605'"

set rs=Server.Creat eObject("ADODB. Recordset")
rs.Open sql,conn

HERE IS THE ERROR:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/MyWeb/T10_Cost.asp, line 202

Line 202 is the first line that I try to access the data in the
recordset.

Any help would be wonderful, thank you

-Jeremy

[Aaron Bertrand - MVP]

Can you show your stored procedure code? Sounds like it would be remedied
if you added SET NOCOUNT ON to the beginning of the proc code, but to be
sure, please post it...
"Jeremy" <ok********@hot mail.com> wrote in message
news:3a******** *************** ***@posting.goo gle.com...

Hi, I am having a problem running an sql stored procedure with
ADO/ASP. If I hard code a select statement, the code works, but when
I try to use a stored procedure it bombs. Here is my code:

THIS WORKS:

sql="SELECT PartNumber FROM Scrap WHERE DateOpened BETWEEN '20030601',
'20030605'"

'Note: I already did my connection object above
set rs=Server.Creat eObject("ADODB. Recordset")
rs.Open sql,conn

THIS DOES NOT:

'my stored procedure is LC_Top50_ByCost
sql="LC_Top50_B yCost 'DB', '20030601', '20030605'"

set rs=Server.Creat eObject("ADODB. Recordset")
rs.Open sql,conn

HERE IS THE ERROR:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/MyWeb/T10_Cost.asp, line 202

Line 202 is the first line that I try to access the data in the
recordset.

Any help would be wonderful, thank you

-Jeremy

[Aaron Bertrand - MVP]

Once again, what does the STORED PROCEDURE code look like??? Maybe you
forgot to include that, or don't believe it's relevant?

And why do you do this:

with cmd
.CommandType=ad CmdText
And then:
with cmd
.CommandType = adCmdStoredProc
Which .commandType did you *really* mean?
rs.MoveFirst
Why are you doing a MoveFirst? Where else do you think the recordset should
start at, the last row? Thought about testing for .EOF before executing any
commands? If .EOF returns true, then you can work backward... because you
know why you got the error...

<%
group_charged=" DB"
begin_date="200 30101"
end_date="20030 501"

set conn=Server.Cre ateObject("ADOD B.Connection")

conn.open "Provider=SQLOL EDB.1;" & _
"Server=myserve r;Database=SQMS ;" & _
"UID=generic;PW D=generic"

sql = "EXEC Top50Cost " & _
"@LineCode = '" & group_charged & "'," & _
"@StartDate = '" & begin_date & "'," & _
"@EndDate = '" & end_date & "'"

set rs = conn.execute(sq l)

if rs.eof then
response.write "Run this in QA. If it returns records, "
response.write " SHOW US THE STORED PROCEDURE."
else
response.write rs(0)
end if

rs.close: set rs = nothing
conn.close: set conn = nothing
%>


"Jeremy" <ok********@hot mail.com> wrote in message
news:3a******** *************** **@posting.goog le.com... Hi Guys, I took out all of my code and JUST left the ado stuff, and it
still gives the same error (Operation is not allowed when the object
is closed) on the rs.MoveFirst method. I changed the code up a bit as
well. Below is the complete ado code. I put in a bogus username and
password as well as server address because I didn't want to post the
real ones here. The parameters I have hard coded into variables and I
know that it should return about 45 rows and does not. Any Help?
thanks!

HERE'S THE CODE:
----------------
<%
group_charged=" DB"
begin_date="200 30101"
end_date="20030 501"
%>
<%
set conn=Server.Cre ateObject("ADOD B.Connection")
set cmd=Server.Crea teObject("ADODB .Command")
set rs=Server.Creat eObject("ADODB. Recordset")

with conn
.CursorLocation = 3
.ConnectionStri ng = "Provider=SQLOL EDB.1;" & _
"Persist Security Info=True;" & _
"Initial Catalog=SQMS;" & _
"Password=gener ic;" & _
"User ID=generic;" & _
"Data Source=myserver ;"
.open
end with

with cmd
.CommandType=ad CmdText
.ActiveConnecti on=conn
end with

with cmd
.CommandType = adCmdStoredProc
.CommandTimeout = 180
.CommandText = "Top50Cost"
.Parameters.App end .CreateParamete r("LineCode", adChar, adParamInput,
2, group_charged)
.Parameters.App end .CreateParamete r("StartDate" , adChar,
adParamInput, 8, begin_date)
.Parameters.App end .CreateParamete r("EndDate", adChar, adParamInput,
8, end_date)

set rs = .Execute

End With

rs.MoveFirst
%>

[Bob Barrows]

All stored procedures should have that line in them, except those where you
WANT to have them waste time and resources sending that message to the
client, that is. Take the time to correct your stored procedures. It will be
worth it.

The only alternative is to use the time-and-resources-consuming
NextRecordset method to get to the recordset containing the data you
actually wanted to receive from your procedure.

Bob Barrows

Jeremy wrote:

Aaron, you are a GENIUS! the set nocount worked, HOWEVER. . .I don't
want to have to go and edit all of my stored procedures to accomplish
this. Is there a way to "SET NOCOUNT ON" in asp with the setup that I
have going here? THANK YOU!

-Jeremy
"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message
news:<eF******* *******@TK2MSFT NGP11.phx.gbl>. ..

Can you show your stored procedure code? Sounds like it would be
remedied
if you added SET NOCOUNT ON to the beginning of the proc code, but
to be
sure, please post it...
"Jeremy" <ok********@hot mail.com> wrote in message
news:3a******** *************** ***@posting.goo gle.com...

Hi, I am having a problem running an sql stored procedure with
ADO/ASP. If I hard code a select statement, the code works, but
when
I try to use a stored procedure it bombs. Here is my code:

THIS WORKS:

sql="SELECT PartNumber FROM Scrap WHERE DateOpened BETWEEN
'20030601', '20030605'"

'Note: I already did my connection object above
set rs=Server.Creat eObject("ADODB. Recordset")
rs.Open sql,conn

THIS DOES NOT:

'my stored procedure is LC_Top50_ByCost
sql="LC_Top50_B yCost 'DB', '20030601', '20030605'"

set rs=Server.Creat eObject("ADODB. Recordset")
rs.Open sql,conn

HERE IS THE ERROR:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/MyWeb/T10_Cost.asp, line 202

Line 202 is the first line that I try to access the data in the
recordset.

Any help would be wonderful, thank you

-Jeremy