Dynamic Dropdown with a twist

jackiew · April 28th, 2015, 11:41 AM

Hello all. I am not really a programmer, just a graphic artist that knew html and is now suddenly web designing when the programmer left. So, please help and forgive poor wording.

I have an employee fired/quit form that collects all the pertinent information, inserts it into a database (EMPLOYEE) and emails the appropriate people. Works like a charm.

I also have a employee directory (EMPDIR) that shows the person's name, IMAGE, department, hire and birth date. Also, works like a charm.

Now, TPTB want me to have the fired person's IMAGE appear on the notice that is emailed out when the Fired/Quit form is filled out. The problem comes in that the Directory and the Quit Form are on two different databases. I want to populate a dropdown list with the image names from the database 'EMPDIR' (Employee Directory) on the Quit Form that is on the database 'EMPLOYEE'. This is what I have so far. It shows the dropdown place holder, but it is not populated and the form goes no further. There are no error codes, so I know I'm close, just not quite there yet.

I have this on the page where I want my dropdown to show up.

<select size="1" name="IMAGE">
<% conn2.open "SERVER=XXX;DATABASE=EMPDIR;UID=YYY;PWD=ZZZ;"
Set rs1 = Server.CreateObject("adodb.recordset")
cmdTemp.CommandText = "SELECT distinct IMAGE FROM directory"
rs1.Open cmdTemp, , AdOpenStatic
while not rs1.eof
%>
<option value="<%=rs1("IMAGE")%>"><%=rs1("IMAGE")%></option>
<%
rs1.movenext
wend
rs1.close
%>
</select>

I hope that's enough code for you, if not, please let me know.

Can anyone help me figure this out? Am I even close?

Thanks in advance.
Jackie

mat41 · April 29th, 2015, 02:11 AM

I can see no issues with your code. Looks like your query does not return any results, I have added some code which will tell you this. Also some oher best practice changes:

Code:

<% conn2.open "SERVER=XXX;DATABASE=EMPDIR;UID=YYY;PWD=ZZZ;" 
   Set rs1 = Server.CreateObject("adodb.recordset")
   cmdTemp.CommandText = "SELECT distinct IMAGE FROM directory" 
   rs1.Open cmdTemp, , AdOpenStatic 
   if not rs1.eof then 
      response.write "<select  name='IMAGE' id='IMAGE'><option value=''>---Select an Image---</option>"
      do until rs1.eof  %>
         <option value="<%=rs1("IMAGE")%>"><%=rs1("IMAGE")%></option>
<%       rs1.movenext
      loop
	  response.write "</select>" 
      rs1.close
	  set rs1 = nothing
   else
      response.write "There are no image results in the directory table"
   end if %>

jackiew · April 29th, 2015, 10:37 AM

Thank you!!! With a little connection string finagling, it worked like a charm! The first time through it told me I wasn't connecting to the database correctly. Once I got the connection string entered properly the list shows up just like I needed it to.

Thanks!!