login page refuse to work..

[weee]

Hi.

I have a loing page that check out for username, password and if the user membership is still valid (datewise).

Here's the code:

If request.form("Password") <> "" Then
SQL = "Select id,user,pass,serviceDate FROM tstTBL Where user='" & Request.form("user") & "' and pass='" & Request.form("pass") & "' and serviceDate > DATE()"
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open SQL,DSN,3,1
if NOT rs.EOF Then
id = rs("id")
Session("login") = "True"
rs.Close
Set rs = Nothing
Response.redirect "user.asp?id=" & id
Response.End
Else
If rs("serviceDate") > DATE() Then
msg = "membership is over."
Else
msg = "wrong username or password"
End If
End if
rs.Close
Set rs = Nothing
End if
%>


The error I'm getting is:
Exception occurred.
login.asp, line 31

This is the line:
If rs("serviceEndDate") > DATE() Then

What is the problem?
why it's not working?

Thanks!

[lmf232s]

If rs("serviceDate") > DATE() Then

What is DATE()
If you are just going for todays date just use DATE and dont add ()

Try this

If rs("serviceDate") > Date then

That should work.

[weee]

R u sure about the "()"?

This SQL works just fine:
SQL = "Select id,user,pass,serviceDate FROM tstTBL Where user='" & Request.form("user") & "' and pass='" & Request.form("pass") & "' and serviceDate > DATE()"

what can I do?

[buntine]

You have enclosed the Date expression in quotes, telling ASP that it is a string. I dont think MS Access supports the SQL DATE() expression.

It should look like this:

SQL = "Select id,user,pass,serviceDate FROM tstTBL WHERE user='" & Request.form("user") & "' AND pass='" & Request.form("pass") & "' AND DATEDIFF('Dd', serviceDate, " & Date() & ") >= 0;"

That should only extract the member if he/she has not expired. If no record is extracted, the members account has expired.

lmf232s
If rs("serviceDate") > Date then

That should work.

You cannot use conditional operators on the date type. You can only use =, >, <, <> on numeric expressions. Though, ASP allows you to check strings using the equality operator.

Regards,
Andrew Buntine.

[lmf232s]

I misread the post and only looked at where the error was thrown. You are correct, It will work with () but it will also work with out the ().
Date
Date()

I have never used the Date with (). My mistake.

Although you can use <,>, = and what not with date. For example this will work

Dim MyDate
MyDate = "6/22/2004"

If MyDate > Date then
response.write " it works"
else
response.write " it does not work"
end if


with this example it works.
I dont know though. Oh well

[weee]

Here's the page...

[CrazyC115]

Try replacing "your check to see whether or not the account is still valid" code with


IF NOT isNull(RS("serviceEndDate")) AND isDate(RS("serviceEndDate")) THEN
IF DATEDIFF("d", DATE(), RS("serviceEndDate")) < 0 THEN
'Account Expired
ELSE
'Possible wrong Username or Password
END IF
ELSE
'serviceEndDate does not contain a date vaule
END IF

[weee]

?

[CrazyC115]

Originally posted by weee
Hi.

The error I'm getting is:
Exception occurred.
login.asp, line 31

This is the line:
If rs("serviceEndDate") > DATE() Then

What is the problem?
why it's not working?

Thanks!

The code i submitted above was in relation to your first post where you where having problems with your conditional statement that checks whether todays date is less then the service end date. You could try replacing the code you have with the code i wrote. I may have my dates in the wrong order though but you can test that.

[weee]

error '80020009'
Exception occurred.


This is the line:
IF DATEDIFF("d", DATE(), RS("serviceEndDate")) < 0 THEN

[CrazyC115]

Originally posted by weee
error '80020009'
Exception occurred.


This is the line:
IF DATEDIFF("d", DATE(), RS("serviceEndDate")) < 0 THEN

Hrm, your absolutely sure that RS("serviceEndDate") contains a date value everytime? The conditional statement I have the above one nested in SHOULD catch any possible invalid dates but it might not be. I would check the data in the database to make sure there are no NULL date values and that all the dates that exist are real date values.

[lmf232s]

I took you code and changed a couple of things.
You may want to use
<%option explict%>
This makes you declare all your variables but it will catch errors.
I use sql server and not access but that should not matter.
I created two records
id, username, pass, servicedate
1. 1,larryf, larryf, 6/4/2004
2 2, joe, joe, 6/1/2004

When i entered larryf, I got to the response.write " i made it" this is where you had response.redirect.

When i entered joe, I got the msg "wrong username or pssword or account is not longer active"

Here is the code

<%option explicit%>

<%
Response.Buffer="true"

Dim strConnection, objConn, SQL, objRS
Dim msg, ID

'My test connection string
strConnection = "Provider=sqloledb;Data Source=APP2;Initial Catalog=Employee;USER ID=test;PASSWORD=test;"
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnection
Set objRS = Server.CreateObject("ADODB.Recordset")

msg = ""

If request.form("password") <> "" Then
SQL = " SELECT ID, UserName, Pass, ServiceDate " & _
" FROM tstTBL " & _
" WHERE (UserName = '" & Request.form("username") & "') and " & _
" (Pass = '" & Request.form("password") & "') and " & _
" (ServiceDate >= '" & Date & "')"


objRS.Open SQL,objConn, 3, 3
If NOT objRS.EOF Then
ID = objRS("ID")
Session("login") = "True"
rs.Close
Set rs = Nothing
Response.redirect "test.asp?id=" & id
Else
msg = "wrong username or pssword or account is not longer active"
End if
objRS.Close
Set objRS = Nothing
End if
%>
<body>
<form name="login" method="post">
<center>
<table cellpadding="0" cellspacing="0" border="0">
<tr>
<td>
<%
If msg <> "" Then
Response.Write msg & "<br><br>"
End If
%>
<input type name="username"> - Username<br>
<input type name="password"> - Password<br>
<input type="submit" value="LogIn" class="BTN">
</td>
</tr>
</table>
</center>
</form>
</body>



If you want to have two messages set up
1. Your membership already expired"
2. wrong username or password
as well as be able to redirec then try replacing

If NOT objRS.EOF Then
ID = objRS("ID")
Session("login") = "True"
rs.Close
Set rs = Nothing
Response.redirect "test.asp?id=" & id
Else
msg = "wrong username or pssword or account is not longer active"
End if

with

If NOT objRS.EOF Then
if rs("serviceEndDate") < Date then
msg = "your membership has already expired"
else
response.redirect "test.asp?id="& id
end if
else
msg = "wrong username or password
end if

and dont filer by servicedate on the SQL statement but filter on the above if statement.

SQL = " SELECT ID, UserName, Pass, ServiceDate " & _
" FROM tstTBL " & _
" WHERE (UserName = '" & Request.form("username") & "') and " & _
" (Pass = '" & Request.form("password") & "') "


Again I can not stress the importance of having
<%option explicit%>
as your very first line on the page,
It will catch so many errors and create less headaces for you in the future.
If this does not work, I dont know what to tell you.
Good luck

[weee]

Just wanted to say that I appriciate all of your help so much.
I figured it out and this is what I got now:


msg = ""

If request.form("password") <> "" Then
Dim MyDate
MyDate = Date

SQL = "Select id,user,pass,serviceDate FROM tstTBL Where user='" & Request.form("user") & "' and pass='" & Request.form("pass") & "'"

Set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open SQL,DSN,3,1
If NOT rs.EOF Then
If rs("serviceDate") < Date then
msg = "membership expired"
Else
id = rs("id")
Session("login") = "True"
rs.Close
Set rs = Nothing
Response.redirect "sa.asp?id=" & id
End If
Else
msg = "<b>wrong pass or user</b>"
End if

rs.Close
Set rs = Nothing
End if


Again, Thanks a lot guys!!!

weee