ASP, MS-SQL Server & Stored Procedures to Add records & return data to an ASP page Without using the Command Object!!! |
| This example is for those looking to write records from an asp page to Microsoft SQL Server 6.5 or 7.0 database using a stored procedure without trying to use the command object like in Microsoft's examples. I could never get those to work! So I invented my own. I'm probably not the 1st to use this method but I was happy I discovered it! This example also after writing the records to the database, information can be retrieved (i.e an Identity column #) and write data back to the asp page & your end user. Sorry this demo is a lot more babble than any other demo's but the concept here is a bit more involved. Most of the examples I found on the net all relate to writing from
ASP to Access97 databases. This is all fine when the app is departmental
in nature or just using the Access db to extract pre-existing information to be
written to the asp page. If your web application is doing inserts, updates or
deletes and have a fair amount of users, use a MS SQL Server database. Any kind
of size to the audience using the web app and I strongly recommend
Microsoft SQL Server as the back-end database. Another nice feature in using SQL Server is then the owners of the data can easily
access live data through an Access 97 database via an ODBC driver. You can build
your front end for the end user of the data using Access 97. I have found most
people like using Access as a front end tool that does Querying/Reporting. In my
opinion that is why Access is the perfect front-end tool. (And NO Microsoft didn't
pay me to say that!!! Wish they did though!!):)) Please take sometime to review this free code, I have invested a great deal of time
searching the Net for information on Active Server Pages. I always believe you get
10 times or more back of what you give. Please take sometime and Click here to download the SQL Server scripts to create the tables in this example.
d. You have created some input form and a asp page to do the processing. The below
example show two WebPages(1: An input form, 2: Processformdata.asp that contains
all the asp code. PAGE ONE INPUT FORM: (SomeInputform.html)
1. the user will input some information and click submit to
an Active server page. This example we will input a firstname
and a last name. Then click submit PAGE TWO ASP PAGE CODE: (processformdata.asp)
<%
'Declare variables
dim strconn
dim conn
dim rs
dim strsql
dim strsql2
dim strsql3
dim recordIDvalue
'This formats the data from the Input form and will be passed into the database.
'At the end of the formatted string is how I return the value
'This easily could be Select * from TableName to return the whole recordset
strsql1 = "Declare @req_num int Exec spName"
strsql2 = "'" & request.form("txtfield1") & "'," & "'" & request.form("txtField2") "'," & @req_num OUTPUT
strsql3 = "select table1.recordid from table1 where recordid = @req_num"
strsql = strsql1 & strsql2 & strsql3
'In this example the formatted string will look like this. A good way to test
'for this is to do a response.write out your code that will be executed on the asp page
'Copy and Paste in the query window of SQL server 6.5 or 7.0. This will help you determine if your syntax is right. Formatted SQL is a bit fussy.
declare @req_num int EXEC spName 'John','Doe', @REQ_NUM OUTPUT SELECT @@Identity
'Set your connection & recordset objects
set conn = server.createobject("adodb.connection")
conn.open "dsnname or connectionstring"
set rs = server.createobject("adodb.recordset")
rs = conn.execute(strsql)
'Set a local variable that will be used to write out the value
recordIDvalue = rs("Identity_Value_That_was_Created_When_Inserted")
conn.close
Set rs = nothing
set conn = nothing
%>
<html>
<head>
<title>Asp page that will do the processing</title>
</head>
<body>
<p>Your record number is:<% = recordIDvalue %> </p>
</body>
</html> |
|
| 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!
|
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!
| | | |
Hold your calendar on January 30, 2008 for this free webcast on the new i5/OS. Rational's Enterprise Modernization products will be discussed at this webcast as they help to drive the application development environment for this new System i OS. <br />And learn how i5/OS will take you to the next step of efficient, resilient business processing. You will hear about the new i5/OS capabilities as it will be the most significant i5/OS release in years. If you cannot join the webcast on 1/30/08 you can still use this link to listen to the replay.<br />
FREE! Go There Now!
| | | |
Visit IBM developerWorks to try the IBM SOA Sandbox for process. The SOA Sandbox for process focuses on providing a trial environment with the necessary tooling and components required to gain a better understanding of business processes and how to best improve existing business processes to derive value quickly.
FREE! Go There Now!
| | | |
As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change.
FREE! Go There Now!
| | | |
With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential.
FREE! Go There Now!
| | | |
Learn from the best! Find out how developers use Rational ClearCase to be more flexible, innovative and deliver higher quality code in the Rational ClearCase Power Users eKit. This complimentary eKit provides a collection of materials, like articles, whitepapers, and demos that can help you become a power user of Rational ClearCase.
FREE! Go There Now!
| | | |
The IBM DB2 Deep Compression ROI tool is designed for DBA’s and IT management personnel to perform a clinical analysis of the cost savings gained from the Storage Optimization feature of DB2 9 for Linux, UNIX and Windows. The feature, also known as Deep Compression, compresses data that lies within a database by up to 80% at times.
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!
| | | |
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!
| | | |
XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
| | | | | | | |  | | |
|
