Populate A Drop-down List 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: 1) In this sample I've used a trusted connection to
SQL Server just to demonstrate the connection string; 2) I grab the display text from the drop-down list by using oAuthors.SelectedItem.Text; 3) 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 solutions for clients who develop and deploy their applications on Microsoft Windows platforms. Services include shared hosting, dedicated hosting, and webfarm hosting, with specialty in .Net, SQL Server, and architecting highly scalable solutions.