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.

Re: Check ID number against Access DB

Gazing into my crystal ball I observed JBiggsCC
writing in news:1180917847.861577.13810@h2g2000hsg.googlegroups.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
%>



<%=message%>

Re: Check ID number against Access DB

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.inetser ver.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"

Re: Check ID number against Access DB

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





<%=message%>

Re: Check ID number against Access DB

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 & "
"
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.