|
|
|
Submitted on: 1/17/2001 9:55:49 PM
By: Eugene
Level: Intermediate
User Rating:
By 1 Users
Compatibility:ASP (Active Server Pages)
Users have accessed this code 12192 times.
| |
| | | A replacement for the AutoNumber field of MS Access.
Sometimes, while working with databases, you need to
know the unique key to a record before or immediately
after you add it. You cannot simply query the database
for the last record, another user may have added a
new one in the meantime.
| |
| |
|
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
1) You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this code from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
|
'**************************************
' Name: Unique Record ID
' Description:A replacement for the Auto
' Number field of MS Access.
Sometimes, While working With databases, you need To
know the unique key To a record before or immediately
after you add it. You cannot simply query the database
For the last record, another user may have added a
new one In the meantime.
' By: Eugene
'
' Returns:Returns a 20 character record
' ID that can be used as a unique identifi
' er.
'
'This code is copyrighted and has ' limited warranties.Please see http://w
' ww.Planet-Source-Code.com/vb/scripts/Sho
' wCode.asp?txtCodeId=6450&lngWId=4 'for details. '**************************************
<%
Randomize
function ZeroPad(sWork, nDigits)
ZeroPad = right(string(nDigits, "0") & trim(sWork), nDigits)
End function
function GetIndexNumber()
sDay = Day(Now())
sMonth = Month(Now())
sYear = Year(Now())
sHour = Hour(Now())
sMinute = Minute(Now())
sSecond = Second(Now())
Tail = Int(1000000 * Rnd)
GetIndexNumber = ZeroPad(sYear, 4) & ZeroPad(sMonth, 2) & ZeroPad(sDay, 2) & ZeroPad(sHour, 2) & ZeroPad(sMinute, 2) & ZeroPad(sSecond, 2) & ZeroPad(Tail, 6)
End function
%> | |
|
|
Other 1 submission(s) by this author
|
|
| |
Report Bad Submission |
|
| |
| Your Vote! |
|
See Voting Log |
| |
| Other User Comments |
1/18/2001 2:51:07 AM: Joseph Miller
I must be stoopid to get no number out of these funtions...
*sigh*
(If this comment was disrespectful, please report it.)
|
1/18/2001 3:49:13 PM: Stephen
You could also try this?
Set DbConn = Server.CreateObject("ADODB.Connection")
dbConn.Provider = "Microsoft.Jet.OLEDB.4.0"
dbConn.ConnectionString = "Data Source=" & Server.MapPath ("data.mdb")
DbConn.Open
Set RS = Server.CreateObject("ADODB.Recordset")
MySQL = "SELECT * FROM title"
rs.Open MySQL, DbConn, 1,3
rs.addnew
rs("setdatetime") = now
rs.update
IDnumber = rs("ID")
rs.close
set rs = nothing
DbConn.Close
Set DbConn = Nothing
(If this comment was disrespectful, please report it.)
|
1/18/2001 6:19:03 PM: Eugene
Stephen, thanks for your update, however
I would suggest against this approach. You
may have situations where two network users
make a submission within the same second.
The NOW function only returns date and time
up to the seconds. You will end up with two
records with the same unique ID.
This is the reason for the 'Tail' which
is a 6 digit random number. This should
take card of the situation that I described
above.
Regards...
(If this comment was disrespectful, please report it.)
|
1/18/2001 6:23:49 PM: Eugene
Joseph, I have to disagree with you.
You are not too stoopid. You could
use the snippett of code that Stephen
provided, but replace the NOW function
with the GetIndexNumber function that
I provided.
Good Luck...
(If this comment was disrespectful, please report it.)
|
1/20/2001 12:13:16 PM: Yasmin
Thanx for this bit of information but i was interested in finding out the key Column of my recordset ie. the primary key of the table. plz b kind enough if u can help me out. bye
(If this comment was disrespectful, please report it.)
|
1/21/2001 2:28:43 AM: Shannon Harmon
You can also do this...
INSERT INTO YourTable(YourColumns) VALUES (YourValues)
SELECT @@IDENTITY AS 'Identity'
The @@IDENTITY will return the autonumber primary key.
(If this comment was disrespectful, please report it.)
|
1/21/2001 2:20:58 PM: Eugene
Sharon,
Yes, you can use this method, however it will return the primary key of the last inserted record. You could have a situation where someone inserts a new record between the time you execute your insert, and the time you execute the 'Select @@identity' statement. You will end up with the primary key of the record that they inserted, not the one you inserted.
(If this comment was disrespectful, please report it.)
|
1/22/2001 2:16:02 AM: Shannon Harmon
Eugene,
Is that true if you do it with a stored procedure call? That is how I do it always, I have a stored procedure write the data and return the identity at the same time. I hope that it will not cause me problems in the future, please respond if you get time. Thanks...
It's Shannon not Sharon, but that's a common mistake with my name:)
(If this comment was disrespectful, please report it.)
|
1/23/2001 9:16:14 PM: Eugene
Hello Shannon,
First, please accept my apologies for your name.
Yes, in a network situation with lots of hits, you always run the risk of interruption. Of course, if you know your environment, and you know that there are only a small number of users, then you can take that chance. You have to realize that in a network environment, even though your code is sequential, the CPU on the server is multi-tasking, or at least pseudotasking. Requests are processed by a bios logic preprocessor, and threads may or may not have anything to do with it.
It will ultimately be your decision as the applications programmer. However, I always suggest to my teams that we do not take that chance, especially when you have business critical app's.
Regards...
(If this comment was disrespectful, please report it.)
|
2/26/2001 9:27:36 AM: Robert Long
Wow!!!Great comments...Thanks all.
(If this comment was disrespectful, please report it.)
|
2/26/2001 8:34:20 PM: Craig
I normaly invoke a tiny locking procedure before dishing out the next insert record ID. It may delay a user by upto a second but this is worth the small wait to maintain 100$ integrity of recordsets.
Another mechanism I once used was if a user had a login name then the record ID can be calculated on thier loginID and grab the next number from a table of numbers maintaned for each user.
(If this comment was disrespectful, please report it.)
|
2/26/2001 10:23:11 PM: Eugene
Record locking is a pretty good idea Craig, however as a programmer, you have to ask the question, how many users will be hitting the application at any given time, and how long of a wait can you afford? I am assuming that you are locking the entire database before proceeding. This will inevitably lock other users from reading as well.
(If this comment was disrespectful, please report it.)
|
2/27/2001 6:42:10 PM: Stephen
IDnumber =
rs(
(If this comment was disrespectful, please report it.)
|
2/27/2001 6:43:03 PM: Stephen
rs("ID")
That is the new number that is generated when you create the new recordset and I just put the date and time thing just so you can have an indication of when the person log in, if 2 people have the same time it doesn't make any difference you are using the ID value what is an autonumber and no dups so who ever was there first will get the first number, the time and date was just if you wanted a record of when they got there number.
(If this comment was disrespectful, please report it.)
|
6/6/2001 10:54:28 PM: phanom
'Select @@identity' statement. will work and you won't get two users with the same number for that table you updated. the reason being is that the identity is unique for the specific connection.
phanom@teknorealm.com
(If this comment was disrespectful, please report it.)
|
10/2/2001 8:53:27 AM: steve
Does anyone know how to do this with Oracle?
(If this comment was disrespectful, please report it.)
|
11/12/2001 5:30:57 PM: Josh
I have been searching for days on how to do this.
I have a question, not sure if I should post here but.
I'm making a wizard type application that will insert into a autonumber table and from there to another page etc... I could not figure out how to get that ID to the next page. Thanks all, If you know of a tutorial or sample code for the project I'm working on (I'm sure it's been done before) Please let us know.
(If this comment was disrespectful, please report it.)
|
11/18/2001 12:07:18 AM: Eugene
Josh,
There are two ways to pass variables from page to page. If you want to use HTML, you canset hidden fields in your HTML, but that can get complicated.
Or you can use Session Variables. This may not be the preferred way of doing things, but it is easy to do, and as long as you don't get out of hand with them, you will not have any problems.
On the starting page, you would put: Session(
(If this comment was disrespectful, please report it.)
|
12/14/2002 8:11:07 PM:
When using your random number generator i get this error:
Error Type:
Microsoft JScript compilation (0x800A03F0)
Expected '{'
/autotest/TMPq12m074z6n.asp, line 98
ZeroPad = right(string(nDigits, "0") & trim(sWork), nDigits)
It works fine on a clean page, but when i want to use it in a already created dynamic page i get this error. I'm a newby to handcoding, so tips would be great!
(If this comment was disrespectful, please report it.)
|
12/14/2002 9:47:38 PM: Eugene
It sounds like you may need to add this clause
@Language=
(If this comment was disrespectful, please report it.)
|
12/15/2002 5:49:19 AM:
Dreamweaver already created <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>.
(If this comment was disrespectful, please report it.)
|
12/15/2002 10:53:53 PM: Eugene
Please excuse my incomplete message... You may need to add the statement:
<% @language=VBScript %>
at the top of the script. The fact that dreamweaver tries to default the language to Javascript explains the error you received.
(If this comment was disrespectful, please report it.)
|
1/20/2003 7:57:34 AM:
Eugene,
Could you possible post the complete code as explained in your post to Josh - doesn't seem to have shown up for some reason.
Many Thanks, Craig
(If this comment was disrespectful, please report it.)
|
1/13/2004 6:30:48 PM:
A very useful bit of code. I used it in my ASP app as an include file - no modifcation. Works perfectly.
Thanks
(If this comment was disrespectful, please report it.)
|
1/13/2004 6:32:46 PM:
A great littel piece of code Eugene. I used it as is in an include file at the top of my code page and drew out the variable later on. Works perfectly
Thanks
(If this comment was disrespectful, please report it.)
|
|
|
Add Your Feedback! |
Note:Not only will your feedback be posted, but an email will be sent to the code's author from the email account you registered on the site, so you can correspond directly.
NOTICE: The author of this code has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular code, please click here.
|
To post feedback, first please login.
|
