View Full Version : Getting query from option selected

10-26-2003, 09:15 PM
Hello all,

I'm having some trouble figuring this one out. I'm trying to make a form for my boss to use. Initially I'm pulling a list of names from an Access DB, populating a select field with the employee names, then she would pick a name, submit it, and the rest of the form would be populated. I'm stuck on getting the selected field from the select input.

Any help would be greatly appreciated.

Here's the code I'm using.

<form action="<%=Request.Servervariables("URL")%>?fLookup=TRUE" method="post">

<div align="right"><h1>UTILITIES COMMISSION</h1></div>

<table width="750" border="0">
<td class="bighead">Employee Performance Review - Hourly Employees</td>
<table width="750" border="0">
<td align="center" bgcolor="#000000" class="littlehead">Employee Information</td>
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsEval 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database

Dim EmpNo
Dim EmpJob
Dim EmpDept
Dim EmpSuper
Dim EmpHired
Dim EmpEvalDate

EmpNo = ""
EmpJob = ""
EmpDept = ""
EmpSuper = ""
EmpHired = ""
EmpEvalDate = ""

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("employeesdb1.mdb")

'Create an ADO recordset object
Set rsEval = Server.CreateObject("ADODB.Recordset")

Lookup Check...
IF Request.QueryString("fLookup") = "TRUE" THEN
Response.Write("Lookup is True...")
IF Request.QueryString("EmployeeName") <> "" THEN
Dim NameLookup
NameLookup = Request.QueryString("EmployeeName")
strSQL = "SELECT * FROM [All BU] WHERE (FName + ' ' + MInitial + ' ' + LName) = '"&NameLookup&"' "

The above line fails horribly.

rsFinal.Open strSQL, adoCon
EmpNo = rsFinal("EmployeeNo")
EmpJob = rsFinal("JobTitle")
EmpDept = rsFinal("Department")
EmpSuper = rsFinal("Supervisor")
EmpHired = rsFinal("HireDate")
EmpEvalDate = rsFinal("Date of Eval")
Set rsFinal = Nothing
Response.Write("Failed Query")
<table width="750" border="0">
<td width="250">Name of Employee Being Reviewed:</td>
<select name="EmployeeName">
<option value=""></option>
strSQL = "SELECT * FROM [All BU] ORDER by LName"
rsEval.Open strSQL, adoCon
Do While NOT rsEval.EOF
Response.Write ("<option value=")
Response.Write (rsEval("FName"))
Response.Write ("&nbsp;")
Response.Write (rsEval("MInitial"))
Response.Write (rsEval("LName"))
Response.Write (">" & rsEval("FName") & " " & rsEval("MInitial") & " " & rsEval("LName") )
Response.Write ("</option>")
strSQL = "SELECT EmployeeNo FROM [All BU] where string from [employee name] = employee being reviewed"

The above and below lines are where I'm stuck getting the lookup value from the select box. The line directly above is just a little pseudo code so I remember what I'm trying to do. :)

rsEval.Open strSQL, adoCon
<input name="empID" type="hidden" value="<%=empID%>">
<td><input name="emplookup" type="submit" value="Go"></td>
<td width="120">Employee ID:</td>
<td><input name="EmpNo" type="text" readonly="true" value="<%=EmpNo%>"></td>
<table width="750" border="0">
<td width="80">Job Desc:</td>
<td><input name="JobTitle" type="text" readonly="true" value="<%=JobTitle%>"></td>
<td><input name="EmpDept" type="text" readonly="true" value="<%=EmpDept%>"></td>
<td><input name="EmpSuper" type="text" readonly="true" value="<%=EmpSuper%>"></td>
<td>Review Period:</td>

Do I need to pre-populate the select box and use an array of some sort to get the lookup value?

Thanks for any advice.


10-26-2003, 10:12 PM
I do the same thing at work but I use this;

Dimension variables, Get your connection, and stuff.

By this point you have your connection and recordset for the select.

<form name="BigForm" action="ThisPage.asp" method="Post">
<select id="lstEmp" size="1" onChange="DoIt()">
<% While Not RS.EOF %>
<option selected value=""></option>
<option value="<% =rs("empNo") %>"><% =rs("empName") %></option>
<% Wend %></select>
<% If Request.Form("lstEmp")<> "" Then
SQL="Select * FROM Employees WHERE EmpID=" & Request.Form("lstEmp")
DbConnection.Execute SQL

If you have already been through you have a RS containing the employee, else you have an empty RS

Put your form fields like inputs or just data values in here.

<td class="FieldName"><% =RS("EmployeeName" %>

Etc, Etc.
End If<%

I didn't have my files here at home, so it is from memory. The only problem I see is how the selected value is exposed in the Request.Form part.

10-27-2003, 04:25 PM
Ok, I'm stumped. :)

I've managed to sort through most of the initial problems with the form, posting the variables, etc -- I think, at least. Now I'm coming up with an unspecified error dealing with the adoCon.Open statement.

adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("employeesdb1.mdb")

This only happens when the page is submitted.

Error Type:
Provider (0x80004005)
Unspecified error
/website/eval/hourly.asp, line 73

I've uploaded the entire page as a .txt file if I could talk anyone into taking a look at it.
Goofy web page text file (http://aww-mud.org/eric/hourly.txt)

(my remote server isn't setup to handle ASP pages, only testing server and work server, so the page wont actually do anything at the above URL)

Thanks for any help!


10-27-2003, 06:56 PM
Nevermind this. Apparently my IIS setup at home is fubar'd or something equally sinister. Works fine at work.

10-28-2003, 03:58 AM
Ok, that's cool. :) I will close this for now, please post with any other problems. :D