asp and ms sql

Discussion in 'ASP General' started by Eugene Anthony, Jul 2, 2005.

  1. 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
     
    Eugene Anthony, Jul 2, 2005
    #1
    1. Advertising

  2. 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/index.asp?pi=links&hp=links.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"
     
    Bob Barrows [MVP], Jul 2, 2005
    #2
    1. Advertising

  3. 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 ***
     
    Eugene Anthony, Jul 2, 2005
    #3
  4. 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 ***
     
    Eugene Anthony, Jul 2, 2005
    #4
  5. 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"
     
    Bob Barrows [MVP], Jul 2, 2005
    #5
  6. yes as bellow:

    Create Procedure usp_CheckSessionID
    @sessionID UNIQUEIDENTIFIER
    As
    SET NOCOUNT ON

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jul 2, 2005
    #6
  7. yes as bellow:

    Create Procedure usp_CheckSessionID
    @sessionID UNIQUEIDENTIFIER
    As
    SET NOCOUNT ON


    Eugene Anthony

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jul 2, 2005
    #7
  8. 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 ***
     
    Eugene Anthony, Jul 2, 2005
    #8
  9. 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"
     
    Bob Barrows [MVP], Jul 2, 2005
    #9
  10. 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 ***
     
    Eugene Anthony, Jul 2, 2005
    #10
  11. 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

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Eugene Anthony, Jul 2, 2005
    #11
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ecoolone
    Replies:
    0
    Views:
    798
    ecoolone
    Jan 3, 2008
  2. Ed Garcia
    Replies:
    4
    Views:
    204
    Guinness Mann
    Aug 7, 2003
  3. Belinda
    Replies:
    4
    Views:
    393
    Bob Barrows [MVP]
    Jun 11, 2004
  4. weiwei

    asp and sql statement in sql server db

    weiwei, Sep 22, 2004, in forum: ASP General
    Replies:
    3
    Views:
    219
    Jeff Cochran
    Sep 22, 2004
  5. Replies:
    10
    Views:
    295
    Bob Barrows [MVP]
    Jan 6, 2005
Loading...

Share This Page