We help IT Professionals succeed at work.
Get Started

ASP w/SQL Form variable question

James B. (Brad) Hale
Last Modified: 2012-08-13
I used to know this, but am drawing a blank.  I've coded a form and have several include files in which I do searches for database information.  I've included the code for the entire include file which does a lookup in the personnel database.  This works fine.  My issue is, once I select the name, I also want to populate a couple variables with other information.  For instance, when I choose a person with this, I also want to record their 'Email' variable and their 'Department' variable and then have it available to the POST so I can pass it on.  What's the best way to do this?
' Create ADO data connection object
Set cnntypeDB = Server.CreateObject("ADODB.Connection")

' Open data connection - Use this line to use Access
 cnntypeDB.Open "DBQ=" & Server.MapPath("employee_new.mdb") & ";" _
	& "Driver={Microsoft Access Driver (*.mdb)};", "", ""

' Build our query for select box 1
 strSQL = "SELECT * FROM Personnel WHERE Status='A' order by LastName, FirstName, MI;"

' Create and open recordset object using existing connection
Set rstequip = Server.CreateObject("ADODB.Recordset")
rstequip.Open strSQL, cnntypeDB, adOpenForwardOnly

' Build our drop down box of equip names
If Not rstequip.EOF Then
	<select name="RptName" tabindex="5">
		<% ' Loop through names
		Do While Not rstequip.EOF
			Response.Write "<option value="""
			Response.Write rstequip.Fields("LastName") & ", " & rstequip.Fields("FirstName") & " " & rstequip.Fields("MI")
			Response.Write """"
			Response.Write ">"
			Response.Write rstequip.Fields("LastName") & ", " & rstequip.Fields("FirstName") & " " & rstequip.Fields("MI")
			Response.Write "</option>" & vbCrLf
			' Move to next record
End If
' Close ADO objects we're finished with and free DB variables
Set rstequip =  Nothing
Set cnntypeDB = Nothing

Open in new window

Watch Question
Top Expert 2011
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE