Pulldown Menu From Access DB !! 1

[kefi2927]

Hi

Can anyone help me with this little pulldown menu problem?
I'm creating an Intranet with all employees contact details available. I have created an admin part to the site that allows certain users to log in and add/delete/edit employee details.

I have created two tables, one with employee details and another with centre details (place of work). The centre names are displayed as a pulldown menu from the centreid field.

My problem is that when I try to display the edit_employee.asp page which comes from this code:

SQL="SELECT * FROM Employees WHERE Employees.EmployeeID LIKE '%" &searchTerm& "%' "

it will not display all of the centres in the pulldown menu only the one that is associated with the EmployeeID. I need it to display all of the centres to be able to edit that record.

I have not long started learning ASP so I might have to be hand held. But any help with this would be much appreciated as I need to get this in place soon for our Intranet.

More code below that is on the edit_employee.asp page:

<!-- #include file = &quot;../includes/ADOVBS.INC&quot; -->
<!-- #include file = &quot;../includes/LOGINNOW.ASP&quot; -->
<!--#include file=&quot;../includes/connection.asp&quot; -->

<%
Dim Conn
Dim RS
Dim SQL

Dim searchTerm

'create connection object
Set Conn=Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open Openvar

'Get record EmployeeID from QueryString
searchTerm = Request.QueryString(&quot;EmployeeID&quot

'set and assign structured query statement to variable
SQL=&quot;SELECT * FROM Employees WHERE Employees.EmployeeID LIKE '%&quot; &searchTerm& &quot;%' &quot;


'create recordset object
Set RS=Server.CreateObject(&quot;ADODB.RecordSet&quot


'opens the table to view all records and open and lock modes
RS.Open SQL, Conn, 3,3

%>

<!-- Content -->
<form action=&quot;employee_edited.asp?EmployeeID=<%=RS(&quot;EmployeeID&quot%>&operation=EDIT&quot; method=&quot;post&quot;>
<table>
<tr>
<td class=&quot;dataHeader&quot;>Centre </td>
<td class=&quot;dataContent&quot;>

<select name=&quot;Centre&quot; class=&quot;formFields&quot;>
<% ' pick out all the centre names from the primary key in centre table for drop down
Response.Write(&quot;<option>&quot; & rs(&quot;CentreName&quot & &quot;</option>&quot
%>
</select>

</tr>
<tr>
<td class=&quot;dataHeader&quot;>Last Name: </td>
<td class=&quot;dataContent&quot;><input type=&quot;text&quot; name=&quot;LastName&quot; value=&quot;<%=RS(&quot;LastName&quot%>&quot; class=&quot;formFields&quot;></td>
</tr>
<tr>
<td class=&quot;dataHeader&quot;>First Name: </td>
<td class=&quot;dataContent&quot;><input type=&quot;text&quot; name=&quot;FirstName&quot; value=&quot;<%=RS(&quot;FirstName&quot%>&quot; class=&quot;formFields&quot;></td>
</tr>
<tr>
<td class=&quot;dataHeader&quot;>Job Title: </td>
<td class=&quot;dataContent&quot;><input type=&quot;text&quot; name=&quot;Title&quot; value=&quot;<%=RS(&quot;Title&quot%>&quot; class=&quot;formFields&quot;></td>
</tr>
<tr>
<td class=&quot;dataHeader&quot;>Title: </td>
<td class=&quot;dataContent&quot;><input type=&quot;text&quot; name=&quot;TitleOfCourtesy&quot; value=&quot;<%=RS(&quot;TitleOfCourtesy&quot%>&quot; class=&quot;formFields&quot;></td>
</tr>

</table>

<input type=&quot;Submit&quot; value=&quot;Edit Employee Details&quot; class=&quot;formButton&quot;>
</form>

<br><br>

<!-- End Main Outer Table -->
</td>
</tr>
</table>

 

[JamesLean]

You will need to have a second piece of SQL to get the complete list of centres. You can then check them as you output the list and display the current one by default:

<%
strCentreSQL = &quot;SELECT centrename FROM centre&quot;
Set rsCentre = conn.Execute(strCentreSQL)
%>
...

<select name=&quot;Centre&quot; class=&quot;formFields&quot;>
<%
Do Until rsCentre.EOF
  name = rsCentre(&quot;centrename&quot;)

  Response.Write &quot;<option value='&quot; & name & &quot;'&quot;

  If name = RS(&quot;centrename&quot;) Then Response.Write &quot; selected&quot;

  Response.Write &quot;>&quot; & name & &quot;</option>&quot;

  rsCentre.MoveNext
Loop
%>
</select>

--James

 

[kefi2927]

Thanks James This is fantastic and also taken a lot of pressure off me from my manager as I can now move forward with putting content on the intranet.

At first it wasn't updating until I realised that the page it was posting to wasn't calling the CentreName to update:

<%
RS (&quot;CentreName&quot=Request.Form(&quot;Centre&quot
RS (&quot;LastName&quot=Request.Form(&quot;LastName&quot
RS (&quot;FirstName&quot=Request.Form(&quot;FirstName&quot
RS (&quot;Title&quot=Request.Form(&quot;Title&quot
RS (&quot;TitleOfCourtesy&quot=Request.Form(&quot;TitleOfCourtesy&quot

RS.Update

%>

Thanks once again.