Example of how to Create Records w/Unique ID # without using Identify Datatype

Contributed by aspfree
Rating:

/ 3
September 01, 1999

Rate this Article:
MEH MEH++

SEARCH ASP FREE

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
%>