Preventing identical records


I would like the users on my website to be able to change their username to anything they desire. But how do I prevent identical usernames?

I know how to update a database, but how to check for identical records, thats the thing!

Any help highly appreciated!!


Who is Participating?
TerryInOhioConnect With a Mentor Commented:
I use Access too.

Public cnnTig As ADODB.Connection
dim strUserNameEntered as String
Public Const DB_PROVIDER = "Microsoft.Jet.OLEDB.4.0"
Public Const StrDataBaseDirectory = "C:\Benefits"

strConnectionString = "Provider=" & DB_PROVIDER & ";Data Source=" & StrDataBaseDirectory & "\" & DB_NAME

cnnTig.Open strConnectionString
strSQL = "SELECT * FROM UsersTable where UserName = '" &  strUserName & "'"
RSwork.Open strSQL, cnnTig, adOpenStatic, adLockOptimistic, adCmdText
If RSwork.RecordCount < 1 Then
       RSwork.Fields("UserName").Value = strUserName
       Msgbox "Use Id " & strUserName & " has been added to the database".
       Msgbox "Use Id " & strUserName & " Already Exists in the database".
end if
Set RsWork = Nothing

If the field that you have the username stored in as a unique key - if you try to insert a duplicate record then you will get an error message if it is duplicated - so if you capture this error then you will know it already exists.

To make sure they did not choose the same user name as another person, do a querry on the database if you come back with 0 records, no one has it and it is safe to insert.  If you come back with a record then put up a message that it is in use.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Ariful AmbiaHead of MISCommented:
      U don’t tell what database ur using. If you use MS SQL server u can follow the steps below.
1. When u create unique constraint u will get a constraint name say “IX_TblUser”

2. Put your Update code in Try catch. When the Try catch got any error due to violation of the constraint “IX_TblUser” it will automatically give u a error with description that the “IX_TblUser” constraint has been violated so this transaction can’t be execute.

3. Create a function (say ErrorEncodedDesc(ErrorDes as String) AS string) which will parse the error description that is given by the database and look for predefine constraint names to match. And when get a match a more user friendly message will return for the function. For example when the “IX_TblUser” constraint violated the error description will pass into the ErrorDes parameter of the function ErrorEncodedDesc. The function will then look for the constraint IX_TblUser in the error description string. When it finds a match the function will return a clearer message to the user say (User name already exists.)

4. After giving the Clear message to the user that the Update failed due to this and this, u don’t have to anything farther coz database already reject the Update.

Hope it will help u.


after putting value in username and password,
do the following steps,
1> check in the database weather ther username is exists or not.
       like : select * from user where username='username';
2>Fire above quary and bind the recordset or datatable
      If recordset or datatable length is greater then 0 then username exists in the database and put the appropriate message.
      IF recordset or datatable length is 0 then username not exists in the database , so u can allow this username

micambAuthor Commented:
Thanx for all the quick responses!

I'm using access at the moment. How would the query look doing that?


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.