ASP w/SQL Form variable question

James B. (Brad) Hale
James B. (Brad) Hale used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.
James B. (Brad) HaleSenior I/T Technician

Author

Commented:
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?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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 ...
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Top Expert 2011
Commented:
@angelIII is actually one of the bests around here, someone I have learned alot from and he is actually telling you what I think is a pretty good approach.

However, here is another angle and I may off from what you need here.

Here is the code and i will explain at the bottom what I *think* I understand you are trying to do.

<%
set cnntypeDB = server.CreateObject("ADODB.Connection")
set objRS =server.CreateObject("ADODB.Recordset")
cnntypeDB.ConnectionString= "Provider=microsoft.jet.oledb.4.0;Data source=" & server.mappath("Paging.mdb")
objRs.CursorLocation = 3
cnntypeDB.Open

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
If rstequip.EOF Then
%>
	<span  class=middletitle style="font-size: small; font-weight: bold;">
	&nbsp;No records available	</span>
<%
Else%>
<!-- get your names into recordset, assign variable and loop through them.-->
<TABLE>
<TR >
          <TD>&nbsp;Full Name, </TD>
          <TD>Department</TD>
          <TD>Email</TD>
</TR>
<%  'Loop and assign values to variables

	Do Until rstequip.EOF
		personnelID=   rstequip("personnelID")
		Email=   rstequip("Email")
		Dept=   rstequip("Department")
		lName	=   rstequip("Lastname")
		fName	=   rstequip("firstName")
		MidInitial	=   rstequip("MI")
       fullName = UCase(Left(lname,1)) & Mid(lname,2) & ", " & UCase(Left(fname,1)) & Mid(fname,2)& "&nbsp;" &midInitial


%>
<form method=post>
	<input type="hidden" name="personnelId" value="<%=personnelId%>">
<TR>

   <TD><input name=fullname size=15 value="<%=fullname%>"></TD>
   <TD><input name=dept size=15 value="<%=dept%>"></TD>
   <TD><input name=email size=15 value="<%=email%>"></TD>
   <TD>
<%  'Get dropdown
      SQLstmt = "SELECT * FROM Personnel WHERE Status='A' order by LastName, FirstName, MI"
           set personnelSet = cnntypeDB.Execute(SQLstmt)
            %>
            <select name="RptName">
            <option value="">&nbsp;</option>
            <%
             Do Until personnelSet.EOF
             personnelID = personnelSet("lastname") & ", " & personnelSet("firstname")& " " & personnelSet("mi")

             If personnelID = RptName Then sel = "SELECTED" Else sel = ""
            %>
            <OPTION Value="<%=ResultID%>" <%=sel%> > <%=fullname%>
           <%
            personnelSet.MoveNext
            Loop

            %>

          </select>
      </TD>
<%
		rstequip.MoveNext
	loop
%>
</TR>
</form>
</table>

<%
end if
rstequip.Close
%>

Open in new window


This part of the code below:

<%  'Loop and assign values to variables

      Do Until rstequip.EOF
            personnelID=   rstequip("personnelID")
            Email=   rstequip("Email")
            Dept=   rstequip("Department")
            lName      =   rstequip("Lastname")
            fName      =   rstequip("firstName")
            MidInitial      =   rstequip("MI")
       fullName = UCase(Left(lname,1)) & Mid(lname,2) & ", " & UCase(Left(fname,1)) & Mid(fname,2)& "&nbsp;" &midInitial

you will have to get from your main table.

In other words, instead of selecting from personnel table, you select I *think*, email and dept from your main table.

Then on the dropdown section, I would either copy the code from your include into the section where I put it on this test code OR I copy this dropdown code into your include file if you are going to be using it in more than one place.

Notice the personneId as a hidden form field in between the <FORM>...</FORM> tag.

Then on your processing page, you will pass them along:

email = request("email")
dept = request("dept")
name = request("fullname")
personnelId = request("personnelid")

then

your update or insert or delete codes here here.

Again, I could be completely off what you are attempting to do.
Top Expert 2011

Commented:
Oops, this:

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

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

You probably figured that out already.
 
James B. (Brad) HaleSenior I/T Technician

Author

Commented:
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.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial