Binding DropDownList With Database in ASP.NET

Introduction

Today, I have provided an article showing you how to bind a DropDownList with a database and display the bound data in a GridView in ASP.NET. In this article, we create a table in a SQL Server database and bind that table with a DropDownList control. After that we select an item in the DropDownList and match with the database table for the records that match with the database. It will display in the GridView control. To do that we create a connection string object to connect the database with the application and read data from the database using the select command to display data in the DropDownList. All you have to do is implement and hook it up to your requirement or need. First, start Visual Studio .NET and make a new ASP.NET web site using Visual Studio 2010.

Creating Table in SQL Server Database

Now create a table named UserDetail with the columns id, name, country and city. Set the identity property=true for id. The table looks as in the following:

img3.jpg

Now insert some values in this table. The table looks like this:

img4.jpg

Now you have to create a web site.

  • Go to Visual Studio 2010
  • New-> Select a website application
  • Click OK

img5.jpg

Now add a new page to the website.

  • Go to the Solution Explorer
  • Right-click on the Project name
  • Select add new item
  • Add new web page and give it a name
  • Click OK

img6.jpg

Design the page and place the required control in it. Now drag and drop one DropDownList control, Button and GridView control on the form. Let's take a look at a practical example.

.aspx Code

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>  
  2.    
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4. <html xmlns="http://www.w3.org/1999/xhtml">  
  5. <head runat="server">  
  6.     <title></title>  
  7. </head>  
  8. <body>  
  9.     <form id="form1" runat="server">  
  10.     <div>  
  11.         <asp:DropDownList ID="DropDownList1" runat="server">  
  12.         </asp:DropDownList>  
  13.         <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>  
  14.         <br />  
  15.         <br />  
  16.         <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click1" Style="height: 26px" />  
  17.         <br />  
  18.         <br />  
  19.         <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#999999"  
  20.             BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">  
  21.             <AlternatingRowStyle BackColor="#DCDCDC" />  
  22.             <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />  
  23.             <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />  
  24.             <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />  
  25.             <RowStyle BackColor="#EEEEEE" ForeColor="Black" />  
  26.             <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />  
  27.             <SortedAscendingCellStyle BackColor="#F1F1F1" />  
  28.             <SortedAscendingHeaderStyle BackColor="#0000A9" />  
  29.             <SortedDescendingCellStyle BackColor="#CAC9C9" />  
  30.             <SortedDescendingHeaderStyle BackColor="#000065" />  
  31.         </asp:GridView>  
  32.     </div>  
  33.     </form>  
  34. </body>  
  35. </html>  

Now add the following namespaces:

  1. using System.Data.SqlClient;
  2. using System.Data;

Now write the connection string to connect to the database:

  1. string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=rohatash;";

Now bind the DropDownList with the database table.

  1. SqlConnection con = new SqlConnection(str);  
  2.         string com = "Select * from UserDetail";  
  3.         SqlDataAdapter adpt = new SqlDataAdapter(com, con);  
  4.         DataTable dt = new DataTable();  
  5.         adpt.Fill(dt);  
  6.         DropDownList1.DataSource = dt;  
  7.         DropDownList1.DataBind();  
  8.         DropDownList1.DataTextField = "Name";  
  9.         DropDownList1.DataValueField = "ID";  
  10.         DropDownList1.DataBind();  

Now double-click on the Button control and add the following code:

  1. SqlConnection con = new SqlConnection(str);  
  2.         SqlCommand cmd = new SqlCommand("select * from UserDetail where id = '" + DropDownList1.SelectedValue + "'", con);  
  3.         SqlDataAdapter Adpt = new SqlDataAdapter(cmd);  
  4.         DataTable dt = new DataTable();  
  5.         Adpt.Fill(dt);  
  6.         GridView1.DataSource = dt;  
  7.         GridView1.DataBind();  
  8.         Label1.Text = "record found";  

In the code-behind write the following code:

Code-behind

To display data in The GridViw use a DataAdapter object to retrieve the data from the database and place that data into a table.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data.SqlClient;  
  8. using System.Data;  
  9.    
  10. public partial class Default2 : System.Web.UI.Page  
  11. {  
  12.     string str = "Data Source=.;uid=sa;pwd=wintellect;database=rohatash";  
  13.     protected void Page_Load(object sender, EventArgs e)  
  14.     {  
  15.         SqlConnection con = new SqlConnection(str);  
  16.         string com = "Select * from UserDetail";  
  17.         SqlDataAdapter adpt = new SqlDataAdapter(com, con);  
  18.         DataTable dt = new DataTable();  
  19.         adpt.Fill(dt);  
  20.         DropDownList1.DataSource = dt;  
  21.         DropDownList1.DataBind();  
  22.         DropDownList1.DataTextField = "Name";  
  23.         DropDownList1.DataValueField = "ID";  
  24.         DropDownList1.DataBind();  
  25.     }  
  26.     protected void Button1_Click1(object sender, EventArgs e)  
  27.     {  
  28.         SqlConnection con = new SqlConnection(str);  
  29.         SqlCommand cmd = new SqlCommand("select * from UserDetail where id = '" + DropDownList1.SelectedValue + "'", con);  
  30.         SqlDataAdapter Adpt = new SqlDataAdapter(cmd);  
  31.         DataTable dt = new DataTable();  
  32.         Adpt.Fill(dt);  
  33.         GridView1.DataSource = dt;  
  34.         GridView1.DataBind();  
  35.         Label1.Text = "record found";  
  36.     }  
  37. }  

Now run the application and select the name from DropDownList control and click on the Button.

img1.jpg

Now click on the Button.

img2.jpg

Similar Articles