The trick of this has two tables, one keeping track of the unique
number, the other one is the main data table. Everytime the
table is opened up it updates the unique number and locks so
your gurenteed a unique number. I would recommend using type
solution in departmental solutions. If your web app is going to be
having the possibility of lots and lots of simultainous users. Look
into something else. Make sure when you first make the Max_id
field that you put a starting value in the table!
This example uses:
1. Sql Server 6.5 or 7.0
2 tables - Max_ID & MainTable
1 Stored Procedure - AddRecord
2. NT Server or Workstation 4.0
3. PWS 4.0 for NT workstation or IIS 4.0
1 Frontpage web named noidentity Here is the example Step one Create a Input Form similiar to the One below and Enter some data in.
once you click submit the data is sent to the Results page and processed.
This is what looks like once submitted and processed?
'This is what gets passed to the database
DECLARE @max INT EXEC addrecord @max,"data1","data2","data3",@max OUTPUT SELECT * FROM MainTable WHERE RecordID = @max | Record ID | 22 | | Field Two | data1 | | Field Three | data2 | | Field Four | data3 |
Here is the sql script & asp page code /* Microsoft SQL Server - Scripting */
/* Server: someserver */
/* Database: noidentity */
/* Creation Date 11/8/98 1:21:55 AM */
/****** Object: Table dbo.MAX_ID_TABLE Script Date: 11/8/98 1:21:59 AM ******/
CREATE TABLE dbo.MAX_ID_TABLE (
MAX_ID int NOT NULL
)
GO
/****** Object: Table dbo.MainTable Script Date: 11/8/98 1:22:04 AM ******/
CREATE TABLE dbo.MainTable (
RecordID int NOT NULL ,
FieldOne varchar (255) NULL ,
FieldTwo varchar (255) NULL ,
FieldThree varchar (255) NULL ,
CONSTRAINT PK___1__10 PRIMARY KEY CLUSTERED
(
RecordID
)
)
GO
/****** Object: Stored Procedure dbo.addrecord Script Date: 11/8/98 1:22:06 AM ******/
CREATE PROC addrecord
(
@RecordId int,
@Fieldone varchar(25),
@Fieldtwo varchar(25),
@Fieldthree varchar(25),
@RTN_REQ_NUM INT OUTPUT
)
AS
BEGIN TRANSACTION
UPDATE MAX_ID_TABLE SET MAX_ID = MAX_ID + 1
DECLARE @MAX INT
SELECT @MAX = MAX_ID FROM MAX_ID_TABLE
INSERT MainTable (RecordID, Fieldone,FieldTwo,FieldThree) values (@max,@Fieldone,@FieldTwo,@FieldThree)
SELECT @RTN_REQ_NUM = @max
COMMIT TRANSACTION
return
GO ASP Code <%
Dim conn
dim rs
dim strsql
dim strconn
'Connection String this could also could be put into a global.asa
strconn = "Driver={SQL Server};Description=noidentity;SERVER=someserver;UID=sa;PWD=;DATABASE=noidentity"
'Build the string that will be passed off to the database
strsql = ""
strsql = strsql & chr(34) & request.form("t1") & chr(34) & ","
strsql = strsql & chr(34) & request.form("t2") & chr(34) & ","
strsql = strsql & chr(34) & request.form("t3") & chr(34) & ","
'Strsql2 is required part of the string this is what the stored procedure
'Uses to find out the output variables and what stored Procedure to use
strsql2 = "DECLARE " & CHR(10) & "@max " & "INT" & CHR(10) & "EXEC " & "addrecord @max,"
'This is required to be able to select all the information once the recordid is created
strsql3 = "@max OUTPUT" & chr(10) & " SELECT * FROM MainTable WHERE RecordID = @max" & Chr(10)
'This puts all the strings together that will be passed to the database.
strsql = strsql2 & strsql & strsql3
'This just writes out the string that will be passed to the database
'This can be deleted if you want to use this code
response.write strsql
set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
conn.open strconn
rs = conn.execute(strsql)
%>
<html>
<head>
<title>Results Page</title>
</head>
<body>
<h2 align="center"><strong>Results Page</strong></h2>
<p align="left"><strong>This demostrates how to insert records using stored procedures, <br>
Active Server Pages & NT 4.0 PWS or IIS 4.0. and not using <br>
the "IDENTITY"Field type. <br>
<br>
This Results page only formats the data into a string <br>
and passes off to the database. Here are the results<br>
of your test.</strong></p>
<table border="1" width="50%">
<tr>
<td>Record ID</td>
<td><% = rs(0) %>
</td>
</tr>
<tr>
<td>Field Two</td>
<td><% = rs(1) %>
</td>
</tr>
<tr>
<td>Field Three</td>
<td><% = rs(2) %>
</td>
</tr>
<tr>
<td>Field Four</td>
<td><% = rs(3) %>
</td>
</tr>
</table>
</body>
</html>
<% conn.close
set rs = nothing
%> | 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!
|
Visit IBM developerWorks to try the IBM SOA Sandbox for connectivity. The SOA Sandbox for connectivity provides a trial environment with the tooling and components to help you explore how to effectively connect your infrastructure and integrate all of the people, processes and information in your company. Use the hosted sandbox to explore SOA techniques that streamline connecting existing IT assets together, as well as learn how to connect them to new business logic.
FREE! Go There Now!
| | | |
As businesses grow increasingly dependent upon Web applications to provide services to customers, employees and partners, these complex applications become more difficult to secure. Although traditional security solutions protect Internet infrastructure layers, they do not guard against HTTP and HTML attacks. Many organizations that conduct security testing still deploy applications that allow attackers to manipulate their logic and wreak havoc on their business. To mitigate this risk, development and delivery teams must address Web application security throughout the lifecycle, addressing the many layers detailed in this paper.
FREE! Go There Now!
| | | |
IBM Enterprise Modernization solutions help organizations evolve core IT systems towards modern architectures and technologies—reducing the burden of maintenance and freeing up resources to develop new business requirements and capabilities. With the IBM Enterprise Modernization Sandbox for System z you can evaluate IBM Enterprise Modernization solutions focused on five key areas: Assets, Architectures, Skills, Processes and Infrastructures, and Investment. Each solution is based upon real customer experiences and offers a proven path to get you started with your modernization projects.
FREE! Go There Now!
| | | |
<a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts.
FREE! Go There Now!
| | | |
This webcast outlines the best practices that must be instituted to gain the maximum benefit from SOA while maintaining high quality of service. Whether you are deploying new applications or managing and monitoring your existing infrastructure, learn how you can ensure high quality of services with SOA based solutions from IBM. All registrants who attend this live Web Seminar will receive complimentary access to a white paper titled “Maintaining QoS in an SOA Environment”.
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!
| | | |
Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications.
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!
| | | |
Join this webcast to see how IBM Data Studio Developer and pureQuery can take the pain out of Java data access. uApplications developed using both Java and SQL have become a common requirement. Database connectivity using Java Database Connectivity (JDBC) to create an application is a multi-step tedious process, and tooling that covers both SQL and Java has been unavailable, until now. IBM Data Studio introduces the pureQuery platform: a high-performance, Java data access platform focused on simplifying the tasks of developing, managing, and optimizing database applications and services.
FREE! Go There Now!
| | | |
Discover how IBM Rational AppScan Standard Edition can help you detext vulnerabilities in your web applications in the Web Application Security eKit. IBM Rational AppScan is a leading suite of automated web application security solutions that scan and test for common Web application vulnerabilities. The new Web Application Security eKit provides you with valuable resources, including white papers, demos, and additional information on the benefits of testing your Web applications.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
