By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,918 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,918 IT Pros & Developers. It's quick & easy.

passing SQL data to ASP

P: n/a
Hi,

I have written some stored procedures in SQL and these procedures
return some value.
I want these values to be captured by the ASP code.

I am able to access the tables using ADO( recordsets ) but is there a
way to pass data returned from stored procedures to vairables in ASP
code.

tia
Hursh
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Here is a sample against NWind using stored procedures

'''****************************************

<HTML>
<HEAD>
<TITLE>Using Stored Procedures</TITLE>
<%
@LANGUAGE="VBSCRIPT"
%>
<!--METADATA TYPE="typelib" UUID="00000205-0000-0010-8000-00AA006D2EA4"
NAME="ADODB Type Library" -->
<!--
The above line declares the ADO type library so that we can use constants.
For details see
http://msdn.microsoft.com/library/de...plications.asp
-->
</HEAD>
<BODY>
Calling Parameterised Stored Procedures from ASP
<%

Dim dbUserName
Dim dbPassword
Dim dbCatalog
Dim dbServer

dbServer = "YourServerName."
dbcatalog = "Northwind"
dbUserName = "ia"
dbPassword = "001test"

Dim oConn
Dim strConn
Dim oCmd
Dim rsData
Dim strSQL

strConn="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" &
dbUserName & ";Password=" & dbPassword & ";Initial Catalog=" & dbcatalog &
";Data Source=" & dbServer

Response.Write "Please select a category<BR>"
strSQL = "select CategoryID, CategoryName from categories"

SET oConn = Server.CreateObject("ADODB.Connection")
SET oCmd = Server.CreateObject("ADODB.Command")

oConn.Open strconn
SET rsdata = oconn.Execute (strsql)
%>
<FORM method="post" action="lesson3.asp" ID="Form1">
<select name="iCatID">
<%
' Build the list box
while not rsdata.eof
response.write "<OPTION VALUE = """ +
CSTR(rsdata.fields("CategoryID").value) + """>" +
rsdata.fields("CategoryName").value + "</Option>" + VBCR
rsdata.movenext
wend

%>
</select>
<INPUT type="submit" value="Enter"> <input type="reset" value="reset">
</FORM>
<%
if (Request.Form("iCatID")<>"") then
Set oConn = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command")
oConn.Open strConn
Set oCmd.ActiveConnection = oConn
' Setup Call to Stored Procedure and append parameters

oCmd.CommandText = "spGetProducts"
ocmd.Commandtype = adCmdStoredProc
oCmd.Parameters.Append ocmd.CreateParameter("@Return_Value", adInteger,
adParamReturnValue)
oCmd.Parameters.Append oCmd.CreateParameter("@CategoryID", adInteger,
adParamInput)

' Assign value to input parameter
oCmd.Parameters("@CategoryID") = Request.Form("iCatID")
' Fire the Stored Proc and assign resulting recordset
' to our previously created object variable

Set rsData = oCmd.Execute
response.Write "Passed in Value = " & Request.Form("iCatID") & "<BR>"
Response.Write "Error Value = " & clng(oCmd("@RETURN_VALUE").Value) &
"<BR>"

%>
<BR>
<BR>
<B>Results</B><BR>
<table width="90%" cellspacing="1" cellpadding="5" border="1">
<%
Dim iCount
'Load the Header Row
response.write "<tr>"
For iCount = 0 to rsData.Fields.Count - 1
response.write "<th>"
response.write rsData.Fields(iCount).Name
response.write "</th>"
Next
response.write "</tr>"

' Load the data Rows
while not rsData.EOF
response.write "<tr>"
For iCount = 0 to rsData.Fields.Count - 1
response.write "<td>"
response.write rsData.Fields(iCount).value
response.write "</td>"
next
response.write "</tr>"
rsData.MoveNext
wend
%>
</table>
<BR>

<FONT color="red">Done.
<br>
</FONT>
<%
end if
%>

</BODY>
</HTML>
''''****************************************

"Hursh" <ag***********@yahoo.com> wrote in message
news:c5**************************@posting.google.c om...
Hi,

I have written some stored procedures in SQL and these procedures
return some value.
I want these values to be captured by the ASP code.

I am able to access the tables using ADO( recordsets ) but is there a
way to pass data returned from stored procedures to vairables in ASP
code.

tia
Hursh

Jul 19 '05 #2

P: n/a
Hursh wrote:
Hi,

I have written some stored procedures in SQL and these procedures
return some value.
I want these values to be captured by the ASP code.

I am able to access the tables using ADO( recordsets ) but is there a
way to pass data returned from stored procedures to vairables in ASP
code.

tia
Hursh


There are three ways for a stored procedure to return data to a client app:
1. in a resultset - ADO transforms this into a recordset. I assume you are
already familiar with this method, so I will move on
2. in an output parameter. Run this script in QA:
create procedure outparam (@p int output) AS
set nocount on
set @p1 = @p1 + 45
go
declare @retval int
set @retval =2
exec outparam @retval output
select @retval as OutputValue
3. in a Return value. Run this script in QA:
create procedure returnvalue AS
set nocount on
Return 45
go

declare @retval int
exec @retval = returnvalue
select @retval as ReturnedValue

There are many ways to get the data in a resultset using ADO, But there is
only one way in ADO to get the data from output or return parameters: a
Command object.

Dan already showed you an example of one. I imagine that coding it looks a
little intimidating, and you know what? It is intimidating, and error-prone,
as well. That is why many people including myself have created code
generators for Command object code. Mine is available here if you wish to
try it:
http://www.thrasherwebdesign.com/ind...asp&c=&a=clear

HTH,
Bob Barrows



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #3

P: n/a
Thanx for the help.

I will definitely try the code on your site.

regards
Hursh

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message news:<eN**************@TK2MSFTNGP10.phx.gbl>...
Hursh wrote:
Hi,

I have written some stored procedures in SQL and these procedures
return some value.
I want these values to be captured by the ASP code.

I am able to access the tables using ADO( recordsets ) but is there a
way to pass data returned from stored procedures to vairables in ASP
code.

tia
Hursh


There are three ways for a stored procedure to return data to a client app:
1. in a resultset - ADO transforms this into a recordset. I assume you are
already familiar with this method, so I will move on
2. in an output parameter. Run this script in QA:
create procedure outparam (@p int output) AS
set nocount on
set @p1 = @p1 + 45
go
declare @retval int
set @retval =2
exec outparam @retval output
select @retval as OutputValue
3. in a Return value. Run this script in QA:
create procedure returnvalue AS
set nocount on
Return 45
go

declare @retval int
exec @retval = returnvalue
select @retval as ReturnedValue

There are many ways to get the data in a resultset using ADO, But there is
only one way in ADO to get the data from output or return parameters: a
Command object.

Dan already showed you an example of one. I imagine that coding it looks a
little intimidating, and you know what? It is intimidating, and error-prone,
as well. That is why many people including myself have created code
generators for Command object code. Mine is available here if you wish to
try it:
http://www.thrasherwebdesign.com/ind...asp&c=&a=clear

HTH,
Bob Barrows

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.