Which Is the Best Approach?

R

rn5a

A Form has a select list which is populated from a MS-Access database
table. The DB table from where the select list is populated has 2
columns - CountryID & CountryName. When the Form is posted, the option
selected in the select list is inserted in another DB table wherein
the column in which the value of the selected option will be populated
accepts only integers. Actually there are 7-8 select lists & the
values of all the selected options in all the select lists are
populated in another DB table. The DB tables also have Primary Keys,
Foreign Keys, Constraints etc. I am using only a single select list
here to maintain brevity.

Now I have set the value of each option in the select list to the
CountryID column & not the CountryName column which means that the
HTML source of the select list would look something like this:

<select name="selCountry">
<option value="1">Afghanistan</option>
<option value="2">Belgium</option>
<option value="3">Canada</option>
<option value="4">Denmark</option>
<option value="5">Egypt</option>
<option value="6">Finland</option>
<option value="7">Ghana</option>
<option value="8">Hungary</option>
<option value="9">India</option>
.........
</select>

Assume that I have selected Canada from the above select list. Now
when the Form that houses the select list is posted,
Request.Form("selCountry") in the action page will be 3. The action
page also needs to send a e-mail to the webmaster with the country
name the user has selected. Since I have selected Canada, the
webmaster will get a mail which will say that "the user has selected
Canada" but since Request.Form("selCountry")=3, the webmaster will get
a mail saying "the user has selected 3" but this is not what the
webmaster wants to see when he gets the mail. He wants the mail to say
"the user has selected Canada" & not "the user has selected 3".

There are quite a few ways to overcome this issue, three of which I am
talking about.

1. Add a hidden field in the Form whose name is, say, "hdnCountry" &
change it's value depending upon the option selected by a user in the
select list with JavaScript using the onChange event function of the
select list. So if a user first selects, say, Egypt in the select
list, the hidden field value will be Egypt. Next he selects Canada;
the hidden field value will change to Canada (note that only 1 option
can be selected in the select list). When the Form posts, use
Request.Form("hdnCountry") in the e-mailing code (instead of
Request.Form("selCountry")) which will give the name of the country.
Since there's only 1 select list, the increase in the volume of data
sent by the browser to the server will be small since along with the
value of the select list, the browser also has to send the value of
the hidden field to the server but if there are 7-8 select lists, then
the volume of data the browser sends to the server will increase
drastically.

2. No hidden field here; only the select list is present.
Request.Form("selCountry") will be 3 (assuming Canada is selected in
the select list). Next using Request.Form("selCountry"), get the
corresponding country name from the database which means an additional
frontend-backend communication which can be avoided if a hidden field
is placed in the Form as mentioned above.

3. No hidden field here as well; only the select list but the value of
the options in the select list is changed so that it includes the
CountryID as well as the CountryName with the two being delimited by a
comma (or something else) which means that the HTML source of the
select list would look something like this:

<select name="selCountry">
<option value="1, Afghanistan">Afghanistan</option>
<option value="2, Belgium">Belgium</option>
<option value="3, Canada">Canada</option>
<option value="4, Denmark">Denmark</option>
<option value="5, Egypt">Egypt</option>
<option value="6, Finland">Finland</option>
<option value="7, Ghana">Ghana</option>
<option value="8, Hungary">Hungary</option>
<option value="9, India">India</option>
.........
</select>

Assuming that Canada is selected in the select list, when the Form
posts, the value of Request.Form("selCountry") will be *3, Canada* (of
course, without the *s). Using various VBScript string functions like
Split, separate Request.Form("selCountry") into 2 halves so that the
1st half retrieves the CountryID & the second half retrieves the
CountryName. So while inserting the selected option in the DB table,
populate the DB table with the first half & send the mail using the
second half of Request.Form("selCountry"). But this method will
consume additional server resources since the server has to do the
additional tasks of using the string functions & dividing
Request.Form("selCountry") into 2 parts.

All the above 3 methods I have mentioned have their pros & cons. So
which one will be the best way to approach in such a situation?

Again please keep in mind that I have used only 1 select list in this
example to maintain brevity but in reality, there are around 7-8
(maybe even more) select lists.
 
M

McKirahan

A Form has a select list which is populated from a MS-Access database
table. The DB table from where the select list is populated has 2
columns - CountryID & CountryName. When the Form is posted, the option
selected in the select list is inserted in another DB table wherein
the column in which the value of the selected option will be populated
accepts only integers.
[snip]

Request.Form("selCountry") into 2 parts.

CountryID = Split(Request.Form("selCountry"),",")(0)
All the above 3 methods I have mentioned have their pros & cons. So
which one will be the best way to approach in such a situation?

[snip]

I would not recommend assigning a number to a country.
Currently yours are in alphabetical and numerical sequence;
what happens when a new country is added?

However, pre-assigned three-digit numbers already exist:
http://en.wikipedia.org/wiki/ISO_3166-1_numeric


<select name="selCountry">
<option value="004">Afghanistan</option>
<option value="056">Belgium</option>
<option value="124">Canada</option>
<option value="208">Denmark</option>
<option value="818">Egypt</option>
<option value="246">Finland</option>
<option value="188">Ghana</option>
<option value="348">Hungary</option>
<option value="356">India</option>
.........
</select>

Build an additional table with these codes and names.
 
R

rn5a

A Form has a select list which is populated from a MS-Access database
table. The DB table from where the select list is populated has 2
columns - CountryID & CountryName. When the Form is posted, the option
selected in the select list is inserted in another DB table wherein
the column in which the value of the selected option will be populated
accepts only integers.
[snip]

Request.Form("selCountry") into 2 parts.

CountryID = Split(Request.Form("selCountry"),",")(0)
All the above 3 methods I have mentioned have their pros & cons. So
which one will be the best way to approach in such a situation?

[snip]

I would not recommend assigning a number to a country.
Currently yours are in alphabetical and numerical sequence;
what happens when a new country is added?

However, pre-assigned three-digit numbers already exist:
http://en.wikipedia.org/wiki/ISO_3166-1_numeric

<select name="selCountry">
<option value="004">Afghanistan</option>
<option value="056">Belgium</option>
<option value="124">Canada</option>
<option value="208">Denmark</option>
<option value="818">Egypt</option>
<option value="246">Finland</option>
<option value="188">Ghana</option>
<option value="348">Hungary</option>
<option value="356">India</option>
........
</select>

Build an additional table with these codes and names.
Currently yours are in alphabetical and numerical sequence

That's just an example but in reality, it may or may not be in
alphabetical & numerical sequence. I want the countries to be listed
alphabetically. So the SQL query will use the ORDER BY clause
something like this:

SELECT * FROM Countries ORDER BY CountryName

Let new countries be added or existing countries be deleted, what
difference does it make?

The Country codes I have assigned is to link/JOIN the Countries table
with some other table. They are for the programmers use; ultimately
users won't be seeing or using those codes in anyway. For e.g. after
adding some items to his cart, a user is told to enter his billing
address. He selects, say, Canada as his country. After confirming,
Canada's country code, which is 3 in this case will be inserted in
another DB table named, say, Details, which holds all the other
personal details of users (like first & last name, e-mail, phone no.
etc.).

Later a user wants to view the order details wherein country is one of
the details. To do so, the SQL query will be something like this:

SELECT D.CountryID, C.CountryName,....FROM Details AS D INNER JOIN
Countries AS C ON C.CountryID=D.CountryID WHERE..........

Then <%= objRS("CountryName") %> will display him his country (objRS
is the recordset).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,734
Messages
2,569,441
Members
44,832
Latest member
GlennSmall

Latest Threads

Top