Connecting to Access using Forms

Selecting a Value from a List

The previous example used the value input from the user to find the current employees in that department. That relied on the user knowing what departments currently existed (and leaves the database open to SQL-Injection attacks!). This example is similar but instead of inputting a value, the database can be queried first and a list generated for the user to select from.

This still requires the need for two pages, a "front" page with the form with a selected list of values and a "back" page with the ASP code, to connect to the database as before. This time the front page is an ASP page rather than html, since it will send an initial query to the database to find the current departments.

This example also makes use of the query2table function, from the library file ASPLib.asp

html form

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

<body>
<h2>Connecting to Access using a form</h2>
<form action="EmpNameBackEnd.asp" method="post">
<p>An example form to enter search for employees from a particular department.</p>
<p>Choose a department: </p>
<select name=dname>
' now generate some SQL to produce the list

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

	SQLStr="SELECT dname from Dept ORDER BY dname"

	' this time we need to loop through the records ourselves to create the menu

	SET result = myConn.execute(SQLStr)
	DO WHILE NOT result.EOF 
		response.write("<option>" & result("dname"))
		result.moveNext
	LOOP 
%>

</select>

<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 dname = '" & request("dname") & "'"
	' 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 %>