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!
|
This demonstration gives you an overview of IBM® Rational® Build Forge Express Edition, a global offering that provides a framework to automate and execute software processes. Rational Build Forge provides a software assembly line that can support all of your tools, technologies, and platforms so you can achieve a repeatable, reliable, and traceable build and release process.
FREE! Go There Now!
| | | |
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, for an overview of Rational’s new software offerings and resources to help modernize and accelerate software innovation on i on Power Systems – while ensuring past application investments are protected and continue to grow. Learn how these solutions are helping customers extend their core i5/OS solutions toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
FREE! Go There Now!
| | | |
This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download IBM DB2 Express-C 9.5, a no-charge version of DB2 Express 9 database server. DB2 Express-C offers the same core data server base features as other DB2 Express editions and provides a solid base to build and deploy applications developed using C/C++, Java, .NET, PHP, and other programming languages.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial version of WebSphere Extended Deployment Compute Grid, which lets you schedule, execute, and monitor batch jobs. Because online transaction processing and batch jobs execute simultaneously on the same server resources, you can avoid costly duplication of resources. Compute Grid supports job types of Java transactional batch, compute-intensive and a new type called "native execution", which enables non-Java workloads to run on distributed end points.
FREE! Go There Now!
| | | |
This tutorial shows new users of IBM WebSphere Business Monitor Version 6.0.2 how to perform the "Hello World" equivalent for monitoring business process applications. It is intended to help you get familiar with the capabilities of the product.
FREE! Go There Now!
| | | |
Discover how Rational tools and best practices for testing can make your job easier. The new Rational Testing eKits provide you with valuable resources – including demos, webcasts, tutorials, and articles – that help you address your specific testing needs across the software lifecycle. Five new eKits are available covering the topics of Requirements and Test Management, Functional Testing, Performance Testing, Code Quality and Embedded Systems, and SOA and Web Services Testing.
FREE! Go There Now!
| | | |
Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily.
FREE! Go There Now!
| | | |
Informix Dynamic Server (IDS) Express Edition offers outstanding online transaction processing (OLTP) database performance, while helping to simplify and automate many of the tasks associated with deploying databases for small business applications. IDS 11 further extends the ease of management and applications integration with the Admin API and Scheduler, high availability with Continuous Log Restore for backup server recovery in case of a primary server failure, and column level encryption to protect personal and company private data.
FREE! Go There Now!
| | | |
Explore how Rational and WebSphere software enable enterprise documentation in SOA environments. Specifically, a new integration between IBM WebSphere® Business Modeler and IBM Rational® Method Composer software can help technical writers more easily keep enterprise operations manuals in sync with changes that are made to business processes, resulting in more accurate and timely documentation that benefits the entire enterprise.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 4 
