How can I add a dropdown list in a data entry form, containing data from a table, which can be used as form input?

I have a Classic ASP web page, containing a simple data entry form, plus another ASP script to process the form's input, and insert that input to a database table. Everything is working just fine.

Now, I would like to have two combo boxes so that users can select items from another table, as input, instead of everything being free-form. I can show a dropdown list with hard-coded items, but I can't figure out how to show items from a table instead (or how to process the selected items).

It should really be a fairly simple task, but I can't seem to find anything on the web that matches my simple question - they are all complex scenarios.

Perhaps if I list the code, someone can show me the extra code required for the combo/dropdown list?

My request comments are on lines 12 and 17.

The main ASP page :

----------------------------------
inc_pastoral_entry.asp
----------------------------------

<form action="updateconcern.asp" method="post">
  <legend>Pastoral Concern Record</legend>
 
  <table>
   <input type="hidden" name="SchoolID" value="<%=intID%>" />
	
	<tr>
     <td><label>Concern:</label></td>
      <td><textarea name="Concern" rows="10" cols="60" wrap="soft"> </textarea></td>
       </tr>

   <tr>  <!---***This is where I want the dropdown containing values 1,2,3,4,5, just the numbers, not pulled from a table***--->
    <td><label>Severity:</label></td>
     <td><input name="Severity" size="12" type="text" /></td>
	  <tr>

   <tr>  <!---***This is where I want the dropdown containing the staff initials - table is called CAN_Staff ***-->
	<td><label>Initials:</label></td>
     <td><input name="Initials" size="12" type="text" />

	   <button type="submit" name="buttonSubmit" onclick="return confirm('Are you sure?')" />Add Concern</button>
	    </td>
         </tr>

 </table>
</form> 	 

Select allOpen in new window

This is the code to process the form data :

-----------------
updateconcern.asp
-----------------

<!--#include file="../../system/includes/inc_security.asp"-->
<!--#include file="adovbs_inc.asp"--> 

<% 'updateconcern.asp

  getSchoolID      = request.form("SchoolID")
  getConcern       = request.form("Concern") 
  getSeverity      = request.form("Severity") 
  getInitials      = request.form("Initials") 
  
' Now update the live CAN_Tbl_Pastoral_Manager table with the inserted record

 Set sqlProp=Server.CreateObject("ADODB.Command")
 sqlProp.ActiveConnection=Conn

  sqlProp.commandtext="INSERT INTO CAN_Tbl_Pastoral_Manager (dtmEntryTime, txtSchoolID, Concern, Severity, User_Initials) VALUES (GETDATE(),?,?,?,?)"
  sqlProp.Parameters.Append sqlProp.CreateParameter("@SchoolID",      adVarChar, adParamInput, 15,    getSchoolID)
  sqlProp.Parameters.Append sqlProp.CreateParameter("@Concern",       adVarChar, adParamInput, 1285,  getConcern)
  sqlProp.Parameters.Append sqlProp.CreateParameter("@Severity",      adInteger, adParamInput, 1,     getSeverity)
  sqlProp.Parameters.Append sqlProp.CreateParameter("@Initials",      adVarChar, adParamInput, 9,     getInitials)
  sqlProp.Execute

  Response.Redirect("viewstudent.asp?id=" & getSchoolID & "#DataEntryTest")
 %>

Select allOpen in new window

This is the main table CAN_Tbl_Pastoral_Manager, and is empty until someone enters a record :

CREATE TABLE CAN_Tbl_Pastoral_Manager(
	CAN_ID        INT IDENTITY(1,1) NOT NULL,
	dtmEntryTime  DATETIME NULL,
	User_Code     NVARCHAR(50) NULL,
	User_Initials NVARCHAR(50) NULL,
	txtSchoolID   NVARCHAR(50) NULL,
	Concern       TEXT NULL,
	Severity      INT NULL
)

Select allOpen in new window

This is the Staff table, and a few test records to work with :

CREATE TABLE CAN_Staff
(
	CAN_ID        INT IDENTITY(1,1) NOT NULL,
	Initials      NVARCHAR(10) NULL	
)

INSERT INTO CAN_Staff (Initials) VALUES ('BEN')
INSERT INTO CAN_Staff (Initials) VALUES ('BOB')
INSERT INTO CAN_Staff (Initials) VALUES ('JOE')
INSERT INTO CAN_Staff (Initials) VALUES ('ANN')

Select allOpen in new window

I hope this is all you will need to answer my question.

Many thanks!