Passing Single Parameters, Returning Records and writing to an asp page

Contributed by aspfree
Rating:

/ 7
January 06, 2000

Rate this Article:
MEH MEH++

SEARCH ASP FREE

This demo shows how format an EXEC string with a single parameter. This formatted string is passed to a Stored Procedure in a MS SQL 7 database. The recordset is then passed back to an ASP page and written out. Using the Command Object was confusing! This technique is much cleaner and with less code. Stored Procedures are an easy way to increase performance of your ASP pages. ASPFree has used this method on high volume pages. We've found it to help eliminate script-timeout errors. Enjoy!!

Code for this demo

Page 1 the input screen

<html>
<head>Single Input Value Form</title>
</head>
<body>
<form method="POST" action="singlevalue2.asp" name="form1">
<p><select name="ColorOfCar" size="1">
<option selected value="Blue">Blue</option>
<option value="Black">Black</option>
<option value="Yellow">Yellow</option>
</select></p>
<p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset"
name="B2"></p>
</form>
</body>
</html>

Page 2 The process page

<%

'Declare variables
dim conn
dim rs
dim strsql
dim strColorOfCar

'Set the information submitted to a local variable
strColorOfCar = request("ColorOfCar")

'Connection and Recordset object
strconn = "Driver={SQL Server};Description=sqldemo;SERVER=servername;UID=loginid;PWD=password;DATABASE=blah"
set conn = server.createobject("adodb.connection")
conn.open strconn

'Set the string that will be passed to the database and execute the
'stored procedure. This will return a recordset
strSql = "Declare @strColorOfCar varchar exec sp_SingleValue " & strColorOfCar
set Rs = server.createobject("adodb.recordset")
Rs.open strSql, conn
rs.movefirst
%>
<html>
<head>
<title>Single Value passing parameters</title>
</head>
<body>
<TABLE BORDER="1" width="80%">
<TR>
<% For Each Field In RS.Fields %>
<TH>
<%
response.write Field.name
%>
</TH>
<% Next %>
</TR>
'This section writes out the DATA of the fields in the Recordset
<% Do While Not RS.EOF %>
<TR>
<% For Each Field In RS.Fields %>
<TD ALIGN=center>
<%
Response.Write Field.Value
%>
</TD>
<% Next
RS.MoveNext %>
</TR>
<% Loop %>
</TABLE>
</body>
<%
Rs.close
set Rs = nothing
conn.close
set conn = nothing
%>
</html>

The stored Procedure that is passed the information

CREATE PROC sp_SingleValue

( 'This is a variable that parses the Parameter
@strColorOfCar varchar(255)
)
AS

SELECT tblParamWithRecordsets.ColorOfCar, tblParamWithRecordsets.TypeOfCar, tblParamWithRecordsets.NumberOfDoors FROM tblParamWithRecordsets WHERE tblParamWithRecordsets.ColorOfCar=@strColorOfCar

Return

GO