asp and ms sql

E

Eugene Anthony

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
 
B

Bob Barrows [MVP]

Eugene said:
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
Click to expand...

You need to add

SET NOCOUNT ON

to both of these procedures (right after the AS keyword) to suppress the
informational messages (x rows were affected ... ) that ms sql returns as
resultsets.

Here is a better way to run these procedures:
http://tinyurl.com/jyy0

You may want to consider using RETURN to return those status codes, instead
of "Select 0". Of course, this will require the use of an explicit Command
object which is a little harder to write the code for (which is why I
created the code generator which you can find at
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear).

Bob Barrows
 
E

Eugene Anthony

If sSessionID = -1 Then
SMessage = "username or password invalid" & sSessionID
else
'Response.Redirect ("home.asp?id=" & sSessionID)
end if

seems that when I remove the Response.Redirect ("home.asp?id=" &
sSessionID) the login.asp page is visible.

Then, when I enter a valid username and password:

sSessionID = rs.Fields(0).Value

sSessionID is empty.
 
E

Eugene Anthony

The code bellow:

If sSessionID = -1 Then
SMessage = "username or password invalid" & sSessionID
else
'Response.Redirect ("home.asp?id=" & sSessionID)
end if

When I remove 'Response.Redirect ("home.asp?id=" & sSessionID) the
login.asp page is displayed.


However when I enter a valid username and password the code bellow:

sSessionID = rs.Fields(0).Value

sSessionID is blank.
 
B

Bob Barrows [MVP]

Eugene said:
If sSessionID = -1 Then
SMessage = "username or password invalid" & sSessionID
else
'Response.Redirect ("home.asp?id=" & sSessionID)
end if

seems that when I remove the Response.Redirect ("home.asp?id=" &
sSessionID) the login.asp page is visible.

Then, when I enter a valid username and password:

sSessionID = rs.Fields(0).Value

sSessionID is empty.
Click to expand...

Did you add the "SET NOCOUNT ON" to your procedure?

Bob Barrows
 
E

Eugene Anthony

yes as bellow:

Create Procedure usp_CheckSessionID
@sessionID UNIQUEIDENTIFIER
As
SET NOCOUNT ON
 
E

Eugene Anthony

yes as bellow:

Create Procedure usp_CheckSessionID
@sessionID UNIQUEIDENTIFIER
As
SET NOCOUNT ON


Eugene Anthony
 
E

Eugene Anthony

seems that Response.Redirect ("home.asp?id=" & sSessionID),
when sSessionID is parsed to home.asp, there is an error which is
"cannot convert varchar to unique identifier".

Eugene Anthony
 
B

Bob Barrows [MVP]

Eugene said:
seems that Response.Redirect ("home.asp?id=" & sSessionID),
when sSessionID is parsed to home.asp, there is an error which is
"cannot convert varchar to unique identifier".

Eugene Anthony
Click to expand...

Have you tested this using query analyzer? if not, response.write the
statement that causes the error and paste the result from te browser window
into query analyzer and try it.

I've never used uniqueidentifier. You should try your question at
..sqlserver.programming.

Bob Barrows
 
E

Eugene Anthony

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Error converting data type varchar to uniqueidentifier.
/Eugene/database_Function.asp, line 21


Eugene Anthony
 
E

Eugene Anthony

Now it works for the login.asp. I did the following changes.

if Len(Trim(Lcase(Request.Form("usid")))) > 0 AND
Len(Trim(Request.Form("password"))) > 0 then

If sSessionID = -1 Then
SMessage = "username or password invalid"
else
Response.Redirect ("home.asp?id=" & sSessionID)
end if

end if

Only problem is with home.asp :)


Eugene Anthony
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

session 1
asp and ms sql 9
MS SQL ASP Update timestamp 4
Problem with a login script, SESSION user rights and put this together so it works with the other pages and MySQL. Code examples. 2
ASP/Html checkbox convert to true/false for MS Access backend. 1
ASP / SQL Problem 8
HELP! ASP and SQL problem. 7
ASP/Html checkbox convert to true/false for MS Access backend 2

Members online

Total: 23 (members: 2, guests: 21)
Robots: 424

Forum statistics

Threads
473,767
Messages
2,569,572
Members
45,045
Latest member
DRCM

Latest Threads

Top