Jump to content

Effectively creating an unused ID number


Eivo
 Share

Recommended Posts

I'm looking for advice on an effective way to create an unused 4 digit identification number to be stored into a database.I thought randomly creating a number and checking for it in a database would may be too intensive (would it be?) and not a great way to do this.I also thought about sorting all the ID numbers, moving to the last one, adding 1 to it, then storing it. But that would skip over any ID's that have been deleted.Any advice?

Link to comment
Share on other sites

To answer part of the question: assuming you have a halfway decent ISP, searching through a database of 10,000 records with a simple match or index routine will seem instantaneous, much much faster than the time it takes to send the results back to the client. I do a similar thing in perl and it's like Boom! here's your result. In my case each record has maybe 8 fields for maybe 50-80 chars per record, so the file is maybe .5 MB. So yeah, don't sweat the search.

Link to comment
Share on other sites

Ok, then that leads me to more of a technical question. I never had to code anything like this before. Currently I connect to my DB like this...

Dim connection_object, sql_command, recordsetSet connection_object = Server.CreateObject("ADODB.Connection")Set recordset = Server.CreateObject("ADODB.Recordset")connection_object.Open "Provider=sqloledb; Data Source=******; Initial Catalog=******; User Id=******; Password=******;"sql_command = "SELECT * FROM client WHERE username LIKE '" & form_name & "' AND password LIKE '" & form_pass & "'"recordset.Open sql_command, connection_object' --- Some  Code ---connection_object.CloseSet connection_object = Nothing

If I wanted to check for a existing number what would be the best way to check the database? Maybe like this...

Dim connection_object, sql_command, recordsetSet connection_object = Server.CreateObject("ADODB.Connection")Set recordset = Server.CreateObject("ADODB.Recordset")connection_object.Open "Provider=sqloledb; Data Source=******; Initial Catalog=******; User Id=******; Password=******;"Dim match_uid, min, max, random_numbermatch_uid = 0max=9999min=1000Do While match_uid = 0random_number = Int((max-min+1)*Rnd+min)sql_command = "SELECT * FROM client WHERE user_id LIKE '" & nf_userid & "'"recordset.Open sql_command, connection_objectIf recordset.EOF Then	match_uid = 0Else	match_uid = 1End IfLoop

Would that work? Is there a better / easier way? Thanks!

Link to comment
Share on other sites

Well the User ID number will only be unique to the account number, which is another field.Something like this...Account # ----- User ID54265847 ----- 458454265847 ----- 215454884125 ----- 4584I hope that helps and doesn't look like crap. So from the *cough* table above, the User ID (which is the number I'm concerned with) can be repeated, but can not be repeated under an Account Number.Could a auto_incrementing / autonumber ID field have this rule built into it?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...