Hi! I recently started to migrate from MSAccess databases to MSSQL Server databases and my problem is that i need to stop using "autonumber" fields in SQLServer but, i need to mantain an unique ID in a table field like for ex: userID or ProductID wich usualy need an unique ID. I think i know how to achieve this with some ASP code that simulates "autonumbers"; here´s how i think i can do it but, tell me if there´s any problem with this:

1 - check highest userID nr inserted in field userID
2 - get highest userID and increment + 1
3 - check if new userID exists already (just in case!)
4 - insert new userID ( all ok! )

Question: will there be any problems in a long term by using this method?

Please Help! Urgent! i need to know if this method is secure and if not, please tell me about a diferent one!

Have you looked into using the <b>identity</b> feature of SQL Server. It'll do exactly what you want, unless I'm mistaken as to what you're trying to do.

-Matt

Edited by - Matt_M on 30 Apr 2004 13:37:13

Matt is right, you really dont want to start generating your own numbers, you will make life very difficult for yourself,

For example, if you get the latest number when you load a page and then complete the page and submit it, if someone else has also loaded the page at the same time, they will both have the same number.

To get around this you will need to process your insert action on a second page and get the latest number just before insert, but this means you cannot use the insert or update functions that come with dreamweaver....

I would also suggest using the identity field type.

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

IN DETAIL: the web aplication i´m making, will SHARE the same MSSQL server database with an offline aplication made with DELPHI wich controls the exact same tables and respective e fields, this aplication does it without "autonumbers" and it works lik e a charm! well! that´s my problem!

More: the ASP code i´m making must work with Access DB and MSSQlServer; if someone doesn´t have mssql server, he can still use access and so, features like @@identify just work with mssql server, not with access if i´m not mistenken!

SO: i really need the autonumbers off! any solutions? no solution? tnx!!!

In which case, you could do the following.

1.Form on a page that collects information

2. post form to a process page

3. process page first queries db to get new id number (last one + 1)

4. process page takes this number and all other posted info and submits with a hand coded insert sql command

5. problem solved.

You must insert directly after getting id to ensure data consistancy.

Let me know if you want a sample insert sql command for your process page.

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

Edited by - bakerv on 30 Apr 2004 17:40:58

yes yes yes! i´m not allone! someone understands me! yes!

thanks a lot vince 4 understanding the problem! could u post an example code here of how to do it so i can compare with what i have? can u? thanks!

here is a sample of an insert sql command

&lt;%
sql_stats ="INSERT INTO tbl_stats (stat_user_id_fk, stat_url) VALUES (" & strStatUser & ",'" & strStatURL & "')"
%&gt;

&lt;%
set InsertStats = Server.CreateObject("ADODB.Command"
InsertStats.ActiveConnection = "dsn=dsnname"
InsertStats.CommandText = sql_stats
InsertStats.CommandType = 1
InsertStats.CommandTimeout = 0
InsertStats.Prepared = true
InsertStats.Execute()
%&gt;

Before this, just a simple recordset ordered in descending order by unique id field and take that field and add it to insert action above.

then redirect command to move to next page (i sometimes just place a your changes saved message on the process page in the body, why make another page?)



Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

thanx a lot ! i´ll try that!

the current system will be generating somekind of unique identifier ont the records in the database....

this is fine as not all database (older ones..) platforms supported autonumbering.

Your problems going to be this...

If you start giving uniqu id's and the delphi's giving unique id's..THen they could conflict...

Is the delphi system simply generating a sequential number? if it is, then, if you want to join in the fun and create records with a number in this same sequence youd have to ..

fetch data - process (get last id) - send back data

this can take time (in code timescales..) and you may end up with duplicates.....

So...

Identify the unique field being generated currently....

What is it doing? is it being used for anything other than unique identification...is it being used for sorting or something.

If it is, then your going to HAVE to generate numbers for and in the same sequence.

If not then any Unique identifier should do.

the key being unique......

i sometimes build a unique key using a stripped out date and time value along with a 6 or so digit randomized number (and letters maybe)...all stuck together..eg

140520041927658754

you can generate this using a few lines of vbscript


cheers...

The [140520041927] part will be unique every second

:: Son, im Thirty.... ::