Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Running an SQL stored procedure with ADO

Reply
Thread Tools

Running an SQL stored procedure with ADO

 
 
Jeremy
Guest
Posts: n/a
 
      07-30-2003
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.CreateObject("ADODB.Recordset")
rs.Open sql,conn

THIS DOES NOT:

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

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

HERE IS THE ERROR:

Error Type:
ADODB.Recordset (0x800A0E7
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
 
Reply With Quote
 
 
 
 
Ray at
Guest
Posts: n/a
 
      07-30-2003
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(sql)

and drop the CreateObject("adodb.recordset") method.

Ray at work

"Jeremy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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.CreateObject("ADODB.Recordset")
> rs.Open sql,conn
>
> THIS DOES NOT:
>
> 'my stored procedure is LC_Top50_ByCost
> sql="LC_Top50_ByCost 'DB', '20030601', '20030605'"
>
> set rs=Server.CreateObject("ADODB.Recordset")
> rs.Open sql,conn
>
> HERE IS THE ERROR:
>
> Error Type:
> ADODB.Recordset (0x800A0E7
> 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



 
Reply With Quote
 
 
 
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      07-30-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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.CreateObject("ADODB.Recordset")
> rs.Open sql,conn
>
> THIS DOES NOT:
>
> 'my stored procedure is LC_Top50_ByCost
> sql="LC_Top50_ByCost 'DB', '20030601', '20030605'"
>
> set rs=Server.CreateObject("ADODB.Recordset")
> rs.Open sql,conn
>
> HERE IS THE ERROR:
>
> Error Type:
> ADODB.Recordset (0x800A0E7
> 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



 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      07-31-2003
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=adCmdText


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="20030101"
end_date="20030501"

set conn=Server.CreateObject("ADODB.Connection")

conn.open "Provider=SQLOLEDB.1;" & _
"Server=myserver;Database=SQMS;" & _
"UID=generic;PWD=generic"

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

set rs = conn.execute(sql)

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) m...
> 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="20030101"
> end_date="20030501"
> %>
>
>
> <%
> set conn=Server.CreateObject("ADODB.Connection")
> set cmd=Server.CreateObject("ADODB.Command")
> set rs=Server.CreateObject("ADODB.Recordset")
>
> with conn
> .CursorLocation = 3
> .ConnectionString = "Provider=SQLOLEDB.1;" & _
> "Persist Security Info=True;" & _
> "Initial Catalog=SQMS;" & _
> "Password=generic;" & _
> "User ID=generic;" & _
> "Data Source=myserver;"
> .open
> end with
>
> with cmd
> .CommandType=adCmdText
> .ActiveConnection=conn
> end with
>
> with cmd
> .CommandType = adCmdStoredProc
> .CommandTimeout = 180
> .CommandText = "Top50Cost"
> .Parameters.Append .CreateParameter("LineCode", adChar, adParamInput,
> 2, group_charged)
> .Parameters.Append .CreateParameter("StartDate", adChar,
> adParamInput, 8, begin_date)
> .Parameters.Append .CreateParameter("EndDate", adChar, adParamInput,
> 8, end_date)
>
> set rs = .Execute
>
> End With
>
> rs.MoveFirst
> %>



 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      07-31-2003
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" <(E-Mail Removed)> wrote in message
> news:<eF$(E-Mail Removed)>...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed) om...
>>> 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.CreateObject("ADODB.Recordset")
>>> rs.Open sql,conn
>>>
>>> THIS DOES NOT:
>>>
>>> 'my stored procedure is LC_Top50_ByCost
>>> sql="LC_Top50_ByCost 'DB', '20030601', '20030605'"
>>>
>>> set rs=Server.CreateObject("ADODB.Recordset")
>>> rs.Open sql,conn
>>>
>>> HERE IS THE ERROR:
>>>
>>> Error Type:
>>> ADODB.Recordset (0x800A0E7
>>> 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




 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
calling stored procedure in ADO.NET 2.0 David Sagenaut ASP .Net 1 10-24-2005 02:19 PM
Cannot get OUTPUT parameter after running Sql stored procedure MS ASP .Net 5 01-31-2005 08:09 AM
A long running Stored procedure is not timing out. Why?? =?Utf-8?B?SW5kaXJh?= ASP .Net 0 01-10-2005 04:07 AM
Win32 Extension ADO with SQL Server Problem to insert money datatype with a stored procedure Quinet, Joel Python 0 08-08-2003 08:13 AM



Advertisments