Forums
This topic is locked
autonumbers no more!
Posted 30 Apr 2004 05:54:12
1
has voted
30 Apr 2004 05:54:12 pedro mauricio posted:
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!
Replies
Replied 30 Apr 2004 13:35:27
30 Apr 2004 13:35:27 Matt Machell replied:
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
Edited by - Matt_M on 30 Apr 2004 13:37:13
Replied 30 Apr 2004 13:41:32
30 Apr 2004 13:41:32 Vince Baker replied:
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
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
Replied 30 Apr 2004 17:33:32
30 Apr 2004 17:33:32 pedro mauricio replied:
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!!!
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!!!
Replied 30 Apr 2004 17:39:52
30 Apr 2004 17:39:52 Vince Baker replied:
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
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
Replied 30 Apr 2004 17:48:49
30 Apr 2004 17:48:49 pedro mauricio replied:
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!
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!
Replied 30 Apr 2004 17:53:23
30 Apr 2004 17:53:23 Vince Baker replied:
here is a sample of an insert sql command
<%
sql_stats ="INSERT INTO tbl_stats (stat_user_id_fk, stat_url) VALUES (" & strStatUser & ",'" & strStatURL & "')"
%>
<%
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()
%>
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
<%
sql_stats ="INSERT INTO tbl_stats (stat_user_id_fk, stat_url) VALUES (" & strStatUser & ",'" & strStatURL & "')"
%>
<%
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()
%>
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
Replied 30 Apr 2004 18:00:16
30 Apr 2004 18:00:16 pedro mauricio replied:
thanx a lot ! i´ll try that!
Replied 07 May 2004 23:51:42
07 May 2004 23:51:42 Andrew Watson replied:
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.... ::
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.... ::