Link to home
Start Free TrialLog in
Avatar of James B. (Brad) Hale
James B. (Brad) HaleFlag for United States of America

asked on

ASP w/SQL Form variable question

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
	rstequip.MoveFirst
	%>
	<select name="RptName" tabindex="5">
		<option></option>
		<% ' 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
			rstequip.MoveNext
		Loop
		%>
	</select>
<%
End If
' Close ADO objects we're finished with and free DB variables
rstequip.Close
Set rstequip =  Nothing
cnntypeDB.Close
Set cnntypeDB = Nothing
%>

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Response.Write "<option value="""
                  Response.Write rstequip.Fields("LastName") & ", " & rstequip.Fields("FirstName") & " " & rstequip.Fields("MI")
                  Response.Write """"
                  Response.Write ">"

should NOT populate all the information. put in there just the KEY field value for the record, so on postback of the page, you will get just the key info to fetch information based on that value.
Avatar of James B. (Brad) Hale

ASKER

That would cause me to have to rewrite a lot of code.  I inherited this system and am trying to get it back up to snuff.  My next move is to move it from Access to SQL, but that's a step or two away.  There is a key value (now) for the records, but none of the system was written using that and it will take some major rewriting to incorporate it into the code.  I know that it's the best way, but I was looking for a workaround for now.  Any other ideas?
either you fetch the details on the other page based on the posted data from the drop-down, or you need some java code to update some hidden fields based on the selected even of the drop down (which requires even more work, and slowdown of the page , as you have to load plenty of unneeded data etc )
I don't see how to do this "efficiently".
you might "rewrite" the pages step by step, check if the information passed is a key or the full data ...
ASKER CERTIFIED SOLUTION
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oops, this:

          <OPTION Value="<%=ResultID%>" <%=sel%> > <%=fullname%>

should
          <OPTION Value="<%=personnelID %>" <%=sel%> > <%=fullname%>

You probably figured that out already.
 
Thanks all, but I guess I was thinking that it was easier than that.  Must be the 'old-timers' disease catching hold of me.  Because of the way the system is written, implementing either of these will cause some major issues with the way everything else is written, so I guess I'm just going to have to bite the bullet and start rewriting it from scratch.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.