Geeks With Blogs

News

Microsoft MVP


DZone MVB


Moderator at CodeASP.NET


Quiz Master







free counters
Free counters
Added on January 19,2012


Follow Me @vmsdurano

A bit About Me



Disclaimer
The opinions expressed herein are my own personal opinions and does not represent the opinions of my employers. Nor does it represent the opinion of my dog, because I don’t have one.


Vinz' Blog (ProudMonkey) "Code, Beer and Music ~ my way of being a programmer"

This example shows how to bind DropDownList, ListBox and CheckBoxList control with data from database using the ADO.NET way.

Note that in this demo, I’m using the Northwind database. Now let’s set up the connection string.

STEP 1: Setting up the Connection string

In your web.config file set up the connection string there as shown below:

<connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;
                             AttachDbFilename=|DataDirectory|\Northwind.mdf;
                             Integrated Security=True;User Instance=True"
                             providerName="System.Data.SqlClient"/>
</connectionStrings>


 

Note: DBConnection is the name of the Connection String that we can use as a reference in our codes later.

In code behind, you can reference that connection string like this:

private string GetConnectionString(){
   return ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
}
 

Since we are done setting up the connection string then we can start populating the Controls, first let’s start populating the DropDownList.

STEP 2: Populating the DropDownList

To start, grab a DropDownList control from the visual studio ToolBox and place it the WebForm. The ASPX source should look something like this:

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Binding DropDownList, ListBox and CheckBoxList</title>

</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="DropDownList1" runat="server">
        </asp:DropDownList>
    </div>
    </form>
</body>
</html>



 

Here’s the code block for binding the DropDownList in the code behind:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;

public partial class _Default : System.Web.UI.Page 
{

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindDropDownList();
        }
    }

    private string GetConnectionString()
    {
       return ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
    }

    private void BindDropDownList()
    {
        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {
            connection.Open();
            string sqlStatement = "SELECT Top(20)* FROM Customers";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                DropDownList1.DataSource =dt;
                DropDownList1.DataTextField = "ContactName"; // the items to be displayed in the list items
                DropDownList1.DataValueField = "CustomerID"; // the id of the items displayed
                DropDownList1.DataBind();
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
    }
}

 

Here’s the page output below

STEP 3: Binding the ListBox Control

We can bind the ListBox control the same way as what we did for binding the DropDownList by simply setting its DataTextField and DataValueField.

Now let’s grab a ListBox control from the visual studio toolbox. The mark up should look something like this:

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">
    <title>Binding DropDownList, ListBox and CheckBoxList</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
       <asp:ListBox ID="ListBox1" runat="server"Height="200px" Width="100px">
       </asp:ListBox>
    </div>
    </form>
</body>
</html>

 

And here are the relevant codes for binding the ListBox in the code behind.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;


public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindListBox();
        }
    }

    private string GetConnectionString()
    {
        return ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
    }

    private void BindListBox()
    {
        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {
            connection.Open();
            string sqlStatement = "SELECT Top(20)* FROM Customers";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                ListBox1.DataSource =dt;
                ListBox1.DataTextField = "ContactName"; // the items to be displayed in the list items
                ListBox1.DataValueField = "CustomerID"; // the id of the items displayed
                ListBox1.DataBind();
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
    }
}

 

Below is the page output:

 

STEP 4: Binding the CheckBoxList Control

Same procedure as what we did for DropDownList and ListBox..

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">
    <title>Binding DropDownList, ListBox and CheckBoxList</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:CheckBoxList ID="CheckBoxList1" runat="server" Width="200px">
        </asp:CheckBoxList>    
    </div>
    </form>
</body>
</html>

 

Here are relevant codes for binding the CheckBoxList in the code behind:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;

public partial class _Default : System.Web.UI.Page 
{

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindCheckBoxList();
        }
    }


    private string GetConnectionString()
    {
        return ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
    }

    private void BindCheckBoxList()
    {
        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {
            connection.Open();
            string sqlStatement = "SELECT Top(20)* FROM Customers";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                CheckBoxList1.RepeatColumns = 4; // set the number of columns in the CheckBoxList
                CheckBoxList1.DataSource =dt;
                CheckBoxList1.DataTextField = "ContactName"; // the items to be displayed in the list items
                CheckBoxList1.DataValueField = "CustomerID"; // the id of the items displayed
                CheckBoxList1.DataBind();
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
    }
}

The page output will look something like below:

That Simple! :)

Technorati Tags: ,,
Posted on Tuesday, February 24, 2009 5:31 PM ADO.NET , ASP.NET , C# | Back to top


Comments on this post: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
nice work mate
Left by lokesh gandhi on Apr 01, 2009 10:33 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
excellent work
Left by anurag vatsa on Aug 03, 2009 7:40 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
awesome work!
will try to implement it in both:

<a href="http://www.guardianinsurance.com.au/>Life Insurance

and

<a href="http://www.realinsurance.com.au/Life Insurance
Left by Life Insurance on Dec 20, 2009 9:20 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
I am trying to complete my drop down list for a whole week, but I was not able to do that. Now, with information you wrote here I fixed it and it works! Thanks!!!
Left by Belinda Guardiolla on Dec 20, 2009 9:31 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
Shouldn't it be simpler?

I can use the following code in the aspx page and not require any code to bind the listbox in the .cs file:

<asp:SqlDataSource ID="IA_Upload_Tables" runat="server"
ConnectionString="<%$ ConnectionStrings:IADataConnectionString %>"
SelectCommand="SELECT [NAME] FROM [UPLOAD_TABLES]"></asp:SqlDataSource>
<br />
<asp:ListBox ID="UploadTable_ListBox" runat="server" Height="77px"
Width="400px" DataMember="DefaultView" DataSourceID="IA_Upload_Tables"
DataTextField="Name" DataValueField="Name" >
</asp:ListBox>
Left by JDK on Apr 09, 2010 4:09 AM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
the codes are working very well.
Left by Income Protection Insurance Aust on Aug 06, 2010 3:35 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
excellent work....
Left by shantesh kulkarni on Nov 18, 2010 6:41 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
I need to force myself to use ado.net on objects rather than older methods...much cleaner, thanks for the code.
Left by electric guitar lessons on Feb 11, 2011 10:44 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
Super work..
Left by Nice Work on Feb 23, 2011 9:48 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
This is just what I needed to finish my project, my boss will be happy :)
Left by top 10 pet stores on Apr 06, 2011 9:20 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
Great Work Boss
That will help me a lot
Thanks
Left by Vaibhav on Jun 10, 2011 3:12 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
Looks like a good method...I'll give it a shot. I hate working with ADO.
Left by Big Forum on Oct 14, 2011 2:45 AM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
thanks for sharing... goodword
Left by Salmon on Oct 23, 2011 5:22 AM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
It was very helpful...... Thank u
Left by Megha on Nov 09, 2011 2:03 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
nice work, tested and found it working.
Left by khan on Nov 15, 2011 6:57 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
Thank you so much!!!
Left by WarmGuy on Sep 01, 2012 2:26 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
how about populate the listbox to another listbox..

example..
when you click the value of the first listbox.. it will display the value in the 2nd listbox..

how can i do that..

nid some help


thanks..
Left by bryan on Nov 09, 2012 2:25 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
Can we put the Dropdown list in Grid view and retrieve the items from the database instead of not creating a dummy array list in code?
reply urgent..Please..anybody Help me Out
Left by rahul on Dec 28, 2012 4:45 PM

# re: Binding DropDownList, ListBox and CheckBoxList Control the ADO.NET way.
Requesting Gravatar...
Can we put the Dropdown list in Grid view and retrieve the items directly from the database?
reply urgent..Please..anybody Help me Out
Left by rahul on Dec 28, 2012 4:46 PM

comments powered by Disqus

Copyright © Vincent Maverick Durano | Powered by: GeeksWithBlogs.net