Effectively creating an unused ID number

[Eivo]

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?

[jeffman]

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.

[Eivo]

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!

[Synook]

Why don't you just create an auto_incrementing / autonumber ID field?

[Eivo]

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?