Best way to populate a page with lots of SQL Data

I have a page that has about 8 dropdown boxes that need to be populated from sql tables. What is the best way to populate these boxes.

Here is how I have it now

conn =New SqlConnection(ConfigurationManager.AppSettings("SQLString"))

''''''''''''' Fill in DropDownList Status '''''''''''''''''''

strSelect ="SELECT * FROM Requests_Status"

cmdSelect =New SqlCommand(strSelect, conn)

conn.Open()

dtrSearch = cmdSelect.ExecuteReader()

ddlRequestStatus.DataSource = dtrSearch

ddlRequestStatus.DataTextField ="RequestStatusName"

ddlRequestStatus.DataValueField ="RequestStatus"

ddlRequestStatus.DataBind()

ddlRequestStatus.Items.Insert(0,New ListItem("-- Select Below --", -1))

cmdSelect.Cancel()

dtrSearch.Close()

conn.Close()

''''''''''''' Fill in DropDownList Container '''''''''''''''''''

strSelect ="SELECT * FROM Containers"

cmdSelect =New SqlCommand(strSelect, conn)

conn.Open()

dtrSearch = cmdSelect.ExecuteReader()

ddlContainer.DataSource = dtrSearch

ddlContainer.DataTextField ="ContainerName"

ddlContainer.DataValueField ="ContainerID"

ddlContainer.DataBind()

ddlContainer.Items.Insert(0,New ListItem("-- Select Below --", -1))

cmdSelect.Cancel()

dtrSearch.Close()

conn.Close()

'''''''''''''''''''''''''''''

I then repeat the same commands as above for the other 6 dropdowns. This seems like a bad way to have to do all this.

Thanks

Craig

Databind the dropdown controls to a SqlDatasource.|||

ugh... Isn't that what I am doing? What is the difference between what you say and the code I posted?

C

|||

You aren't using a SqlDatasource, you are databinding to a SqlCommand in code.

Switch to design view, look for a control named SqlDatasource and drop one on your page. Run the wizard. Set the dropdown's datasourceid to the name of the sqldatasource (Probably SqlDatasource1). Rinse and repeat.