Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-05-01
9
Medium Priority
?
881 Views
Last Modified: 2006-11-17
I have three tables: tblClient, tblRegion and tblCategory.  The tblClient has foreign key relationships with tblRegion and tlbCategory.  I want a web form, where the client can select a region and a category, from a drop-down combo box, which are populates with the values from the related tables i.e. tblRegion preferably shows the Region however, I want to insert the RegionID into the Client table and likewise with the category table.

How do I populate the drop down combo boxes? How do I get the record ID for the selected entries and write this in the insert statement?

Any help at all gratefully appreciated

Cheers
Lesley
0
Comment
Question by:lesleyag
  • 4
  • 3
  • 2
9 Comments
 
LVL 7

Expert Comment

by:minnirok
ID: 10969235
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.
0
 
LVL 7

Expert Comment

by:minnirok
ID: 10969241
//dropdown for categories
echo "<select name=\"category\">\n";
-------------------------------------|

I left out an escape character there b4 the quote..
0
 
LVL 7

Expert Comment

by:minnirok
ID: 10969243
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";
##############################################################
 
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lesleyag
ID: 10969316
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
0
 
LVL 7

Expert Comment

by:minnirok
ID: 10969683
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?
0
 

Author Comment

by:lesleyag
ID: 10970808
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
0
 

Accepted Solution

by:
steveshap earned 1200 total points
ID: 10972044
I usually write my own querys outside of dreamweaver's standard code but you could try this
Change this line:

CommandText='<%# "INSERT INTO dbo.tblClients (FirstName, LastName, EmailAddress) VALUES (@FirstName, @LastName, @EmailAddress)" %>'

to this:

CommandText='<%# "INSERT INTO dbo.tblClients (FirstName, LastName, EmailAddress, RegionID) VALUES (@FirstName, @LastName, @EmailAddress, @RegionID)" %>'

And add this line to the <parameters> section:

<Parameter Name="@regionID" Value='<%# IIf((Request.Form("regionID") <> Nothing), Request.Form("regionID"), "") %>' Type="VarChar" />

In the above I assumed the form drop down box you are using was named "regionID" and that the database variable type is "VarChar".  If the form field for the drop down box is named something else, change the name in the two instances of Request.Form("<insert form field name here>") above.  Try that and see if it works out for you.
0
 

Author Comment

by:lesleyag
ID: 10973027
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
0
 

Expert Comment

by:steveshap
ID: 10973533
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.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question