generating UserIDs

[lunargirrl]

Hello,
I had a problem constructing this code and some friends from this forum helped me I'm still thankful for that!
The objective is to generate userId's taking the first character of the user's first name and the whole last name.

The problem now:
e.G. Marianne Peterson
Mariah Peterson

my code generates mpeterson01 for both.
How can implement my code that if mpeterson01 already exists in my db, it generates a second one: mpeterson02??

I tryed to make that but I dont have a clue how to threat the 01, 02, etc.


**************************************************
StringArray = Me.txtName.Text.Split(" ")
strName = StringArray(StringArray.Length - 1)

Dim strResult As String
strResult = Left(txt_Name.Text, 1) & strName & "01"
***************************************************

tia,
Gis.

 

[jzelhart]

Do you have a table with the UserIDs listed? If so, why don't you open a recordset the strName and a wild card. If you don't come back with any records, then you can use the 01. If you do, you can open another recordset with 02 to see if you find a match etc.

Hope this helps.

Hope everyone is having a great day!

Thanks - Jennifer

 

[lunargirrl]

Jennifer, I think I didnt understand what you mean. sorry

I thought about making a counter.

for example, i make a select command in my tbl, to check if i have the value mpeterson01, if my dataset returns something then I add '02' to the name mpeterson, if I need to create mpeteron03,04,05... I see the complication, I wanted to make it dinamically using a counter = counter + 1, but how can i retrieve the maximum value of my table if its a char field? Maybe there's a better way to do that.

tks,
gis.

 

[jzelhart]

I would do it the way you are mentioning except use a For Loop. Check for a match in the loop. If there is a match keep going, if not then exit the for statement.

For i = 1 To 25
'Set UserID with i in proper format
'Open the dataset to see if records are returned
'if record found go to next
'if not, then exit for and use the UserID with that number
Next

Hope this is more clear.

Hope everyone is having a great day!

Thanks - Jennifer

 

[cappmgr]

How about selectiing the name using your like function and order by clause
assumption is userid is lower case

SELECT userid
FROM userid_table
WHERE userid LIKE 'mpeterson%'
AND userid NOT LIKE 'mpeterson[a-z]'
ORDER BY userid DESC

No resultset means make your first mpeterson01
if you have a result take the first record parse out the number from the userid figure out your next sequence number
and insert.

Your life might be easier if your userid was a composite of two fields username and username_seq_no.

Just a thought.
Marty

 

[edelwater]

I agree with Marty. Suppose you need this for creating a unique userlogon and store this information in ldap and you have this helper util...

Simple Database Normalization :

[ firstname_firstchar ] [lastname] (table 1)

In table 1 there is nothing unique to find for a primary key. So you have to make it unique by

[ firstname_firstchar ] [ lastname ] [ ID ]

Ofcourse this is from a database point of view but if you store the unique userids in a new table...it would save you a table and you could have the relations running from the original table, you also wouldnt need to create something like this :

[firstname][lastname][unique_user_id]

but it could simply be

[firstname][lastname][id]

since parts of the information in the [unique_user_id] would somehow be already in available in the other fields.







--------------------------------------
deleau@gmail.com

http://de-leau.eigenstart.nl

 

[BoulderBum]

Can you define the user name as unique, the ID as an identity or GUID, and simply have a validation routine to tell the user when a given user name is taken?