473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Check ID number against Access DB

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.

Jun 4 '07 #1
4 3502
Gazing into my crystal ball I observed JBiggsCC <th*****@gmail.com>
writing in news:11*********************@h2g2000hsg.googlegrou 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

Jun 4 '07 #2
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"
Jun 4 '07 #3
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 <th*****@gmail.com>
writing in news:11*********************@h2g2000hsg.googlegrou 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
Jun 4 '07 #4
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.
Jun 4 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

27
by: mrbog | last post by:
Tell me if my assertion is wrong here: The only way to prevent session hijacking is to NEVER store authentication information (such as name/password) in the session. Well, to never authenticate...
20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
5
by: Steve Wylie | last post by:
I am constructing an HTML questionnaire and one of the questions requires people to rate some choices from 1 to 5, where 1 is their favourite and 5 is their least favourite: Car Bus Taxi cab...
4
by: Geir Baardsen | last post by:
Hi! Is there any way to check if printer is active in access 2000? I have a report that is printing (local printer), and before I do anything more in code, I do want to check if printer is...
10
by: eyh5 | last post by:
Hi, My C code (running on Soalris Unix) has some "segmentation fault" that I wish to use purify to do it. I poked around the web, and found some information about adding some lines in a Makefile...
3
by: Caspy | last post by:
I just get stuck on how to check if a user is a member of network (domain). I am building an internal tracking system with ASP.Net with Form authentication. When an user is added into the system,...
3
by: Amy | last post by:
Hi, I have 6 If Then Else statements I was supposed to write. I did so but I know that they have to be wrong because they all look the same. Could someone take a look at them and point me in the...
1
by: huyuhui | last post by:
The following is a question of LOAD utility. Question: How does the DB2 enforce table check constraints for data added to table with the LOAD utility? A. With the BUILD phase of LOAD B. With the...
6
by: Hemant Shah | last post by:
Folks, One of our clients is performing an audit, and the auditor(s) asked following question, and I am not sure how to answer it: 28. Is there a control that prevents the corruption of...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.