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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles
More By aspfree  developerWorks - FREE Tools!
|
Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial of the latest release of IBM Lotus Sametime Standard V8.0. Lotus Sametime Standard V8.0 is a platform for unified communications and collaboration that combines security features with an extensible, open solution including integrated Voice over IP, geographic location awareness, mobile clients, and a robust Business Partner community offering telephony and video integration.
FREE! Go There Now!
| | | |
Join us for this web seminar to learn how you can defend your web applications from attack. Learn about the 3 most common web application attacks, including how they occur and what can be done to prevent them. We’ll also discuss manual versus automated approaches for scanning and identifying web application vulnerabilities and how IBM Rational AppScan, an automated vulnerability scanner, can help you automate more of what you are doing manually today.
FREE! Go There Now!
| | | |
Analysts, architects, and developers who have existing COBOL or PL/I skills and want to extend those skills to deploy new workloads on the mainframe can use the IBM Enterprise Modernization Sandbox for System z to find hands-on walkthroughs of common real world scenarios. The scenarios provide examples of how to rapidly design, create, assemble, test, and deploy high-quality Web, Web services, portal, and SOA applications for IBM CICS, IBM IMS, and IBM WebSphere Application Server.
FREE! Go There Now!
| | | |
This Fall, IBM Rational talks to you directly through a special teleconference series giving you access to the best minds in IBM Rational - product experts and market thought leaders who will answer your questions during these pre-scheduled telephone conference calls. Register today!
FREE! Go There Now!
| | | |
Learn how to do more with your reusable assets with the free Rational Asset Manager eKit. The eKit includes demos on how Rational Asset Manager tracks and audits your assets in order to utilize them for reuse. Plus you’ll find white papers and a Webcast that discuss the challenges of a Service Oriented Architecture and how Rational Asset Manager can provide quick and effective solutions.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
FREE! Go There Now!
| | | |
Because access to government information continues to be an area of concern for many U.S. citizens with disabilities, the U.S. government enacted Section 508 of the Rehabilitation Act in 2001 to ensure that government agencies create accessible Web content, enabling all citizens to access the information they need. A fully accessible Web site makes Web content accessible to all individuals, including those with disabilities, who may be accessing Web content via a variety of user agents. Common user agents include standard Web browsers, text-only browsers, assistive devices and mobile devices such as cell phones or personal digital assistants (PDAs).
FREE! Go There Now!
| | | |
Join this webcast to discover the key requirements for successful change and release management. Learn how to extend your .NET environment to improve productivity and collaboration, and address core problems afflicting team development. In this webcast, we’ll review typical challenges faced by customers and how to resolve them with the IBM Rational Change and Release Management solution, including Rational ClearCase, Rational ClearQuest and Rational Build Forge. Replay is available for 9 months.
FREE! Go There Now!
| | | |
Viper 2 brings a great value to developer communities including SQL, XML, PHP, Ruby, .NET and Java. You probably already know that DB2 Express-C is free for developers to develop, deploy and distribute. Viper 2 provides a variety of means that help move your application from the development stage to deployment more rapidly. This webcast shows how to best utilize the latest tools available for developing DB2 applications.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 3 
