Check ID number against Access DB

Discussion in 'ASP General' started by JBiggsCC, Jun 4, 2007.

  1. JBiggsCC

    JBiggsCC Guest

    I have a very simple login page which takes an ID number via a HTML
    form GET. What is easiest way to check that ID number against an
    Access DB to see if it exists?

    I want to redirect with the ID in the query string if it does exist
    and have them re-enter if incorrect.
     
    JBiggsCC, Jun 4, 2007
    #1
    1. Advertising

  2. Gazing into my crystal ball I observed JBiggsCC <>
    writing in news::

    > I have a very simple login page which takes an ID number via a HTML
    > form GET. What is easiest way to check that ID number against an
    > Access DB to see if it exists?
    >
    > I want to redirect with the ID in the query string if it does exist
    > and have them re-enter if incorrect.
    >
    >


    <%
    id = request.querystring("id")

    if id <> "" then
    sql = "SELECT username FROM db WHERE id = " & id
    'create recordset and open it

    if rs.EOF then
    'the person put something in wrong
    required = "id"
    else
    'the person put the correct thing ing
    'do whatever from here
    end if

    if required <> "" then
    message = required & " is invalid"
    end if

    end if
    %>
    <style type="text/css">
    <% if required <> "" then%>
    #<%=required%>1 {background-color:yellow; color: red;}
    #<%=required%> {background-color: pink; color: #000;}
    <% end if%>
    </style>
    </head>
    <body>
    <%=message%>
    <form method="get" action="<%=request.servervariables("script_name")%>">
    <div>
    <label for="id" id="id1">ID Number: </label>
    <input type="text" name="id" id="id" value="<%=id%>">
    <input type="submit" value="Submit">
    </div>
    </form>


    --
    Adrienne Boswell at Home
    Arbpen Web Site Design Services
    http://www.cavalcade-of-coding.info
    Please respond to the group so others can share
     
    Adrienne Boswell, Jun 4, 2007
    #2
    1. Advertising

  3. JBiggsCC wrote:
    > I have a very simple login page which takes an ID number via a HTML
    > form GET. What is easiest way to check that ID number against an
    > Access DB to see if it exists?
    >
    > I want to redirect with the ID in the query string if it does exist
    > and have them re-enter if incorrect.


    The easiest way is via a saved query.
    Create a saved query (stored procedure) in your Access DB. Call it:
    qIDCheck. Use this SQL:

    Select count(*) as IDCount from tablename Where ID=pID

    Since pID is undefined, Jet will treat it as a parameter. Test it and note
    how Access prompts you for the value. You will supply that value in your
    vbscript code, like this:

    <%
    dim ID
    ID - request.querystring("ID")
    'validate that ID contains nothing but a number. Redirect user
    'if non-numeric characters are present
    dim cn, rs, cntset cn=createobject("adodb.connection")
    cn.open "provider=microsoft.jet.oledb.4.0;" & _
    "data source = p:\ath\to\db.mdb"
    set rs=createobject("adodb.recordset")
    cn.qIDCheck ID, rs
    cnt=rs(0)
    rs.close:set rs=nothing
    cn.close:set cn=nothing
    if cnt = 0 then
    redirect user to login page
    else
    'accept the user
    end if
    %>

    Read up on the dangers of SQL Injection:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    If you wish to avoid saved parameter queries, here is an altenative
    technique that also uses parameters to defeat SQL Injection:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e


    --
    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], Jun 4, 2007
    #3
  4. JBiggsCC

    JBiggsCC Guest

    I am trying to use the following code but getting a HTTP 500 error.
    Any suggestions?

    <%
    ssn = request.querystring("ssn")

    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open Server.MapPath("../../logins.mdb")
    set rs = Server.CreateObject("ADODB.recordset")
    rs.Open "SELECT FirstName FROM clients WHERE SSN = '" & ssn & "'",
    conn
    If Not rs.EOF Then
    'redirect to site

    Else
    'Print the error message
    required = "ssn"
    End If
    rs.close
    conn.close

    If required <> "" Then
    message = required & " is invalid"
    End If
    %>


    <style type="text/css">
    <% if required <> "" then%>
    #<%=required%>1 {background-color:yellow; color: red;}
    #<%=required%> {background-color: pink; color: #000;}
    <% end if%>
    </style>
    </head>
    <body>
    <%=message%>
    <form method="get" action="<%=request.servervariables("script_name")
    %>">
    <div>
    <label for="ssn" id="ssn">Social Security Number: </label>
    <input type="text" name="ssn" id="ssn" value="<%=ssn%>">
    <input type="submit" value="Submit">
    </div>
    </form>
    Adrienne Boswell wrote:
    > Gazing into my crystal ball I observed JBiggsCC <>
    > writing in news::
    >
    > > I have a very simple login page which takes an ID number via a HTML
    > > form GET. What is easiest way to check that ID number against an
    > > Access DB to see if it exists?
    > >
    > > I want to redirect with the ID in the query string if it does exist
    > > and have them re-enter if incorrect.
    > >
    > >

    >
    > <%
    > id = request.querystring("id")
    >
    > if id <> "" then
    > sql = "SELECT username FROM db WHERE id = " & id
    > 'create recordset and open it
    >
    > if rs.EOF then
    > 'the person put something in wrong
    > required = "id"
    > else
    > 'the person put the correct thing ing
    > 'do whatever from here
    > end if
    >
    > if required <> "" then
    > message = required & " is invalid"
    > end if
    >
    > end if
    > %>
    > <style type="text/css">
    > <% if required <> "" then%>
    > #<%=required%>1 {background-color:yellow; color: red;}
    > #<%=required%> {background-color: pink; color: #000;}
    > <% end if%>
    > </style>
    > </head>
    > <body>
    > <%=message%>
    > <form method="get" action="<%=request.servervariables("script_name")%>">
    > <div>
    > <label for="id" id="id1">ID Number: </label>
    > <input type="text" name="id" id="id" value="<%=id%>">
    > <input type="submit" value="Submit">
    > </div>
    > </form>
    >
    >
    > --
    > Adrienne Boswell at Home
    > Arbpen Web Site Design Services
    > http://www.cavalcade-of-coding.info
    > Please respond to the group so others can share
     
    JBiggsCC, Jun 4, 2007
    #4
  5. JBiggsCC wrote:
    > I am trying to use the following code but getting a HTTP 500 error.
    > Any suggestions?


    Not without knowing what the error is.
    See http://www.aspfaq.com/show.asp?id=2109

    Also, let us know what the result of the concatenation is by assigning
    your sql statement to a variable and writing it to response:

    sql="SELECT FirstName FROM clients WHERE SSN = '" & ssn & "'"
    Response.Write sql & "<BR>"
    rs.Open sql,conn,1


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jun 4, 2007
    #5
    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. Caspy
    Replies:
    3
    Views:
    3,577
    Sean M
    Aug 4, 2005
  2. rote
    Replies:
    2
    Views:
    501
  3. wswilson
    Replies:
    16
    Views:
    457
  4. Craig Vedur
    Replies:
    5
    Views:
    705
  5. babu17
    Replies:
    1
    Views:
    148
Loading...

Share This Page