creating a drop down combo box and using recordID in insert statement

You need get the relevant data from the database, then loop through each item, creating a select dropdown with the ID values associated with each written name displayed in the list.  Using PHP/mysql you could do the following:

#######################################################
//initialize link to database
$link_id = mysql_connect( $dbhost, $dbusername, $dbuserpassword );

//get regional information from all regions, sort alphabetically
$query1 = "SELECT RegionID, Region FROM tblRegion ORDER BY Region ASC";
$result1 = mysql_query( $query1, $link_id );

//get category information, sort alphabetically
$query2 = "SELECT CategoryID, Category FROM tblCategory ORDER BY Category ASC";
$result2 = mysql_query( $query2, $link_id );

//dropdown for regional information
//print out using for loop
echo "<select name=\"region\">\n";
for( $i=0; $i<mysql_num_rows($result1); $i++ ){
      echo "<option value=\"" . $mysql_result( $result1, $i, "RegionID" ) . "\">" . $mysql_result( $result1, $i, "Region" ) . "</option>\n";
}
echo "</select>\n";

//dropdown for categories
echo "<select name=\"category">\n";
for( $i=0; $i<mysql_num_rows($result2); $i++ ){
      echo "<option value=\"" . $mysql_result( $result2, $i, "CategoryID" ) . "\">" . $mysql_result( $result1, $i, "Category" ) . "</option>\n";
}
echo "</select>\n";
##############################################################


Now, when you submit the form it will hold values for the two fields "region" and "category".  The value will be the ID number of each relevant item.

So, to insert, you would do something like:

$queryInsert = "INSERT INTO tblClient ( `ClientID`, `RegionID`, `CategoryID` ) VALUES ( '" . $userID . "', '" . $_POST['region'] . "', '" . $_POST['category'] . "' );";

if( mysql_query( $queryInsert, $link_id ) )
      echo "record successfully inserted!<br />\n";
else
      echo "record insertion failed inserted!<br />\n";

You will most likely need to adapt the example here to your own needs, but hopefully it will give you an idea of the methodology for completing what you're trying to do.

//dropdown for categories
echo "<select name=\"category\">\n";
-------------------------------------|

I left out an escape character there b4 the quote..

Comment from minnirok
Date: 05/01/2004 05:50PM EDT
 Your Comment  


You need get the relevant data from the database, then loop through each item, creating a select dropdown with the ID values associated with each written name displayed in the list.  Using PHP/mysql you could do the following:

#######################################################
//initialize link to database
$link_id = mysql_connect( $dbhost, $dbusername, $dbuserpassword );

//get regional information from all regions, sort alphabetically
$query1 = "SELECT RegionID, Region FROM tblRegion ORDER BY Region ASC";
$result1 = mysql_query( $query1, $link_id );

//get category information, sort alphabetically
$query2 = "SELECT CategoryID, Category FROM tblCategory ORDER BY Category ASC";
$result2 = mysql_query( $query2, $link_id );

//dropdown for regional information
//print out using for loop
echo "<select name=\"region\">\n";
for( $i=0; $i<mysql_num_rows($result1); $i++ ){
     echo "<option value=\"" . $mysql_result( $result1, $i, "RegionID" ) . "\">" . $mysql_result( $result1, $i, "Region" ) . "</option>\n";
}
echo "</select>\n";

//dropdown for categories
echo "<select name=\"category\">\n";
for( $i=0; $i<mysql_num_rows($result2); $i++ ){
     echo "<option value=\"" . $mysql_result( $result2, $i, "CategoryID" ) . "\">" . $mysql_result( $result2, $i, "Category" ) . "</option>\n";
}
echo "</select>\n";
##############################################################
 

Thanks a lot to digest for a novice but I am sure I will pull it to pieces to try and understand it.  Pardon my ignorance but do I write this in the ASPX page?  Also, how do I insert the combo box and tie this code to it

Cheers
Lesley

the code for the example I wrote is in PHP (Hypertext Preprocessing)  http://www.php.net  It's an open source & free - It's portable to linux, win & mac and it's syntax is comprable to C++ or Java.  Like ASP, it will process code on the server and return HTML output.

The same basic concepts apply for both PHP and ASP & the SQL and HTML code remains the same.  The translation of the region select box into Visual Basic for ASP would be something like:

<%
dim link_id = Server.CreateObject("ADODB.Recordset")
dim query1 = "SELECT RegionID, Region FROM tblRegion ORDER BY Region ASC"
link_id.Open query1, "DSN=LesleysDatabase"

response.write "<select name=\"region\">" & vblfcr;
Do While NOT link_id.EOF
      response.write "<option value=\"" & link_id("RegionID") & "\">" & link_id("Region") & "</option>" & vblfcr
      link_id.MoveNext
Loop
response.write "<\select>" & vblfcr;
%>

The first step is getting your connection through ASP to the database and being able to write out the data you're looking for.

Once you get that, you'll want to structure your loop in such a way that you're printing out each option for the "Combo Box", which is known as a select box if we're thinking about the same thing.  So for each region, you're printing out:

<option value="regionID">region</option>

so assuming regionID is 4, and region is new york city:

<option value="4">New York City</option>

Now, you'll nest this inside a select tag, so that it will appear as a drop down menu.  If your table contained 3 regions, NYC, LA, and London then the HTML output would look similar to:

<select name="region">
<option value="2">London</option>
<option value="3">Los Angeles</option>
<option value="4">New York City</option>
</select>

Is this making sense at all?

This is my code so far:
<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls" Assembly="DreamweaverCtrls,version=1.0.0.0,publicKeyToken=836f606ede05d46a,culture=neutral" %><MM:Insert
runat="server"
CommandText='<%# "INSERT INTO dbo.tblClients (FirstName, LastName, EmailAddress) VALUES (@FirstName, @LastName, @EmailAddress)" %>'
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_connTest") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_connTest") %>'
Expression='<%# Request.Form("MM_insert") = "frmEmail" %>'
CreateDataSet="false"
SuccessURL='<%# "EmailAddedOK.htm" %>'
Debug="true"
><Parameters>
  <Parameter Name="@FirstName" Value='<%# IIf((Request.Form("txtFirstName") <> Nothing), Request.Form("txtFirstName"), "") %>' Type="VarChar" />  
  <Parameter Name="@LastName" Value='<%# IIf((Request.Form("txtLastName") <> Nothing), Request.Form("txtLastName"), "") %>' Type="VarChar" />  
  <Parameter Name="@EmailAddress" Value='<%# IIf((Request.Form("txtEmail") <> Nothing), Request.Form("txtEmail"), "") %>' Type="VarChar" />
</Parameters>
</MM:Insert>
<MM:DataSet
id="dsRegion"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_connTest") %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_DATABASETYPE_connTest") %>'
CommandText='<%# "SELECT *  FROM dbo.tblRegion" %>'
Debug="true"
></MM:DataSet>
<MM:PageBind runat="server" PostBackBind="true" />
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form runat="server" action="" method="post" name="frmEmail" target="_blank" id="frmEmail">
  <p>Enter details to subscribe to newsletter:</p>
  <p>First Name:
    <input name="txtFirstName" type="text" id="txtFirstName">
</p>
  <p>Last Name:
    <input name="txtLastName" type="text" id="txtLastName">
</p>
  <p>Email Address:
    <input name="txtEmail" type="text" id="txtEmail" size="50">
</p>
  <p>Region:
    <asp:dropdownlist
       DataMember="datamember"
         DataSource="<%# dsRegion.DefaultView %>"
         DataTextField="Region"
         DataValueField="RegionID"
         ID="dropdownlistid" runat="server">
</asp:dropdownlist>
</p>
  <p>
    <input type="submit" name="Submit" value="Insert Record">
</p>
  <input type="hidden" name="MM_insert" value="frmEmail">
</form>
</body>
</html>

As you will see, I have added a DropDownList called "dropDownListID", which displays the list of regions.  How do I insert the RegionID into the Clients table?  As you can see, my code does not do this yet?  I know it needs to say something like "insert into tblClients VALUES (@FirstName, @LastName, @EmailAddress,@RegionID)" but I don't know how to assign "dropdownlistid" as the parameter for @RegionID

Thanking you in advance

Lesley

I will try that but I want to store the regionid, although the user will select the "text" e.g. Region is called Bay of Plenty but its record number is 3, so I want the user to select "Bay of Plenty" from the list and write 3 to the table.

Does this make sense at all?

Cheers
Lesley

I understand what you are trying to accomplish.  
I tried to understand from your code what the form field name was for the drop down.  Im not terribly familiar with ASP.NET but very familiar with ASP.
In order to get values (not the labels or names such as "Bay of Plenty", but actual values such as 3") from form fields that have been submitted through the "POST" method, you would use @regionID = request.form("formfieldname").
So if the drop down name is 'dropdownlistid' and you are submitting through "POST" method you would do @regionID=request.form("dropdownlistid"), and if you selected "Bay of plenty" on the form, the value of @regionID should be 3.  

If you are submitting the form using the GET method, you would use something along the lines of @regionID = request.querystring("dropdownlistid").  You could just do request("dropdownlistID") and it would work for POST, or GET method.  By the looks of your other variables they are getting data from the request.form method.  

As long as you are referencing the form field name, whether its a drop down, a text field or a select box the request("fieldname") will equal the value associated with the form.  If its a text field it will simply equal the text typed in, if its a drop down it will equal the drop down value(not the label)... and so on.

I know its a somewhat technical explanation but I hope it helps.