Populate a DropDownList from a SQL Server Database
page 1 of 1
Published: 02 Dec 2004
Unedited - Community Contributed
Abstract
The code below demonstrates how to populate an ASP.NET DropDownList control with the results of a query against SQL Server.
by Web Team at ORCS Web
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 143930/ 209

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:

  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 services for clients who develop and deploy their applications on Microsoft Windows platforms.



User Comments

Title: sql functions   
Name: nomusa ndlovu
Date: 5/3/2011 5:13:49 AM
Comment:
thank you for insight it is very helpfully
Title: sql procedures and functions   
Name: sinethemba
Date: 5/3/2011 5:12:10 AM
Comment:
im having difficult in populating a drordown box using a procedure
Title: dropdownlist as a Date of birth entry in Sql table in Asp.net   
Name: Nidhi Shah
Date: 2/17/2011 12:34:06 AM
Comment:
Thank You
Title: to update dropdown list dynamically   
Name: Ankit garg
Date: 12/1/2010 5:29:34 AM
Comment:
i have 1 drop down list which contain cities name.
the owner have the perevilage to update it dynamically hw can i achieve it.
plz solve the problem...
thnks
ankit.garg2006@yahoo.com
Title: Retreive DropDownList box value from DataBase   
Name: Abi
Date: 11/12/2010 4:41:05 AM
Comment:
Pls Help me.In My webform i am having university dropdown list box to list university names.now i want to retreive that value from database to display in the edit option.

now help me how to do that.Pls give me the solution to my MailId-ablakit@gmil.com
Title: Mr   
Name: John Cazaly
Date: 9/5/2010 11:27:52 AM
Comment:
Sir,

I am creating a stock ordering system and using a table called Orders as the book cover for a StockOrders table. When I go to enter new records into the StockOrders table I need to select which Order I am putting them into. I have a short sub routine to put the order date and Order ID into a drop dpwn list. I am using SQL 2008 and VS 2010 VB. The database field is a Date type and when viewed in SQL shows only the date. When this goes into the ddl it shows the dats in the correct UK format dd/mm/yy (held as yyyy-mm-dd in SQL) but it adds 00:00:00 as the minutes etc. I really do not need the minutes, how do I get rid of them please and shorten the data. I am not putting thme in and selected this field type to show only the date. I am scratching my head as I can't see where to put in a format string. My code (please don't laugh) is ...

Public Sub LoadddlOrders()
Dim DSPageData As New DataSet
Dim DBConn As SqlConnection
Dim DBCommand As SqlDataAdapter

DBConn = New SqlConnection("Server=localhost;Initial Catalog=OLB;" _
& "Integrated Security=SSPI")

DBCommand = New SqlDataAdapter _
("Select OrderID, OrderDate From Orders " _
& " Order By OrderDate DESC", DBConn)


DBCommand.Fill(DSPageData, "Ord")
ddlOrder.DataSource = DSPageData.Tables("Ord").DefaultView
ddlOrder.DataTextField = "OrderDate"
ddlOrder.DataValueField = "OrderID"
ddlOrder.DataBind()

End Sub

BTW: just a quickie, what is the difference between a Public and Private sub?

Thank you for your help

Kind regards

John,
London
Title: populating data from 3 tables   
Name: Tabrez Ali
Date: 5/14/2010 2:33:24 PM
Comment:
i have three dropdownlist control on my Webform . first one is configured to display country. and the second dropdown list control have to display the states of the country when the user will select the country from the first dropdown list control at the same time when the user will selct the state from the 2nd ddl control , then 3rd dropdown list control have to display the cities of the state . can u plz post me the solution with using C# to my mail id tabrez_chd@yahoo.com. its very urgent. i shall be very thankfull to u
Title: Populate State where Country is selected in ASP.NET C#   
Name: Emeka Ezeoke
Date: 5/10/2010 1:31:28 PM
Comment:
Hello,
I am trying to populated a dropdownlist box with states when their corresponding country is selected in another dropdownlist box using a SQL server 2005 database.I am programming in C#.

Thanks
Title: populate listbox with database data   
Name: elastos monyepao
Date: 5/3/2010 9:01:34 AM
Comment:
please help me
i want to display multiple data from a database using listbox.i dont want to use data binding instead need help with raw coding.that is,read data from database,e.g customerNames into a list box.thanks
Title: populate the data into a dropdownlist   
Name: tapas
Date: 2/23/2010 1:24:15 AM
Comment:
i want to populate the list from the values in the database
my prob is asme as neha below described..plz help me out as soon as possible.
Title: populate the data into a dropdownlist   
Name: neha
Date: 1/18/2010 6:28:03 AM
Comment:
i have 2 dropdownlist control on my form . first one is configured to display Universities names and the second dropdown list control have to display the school names of that corresponding universities. when the user select the university from the first dropdownlist control ,the schools names of that universities should display in the second dropdownlist.Please can u help me doing this using asp.net with C# code.Thank you in advance,
Please can u help to solve this problm and post me the solution to my mail id:hinatazeen@gmail.com.
Thank u once again..
Title: droplist   
Name: krupa
Date: 11/30/2009 5:12:01 AM
Comment:
i have trying project of travles when the coustmer select in the dropdownlist1 value and dropdwonlist2 value both r same then i have print some message
Title: how to link 3 dropdownlist control so that the second and third dropdownlist control will display depanding up on the first one   
Name: Nixonio
Date: 1/9/2009 12:32:16 AM
Comment:
i have three dropdownlist control on my form . first one is configured to display country. and the second dropdown list control have to display the states of the country when the user will select the country from the first dropdown list control at the same time when the user will selct the state from the 2nd ddl control , then 3rd dropdown list control have to display the cities of the state . can u plz post me the solution using VB behind the code to my id nixonio@yahoo.com
Title: HOw to get the data grid using drop down lisr   
Name: Trupti
Date: 11/5/2008 11:48:01 AM
Comment:
HI

Can anybody say that how to get datagrid sql different queries from drop down list using aspdotnet c#
..
Thanks
Trupti
Title: asp ddl   
Name: siva srinivas
Date: 9/4/2008 6:19:02 AM
Comment:
Comment:
i have three dropdownlist control on my form . first one is configured to display country. and the second dropdown list control have to display the states of the country when the user will select the country from the first dropdown list control at the same time when the user will selct the state from the 2nd ddl control , then 3rd dropdown list control have to display the cities of the state . can u plz post me the solution with out using ajax to my ID sivasrinivas.luck@gmail.com
Title: How to make a function to fill drop down list   
Name: Shefali
Date: 7/19/2008 7:08:40 AM
Comment:
I have an Emplyee form in which ,I have tofill the dropdown with country,state,city values from the differnt tables.I have to make a procedure or function to populate the dropdown list.So how do write a code for this in VB.Pls tell me the code.
Title: how to display a label message when the max data grid rows exceeded   
Name: Hari
Date: 5/21/2008 9:37:29 AM
Comment:
I have a data grid,i will give the limit to the datagrid, if the limit exceeded i should display a message, so how do i write the code for this in c# please tell me the code to my mail ID hari_krishna036@yahoo.com
Title: Title: how to link 3 dropdownlist control so that the second and third dropdownlist control will display depanding up on the first one   
Name: Name:hari
Date: 5/21/2008 9:27:25 AM
Comment:
Comment:
i have three dropdownlist control on my form . first one is configured to display country. and the second dropdown list control have to display the states of the country when the user will select the country from the first dropdown list control at the same time when the user will selct the state from the 2nd ddl control , then 3rd dropdown list control have to display the cities of the state . can u plz post me the solution with out using ajax to my ID hari_krishna036@gmail.com
Title: how to get the values into dropdownlist from tables   
Name: kishan
Date: 4/24/2008 11:35:55 PM
Comment:
plz help me, i couldn't get the data from the table into dropdown list after compilation,
Title: plz help me   
Name: Dipti
Date: 4/8/2008 1:44:56 AM
Comment:
i dont have the spplicstin
Title: how to convert this in c#   
Name: Tom Pearson
Date: 3/4/2008 11:11:52 AM
Comment:
I need this dropdown code in c#.net??
Title: Related query   
Name: Mohammed Zoheb
Date: 2/23/2008 12:56:38 PM
Comment:
i forgot to mention the control
i used dropdownlist for binding my data
Title: Related query   
Name: Mohammed Zoheb
Date: 2/23/2008 12:55:28 PM
Comment:
hi there,
actually i have done the binding of column name using dataset prpperty and not data reader
i m sucessful in adding the column name but problem here arise is every time the page is postback the same items is getting added again and again following is the code i used:


Dim x As Integer
Dim constr As String = "select * from authors"
Dim conn As New OleDbConnection("provider=SQLOLEDB;server=.;initial catalog=pubs;User Id=sa;Password=")
conn.Open()
Dim cmd As New OleDbCommand(constr, conn)
Dim ds As New DataSet
Dim da As New OleDbDataAdapter
da.SelectCommand = cmd
da.Fill(ds, "authors")
For x = 0 To ds.Tables.Item("authors").Columns.Count - 1
DropDownList1.Items.Add(New ListItem(ds.Tables.Item("authors").Columns.Item(x).ColumnName))
Next
conn.Close()

please help
if possible hw i can use this code by using datareader
Title: No output   
Name: Mohan
Date: 9/10/2007 4:40:37 PM
Comment:
Hi ,

I have done the same way as u have said , but I am not able to display any output when I clicked the Submit button.But I have got the dropdownlist.
Title: 1 table   
Name: rakesh
Date: 8/6/2007 3:04:45 AM
Comment:
i hav to display party code corresponding to party name selected from dropdown list..on the lable
or when i select party name then on lable i need party code both r from single table on selected index change
plz reply on my id jn.rakesh@gmail.com
thanksssss
Title: 3 tables   
Name: aishea
Date: 7/31/2007 5:06:57 AM
Comment:
I have the same problem that Raj Soni.... I need to populate 3 dropdownlist with 3 diferent tables.
Can you please post here the solution?
Thx
Title: populating data from 3 tables   
Name: Raj Soni
Date: 7/18/2007 12:28:04 PM
Comment:
i have three dropdownlist control on my form . first one is configured to display country. and the second dropdown list control have to display the states of the country when the user will select the country from the first dropdown list control at the same time when the user will selct the state from the 2nd ddl control , then 3rd dropdown list control have to display the cities of the state . can u plz post me the solution with using ajax to my id rajusoni_2004@yahoo.com. its urgent....
Title: how to link 3 dropdownlist control so that the second and third dropdownlist control will display depanding up on the first one   
Name: prasant palo
Date: 7/13/2007 3:29:58 PM
Comment:
i have three dropdownlist control on my form . first one is configured to display country. and the second dropdown list control have to display the states of the country when the user will select the country from the first dropdown list control at the same time when the user will selct the state from the 2nd ddl control , then 3rd dropdown list control have to display the cities of the state . can u plz post me the solution with out using ajax to my id palo.prasant@gmail.com
Title: Regarding Drop down   
Name: sriram
Date: 11/27/2006 4:40:43 AM
Comment:
hi friends,

I am an asp beginner, i want to display datas from sqlserver using drop down. on changing this drop down box it should communicate to database and display from database.
Title: please help me   
Name: Vivek
Date: 9/15/2006 6:06:46 AM
Comment:
hello
please i need a seious help here
i just finished a web application and i want to put it on a web serverbut when i did that i faced with a serious problem which is thatmy asp.net application didn't work because the web server have not the .net framework,
so i need any way to modify my web application to work properly onthat web server .
please help me and send me email at
vivekgourmca@gmail.com
Title: Unable to obtain data in the Label   
Name: tvkswamy
Date: 7/21/2006 1:13:08 AM
Comment:
When this code was implemented I was able to obtain the dropdownlist, but when the Go button (which redirects to Go_Click) is clicked the Label with the ID Selected Item does not display any output at all.

I am left astray.

tvks@rediffmail.com
Title: hihi   
Name: yagoub hassen
Date: 5/16/2006 8:20:25 AM
Comment:
very good thank you very much yagoubut@gmail.com
Title: Graph in C#   
Name: Siva.P
Date: 4/25/2006 2:07:11 AM
Comment:
Please send the grapph coding using ASP.Net with C#
Title: Nice article   
Name: Ash
Date: 3/23/2006 1:59:20 AM
Comment:
Thanx. It was really helpful and easy to understand.
Title: Populate a DropDownList from a SQL Server Database   
Name: ajaysingh25@rediffmail.com
Date: 1/7/2006 3:26:54 AM
Comment:
Hello i need the same coding for server side page, not in html. if you could provide me, would a great help from your side.

Regards,
Ajay Singh
Title: Manually Inserting an item   
Name: Brad
Date: 12/6/2005 11:26:10 AM
Comment:
I believe you need to use the Items.Insert method which should allow you to specify an index location within the list for the new item.

thx
bk

www.orcsweb.com
Title: select distinct   
Name: Brad
Date: 12/6/2005 11:21:17 AM
Comment:
SELECT DISTINCT is going to give distinct rows... but rows where each column is distinct, so, if you have these rows:
Name Value
'Drop-down list 1' 1
'Drop-down list 2' 2

Those are not distinct even though the name value is distinct. You'll need to adjust the data, or your query, to truly return completely distinct rows.

thx
bk

www.orcsweb.com
Title: headline for the list   
Name: Hani
Date: 12/5/2005 9:08:26 AM
Comment:
Hi,
I have a dropdown list that bind a data from DB, but I want to add a title for the list that should be the first in the list and has no value.
I can add the item after the list bind the data but it comes in the last place of the list.
I'll be thankfull for any help.
al_b7r@hotmail.com
Title: Distinct Item   
Name: Michael Lim
Date: 12/5/2005 2:57:15 AM
Comment:
Woops, forgot to mention my mail.
It's michaellks@hotmail.com
Title: Distinct Item   
Name: Michael Lim
Date: 12/5/2005 2:56:08 AM
Comment:
Hi there, I would need help on how to get items in my DropDownList distinct? I used the SELECT DISTINCT clause in the SQL language, but the items are still repeated and not distinct. Your help would be greatly appreciated.
Title: Please help me,urgent   
Name: Daniel
Date: 11/26/2005 11:26:50 PM
Comment:
hi,I want to populate a drop down list from SQL server database and then if one of the item in the list was selected,it will populate a datagrid based on the selected item,the datagrid's data should retrieved from SQL server as well.
please help me and send me email at
mansun630@hotmail.com
Title: How To?   
Name: Preetha
Date: 11/25/2005 1:08:12 AM
Comment:
Hi i want to populate some items from the database into a dropdown list in such a way that the list should have the item as selected(the item which was originally selected and fed into database and is now called with the help of a primary key)and when i click on the dropdown list, other items also should be dispalyed so that i can choose from the list and can update it as i want.
Please help me . The code as ddl.selecteditem.text=... is not working here as i am getting the data to the ddl at the same time...
preeta_mol1@yahoo.com
Title: Need Help   
Name: ASP JUNKIY
Date: 7/28/2005 1:10:36 PM
Comment:
Just started to put an access database with the asp interface, having problems on updating a database record and with recalling the previously entered value in the list with the choice of editting the value wiht the rest of the list.

Example:
The list includes: AAA, BBB, CCC, DDD, EEE
User Selected value: DDD
On editting screen: DDD (as selected), AAA, BBB, CCC, EEE (display as option for editting)

Will really appreciate any help as deadline is getting closer.
Thanks
Ebaad.
ebaad1@hotmail.com
Title: Windows Forms   
Name: Brad
Date: 5/31/2005 1:12:43 PM
Comment:
I haven't done any non-web development in almost 10 years, but I believe that the current versions of Visual Studio will let you do this fairly easily right through the GUI designer. You can most likely set up a control and a data connection then link the two so that the control auto-populates with the data.

~Brad
Title: Equivalent in windows forms   
Name: Bill
Date: 5/31/2005 12:47:40 PM
Comment:
How would one do the same thing, or something comparable, in a winodows application using windows forms?
Title: How About Within A DataGrid?   
Name: Ken Powers
Date: 12/8/2004 7:58:28 AM
Comment:
How would you adapt this to work within a DataGrid? I have a list box within an EditItemTemplate column. I can populate the list box, but I cannot set the selected index to the current value of the database record. Any ideas?
Title: Please Help me   
Name: Mahmoud Tahoon
Date: 12/3/2004 8:18:13 AM
Comment:
hello
please i need a seious help here
i just finished a web application and i want to put it on a web server
but when i did that i faced with a serious problem which is that
my asp.net application didn't work because the web server have not
the .net framework,
so i need any way to modify my web application to work properly on
that web server .
please help me and send me email at
eng_mahmoud_tahoon@hotmail.com






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 11/28/2014 1:44:04 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search