Arrays and DD boxes

[Steve G]

Hi All:

I am trying to set up a drop-down box in a web page, which gets populated by
information from a database. I'm using the getrows method to set up the
values of data to be passed from the form. Something like:

alldata=objrs.getrows
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)
<%
for i = 0 to numRows
response.write "<option value="""
response.write alldata(0,i)
response.write """>"
response.write alldata(0,i)
response.write "</option>"
next
%>

This is working fine, in terms of passing the correct data. The thing is, I
would like to change the last response.write so that it displays text other
than what is coming out of the database. For instance, if the value of
allData(0,i) = cmo, I would it to appear in the list as Central Missouri.
I've come close to making it work a couple of times, but just can't seem to
get it right. Could anyone add a helping hand? I sure would appreciate it.

TIA

Steve G

[Tim Williams]

How are you mapping "cmo" to "Central Missouri". Do you have a table in
your database with this information?

If so, you should be able to bring that information in with the query which
populates your list. If the full value is the second column in your
recordset then you would just switch the line to:

response.write alldata(1,i)


If not, you'll have to use some other mechanism: if you have relatively few
of values to map then a "select case" approach would work.

Tim.


"Steve G" <steve@nospam.tnccreations.com> wrote in message
news:OzrDGwIuDHA.2464@TK2MSFTNGP12.phx.gbl...

> Hi All:
>
> I am trying to set up a drop-down box in a web page, which gets populated

by

> information from a database. I'm using the getrows method to set up the
> values of data to be passed from the form. Something like:
>
> alldata=objrs.getrows
> numcols=ubound(alldata,1)
> numrows=ubound(alldata,2)
> <%
> for i = 0 to numRows
> response.write "<option value="""
> response.write alldata(0,i)
> response.write """>"
> response.write alldata(0,i)
> response.write "</option>"
> next
> %>
>
> This is working fine, in terms of passing the correct data. The thing is,

I

> would like to change the last response.write so that it displays text

other

> than what is coming out of the database. For instance, if the value of
> allData(0,i) = cmo, I would it to appear in the list as Central Missouri.
> I've come close to making it work a couple of times, but just can't seem

to

> get it right. Could anyone add a helping hand? I sure would appreciate it.
>
> TIA
>
> Steve G
>
>

[Aaron Bertrand [MVP]]

> How are you mapping "cmo" to "Central Missouri". Do you have a table in

> your database with this information?

Sounded to me like Steve expected the database to know that Missouri is
abbreviated as MO. :-)

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Steve G]

No, I am smart enough to know better than that.

The values in the database are abbreviations for the full text I am trying
to display. The values are being passed to another page to display data from
the database related to those values. I am simply trying to display text in
the drop down box that will make more sense to the viewer than the
abbreviations. I tried select case, however I am unable to get it to work.
Instead of showing each of the text descriptions, it is stopping at the
first item, and displays that description for each of the items in the
array. For instance, if there are 6 items in the array, it will show the
first description for all 6 items. It does however, pass the correct value
to the next page, so at least that part is working correctly.

Steve G

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:OrSbCnJuDHA.1996@TK2MSFTNGP12.phx.gbl...

> > How are you mapping "cmo" to "Central Missouri". Do you have a table in
> > your database with this information?

>
> Sounded to me like Steve expected the database to know that Missouri is
> abbreviated as MO. :-)
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

[Bob Barrows]

You can eliminate all of these questions by showing us your database table
structure and the sql query you are using to retrieve the data.

Bob Barrows

Steve G wrote:

> No, I am smart enough to know better than that.
>
> The values in the database are abbreviations for the full text I am
> trying to display. The values are being passed to another page to
> display data from the database related to those values. I am simply
> trying to display text in the drop down box that will make more sense
> to the viewer than the abbreviations. I tried select case, however I
> am unable to get it to work. Instead of showing each of the text
> descriptions, it is stopping at the first item, and displays that
> description for each of the items in the array. For instance, if
> there are 6 items in the array, it will show the first description
> for all 6 items. It does however, pass the correct value to the next
> page, so at least that part is working correctly.
>
> Steve G
>
> "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
> news:OrSbCnJuDHA.1996@TK2MSFTNGP12.phx.gbl...

>>> How are you mapping "cmo" to "Central Missouri". Do you have a
>>> table in your database with this information?

>>
>> Sounded to me like Steve expected the database to know that Missouri
>> is abbreviated as MO. :-)
>>
>> --
>> Aaron Bertrand
>> SQL Server MVP
>> [url]http://www.aspfaq.com/[/url]

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Steve G]

Thanks for your response, Bob:

Database Table:
Name = region
field 1 is ID contains values of 1, 2, 3, 4
field 2 is Description contains values of cmo, swmo, semo, nwmo

Retrieving data (I've left some of the obvious stuff out such as the
connection)

set objRs = server.createobject("ADODB.recordset")
Query is: "Select distinct description from region"

Setting up array:

alldata=objRs.getrows
numcols=ubound(alldata,1)
numrows=ubound(alldata,2)

Populating the drop down box:

for i = 0 to numRows
response.write "<option value="""
response.write alldata(0,i)
response.write """>"
response.write alldata(0,i) //// this is where I need to convert the
data from the database to text descriptions
response.write "</option>"
next

Steve G

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23jDoLfOuDHA.2308@TK2MSFTNGP09.phx.gbl...

> You can eliminate all of these questions by showing us your database table
> structure and the sql query you are using to retrieve the data.
>
> Bob Barrows
>
> Steve G wrote:

> > No, I am smart enough to know better than that.
> >
> > The values in the database are abbreviations for the full text I am
> > trying to display. The values are being passed to another page to
> > display data from the database related to those values. I am simply
> > trying to display text in the drop down box that will make more sense
> > to the viewer than the abbreviations. I tried select case, however I
> > am unable to get it to work. Instead of showing each of the text
> > descriptions, it is stopping at the first item, and displays that
> > description for each of the items in the array. For instance, if
> > there are 6 items in the array, it will show the first description
> > for all 6 items. It does however, pass the correct value to the next
> > page, so at least that part is working correctly.
> >
> > Steve G
> >
> > "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
> > news:OrSbCnJuDHA.1996@TK2MSFTNGP12.phx.gbl...

> >>> How are you mapping "cmo" to "Central Missouri". Do you have a
> >>> table in your database with this information?
> >>
> >> Sounded to me like Steve expected the database to know that Missouri
> >> is abbreviated as MO. :-)
> >>
> >> --
> >> Aaron Bertrand
> >> SQL Server MVP
> >> [url]http://www.aspfaq.com/[/url]

>
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Aaron Bertrand - MVP]

> Database Table:

> Name = region
> field 1 is ID contains values of 1, 2, 3, 4
> field 2 is Description contains values of cmo, swmo, semo, nwmo

So where does cmo map to Central Missouri?

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Steve G]

The text "Central Missouri" does not exist in the database. I think Tim was
on the right track about using Select Case, but I am unable to figure out
the syntax for it.

Tried something like:

regions = allData(0,i)
Select Case Regions
case cmo
response.write "Central Missouri"
case swmo
response.write "Southwest Missouri"
end select

But this does not result in what I want.

Steve G

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:uLqU6tOuDHA.3224@tk2msftngp13.phx.gbl...

> > Database Table:
> > Name = region
> > field 1 is ID contains values of 1, 2, 3, 4
> > field 2 is Description contains values of cmo, swmo, semo, nwmo

>
> So where does cmo map to Central Missouri?
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

[Aaron Bertrand - MVP]

> The text "Central Missouri" does not exist in the database. I think Tim
was

> on the right track about using Select Case, but I am unable to figure out
> the syntax for it.

Or you could just put the text in the database, instead of hard-coding it
into your ASP page!

> regions = allData(0,i)
> Select Case Regions
> case cmo
> response.write "Central Missouri"

Did you try treating "cmo" as a string, instead of a variable name?

Select Case allData(0,i)
case "cmo"
response.write "Central Missouri"

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Steve G]

I can't hard code it, since it needs to be dynamic. Eventually, the data
displayed in the list will change, depending upon how someone gets to the
page that shows the drop down. The sql will change to a parameter query such
as:
"Select distinct description from region where category = someValue"
At that time, category will be added to the list of fields in the table, and
the data that appears in the drop down box will be dependent on the where
clause.

Yes, I have tried encasing the value in quotes, but I get the same results,
or maybe better said, no results. Below is the complete code I am trying to
use currently without the quotes:

for i = 0 to numRows2
response.write "<option value="""
response.write alldata(0,i)
response.write """>"

regions = allData(0,i)
select case regions
case cmo
response.write "Central Missouri"
case fl
response.write "Florida"
case ia
response.write "Iowa"
case il
response.write "Illinois"
case kc
response.write "Kansas City"
case ks
response.write "Kansas"
case nc
response.write "North Carolina"
case ne
response.write "Nebraska"
case neks
response.write "Northeast Kansas"
case nemo
response.write "Northeast Missouri"
case nwks
response.write "Northwest Kansas"
case nwmo
response.write "Northwest Missouri"
case pa
response.write "Pennsylvania"
case sd
response.write "South Dakota"
case seks
response.write "Southeast Kansas"
case semo
response.write "Southeast Missouri"
case sl
response.write "St. Louis"
case swks
response.write "Southwest Kansas"
case swmo
response.write "Southwest Missouri"
case tx
response.write "Texas"
end select
response.write "</option>"
next



"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:%23RTX51OuDHA.2360@TK2MSFTNGP09.phx.gbl...

> > The text "Central Missouri" does not exist in the database. I think Tim

> was

> > on the right track about using Select Case, but I am unable to figure

out

> > the syntax for it.

>
> Or you could just put the text in the database, instead of hard-coding it
> into your ASP page!
>

> > regions = allData(0,i)
> > Select Case Regions
> > case cmo
> > response.write "Central Missouri"

>
> Did you try treating "cmo" as a string, instead of a variable name?
>
> Select Case allData(0,i)
> case "cmo"
> response.write "Central Missouri"
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

[Aaron Bertrand - MVP]

> I can't hard code it, since it needs to be dynamic.

You're contradicting yourself. The point of a database is so that it can be
dynamic. Hard-coding it into a SELECT CASE statement on your ASP page is,
well, hard-coding it.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Phillip Windell]

"Steve G" <steve@nospam.tnccreations.com> wrote in message
news:uBMgzCPuDHA.3116@tk2msftngp13.phx.gbl...

> regions = allData(0,i)
> select case regions
> case cmo
> response.write "Central Missouri"
> case fl
> response.write "Florida"
> case ia

Use the quotes, that is *not* an optional thing. If you don't it will
try to treat them as variable names. Also use a Case Else and
Response.End to trap it when there is no match due to a problem. Then
you will *know* what the current value of regions is, even if it is
blank. Do it like this......

regions = allData(0,i)
select case regions
case "cmo"
response.write "Central Missouri"
case "fl"
response.write "Florida"
case else
response.write "No match, something is wrong!<br>"
response.write "The current value of Regions is: " &
regions & "<br>"
response.end
end select


--

Phillip Windell [CCNA, MVP, MCP]
WAND-TV (ABC Affiliate)
[url]www.wandtv.com[/url]