Table created in ms sql:
create table tbl_users
(
SessionID UNIQUEIDENTIFIER Primary Key,
usID Varchar(20),
Password Varchar(20),
LastUpdate Smalldatetime
);
2 Stored Procedures created in ms sql:
Create Procedure usp_CheckSessionID
@sessionID UNIQUEIDENTIFIER
As
if EXISTS(SELECT 1 FROM tbl_users WHERE sessionID=@sessionID AND DATEDIFF(n,LastUpdate,GETDATE())<=20)
begin
update tbl_users set LastUpdate = GETDATE() WHERE sessionID=@sessionID
Select 0
end
else
Select -1
Return
GO
create procedure usp_CheckLogin
@usID Varchar(20)
@password varchar(20)
As
Declare @sessionID as UNIQUEIDENTIFIER
if exists(Select 1 from tbl_users where usID=@usID AND password=@password)
Begin
set @sessionID = NEWID()
Update tbl_users Set sessionID=@sessionID,LastUpdate=GetDate() where usID = @usID and password = @password
Select @sessionID
End
else
Select -1
Return
GO
In database_Function.asp :
<%
dim objConn,rs
sub openDB()
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=127.0.0.1;UID=papa;PWD=mama;DATABASE=quan
end sub
sub updateDB(SQL,rs)
openDB()
set rs = objConn.Execute(SQL)
end sub
sub getFromDB(SQL,rs,filename)
openDb()
set rs = Server.CreateObject("ADODB.Recordset")
rs.lockType = adLockReadOnly
rs.cursorType = adOpenStatic
rs.Open SQL, objConn
end sub
sub closeDB()
objConn.Close
set objConn = nothing
end sub
%>
In my home.asp :
<!--#include file="database_Function.asp"-->
<%
if Len(Request.QueryString("id")) = 0 then
response.redirect "login.asp"
end if
mySQL = "EXECUTE usp_CheckSessionID @sessionID='" & Trim(Lcase(Request.Form("id"))) & "'"
call updateDB(mySQL, rs)
if rs.Fields(0).Value = 0 then
response.redirect "login.asp"
end if
CloseDB()
%>
In login.asp :
<!--#include file="database_Function.asp"-->
<%
Dim sSessionID, sMessage
If Len(Request.Form("cmdSubmit")) > 0 then
mySQL = "EXECUTE usp_CheckLogin @usid='" & Trim(Lcase(Request.Form("usid"))) & "',@password='" + Trim(Request.Form("password")) & "'"
call updateDB(mySQL, rs)
sSessionID = rs.Fields(0).Value
rs.close()
CloseDB()
end if
If sSessionID = -1 Then
SMessage = "username or password invalid"
else
Response.Redirect ("home.asp?id=" & sSessionID)
end if
%>
<html><head><title>login page</title></head>
<body>
<form method="post" action="login.asp">
<table>
<tr><td colspan="2"><h3>Login Page</h3></td></tr>
<tr><td colspan="2"><% = sMessage%></td></tr>
<tr>
<td>user name<td>
<td><input type="text" name="usid"
value="<% = Request.Form("usID")%>"></td>
</tr>
<tr>
<td>password<td>
<td><input type="password" name="password"
value="<% = Request.Form("password")%>"></td>
</tr>
<tr>
<td> <td>
<td><input type="submit" name="cmdSubmit" value="login"></td>
</tr>
</table>
</form>
</body>
</html>
I am facing a problem in my login.asp. The problem is when I enter an invalid login ID and password it works. However when I add a valid login ID and password it takes me to a page not found of my browser. How do I solve the problem?. I have inserted 1 record in my tbl_users table to test my code.
INSERT INTO tbl_users VALUES (NEWID(),"mama","papa",GetDate())
Regards
Eugene
Linear Mode
