How to: Populate List Web Server Controls from a Data Source
The information in this topic applies to all list Web server controls:
You can use a list Web server control to display items that are read from a data source. Each item in the control corresponds to an item—such as a row in a data table—in the data source. The control can display one field from the source and can optionally use a second field as the item value.
A list control can display both items that you create at design time and items read from a data source. For example, in a DropDownList, control, you might create an item at design time that says "Select from the list." You can then populate the control with data from a data source, appending the data to the first item that you created statically.
To populate a list control from a data source
If the list control includes items that you have defined at design time, and you want to append the item from the data source to the static items, set the control's AppendDataBoundItems property to true.
Configure the data source control with connection string and query information. (To populate a list control, you need to define only a SELECT query.)
Set the list control's DataSourceID property to the ID of the data source control.
Specify which fields in the data source should be used to populate each item by setting the following properties:
DataTextField The name of the field whose value will be displayed in the list.
DataTextFormatString A formatting expression for the list item text. The following table shows examples of formatting strings:
For numeric/decimal data. Displays the literal "Price:" followed by numbers in currency format. The currency format depends on the culture setting specified in the culture attribute on the Page directive or in the Web.config file.
For integer data. Cannot be used with decimal numbers. Integers are displayed in a zero-padded field that is four characters wide.
For numeric data. Displays the number with 2-decimal place precision followed by the literal "%".
For numeric/decimal data. Numbers are rounded to one decimal place. Numbers less than three digits are zero-padded.
For date/time data. Displays long date format ("Thursday, August 06, 1996"). Date format depends on the culture setting of the page or the Web.config file.
For date/time data. Displays short date format ("12/31/99").
For date/time data. Displays date in numeric year-month-day format (96-08-06)
For information about creating a formatting expression, see Composite Formatting.
If the data source has only one field, you do not have to explicitly set these fields, because the control will simply display the single field.
Optionally set the control's AutoPostBack property to allow the list control to post back when a selection is made.
If the AutoPostBack property is set to false, you must provide some other means for the user to submit the page, such as a Button control.
The following example shows how to populate a ListBox control with data from a SQL Server database by binding it to a SqlDataSource control. The SqlDataSource control reads data from the Categories table in the Northwind database. (The connection string for the control is stored in the Web.config file). The ListBox control displays the CategoryName field and uses the CategoryID field as the value of each item.
<asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource1" DataTextField="CategoryName" DataValueField="CategoryID"> </asp:ListBox> <asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" ID="SqlDataSource1" runat="server" SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories]"> </asp:SqlDataSource>
Optionally, include one or more static items that you define at design time. To populate the list control with both static items and items generated from a data source, set the list control's AppendDataBoundItems property to true. The following example shows a DropDownList control that includes a static item that reads "Select from list" but otherwise is populated from a database table. A CompareValidator control is bound to the DropDownList control and is configured not to allow users to select the static item.
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="CategoryName" DataValueField="CategoryID" AppendDataBoundItems="True" > <asp:ListItem> Select from list </asp:ListItem> </asp:DropDownList> <asp:CompareValidator ID="CompareValidator1" runat="server" ErrorMessage="Select an item!" Operator="NotEqual" ValueToCompare="Select from list" ControlToValidate="DropDownList1"> Please select an item! </asp:CompareValidator>