ASP - check username loop - going insane

I think I need some kind of nested loop - but my brain won't process it

[Stratus42]

hey folks,

I'm loosing what's left of my mind on this one. I have a user registration page where folks can register to use my customer suport area of my website.

When they sign up, a username and a password is created for them. I build the username out of their last name and first initial.

I need the usernames to be unique though, so If I have more than one Jsmith - my whole system falls apart.

so.. I want to assign them a username, then check the database (access) to see if it's already there.. if it is, I want to assign them the same name with a number on it.. (ie: if JSmith is taken, they become JSmith2).. then I want to check the database to see if my newly assigned name is taken. if it is.. I want to keep looping through the second name assignment routien untill we come up with one that is not. so in the end.. we may end up with username JSmith945 right?

ok.. this is what I have.. but I can't figure out how to do what I've just said..

please.. if anybody can help.. <bow..bow...scrape..scrape.. grovel grovel>

adoCon.Open cString
strSQL="SELECT * FROM tbl_users"
set rs_check_username=server.CreateObject("ADODB.Recordset")
rs_check_username.open strSQL,adoCon '
'time to construct a unique username for them using their first initial and their last name
str_lastname = Trim(Request.form("surname"))
str_firstname= Trim(Request.form("firstname"))
str_firstinitial= UCase(Left(str_firstname, 1))
str_username = str_firstinitial & str_lastname

'this is the bit that checks to see if the username is already in use
Do While not rs_check_username.EOF
str_db_username = rs_check_username("name")'Set variable to hold a database username value
'see if the variable is unique, if it is not, add the number 2 to the end of the username and check the whole recordset again
IF str_db_username = str_username THEN
dim number
number=2
str_username=str_username+number
rs_check_username.MoveNext
Loop
rs_check_username.Close
set rs_check_username_again = server.CreateObject("ADODB.Recordset")
rs_check_username_again.open strSQL,adoCon

Do While not rs_check_username_again.EOF
str_db_username = rs_check_username_again("name")
IF str_db_username = str_username THEN
number= number+1
str_username=str_username+number
rs_check_username.MoveNext
Loop

Set rs_check_username = Nothing
adoCon.close

:-)

Lana

[txbakers]

in psuedocode:

IF request to create new user THEN
create newuser code
run query against user table to check for that code

IF above query is EOF THEN
not in database, proceed with insert
ELSE username already exists THEN
modify username
response.write new username (or show the user the possibility)
END IF
END IF

[martyt]

Why not let your users define their own userid? They're a lot more likely to remember something they made up.

Or use their e-mail address as the userid.

Then you don't have to go through the silliness of assigning someone the ID "JSmith303"...

But if you don't want to do that, try re-writing your query to return only a subset of the records.

First build the proposed username

Then modify your query like so (I presume 'name' is the name of your database column that has the userids in it):

strSQL = "SELECT * from tbl_users where name like "+str_username+"% ORDER BY name"

If you get an empty recordset, the userid doesn't exist and you're done.

If you get a non-empty recordset you know that at least 1 person shares some or all of the username you're proposing. (you might have a JSmith and a JSmithsonvanderbergski already). So iterate through the recordset, looking for records that match the root portion of your proposed name and have one or more numbers following. Find the last of those records, parse out the number, add 1 and there's your new username.

Of course this all falls apart when two JSmith's try to register simultanously, as the results are unpredictable.

Which is why you'd do better to let them pick their own userid.

[f00sion]

get rid of the first loop, build the username before executing the first query, then you can add a where clause to quickly see if that name already exists... looping through every record and checking for a match in asp is incredibly inefficient.

[Easy_Coder]

FYI.... this code

SELECT * FROM tbl_users

causes a total table scan to occur

I would suggest creating a single stored procedure that accepts your user defined data as parameters. Within the sproc you can perform existance checks, validation... everything and then passback a bit flag or something that indicates success...

Need a sample? Let me know

[duckhunter]

Easy_Coder is right. I do just that for one of my sites. Here's one of my procs for checking the Admin user table. Get a Return Value of 0 You know you can insert a new record. You can even have this proc do the insert if you want rather than Returning a True/False.

CREATE PROCEDURE dbo.CheckUser
@username varchar(15),
@RETURNVAL int OUTPUT
AS
IF EXISTS(SELECT USERNAME FROM ADMIN WHERE USERNAME = @username)
BEGIN
SELECT @RETURNVAL = 1
RETURN
END
ELSE
SELECT @RETURNVAL = 0
RETURN

Calling it in T-SQL:
DECLARE @RETURNVALUE INT
EXECUTE CheckUser 'testusername', @RETURNVAL = @RETURNVALUE OUTPUT
SELECT @RETURNVALUE

Call the Stored Procedure from VB Script with the ADODB.COMMAND Object and Append the parameters you need as input as well as the OUTPUT parameter.

P.S. If you user table is large, be sure to index the Username column to speed up this search as it is text based.

[Stratus42]

wow.. thanks for the suggestions folks.

I had originally had it set up so that the person chose their own username - but I changed it.. I might change back - since this dosn't look like it's going to work for me.. the biggest issue with the way I had it before was.. when I was testing.. 1 time out of 15, if a person picked a username that was already in the database - my routein - (the above one really.. with a small variation) - would miss it!

So.. say my username is "lana" - and somebody else tried to register in with "lana".. 1 time out of 15 or 20 - it would LET THEM.. the other times it would appropriately direct them to the "sorry, that user name is already taken " page .

but.. the WORST part was.. it would then go and email them the password that goes with the original name. so.. i'm "lana".. I have super-user rights - newbie user gets sent my password and suddenly has the rights to do anything they like.. include delete all the other administrators (and me) and access personal information about other clients.

very very very wierd.. so I figured.. right.. nobody gets sent username or password till we validate them..

I could not discover why it was doing it.. so I thought I'd fix it by creating a procedure that would make it very unlikely for somebody to try submitting "lana" 40 times till it is accepted and they get sent my password.. the best way I figured would be <a> don't let them choose a username and <b> don't send them their username/password till they are validated.

Anyways..

thanks so much for the advice - I will change from my current method of "select * from tbl_users" to something a little more efficient..

and MARTYT - thanks.. that was very helpful - I'll work with that idea for a bit today - I need to take a more advanced ASP course or a SQL query string course or something - I forget what is possible -

duckhunter - erm.. thanks.. but I'm afraid that's over my head.. you've lost me completely :-)

back to the grindstone.. cheers folks

Lana