problem with if...or...then statement

lightmaker · June 4th, 2008, 02:17 PM

Hello,

I've got a problem with an if... or... then statment

f_name=request.form("f_name")
password=request.form("password")

path=server.mappath("mytfasim.mdb")
set conn=server.createobject("ADODB.Connection")
conn.open "driver={Microsoft Access Driver (*.mdb)}; DBQ=" & path
set rc=conn.execute("select * From tabla1 Where f_name='"& f_name&"'")

if rc.eof or ("&rc.fields("password")<>password&") then
session("userchek")="false"
response.redirect pleaseregister.htm
else
session("userchek")="true"

Microsoft VBScript compilation (0x800A03EE)
Expected ')'
end if

Old Pedant · June 4th, 2008, 03:35 PM

Learn what a *STRING* is!

That is, what constitutes a LITERAL (quoted) string.

A literal string starts with a " mark and continues until the VERY NEXT " mark. *EVERYTHING* between the two " marks is considered JUST CHARACTERS and is *NEVER* considered to be code.

So in your line:

if rc.eof or ("&rc.fields("password")<>password&") then

You have two strings. As shown here:

if rc.eof or ("&rc.fields("password")<>password&") then

Remember what I said: Characters inside quote marks are *NOT* code.

So you could just as easily have written

if rc.eof or ("zambonis"password"rule") then

And so the compiler breaks that down as
    ... or ( [string] password [string] )
and since password is MEANINGLESS to VBScript, of course you get an error!

What I *THINK* you were after would be this:

    if rc.eof or (rc.fields("password")<>password) then

BUT!!! But that *STILL* won't work!

The reason: VBScript doesn't use "shortcut" operators for AND and OR. So *all* the parts of an AND or OR logical expression are evaluate *BEFORE* the AND or OR is actually used.

Which means *NO MATTER WHETHER you are at the EOF or not, VBScript will *STILL* try to execute the
     rc.fields("password")
code! So if you *ARE* at EOF, then you will get an error from that code!

....

So...let's TOSS OUT all your code and rewrite it. And, in the process, we will protect you against SQL Injection attacks:

Code:

f_name = Trim(request.form("f_name"))
password = Trim(request.form("password"))

path = server.mappath("mytfasim.mdb")
set conn=server.createobject("ADODB.Connection")
' you *REALLY* should *NOT* use the Access Driver!  
' it is BUGGY and unreliable compared to the JET OLEDB driver
conn.open "driver={Microsoft Access Driver (*.mdb)}; DBQ=" & path

SQL = "SELECT * From tabla1 " _
    & " WHERE f_name = '" & Replace(f_name,"'","''") & "' " _
    & " AND [password] = '" & Replace(password,"'","''") & "'"
    ' (we must put [...] around the field name because password is keyword in Access)

SET rc = conn.execute( SQL )
If  rc.eof Then
    rc.Close
    conn.Close
    session("userchek")="false"
    response.redirect "pleaseregister.htm"
    ' No need for "ELSE" here!  Redirect happens *immediately* and
    ' no code after the redirect will be executed!
End If

session("userchek")="true"
...
%>

lightmaker · June 5th, 2008, 12:31 PM

well I've found my mistake since I've posted this post last night, and I've fixed it this way :

path=server.mappath("mytfasim.mdb")
set conn=server.createobject("ADODB.Connection")
conn.open "driver={Microsoft Access Driver (*.mdb)}; DBQ=" & path
set rc=conn.execute("select * From tabla1 Where f_name='"& f_name&"' and password='"& password&"'")

if not rc.eof then
session("userchek")="false"
Response.redirect "pleaseregister.htm"
end if
session("userchek")="true"
Response.redirect "main.htm"

and I no longer get an error, but.. the problem is it always skips the if, it doesn't matter wiether the user exists or not.

P.S. I've tried your way it skips the if too, and what should I write instead of ADODB.Connection?

Old Pedant · June 5th, 2008, 03:20 PM

My mantra: DEBUG DEBUG DEBUG DEBUG

First of all, you have your test of the EOF *BACKWARDS*!!!

Your code is

Code:

    if not rc.eof then
         session("userchek")="false"
         response.redirect pleaseregister.htm

But you need to understand that IF ANY matchin record *IS* FOUND in the table you will *NOT* have rc.EOF.

So your code is saying "If I found that user and password in the table, then the check is false, so go make him register"!!!!

BACK-ASSWARDS!

You need to change
if not rc.eof then
to simply
If rc.EOF Then

IF THAT DOES NOT FIX IT...

Then time to DEBUG.

Try this:

Code:

...
conn.open "driver={Microsoft Access Driver (*.mdb)}; DBQ=" & path

SQL = "select * From tabla1 Where f_name='"& f_name&"' and password='"& password&"'"
Response.Write "DEBUG SQL: " & SQL & ""

Set rc = conn.Execute(SQL)
Response.Write "DEBUG rc.EOF is " & rc.EOF & ""
If Not rc.EOF Then
    For fnum = 0 To rc.Fields.Count-1
        Set fld = rd.Fields(fnum)
        Response.Write fld.name & "=" & fld.Value & "<br/>" & vbNewLine
    Next
End If

Response.Write "END DEBUG FOR NOW"
Response.End
...
%>

***************

Incidentally, you completely IGNORED my comment about SQL Injection Attacks!

You *SHOULD* "sanitize" the input from the user via the REPLACE function calls as I showed in my code!!!! That is VERY IMPORTANT.

Granted, it's less important with Access then with other DBs, but it's still a really bad habit to get into, allowing unsanitized user input!

Old Pedant · June 5th, 2008, 03:29 PM

AHA!!! I *KNOW* your problem!!!

You are using ON ERROR RESUME NEXT on your page!!! Somewhere at the top of the page, I bet.

That is the WORST THING you can do when coding in VBScript!

ON ERROR RESUME NEXT is *POISON* to your programs when used wrong! It means that NO MATTER HOW BAD AN ERROR you make in your code, VBScript will simply *IGNORE* all your errors!

EVEN WHEN YOU MAKE a really really really bad error! You should limit your use of ON ERROR RESUME NEXT to no more than one or two lines at a time (you use ON ERROR GOTO 0 to turn it back off). And then *ONLY* when you *clearly* understand (a) WHAT you are doing, (b) WHY you are doing it, and (c) the consequences of doing it.

So what is the root of your problem?

I actually TOLD you the cause in my first posting: In Access SQL, the name password is a *keyword*. And so you are getting a SQL *error* that you are IGNORING!

When you use keywords in an Access SQL query, you *MUST* enclose them in square brackets, thus: [password].

So...
AS I SAID in my first post, you need to use *this* for your SQL:

Code:

SQL = "SELECT * From tabla1 " _
    & " WHERE f_name = '" & Replace(f_name,"'","''") & "' " _
    & " AND [password] = '" & Replace(password,"'","''") & "'"
    ' (we must put [...] around the field name because password is keyword in Access)

See? I even commented on the use of [password] *and* I am protecting you agains SQL injection attacks!

************

Oh, and GET RID of ON ERROR RESUME NEXT until and unless you learn how to use it in the (very very limited!) correct ways.

(And if I am wrong about you using ON ERROR RESUME NEXT, then you still need to put in the DEBUG code I showed in the just prior post.)

lightmaker · June 6th, 2008, 06:45 AM

well I fixed the bug, apparently in the registration there was a space and every entry there was with a space, the chek.asp didn't recognize it and treater everything as not there. (and sorry for wasting your time on an irrelevant mistake)
(and I'm not using ON ERROR RESUME NEXT)

thanks for all the input ! I will start using sql=.... set rc=(sql) as it really does clutter the line making it impossible to find an error.

Old Pedant · June 6th, 2008, 01:01 PM

Well, I'm amazed.

I could have sworn your problem was that you used
password
in your SQL instead of
[password]

So, I apologize for the obviously irrelevant rant about ON ERROR RESUME NEXT.

I still don't understand how you were getting past the SQL query, but that's life.

mat41 · June 9th, 2008, 06:49 PM

Havnt used Access in while however from memory keywords always needed []

very strange...

Wind is your friend
Matt
www.elitemarquees.com.au