Help - ASp Username Check

El_mariachi

I want to check one piece of my form data, the user name, against an access database to see if someone is trying to re-register the same user name.

How do i do this?

the table i am using is called MemberDetails
The field is called UserName

Evil Phil

Sql count function should do the job.

El_mariachi

I still can't get it working
<%
Dim adoCon 'Holds the Database Connection Object
Dim rsAddMemDetails 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database
Dim strSQLCheck 'Holds the SQL to check if username is already taken
Dim UName

UName=("Sarah")

Set adoCon = Server.CreateObject("ADODB.Connection")
'DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/Members.mdb")

Set rsAddMemDetails = Server.CreateObject("ADODB.Recordset")
strSQLCheck = "SELECT COUNT(*) FROM MemberDetails WHERE UserName='Sarah';"

rsAddMemDetails.Open strSQLCheck, adoCon


If strSQLCheck = "1" then
Response.Write("Name is taken")

else

rsAddMemDetails.Close
Set rsAddMemDetails = Nothing
Set adoCon = Nothing
Response.Redirect(index.html)

End if

%>

Evil Phil

I can't remember vb well enough to give you code but you need to check if the first record and/or first field of rsAddMemDetails is equal to 1 not strSQLCheck. strSQLCheck is a string.

Ginger

-try the following instead

strSQL = "SELECT Username FROM MemberDetails WHERE Username = 'Sarah';"

objRST.Open strSQL

IF ObjRST.EOF AND objRST.BOF Then
  Response.Write "Username is free"
Else
  Response.Write "Username taken"
End IF

Checks to see if your recordset is empty or not if it is .. username isnt tajken

Ginger

El_mariachi wrote:

I still can't get it working
<%
Dim adoCon 'Holds the Database Connection Object
Dim rsAddMemDetails 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database
Dim strSQLCheck 'Holds the SQL to check if username is already taken
Dim UName

UName=("Sarah")

Set adoCon = Server.CreateObject("ADODB.Connection")
'DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/Members.mdb")

Set rsAddMemDetails = Server.CreateObject("ADODB.Recordset")
strSQLCheck = "SELECT COUNT(*) FROM MemberDetails WHERE UserName='Sarah';"

rsAddMemDetails.Open strSQLCheck, adoCon


If strSQLCheck = "1" then
Response.Write("Name is taken")

else

rsAddMemDetails.Close
Set rsAddMemDetails = Nothing
Set adoCon = Nothing
Response.Redirect(index.html)

End if

%>

You dont check strSQLCheck you check rsAddMemDetails.Fields(0). The code I posted above is slightly easier as you still have to check for NULL recordsets. If you read your code you will see you are checking for the value of strSQLCheck which will not change as you have already set it to SELECT COUNT(*) FROM MemberDetails WHERE UserName='Sarah';"


Anyways if you wanted to use the code above use the following

rsAddMemDetails.Open strSQLCheck, adoCon

      If CInt(rsAddMemDetails.Fields(0)) > 0 Then
       Response.Write "Username is Taken"
     End If
'This code doesnt check for any common problem use at own risk!!!!

Eoin

Is the username set to be unique in the database?

In the table design, it should be under "Indexed: Yes (No Duplicates)". If you then try to add another record with the same value, the error number returned is -2147217900

On Error Resume Next
objConn.Execute(sSQL)
If err.number <> 0 Then
    If err.number = "-2147217900" Then
	sErrorMessage = "This username has already been registered"
    Else
        ' another error occurred, be sure to handle.
    End If
Else
    ' You are good to go
End If

I don't know if it is the cleanest solution though.

Ginger

Not really the cleanest solution as it doesnt allow if the system changes db backends for one.

I wouldnt encourage On Error Resume Next especially on starting to write ASP pages because it can give the impression that the function has worked if not all likely errors are trapped.

Eoin

Ginger wrote:

Not really the cleanest solution as it doesnt allow if the system changes db backends for one.

I wouldnt encourage On Error Resume Next especially on starting to write ASP pages because it can give the impression that the function has worked if not all likely errors are trapped.

Both good points, in fact after a quick google, I don't think that error code refers specifically to a duplicate row. I'd still recommend making the field unique in the database though, even if you use a different method in the ASP to check.

Ginger

Yup .. good idea will ensure that it cant be deplicated

El_mariachi

emmm sorry guys bout an hour after i post the code i changed it and got it working Thanks for ur input though