Check ID number against Access DB

JBiggsCC · Jun 4, 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.

Adrienne Boswell · Jun 4, 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.

<%
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>

Bob Barrows [MVP] · Jun 4, 2007

JBiggsCC said:
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

JBiggsCC · Jun 4, 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>

Bob Barrows [MVP] · Jun 4, 2007

JBiggsCC said:
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

I dont get this. Please help me!!	2	Jan 24, 2023
Survey details won't go through using php, ajax, Mysql	0	Oct 26, 2023
Uhhhhh, What can I do next?	6	Nov 25, 2023
Problem with a login script, SESSION user rights and put this together so it works with the other pages and MySQL. Code examples.	2	May 5, 2023
Only one table shows up with the information	2	Mar 29, 2023
Please, help me.	1	Jun 30, 2021
Password confirmation issue	5	Sep 20, 2023
Issue with passing fetched data to POST form. How can I?	0	Jul 23, 2023

Check ID number against Access DB

JBiggsCC

Adrienne Boswell

Bob Barrows [MVP]

JBiggsCC

Bob Barrows [MVP]

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads