Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simply calling a Stored Procedure!

Status
Not open for further replies.
dhshaikh

dhshaikh

Technical User
Joined
Jun 19, 2003
Messages
9
Location
GB
Hi,

I'm completely new to ASP. I am connecting to a SQL database where I have my stored procedure "Count_Items".

How do I run the Stored procedure and display the result on my ASP page?

My help files aren't properly installed and there is nothing that simple that i can find on the web.

Any help is greatly appreciated.

Danial
 
Sort by date Sort by votes
You need to write the stored procedure into your ASP script. Take the code from your procedure in the database and rewrite it into your ASP script and display the results in HTML format. You can access the data within a database and manipulate it, but you cannot execute the code stored within a database.
 
Upvote 0 Downvote
Yes, you can call a stored procedure from ASP (thank goodness, or many, many sites I've done wouldn't be possible).

There are three different ways to do it, as described in this Microsoft KnowledgeBase article:


I tend to formally declare the parameters, but that's because it's a very clean way of receiving output parameters back from the stored procedure.
 
Upvote 0 Downvote
Ok, my head is spinning!

My stored procedure is very simple... no parameters or anything!

I have used the code as specified in the kb article provided by Genimuse (thank you). Problem is, it doesn't work.

Heres the script:
Code: 
<html>
<body>
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider = SQLOLEDB;Data Source=mydbsource; Initial Catalog=mydb;User ID=uid;Password=pwd;Network Library=dbmssocn;"

set comm = Server.CreateObject("ADODB.Command")
set comm.ActiveConnection = conn
	comm.CommandText = "WeeklyCount"
	comm.CommandType = adCmdStoredProc
	comm.Execute
%>

ReturnValue = <% Response.Write comm %>

<%
conn.Close
set rs=Nothing
set conn=Nothing
%>

</body>
</html>

the error i get on the web page is:

Error Type:
ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/components/root/copyindex.asp, line 23

line 23 being the one where i say commandtype is adcmdstoredproc.

The stroed proc itself works a treat in sql... heres a copy of the stored proc:
Code: 
CREATE PROCEDURE WeeklyCount AS

SELECT count(tblItems.[ItemNumber])
FROM tblItems
WHERE tblItems.[Start_Date] Between getdate()-7 and getdate();
Like I said the stored procedure works a treat, my connections work from the web page because i can create sql statements in it and display the results, its only when I try to call the stored procedure i get the error!

Please help...

Regards,
Danial



 
Upvote 0 Downvote
Like xwb said, use 4 instead of adCmdStoredProc -- you have to include the adovbs.inc file if you want ASP to know what adCmdStoredProc stands for... in your case, since you're only using the one value, it's probably easier to avoid the include and just hardcode the value.
 
Upvote 0 Downvote
Heres a example

dim dbConn
set dbConn = server.createobject("adodb.connection")

I store the string of the database in my session global.asa but you can just type it in after the open in quotes
"Database etc..."

Don;t know the string do this...go to windows explorer
and right click create a new text file...go and rename it to udl.....then double click it ..it will bring up your ODBC...do the ODBC part close it...then open up in windows note pad...get the string ...pass below


dbConn.open Session("DATABASE")

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

'Open our recordset
Dim strSQL

'---use if just doing select statement and no procedure
strSQL = "SELECT * FROM ASSETMAINT "
--here just put in your stored procedure
strSQL = "sp_mystoredprocedure"
--got parameters do
strSQL = "sp_mystoredprocedure '" & myvalue & "'"


set rs = dbConn.Execute(strSQL)
IF not rs.EOF then
do while not rs.EOF
response.write rs("mycolumnvalue")
rs.movenext
loop
else
response.write "no records"
end if

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
725
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question Question
Wrong code in a href ?
Replies
1
Views
754
leifoet
leifoet
leifoet
  • Locked
  • Question Question
How to check if a datatable is empty ?
Replies
0
Views
805
leifoet
leifoet
penguinspeaks
  • Locked
  • Question Question
Start loop from a specified point
Replies
0
Views
646
penguinspeaks
penguinspeaks
DiDYRO_
  • Locked
  • Question Question
How do I make a VBS file that can collect dialog-type answers in a local .log file?
Replies
0
Views
640
DiDYRO_
DiDYRO_

Part and Inventory Search

Sponsor

Back
Top