I discovered how to call stored procedures with less than 5 lines of code. I
recently had to display a webpage that was based on a table that contained several
calculated totals. The reason I had this summary/reporting table is a lot of data
was calculated and allowed for almost anyway of displaying stat's about the data.
Here is a brief summary of what I did. I created a table in SQL 7 that held the
data.
Every 4 hours the table was refreshed with new data using a Stored
Procedure. Then I created another Stored procedure that was being called
from the ASP page. Without writing a book on what I did here is a 10,000 ft
level overview. Hope this demo shows another way of using Stored Procedures to
retrieve data a Data Source using ASP. Stored Procedures are no more than an SQL
statement retreving data. Why hold that Query inside an ASP page when you can use
the Database server to run your Query's
Step 1
Create a Reporting Table that will be populated with the totals. This is
the SQL Generated Script that will created the table. This table holds all the
calculated data that is based on Raw numbers that are inputted.
<p>CREATE TABLE [dbo].[test2] (
[NickName] [char] (50) NULL ,
[TotalPoints] [int] NULL ,
[Count_NickName] [char] (50) NULL ,
[AvgScore] [decimal](18, 1) NULL ,
[NumOfStrikes] [decimal](18, 0) NULL ,
[WinLossPercentage] [decimal](18, 2) NULL ,
[Wins] [int] NULL ,
[Loses] [int] NULL
) ON [PRIMARY]
GO
</p>
Step 1 1/2 Here is some data to load in the db.
| NickName | TotalPoints | Count_NickName | AvgScore | NumOfStrikes | Winning_Percentage | Wins | Loses | | NickName1 | 611 | 138 | 4.4 | 28 | 52.9 | 73 | 65 | | NickName2 | 445 | 94 | 4.7 | 28 | 57.45 | 54 | 40 | | NickName3 | 419 | 97 | 4.3 | 35 | 49.48 | 48 | 49 | | NickName4 | 383 | 83 | 4.6 | 24 | 51.81 | 43 | 40 | | NickName5 | 337 | 74 | 4.6 | 20 | 54.05 | 40 | 34 | | NickName6 | 329 | 79 | 4.2 | 14 | 55.7 | 44 | 35 | | NickName7 | 324 | 81 | 4 | 16 | 45.68 | 37 | 44 |
Step 2
Create Stored Procedure that will be called from an ASP page. Ok this is
just a normal select statement but I'm convinced that put code where it will run the
fastest. On the database server, you only have to call the stored Procedure and
return the data. Instead of parsing the query and then running the statement.
CREATE PROCEDURE [TestStoredProcedure] AS
SELECT test2.NickName, test2.TotalPoints, test2.Count_NickName, test2.AvgScore,
test2.NumOfStrikes, test2.WinLossPercentage, test2.Wins,test2.Loses
FROM test2
ORDER BY test2.NickName;
GO
Step 3
Create an ASP page. The normal stuff, connection string, recordset object
etc.. here is the code
<% @language="vbscript" %>
<%
dim conn
dim strconn
dim rs
dim strql
'Normal Connection String
strconn= "Driver={SQL
Server};SERVER=127.0.0.1;UID=LoginID;PWD=password;DATABASE=some_db"
set conn = server.createobject("adodb.connection")
conn.open strconn
'This is a local variable that holds the variable the execute the stored proc.
<font color="#FF0000">strsql = "Exec TestStoredProcedure"
set rs = server.createobject("adodb.recordset")
rs.open strsql, conn
%>
<html>
<head>
<title>Example</title>
</head>
<body>
<% rs.movefirst %>
<table BORDER="1" width="80%">
<tr></p>
<p><td colspan="8"><center><b><strong>This section writes out
the Names of the fields in the Recordse</strong>t</center></b></td></tr>
<tr>
<% For Each Field In RS.Fields %>
<th>
<%
response.write "<b>" & Field.name & "</b>"
%>
</th>
<% Next %>
</tr>
<tr></p>
<td colspan="8"><center>This section writes out
the DATA of the fields in the Recordset</strong></center></b></td></tr>
<% Do While Not RS.EOF %>
<tr>
<% For Each Field In RS.Fields %>
<td ALIGN="center">
<%
If IsNull(Field) Then
Response.Write ""
Else
Response.Write Field.Value
End If %>
</td>
<% Next
RS.MoveNext %>
<% Loop %>
</table>
</body>
</html>
<%
'Close all objects and set to nothing to keep resources free!
rs.close
conn.close
set rs = nothing
set conn = nothing
%></p>
|
|
| 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!
|
You'll get answers to many questions and more from David Barnes, Lead Evangelist for IBM Emerging Internet Technologies. David will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
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 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!
| | | |
Rational Build Forge Express Edition is an automation framework that packages the latest enterprise-grade technologies into a reliable, flexible and robust configuration designed and priced specifically for small to midsize businesses. The new Rational Build Forge Express eKit provides you with valuable resources – including a case study, podcast, demo, and articles – to help you increase staff productivity, compress development cycles and deliver better software, fast.
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!
| | | |
You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online.
FREE! Go There Now!
| | | |
Join this webcast to learn how IBM Rational's Functional Testing solution enables you to implement automation your way, at your pace, with your existing staff. In this webcast, you’ll learn how you can eliminate redundancy of manual test scripts, reduce errors, and increase test coverage through test automation. After this presentation you will understand how IBM Rational Functional Testing solution can streamline your manual testing and make test automation easily attainable.
FREE! Go There Now!
| | | |
WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
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!
|
|
