Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Check ID number against Access DB

Reply
Thread Tools

Check ID number against Access DB

 
 
JBiggsCC
Guest
Posts: n/a
 
      06-04-2007
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.

 
Reply With Quote
 
 
 
 
Adrienne Boswell
Guest
Posts: n/a
 
      06-04-2007
Gazing into my crystal ball I observed JBiggsCC <(E-Mail Removed)>
writing in news:(E-Mail Removed) ps.com:

> 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

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-04-2007
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/...e36562fee7804e


--
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
 
JBiggsCC
Guest
Posts: n/a
 
      06-04-2007
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 <(E-Mail Removed)>
> writing in news:(E-Mail Removed) ps.com:
>
> > 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


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      06-04-2007
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.


 
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
M$ against Blu-ray, M$ for Blu-ray, M$ against Blu-ray, M$ forBlu-ray, ...... Blig Merk DVD Video 66 04-27-2008 04:46 AM
How to check users against security groups in Active Directory rote ASP .Net 2 11-15-2007 01:46 AM
how to count number of check boxes in a check box list. babu17 Javascript 1 03-30-2006 01:09 PM
OT: Number Nine, Number Nine, Number Nine FrisbeeŽ MCSE 37 09-26-2005 04:06 PM
check if user belong to a domain against active directory without impersonation Caspy ASP .Net 3 08-04-2005 01:01 PM



Advertisments