Solved

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

Posted on 2004-05-01
9
855 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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 300 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now