ID Creation/ASP/SQL Server

Using SQL Server with ASP for an intranet website.
I'm generating a ID using a user defined function. (getting the Maximum and
adding 1 to get a new ID )
During testing i have got 2 similar ids (example: 31000,31000) for 2 records
when there is concurrent access to a website
here i need to track the ID generated,manipulate and convert into string
format (example: U31000_CTF_FILE)
so im generating it thru function.
is there any other best way to generate & track IDs.
Note: im not using any stored procedures moreover i want to use normal ASP
code only
please help
-dnkHi
You can try somethimg like that
CREATE PROC spNewIds
AS
DECLARE @.new_id INT
BEGIN TRANSACTION
SELECT @.new_id =COALESCE(MAX(Id)+1,0) FROM MyTable WITH (UPDLOCK,HOLDLOCK)
INSERT INTO AnotherTable (colname) VALUES (@.new_id )
COMMIT TRANSACTION
"DNKMCA" <dnk@.msn.com> wrote in message
news:uPeCLiOFGHA.916@.TK2MSFTNGP10.phx.gbl...
> Using SQL Server with ASP for an intranet website.
> I'm generating a ID using a user defined function. (getting the Maximum
> and
> adding 1 to get a new ID )
> During testing i have got 2 similar ids (example: 31000,31000) for 2
> records
> when there is concurrent access to a website
> here i need to track the ID generated,manipulate and convert into string
> format (example: U31000_CTF_FILE)
> so im generating it thru function.
> is there any other best way to generate & track IDs.
> Note: im not using any stored procedures moreover i want to use normal
> ASP
> code only
> please help
> -dnk
>|||Why don't you just have a dummy table with an IDENTITY column?
CREATE TABLE dbo.MyTable(ID INT IDENTITY(1,1))
Now In the ASP code,
set rs = conn.execute("SET NOCOUNT ON; INSERT dbo.MyTable DEFAULT VALUES;
SELECT SCOPE_IDENTITY()")
Response.Write rs(0)
Though I'm not sure I understand the objection to using stored procedures,
or what you mean by "track IDs"...
"DNKMCA" <dnk@.msn.com> wrote in message
news:uPeCLiOFGHA.916@.TK2MSFTNGP10.phx.gbl...
> Using SQL Server with ASP for an intranet website.
> I'm generating a ID using a user defined function. (getting the Maximum
> and
> adding 1 to get a new ID )
> During testing i have got 2 similar ids (example: 31000,31000) for 2
> records
> when there is concurrent access to a website
> here i need to track the ID generated,manipulate and convert into string
> format (example: U31000_CTF_FILE)
> so im generating it thru function.
> is there any other best way to generate & track IDs.
> Note: im not using any stored procedures moreover i want to use normal
> ASP
> code only
> please help
> -dnk
>sql