Populate a DropDownList from a SQL Server Database
By Brad Kingsley
October 4, 2001
The code below demonstrates how to populate an ASP.NET DropDownList control with the results of a query against SQL Server.
A couple of things to note:
- In this sample, I've used a trusted connection to SQL Server just to demonstrate the connection string.
- I grab the display text from the drop-down list by using oAuthors.SelectedItem.Text
- I grab the actual value of the drop-down list item by using oAuthors.SelectedItem.Value.
<%@ Page language="VB" Debug="false" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
if Not Page.IsPostBack Then
Dim oConn As SQLConnection
Dim oComm As SQLCommand
Dim oReader As SQLDataReader
Dim sSQL As String
Dim sConn As String
sSQL= "SELECT au_id, au_fname + ' ' + au_lname AS AuthorName "
sSQL+= "FROM Authors"
sConn= "server=(local);database=pubs;Trusted_Connection=yes"
oConn = New SQLConnection(sConn)
oConn.Open()
oComm = New SQLCommand(sSQL,oConn)
oReader = oComm.ExecuteReader()
oAuthors.DataSource = oReader
oAuthors.DataBind()
end if
End Sub
Sub Go_Click(Src As Object, E As EventArgs)
SelectedItem.Text = "You picked " & oAuthors.SelectedItem.Text & " (" & _
oAuthors.SelectedItem.Value & ")."
End Sub
</script>
<html>
<head>
<title>Populate A Drop-down List From A SQL Server Database</title>
</head>
<body>
<form runat="server">
Please select an author:
<asp:DropDownList ID="oAuthors" DataTextField="AuthorName"
DataValueField="au_id" Runat="server" />
<asp:Button ID="Go" Text="Go" OnClick="Go_Click" Runat="server" />
<br />
<br />
<asp:Label ID="SelectedItem" Runat="server" />
</form>
</body>
</html>
~Brad
Brad Kingsley is founder and president of ORCS Web, Inc. - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.
