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 (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 4 13096
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" <ok********@hotmail.com> wrote in message
news:3a**************************@posting.google.c 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 (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
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********@hotmail.com> wrote in message
news:3a**************************@posting.google.c 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 (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
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" <ok********@hotmail.com> wrote in message
news:3a*************************@posting.google.co 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 %>
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***@TRASHaspfaq.com> wrote in message news:<eF**************@TK2MSFTNGP11.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********@hotmail.com> wrote in message news:3a**************************@posting.google.c 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 (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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rittercorp |
last post by:
I am debugging an app which blocks many processes in a SQL7 server DB.
The app log writes every transaction "open" and "close".
The weird thing is : when the app logfile says the transaction is...
|
by: Dino L. |
last post by:
How can I run stored procedure (MSSQL) ?
|
by: MS |
last post by:
Here's my simple stored procedure:
ALTER PROCEDURE GetMemberIDByEmail
@Email EmailAddress,
@ID int OUTPUT
AS
SELECT @ID = ID FROM tbl_Member WHERE Email=@Email
RETURN
|
by: Bill Nguyen |
last post by:
What's the VB syntax to run the CR report using the following SP?
I use CrystalreportViewer and ReportDocument.
Thanks
Bill
Here's the SP in SQLserver 2K:
CREATE proc mysp_ReportSubmission...
|
by: stand__sure |
last post by:
Stepping into a stored procedure used to be fairly straight-forward, but
after following the guidance in all 6 or so of the MSDN pages about enabling
debugging of stored procedures in SQL Server...
|
by: Jerry |
last post by:
I'm trying to execute a stored procedure in a loop while paging through
database table records but the stored procedure isn't running. I get
the folowing error: The component 'adodb.connection'...
|
by: eRTIS SQL |
last post by:
hi,
I want to use a stored procedure inside a stored procedure simulteanously changing the database.
this is my base store procedure
alter PROCEDURE create_file @dbname sysname
AS
declare...
|
by: nishi57 |
last post by:
I hope I can get some help regarding this issue, which has been going
on for a while. I have a desktop user who is having problem running
"Stored Procedures". The DB2 Connect application works fine...
|
by: DR |
last post by:
what are the memory caps for threads running as a CLR stored procedure
executed by sql server 2005? is it limited by OS only or also by sql servers
memory limits? e.g. lets say my clr stored...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |