help with loop in code

[Richey]

Hi

I've got this bit of code which basically checks that the username a customer has chosen hasn't been already used. If it has then it says either try again or it sugessts one by concatenating their effort with the @@identity. I'm missing a loop or something though because when it gets to the line

llIDnew = left(str, 8) & "" & llID
i need to check that the value of llIDnew doesn't exist also or either try another one, if you see what i mean ! (i.e. insert another record into tblUserNames get the @@identity from that record and try that again until it gets an unused one)

any ideas appreciated
richey


SQLNameCheck = "SELECT UserName from tbluserNames where UserName = '" & cleansql(str) & "'"
set objRSName = objCon.Execute(SQLNameCheck)
if not objRSName.eof then
ErrorMsg1 = ErrorMsg1 & "The UserName has been already used - "
ErrorMsg1 = ErrorMsg1 & "Please try to select a unique username; you may want to try adding numbers to your username,
(e.g.johnsmith1234). "
SQLSuggestName = "INSERT INTO tblUserNames(UserName)"
SQLSuggestName = SQLSuggestName & " VALUES ("
SQLSuggestName = SQLSuggestName & "'" & cleansql(str) & "'"
SQLSuggestName = SQLSuggestName & ")"
objCon.execute(SQLSuggestName)
lsSQL = "SELECT @@IDENTITY AS NewID"
Set loRs = objCon.Execute(lsSQL)
llID = loRs.Fields("NewID").value
llIDnew = left(str, 8) & "" & llID
ErrorMsg1 = ErrorMsg1 & "Alternatively use " & left(str,8) & "" & llID & "<br></br>"
else
end if

 

[Chopstik]

Perhaps I am being thick, but I am not entirely certain I understand what you are trying to accomplish. Here's my understanding from what you have posted and you can clarify for me so that we can try to help you fix the problem you are having.

You check to see if the name already exists in your table/database. If it does exist, then you create an error message and then you create a new SQL string that will insert that SAME record into your database (?!) and then return the identity value (do you have an identity value in this table?) of the last inserted record. You then create a new value from this record and include this as part of your error message to the user (is this supposed to be a suggestion of what they could use as an alternative?). I'm sorry, but I do not understand why you would attempt to insert the same record into your table if you know it is already there.

If you could explain what kind of an error you are receiving, it might help to find a resolution.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[DotNetGnat]

Shouldnt it follow something like this...

1. check if the username already exits
2. If exists throw an error else add the new user
3. In case there is an error we also present the user with alternate choices or ask him to select his own username
4. Perform step 1 again


-DNG

 

[Richey]

yeah I agree but

say someone has added a record 'password12' - inserts fine -we have another 10 entries which are ok - then on the 12th someone tries 'password12' - it says sorry already used, gets the @@identity which is 12 and would then suggest password12 !?! which is no good, so it inserts the entry (into tblUserNames which just holds id numbers and usernames) comes back and tries password13 which is ok and would then insert into the main table.

thanks

 

[Chopstik]

Are you receiving an error message? Or, what exactly is the problem that you are encountering? From the code you have above, and as I noted in my first post, you appear to be first searching for the UserName the user entered. If that name already exists, then you are inserting (or attempting to, anyway) it into your table anyway and pulling the identity value of the record you just attempted to insert. The problem, as I see it, is that your insert will fail (if your db is not set up to allow duplicate UserNames) and thus you are not retrieving an identity value.

But it would be more helpful if you could post exactly what is happening or any error messages you are encountering. That said, I will also add that I am in agreement with DNG as to how I would approach it.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)