473,545 Members | 1,974 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Running an SQL stored procedure with ADO

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
Jul 19 '05 #1
4 13106
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

Jul 19 '05 #2
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

Jul 19 '05 #3
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
%>

Jul 19 '05 #4
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


Jul 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
12952
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 dropped (object closed) the db keeps showing the process "running", in a sleeping mode, with open_tran in 2 or even 3 and in an awaiting command...
2
5441
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
5
6498
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
12
10373
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 @salesdate as varchar(20),
0
1149
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 2005 using VS .NET 2005 running on WinXP Pro (SP2) -- I'm still having no luck. Remote debugging works fine. Stepping into a procedure through the...
7
3242
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' cannot be created. Apartment threaded components can only be created on pages with an <%@ Page aspcompat=true %> page directive. Can anyone tell me...
0
1623
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 @fname varchar(30) declare @fsizes nvarchar BEGIN
4
3969
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 but when he runs the stored procedure, he gets the following error message. "SYSPROC".CSGCSB54 - Run started. Data returned in result sets is...
0
1214
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 procedure is executed by sql server 2005 then it creates 10 threads and each thread builds a giant array on the stack, is the limits of each array limited...
1
7439
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7773
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5987
agi2029
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5343
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4962
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3468
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3450
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1901
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1028
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.