Connecting to Access using Forms

Inputting a value

Getting a value from the user and submitting it to a database requires the use of a form.

You therefore need two pages, a "front" page with the form to get the user input and a "back" page with the ASP code, to connect to the database.

This example also makes use of a function query2table. This formats the results from the database into a html table to make the results look better. Since it is a useful function that could be used in further ASP pages, it has been saved to a library file: ASPLib.asp

Warning!

These examples were put together years ago, long before SQL-Injection was heard of, or understood. Allowing the user to type the value in the fields is not recommended, since it leaves it open to SQL-Injection and other nasties!

It is better practice to allow the user to pick a value from a list instead, see this example instead.

html Form

<html>
<head>
<title>Forms and a Database</title>
</head>

<body>
<h2>Connecting to Access using a Form</h2>
<form action="EmpBackEnd.asp" method="post">
<p>An example form to enter search for employees from a particular department.</p>
<P>Enter department number (10, 20, 30, 40, 90)
<INPUT TYPE="text" NAME="deptno"> </p>
<p><input type="submit" value="Search Database"> </p>
</form>
</body>
</html>

ASP Code

<html>
<head>
<title>Result of Database Query</title>
</head>
<body>

<h1>Employees from a particular department</h1>
<!--#include file="_lib\ASPLib.asp"-->

<% SET myConn=SERVER.CreateObject("adodb.connection")
DBpath=SERVER.MapPath("_private")
strConn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
strConn=strConn & DBpath
strConn=strConn & "\Employees.mdb;"
myConn.Open (strConn)

' request gets the value of the deptno variable
SQLStr="SELECT empno, ename, hiredate, e.deptno from Dept d, Emp E WHERE "
SQLStr=SQLStr & "d.deptno=e.deptno AND e.deptno=" & request("deptno")

' see the string generated
response.write ("'" & SQLStr &"'")
CALL query2table(SQLStr,myConn)
myConn.Close %>

</body>
</html

ASP Include File

<% SUB query2table(inputquery, conntemp)
DIM rstemp
SET rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1%>

<table border=2><tr>
<% 'Put Headings On The table of Field Names
for i=0 to howmanyfields %>
<td><B><%=rstemp(i).name%></B></td>
<% NEXT %>
</tr>
<% ' Now lets print the records
DO WHILE NOT rstemp.EOF %>
<tr>
<% FOR i=0 TO howmanyfields
thisvalue=rstemp(i)

' hardcode a space if value blank
IF ISNULL(thisvalue) THEN
thisvalue=" "
END IF%> <td VALINE=TOP><%=thisvalue %></td>
<% NEXT %>
</tr>

<%rstemp.MoveNext
LOOP %>
</table>

<% ' close the connections
rstemp.Close
SET rstemp=nothing
END SUB %>