Best way to populate ListBox from SQL Server?

[Robert W.]

I have a SQL Server table called "ClosedMonths" that contains two pertinent
fields:

Yearx Monthx
2000 1
2000 2
2000 3
I want to simply query this table and populate a WebControls listbox with
the contents, though in this format:

Jan 2000
Feb 2000
Mar 2000
So I wrote the following code:

DataSet dataSet = new DataSet();
dataSet.Tables.Add(BusObjects.Archiving.GetClosedM onths());
dataSet.Tables[0].Columns.Add("MonthDate");
foreach(DataRow row in dataSet.Tables[0].Rows)
{
row["MonthDate"] = Enum.GetName(typeof(MonthShort), row["Monthx"])
+ " " + row["Yearx"].ToString();
}

listBoxDates.DataSource = dataSet.Tables[0];
listBoxDates.DataTextField = "MonthDate";

listBoxDates.DataBind();
Where 'MonthShort' is a simple enumeration in which Jan = 1, Feb = 2, etc.

Anyhow, this code works fine but I'm wondering if there isn't a simpler way
to do it?
I also have a second question: Is there a way to change the alignment of the
contents of the ListBox to be something other than left-aligned?

--
Robert W.
Vancouver, BC
www.mwtech.com

[sloan]

Look at the ListItem, and add them individually.

ListItem li = new ListItem("--Select--" , 0);
ddlMyDropDown.Items.Add (li);

li = new ListItem("hello");
ddlMyDropDown.Items.Add(li);

There are 3 constructors for ListItem, pick the best one for your needs.

(I'm going by memory above, but it should be close enough)

That's a suggestion if you want to modify the items text ... as it comes
from the database.
(aka, this is a "presentation" change, so looping thru the DataSet items to
alter the display is acceptable)

...

go to the aspx page, and the html ... and see if there is a "align" property
for the listbox. I don't think there is.


"Robert W." <Ro*****@discussions.microsoft.com> wrote in message
news:28**********************************@microsof t.com...

I have a SQL Server table called "ClosedMonths" that contains two pertinent fields:

Yearx Monthx
2000 1
2000 2
2000 3
I want to simply query this table and populate a WebControls listbox with
the contents, though in this format:

Jan 2000
Feb 2000
Mar 2000
So I wrote the following code:

DataSet dataSet = new DataSet();
dataSet.Tables.Add(BusObjects.Archiving.GetClosedM onths());
dataSet.Tables[0].Columns.Add("MonthDate");
foreach(DataRow row in dataSet.Tables[0].Rows)
{
row["MonthDate"] = Enum.GetName(typeof(MonthShort), row["Monthx"]) + " " + row["Yearx"].ToString();
}

listBoxDates.DataSource = dataSet.Tables[0];
listBoxDates.DataTextField = "MonthDate";

listBoxDates.DataBind();
Where 'MonthShort' is a simple enumeration in which Jan = 1, Feb = 2, etc.

Anyhow, this code works fine but I'm wondering if there isn't a simpler way to do it?
I also have a second question: Is there a way to change the alignment of the contents of the ListBox to be something other than left-aligned?

--
Robert W.
Vancouver, BC
www.mwtech.com

[Robert W.]

Dear Sloan,

Thanks for your feedback. But I was actually hoping that some SQL Server
whiz might know a much more streamlined way to 'create' the required data
table "on the fly".
--
Robert W.
Vancouver, BC
www.mwtech.com

"sloan" wrote:

Look at the ListItem, and add them individually.

ListItem li = new ListItem("--Select--" , 0);
ddlMyDropDown.Items.Add (li);

li = new ListItem("hello");
ddlMyDropDown.Items.Add(li);

There are 3 constructors for ListItem, pick the best one for your needs.

(I'm going by memory above, but it should be close enough)

That's a suggestion if you want to modify the items text ... as it comes
from the database.
(aka, this is a "presentation" change, so looping thru the DataSet items to
alter the display is acceptable)

...

go to the aspx page, and the html ... and see if there is a "align" property
for the listbox. I don't think there is.


"Robert W." <Ro*****@discussions.microsoft.com> wrote in message
news:28**********************************@microsof t.com...

I have a SQL Server table called "ClosedMonths" that contains two

pertinent

fields:

Yearx Monthx
2000 1
2000 2
2000 3
I want to simply query this table and populate a WebControls listbox with
the contents, though in this format:

Jan 2000
Feb 2000
Mar 2000
So I wrote the following code:

DataSet dataSet = new DataSet();
dataSet.Tables.Add(BusObjects.Archiving.GetClosedM onths());
dataSet.Tables[0].Columns.Add("MonthDate");
foreach(DataRow row in dataSet.Tables[0].Rows)
{
row["MonthDate"] = Enum.GetName(typeof(MonthShort),

row["Monthx"])

+ " " + row["Yearx"].ToString();
}

listBoxDates.DataSource = dataSet.Tables[0];
listBoxDates.DataTextField = "MonthDate";

listBoxDates.DataBind();
Where 'MonthShort' is a simple enumeration in which Jan = 1, Feb = 2, etc.

Anyhow, this code works fine but I'm wondering if there isn't a simpler

way

to do it?
I also have a second question: Is there a way to change the alignment of

the

contents of the ListBox to be something other than left-aligned?

--
Robert W.
Vancouver, BC
www.mwtech.com

[bruce barker \(sqlwork.com\)]

the typical sql way would be to create a domain table Monthxx, MonthName and
join to it, or use a case statement, or user defined function if done option

use case:

select case Monthx
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
when 9 then 'Aug'
when 10 then 'Sep'
when 11 then 'Nov'
when 12 then 'Dec'
end + ' ' + convert(varchar,Yearx) as MonthDate
from ClosedMonths

or calc it:

select substring('JanFebMarAprMayJunJulAugSepOctNovDec',( Monthx-1)* 3 + 1,3)
+ ' ' + convert(varchar,Yearx) as MonthDate
from ClosedMonths

-- bruce (sqlwork.com)

"Robert W." <Ro*****@discussions.microsoft.com> wrote in message
news:C6**********************************@microsof t.com...

Dear Sloan,

Thanks for your feedback. But I was actually hoping that some SQL Server
whiz might know a much more streamlined way to 'create' the required data
table "on the fly".
--
Robert W.
Vancouver, BC
www.mwtech.com

"sloan" wrote:

Look at the ListItem, and add them individually.

ListItem li = new ListItem("--Select--" , 0);
ddlMyDropDown.Items.Add (li);

li = new ListItem("hello");
ddlMyDropDown.Items.Add(li);

There are 3 constructors for ListItem, pick the best one for your needs.

(I'm going by memory above, but it should be close enough)

That's a suggestion if you want to modify the items text ... as it comes
from the database.
(aka, this is a "presentation" change, so looping thru the DataSet items
to
alter the display is acceptable)

...

go to the aspx page, and the html ... and see if there is a "align"
property
for the listbox. I don't think there is.


"Robert W." <Ro*****@discussions.microsoft.com> wrote in message
news:28**********************************@microsof t.com...

> I have a SQL Server table called "ClosedMonths" that contains two

pertinent

> fields:
>
> Yearx Monthx
> 2000 1
> 2000 2
> 2000 3
>
>
> I want to simply query this table and populate a WebControls listbox
> with
> the contents, though in this format:
>
> Jan 2000
> Feb 2000
> Mar 2000
>
>
> So I wrote the following code:
>
> DataSet dataSet = new DataSet();
> dataSet.Tables.Add(BusObjects.Archiving.GetClosedM onths());
> dataSet.Tables[0].Columns.Add("MonthDate");
> foreach(DataRow row in dataSet.Tables[0].Rows)
> {
> row["MonthDate"] = Enum.GetName(typeof(MonthShort),

row["Monthx"])

> + " " + row["Yearx"].ToString();
> }
>
> listBoxDates.DataSource = dataSet.Tables[0];
> listBoxDates.DataTextField = "MonthDate";
>
> listBoxDates.DataBind();
>
>
> Where 'MonthShort' is a simple enumeration in which Jan = 1, Feb = 2,
> etc.
>
> Anyhow, this code works fine but I'm wondering if there isn't a simpler

way

> to do it?
>
>
> I also have a second question: Is there a way to change the alignment
> of

the

> contents of the ListBox to be something other than left-aligned?
>
> --
> Robert W.
> Vancouver, BC
> www.mwtech.com
>