Why do I always have funky issues with SQL in FM? Here's another...???

[angelleye]

I'm trying to build a very simple login for a web page. I've created a table in FM to store username's and password's. Right now I have 2 records in there:

testUser1 testPW1

testUser2 testPW2

When I create a recordset like:

SELECT * FROM POWebUsers

I indeed get a recordset with both records in it. However, if I try and check for values in username and password to authenticate a user:

SELECT * FROM POWebUsers WHERE username = 'testUser1' AND password = 'testPW1'

I get an empty recordset!? What on earth is going on here that's causing me to waste so much time on such a simple task? Any information would be greatly appreciated. Thanks!

[angelleye]

SELECT * FROM POWebUsers WHERE password <> 'testPW1'

successfully returns only the testUser2 record.

Then I try:

SELECT * FROM POWebUsers WHERE username <> 'testUser2'

And I get both records returned! I'm as confused as I've ever been.

[The Shadow]

username is a reserved word in standard SQL. It is likely returning the name you're logged in as. Try changing your field's name.

[angelleye]

Ok, now I've switched the field names from username and password to varUsername and varPassword. Then when I try

WHERE varUsername <> 'testUser2'

it works. However, I still can't make the whole thing work:

WHERE varUsername = 'testUser2' AND varPassword = 'testPW2'

[angelleye]

Ok...actually it does seem to be working now. I guess that did it. Thanks for the info!