classic asp with sql or an access db
utilize a ado connection, write the script in vbscript
sample code, sorry for the lack of comments
Code:
<html>
<body OnLoad="document.getname.agentname.focus();" bgcolor=#cfcfd7>
<div id="Instructions" style="display:none;">
- Enter any part of the agency name to show a list of agencies. For instance, entering salem will show
all agencies with salem in their name
- Next, find the code for the agency you wish to lookup, click Back to Agent Lookup
- Then, click search by agent code and enter the code you found.
</div>
<input type=button onclick="document.getElementById('Instructions').style.display=(document.getElementById('Instructions').style.display=='block')?'none':'block';" value="Show/Hide Instructions">
<form action=" <%= request.servervariables("URL") %>" method="get" name="getname">
<input type="hidden" name="action" value="getlist">
<input type="text" name="agentname">
<input type="submit" value="Get List">
<input type="button" onclick="window.location = 'http://sus/tasks/agent%20lookup.htm'" value="Back to Agent Lookup">
</form>
<%
dim conn, rs, tries, routefile
routefile = "\\sus\tasks\actagt2.mdb" 'LOCATION OF THE ROUTE FILE'
sub openconn ' open a connection to access db'
dim tries
set conn = CreateObject("ADODB.Connection") 'prep connection'
set rs = CreateObject("ADODB.Recordset") ' prep recordset'
tries = 0 ' clear tries counter'
do 'try to open connection to the db
conn.open "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & RouteFile & ";DefaultDir=;UID=;PWD=;"
tries = tries + 1
if (err.number <> 0) then 'if errored, close this connection
conn.close
end if
Loop While (Err.Number<>0) And tries < 10 'if errored, try again. MAX TRIES = 10
end sub
sub closeconn ' close the connection to the db
rs.close
set rs = nothing
conn.close
set conn = nothing
end sub
if Request.QueryString("action") = "getlist" then 'WAS A NAME ENTERED?'
openconn 'OPEN CONNECTION TO DB'
query = "Select code, name from agents where name like '%" & Request.QueryString("agentname") & "%'" 'SETUP QUERY'
'response.write query 'DISPLAY QUERY FOR DEBUG'
rs.open query, conn 'RUN QUERY'
if rs.eof then 'IF NO RECORDSET, DISPLAY NONE FOUND'
response.write "
<font size=+2 color=red>Name Not Found</font></p>"
else
%>
<table border="1" cellspacing="2" cellpadding="2">
<thead>
<tr>
<th>Agent Code</th>
<th>Agency Name</th>
</tr>
</thead>
<tbody>
<%
Do While Not rs.EOF
%>
<tr>
<td><%= rs.Fields("CODE").Value %></td>
<td><%= rs.Fields("NAME").Value %></td>
</tr>
<%
rs.MoveNext
Loop
%>
</tbody>
</table>
<%
end if
closeconn 'CLOSE THE CONNECTION TO THE DB'
'response.write "conn closed" 'NOTIFY THAT THE DB WAS CLOSED'
end if
%>
</body>
</html>