Populate a Dropdown list With SQL

问题:

i have a DropDownList in my asp.net website but i can not get it to populate with data from my sql server.

am i missing something?

Dim sqlconn As New SqlConnection  
Dim sqCmd As New SqlCommand       
Dim sqlreader As SqlDataReader    

sqlconn.ConnectionString = "server = .......;Database=......;User ID=....
Password=...." 

sqCmd.Connection = sqlconn
sqlconn.Open()
sqCmd.CommandText = "SELECT DISTINCT CompanyDivision FROM Reports"
sqlreader = sqCmd.ExecuteReader()

DropDownList.DataSource = sqlreader
DropDownList.DataTextField = "CompanyDivision"
DropDownList.DataValueField = "CompanyDivision"
DropDownList.DataBind()

sqlreader.Close()
sqlconn.Close()

回答1:

The most straightforward way of attaching a sql query to a dropdown is through the use of a SqlDataSource in the aspx.

You can do it like this:

<asp:SqlDataSource runat="server" 
                   ID="CompanyDivisionSource"
                   DataSourceMode="DataReader"
                   ConnectionString="<%$ ConnectionStrings:YourConnectionStringKey%>"
                   SelectCommand="SELECT DISTINCT CompanyDivision FROM Reports" />
<asp:DropDownList runat="server" 
                  DataSourceID="CompanyDivisionSource" 
                  DataTextField="CompanyDivision"
                  DataValueField="CompanyDivision" />

I recommend the use of that expression for the connection string also. You need to place your connection string on the web.config file, under the <connectionStrings> tag. You will them be able to reuse the same connection across many pages without having to hardcode it on every use.

More about the SqlDataSource can be found on this overview.