Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > asp and ms sql

Reply
Thread Tools

asp and ms sql

 
 
Eugene Anthony
Guest
Posts: n/a
 
      07-02-2005
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

-----------------------------
This message is posted by http://asp.forumszone.com

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-02-2005
Eugene Anthony wrote:
> 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
>


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/ind...asp&c=&a=clear).

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
 
 
 
Eugene Anthony
Guest
Posts: n/a
 
      07-02-2005
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.



*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
Eugene Anthony
Guest
Posts: n/a
 
      07-02-2005
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.

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-02-2005
Eugene Anthony wrote:
> 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.
>
>


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

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
Eugene Anthony
Guest
Posts: n/a
 
      07-02-2005
yes as bellow:

Create Procedure usp_CheckSessionID
@sessionID UNIQUEIDENTIFIER
As
SET NOCOUNT ON

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
Eugene Anthony
Guest
Posts: n/a
 
      07-02-2005
yes as bellow:

Create Procedure usp_CheckSessionID
@sessionID UNIQUEIDENTIFIER
As
SET NOCOUNT ON


Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
Eugene Anthony
Guest
Posts: n/a
 
      07-02-2005
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

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-02-2005
Eugene Anthony wrote:
> 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
>


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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
Eugene Anthony
Guest
Posts: n/a
 
      07-02-2005
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

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
MS Access SQL > ASP SQL problem.... david@scene-double.co.uk ASP General 10 01-06-2005 12:23 PM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM
How to read an SQL Server into a ASP page and then change, add, delete and write it back to SQL Server Belinda ASP General 4 06-11-2004 12:16 PM
ASP SQL - using variables in SQL select screen Ed Garcia ASP General 4 08-07-2003 07:41 PM



Advertisments