Login Form (1 Viewer)

[moi]

Hi all,

Can someone please examine the code
I got this code from a youtube video, i can't figure out what is wrong, it is working in the youtube video.

If IsNull(DLookup("userID", "tbluser", "username = '" & Me.txtUserName & "' and password = '" & Me.txtPAssword & "'")) Then

When run the above code, i got this error message "Run-time error '2471' - The expression you entered as a query parameter produced this error: 'password'.

Username = text
Password=number

Thanks you.

 

[pbaldy]

If password is a numeric field try

If IsNull(DLookup("userID", "tbluser", "username = '" & Me.txtUserName & "' and password = " & Me.txtPAssword)) Then

 

[plog]

Divide. Isolate. Conquer.

DLookup("userID", "tbluser", "username = '" & Me.txtUserName & "' and password = '" & Me.txtPAssword & "'")

You've got 3 strings in DLookup (the field, the table, the criteria) which means you have 3 places you can screw up. The field and the table are simple strings which means its less likely you screwed them up. But let's be sure. Remove the criteria from the Dlookup to see if it works with just the field and table data:

DLookup("userID", "tbluser")

Does the above work? If not, then you have mistyped either the field (userID) or the table (tbluser). If it does, then its time to work on your criteria and we use the same divide and isolate method. Instead of replacing the whole criteria string, just put back in the username portion. When that works, put back in the password portion). Whenever you get unexpected results or an error message dig back into the last thing you changed.

Lastly, when you are just trying to find out if data is in a table and don't really care about returning a field, do not use a Dlookup. A Dcount would much better suit your purpose of the above code. DCount will never return NULL and therefore don't have to account for it. If no records match it will return a 0. That way you don't need the NZ and makes things cleaner and less complicated.

 

[Pat Hartman]

When you are building complex strings, the best solution is to build them into a variable. Then you can print the varialble to see what you ended up with.

dim strWhere As String
strWhere = "username = '" & Me.txtUserName & "' and password = '" & Me.txtPAssword & "'"
If IsNull(DLookup("userID", "tbluser", strWhere)) Then

You put a breakpoint on the If and then use debug.print to print the strWhere value.

 

[moi]

When you are building complex strings, the best solution is to build them into a variable. Then you can print the varialble to see what you ended up with.

dim strWhere As String
strWhere = "username = '" & Me.txtUserName & "' and password = '" & Me.txtPAssword & "'"
If IsNull(DLookup("userID", "tbluser", strWhere)) Then

You put a breakpoint on the If and then use debug.print to print the strWhere value.

how i wish that i do have a deeper knowledge on code.. at the moment i can create a simple db through "wizard".. and offcourse from this forum with the help of generous experts.

am trying hard to learn..

 

[moi]

Divide. Isolate. Conquer.

DLookup("userID", "tbluser", "username = '" & Me.txtUserName & "' and password = '" & Me.txtPAssword & "'")

You've got 3 strings in DLookup (the field, the table, the criteria) which means you have 3 places you can screw up. The field and the table are simple strings which means its less likely you screwed them up. But let's be sure. Remove the criteria from the Dlookup to see if it works with just the field and table data:

DLookup("userID", "tbluser")

Does the above work? If not, then you have mistyped either the field (userID) or the table (tbluser). If it does, then its time to work on your criteria and we use the same divide and isolate method. Instead of replacing the whole criteria string, just put back in the username portion. When that works, put back in the password portion). Whenever you get unexpected results or an error message dig back into the last thing you changed.

Lastly, when you are just trying to find out if data is in a table and don't really care about returning a field, do not use a Dlookup. A Dcount would much better suit your purpose of the above code. DCount will never return NULL and therefore don't have to account for it. If no records match it will return a 0. That way you don't need the NZ and makes things cleaner and less complicated.

it shows the same error (Run-time error: 2471)..however if i hoover the mouse over the txtusername and txtpassword control it shows correct value..

 

[plog]

So did it work in the 2 steps I said you should take prior to that?

 

[moi]

So did it work in the 2 steps I said you should take prior to that?

Yes because when i hoover over the username and password, it shows the correct value..

 

[moi]

If password is a numeric field try

If IsNull(DLookup("userID", "tbluser", "username = '" & Me.txtUserName & "' and password = " & Me.txtPAssword)) Then

nope, it throws the same error..

 

[moi]

When you are building complex strings, the best solution is to build them into a variable. Then you can print the varialble to see what you ended up with.

dim strWhere As String
strWhere = "username = '" & Me.txtUserName & "' and password = '" & Me.txtPAssword & "'"
If IsNull(DLookup("userID", "tbluser", strWhere)) Then

You put a breakpoint on the If and then use debug.print to print the strWhere value.

it shows the (strwhere = "username = 'moi' and password = '123'"

 

[Josef P.]

If you test the where expression in a query, the error may show up better.

dim strWhere As String
dim TestSql sa String

strWhere = "username = '" & Me.txtUserName & "' and password = '" & Me.txtPAssword & "'"
Debug.Print "strWhere: "; strWhere

TestSql = "select * from tbluser where " & strWhere
Debug.Print "TestSql: "; TestSql
stop ' copy TestSql from immediate windows and paste into an Access query (SQL view).

If IsNull(DLookup("userID", "tbluser", strWhere)) Then
...

BTW:
Try this password: 1' or ''='
... SQL injection!

 

[Gasman]

If the password is actually a number (type), then you do NOT use quotes around it, that is for strings.


Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

 

[moi]

If the password is actually a number (type), then you do NOT use quotes around it, that is for strings.


Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

hi, here is the original code:
If IsNull(DLookup("userID", "tbluser", "username = '" & Me.txtUserName & "' and password = '" & Me.txtPAssword & "'")) Then

I tried all the suggestions above, but it throws same error..

 

[Josef P.]

I tried all the suggestions above ..

Somehow I don't believe it.

What is the data type of the password data field in the table?

Test in Access query:
select * from tbluser where username = 'moi' and password = '123'
vs
select * from tbluser where username = 'moi' and password = 123

 

[Gasman]

hi, here is the original code:
If IsNull(DLookup("userID", "tbluser", "username = '" & Me.txtUserName & "' and password = '" & Me.txtPAssword & "'")) Then

I tried all the suggestions above, but it throws same error..

YOU HAVE NOT !!! as you still have quotes around the password?
Until you get the criteria correct, a Dcount() is not going to help either.

What type of data is Password in the table?

Put all the criteria into a string variable and post the result of the debug.print back here withing code tags.

 

[Pat Hartman]

That is at least four times someone has asked what the data type of the password column is. Maybe you should answer. I find it hard to believe that it is numeric but what do I know.

 

[cheekybuddha]

BTW:
Try this password: 1' or ''='
... SQL injection!

Came here to say this.

Do not use this method to verify your users.

 

[Pat Hartman]

I know you are talking about SQL injection but I can't figure out what your SQL statement would need to be for that string to cause a problem.

 

[Josef P.]

Dim UserName As String
Dim Password As String
Dim strWhere As String

UserName = "xyz"
Password = "1' or ''='"

strWhere = "username = '" & UserName & "' and password = '" & Password & "'"
Debug.Print strWhere

Simple to fix:

strWhere = "username = '" & replace(UserName, "'", "''") & "' and password = '" & replace(Password, "'", "''") & "'"

However, this has nothing to do with the error.

 

[Pat Hartman]

I guess this is just another reason to not use embedded SQL but to use saved querydefs instead. I can't get the injection to work with a querydef that takes arguments. Also the query wouldn't work anyway with -- password = '1' or ''='
The syntax doesn't work.

    Set db = CurrentDb()
    Set qd = db.QueryDefs!qValidateUser
        qd.Parameters!EnterNetworkId = Me.txtNetworkID
        qd.Parameters!EnterPassword = Me.txtPassword
    Set rs = qd.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    If rs.EOF Then
        MsgBox "Invalid user or password.", vbOKOnly + vbInformation
        gUserID = 0     'invalid userid
        Exit Function
    Else
        gUserID = rs!UserID
    End If