467,912 Members | 1,410 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,912 developers. It's quick & easy.

Combining two different data types in a listbox

I currently have a list box that contains regions in the US
(Northeast, Midwest, South, etc.). I am retrieving this data from my
Region table(see below). Users have the ability to select a region as
part of a search. The results were too broad so we had to make an
Office table.

Region Table
RegionCd varchar(2)
RefionDesc varchar(50)

Office Table
OfficeId Int
OfficeName varchar(50)
RegionCd varchar(2)

What I want to do is load a listbox so that it has the region first
and then all the related offices below it:

Midwest
Chicago
Des Moines
Northeast
Boston
New York
....

The problem here would be that the region code is a two character
varchar code and the office is an integer. How can I combine in one
list box to perform my search?
Jul 19 '05 #1
  • viewed: 2172
Share:
4 Replies
One idea is to prefix the region codes with RC and office codes with OC, and
trap these in your search, and proceed.

<item id="RC12">Midwest
<item id="OC12"> Chicago

Then on the server side,

If Left(Request.Form("itemid"), 2) = "RC"
... string search
Else
... integer search
End If

Hope that helps.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"John" <jr****@yahoo.com> wrote in message
news:f8**************************@posting.google.c om...
I currently have a list box that contains regions in the US
(Northeast, Midwest, South, etc.). I am retrieving this data from my
Region table(see below). Users have the ability to select a region as
part of a search. The results were too broad so we had to make an
Office table.

Region Table
RegionCd varchar(2)
RefionDesc varchar(50)

Office Table
OfficeId Int
OfficeName varchar(50)
RegionCd varchar(2)

What I want to do is load a listbox so that it has the region first
and then all the related offices below it:

Midwest
Chicago
Des Moines
Northeast
Boston
New York
...

The problem here would be that the region code is a two character
varchar code and the office is an integer. How can I combine in one
list box to perform my search?

Jul 19 '05 #2
"John" <jr****@yahoo.com> wrote in message
news:f8**************************@posting.google.c om...
I currently have a list box that contains regions in the US
(Northeast, Midwest, South, etc.). I am retrieving this data from my
Region table(see below). Users have the ability to select a region as
part of a search. The results were too broad so we had to make an
Office table.

Region Table
RegionCd varchar(2)
RefionDesc varchar(50)

Office Table
OfficeId Int
OfficeName varchar(50)
RegionCd varchar(2)

What I want to do is load a listbox so that it has the region first
and then all the related offices below it:

Midwest
Chicago
Des Moines
Northeast
Boston
New York
...

The problem here would be that the region code is a two character
varchar code and the office is an integer. How can I combine in one
list box to perform my search?


At first, I thought you wanted an OPTGROUP:
http://www.w3.org/TR/html4/interact/forms.html#h-17.6

But I assume you want to be able to pass EITHER the region or the office in
your search.

As for combining them... I assume you are asking how to combine them with a
SQL statement? Something like this:

SELECT
Region.RegionCd, Region.RegionDesc, Office.OfficeId, Office.OfficeName
FROM
Region
INNER JOIN
Office
ON
Region.RegionCd = Office.RegionCd
ORDER BY
Region.RegionDesc, Office.OfficeName

This would give you a recordset like:

1, Midwest, 1, Chicago
1, Midwest, 3, Des Moines
2, Northeast, 2, Boston
2, Northeast, 4, New York

Use those values as the values for your select options, but perhaps prepend
something to each that identifies weather it's an OfficeID or a RegionCd.
For example:

<select name="foo">
<option name="R1">Midwest</option>
<option name="O1">Chicago</option>
<option name="O3">Des Moines</option>
<option name="R2">Northeast</option>
<option name="O2">Boston</option>
<option name="O4">New York</option>
</select>

Then on the page that this gets submitted to, take the value of "foo", strip
off the first character to see whether it's R for Region or O for Office,
and then use the rest of the value for performing the search as needed.

Hope this helps.
Regards,
Peter Foti

Jul 19 '05 #3
Thanks. Then on my stored procedure I will have to do some sort of
If..Then to search by Office or Region. Depending on which was
selected. Correct?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 19 '05 #4
"Amy Snyder" <am**@yahoo.com> wrote in message
news:uh**************@TK2MSFTNGP11.phx.gbl...
Thanks. Then on my stored procedure I will have to do some sort of
If..Then to search by Office or Region. Depending on which was
selected. Correct?


Close. I imagine you would probably want to have 2 stored proceedures...
one to search by Office, and one to search by Region. Then, on the page
that you submit the form to, you would call the appropriate stored
proceedure based on which value was selected.

Regards,
Peter
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by Phillip Rhodes | last post: by
7 posts views Thread by James CC | last post: by
11 posts views Thread by Brian | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.