Best way to populate ListBox from SQL Server?

[Guest]

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.GetClosedMonths());
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?

 

[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.

 

[Guest]

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

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.

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.GetClosedMonths());
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?

 

[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)

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

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.

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.GetClosedMonths());
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?