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!
|
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!
| | | |
Download a free trial version of IBM DB2 9.5 for Linux, UNIX, and Windows. DB2 9 is the result of a five-year development project that transformed traditional (static) database technology into an interactive data server that merges the high performance and ease of use of DB2 with the self-describing benefits of XML.
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!
| | | |
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!
| | | |
Ken Krugler, co-founder of code search company Krugle, and Laura Merling, vice president of Marketing and Business Development for Krugle, join to talk about the ins and outs of code search and what it means as a new feature for developerWorks users.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference on December 11 at 1:00 pm ET to get tips on building your own plugins with Rational Method Composer. Get your questions answered!
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference on December 4 at 1:00 pm ET to discuss how Rational Method Composer can help meet your compliance objectives. Get your questions answered!
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!
| | | |
Join the IBM Watchfire team for an informative discussion on techniques and best practices to proactively manage Web application security and how to effectively build application security testing into the software development lifecycle (SDLC). In this Software Delivery Platform webcast you will learn: How to better understand potential web application security vulnerabilities, best practices and how to effectively integrate application security testing into the software development lifecycle, the importance of detecting and removing software vulnerabilities during application development.
FREE! Go There Now!
| | | |
The unprecedented scope of a service-oriented architecture (SOA) initiative brings to the forefront a number of management and governance issues that were sidestepped in the past. The key to a successful SOA implementation is managing and governing activities throughout the entire SOA delivery lifecycle by ensuring that services conform to the needs of all of the business’s stakeholders. Learn how service lifecycle management allows the business to ensure that the process by which services are defined, created, tested, deployed, optimized and retired is manageable, repeatable and auditable.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
