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"

I decided to write this example because I always encounter this kind of issues at http://forums.asp.net .

This demo basically shows how to save the CheckBoxList selected items in the database and retain its selected items on postbacks or when the page is loaded again. To get started then lets start by creating the database for storing the data.

STEP 1: Creating the database.

  1. Launch Sql Server Management Studion Express and then connect
  2. Expand the Databases folder from the Sql Server object explorer
  3. Right click on the Databases folder and select “New Database”
  4. From the pop up window, input the database name you like and click add
  5. Expand the Database folder that you have just added
  6. Right click on the Tables folder and select “New Table”

Then add the following fields below:


 

 

Note that in this demo, I named the table as “MyTable". I  also set the Id to auto increment so that the id will be automatically generated for every new added row. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.

Now, since the table is new then lets add a data in the Employees field for displaying purposes. The table would look something like this:



STEP2: Setting up the UI.

For the simplicity of this demo I just set the UI like this below in the WebForm.



 

 

ASPX Source:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Demo.aspx.cs"
Inherits="Demo" Debug="true" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Sample Demo</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:CheckBoxList ID="CheckBoxList1" runat="server">
        </asp:CheckBoxList> <br />
        <asp:Button ID="Button1" runat="server" Text="Save Changes"
                    OnClick="Button1_Click" />
    </div>
    </form>
</body>
</html>

As  you can see the IU is very simple. It just contain a single CheckBoxList and a Button.

STEP3:  Setting up the ConnectionString

We can set the ConnectionString in the web.config file this way:

<connectionStrings>
    <add name="MyConsString" connectionString="Data Source=WPHVD185022-9O0;
                             Initial Catalog=MyDatabase;
                             Integrated Security=SSPI;"
                             providerName="System.Data.SqlClient" />
</connectionStrings>

Note: MyConsString is the name of the Connection String that we can use as a reference in our codes for setting the connection string later.

Then we can call the ConnectionString that was set up from the web.config file this way:

public string GetConnectionString(){
        //sets the connection string from your web config file "ConnString" is the name of your Connection String
        return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;
}

STEP4: Binding the CheckBoxList with Data.

In this demo, we are going to bind our CheckBoxList with the list of Employees that comes from the Northwind database using the ADO.NET way.

Here are the code blocks below:

    private void BindCheckBoxList(){

        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {
            connection.Open();
            string sqlStatement = "SELECT * FROM MyTable";
            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 = "Employees"; // the items to be displayed in the list items
                CheckBoxList1.DataValueField = "Employees"; // the id of the items displayed
                CheckBoxList1.DataBind();

                //Setting the Selected Items in the ChecBoxList based from the value in the database

                //to do this, lets iterate to each items in the list

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[i]["IsSelected"].ToString()))
                    {
                        CheckBoxList1.Items[i].Selected = Convert.ToBoolean(dt.Rows[i]["IsSelected"]);
                    }
                }
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
    }

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

STEP5: Creating the Method for Saving the CheckBoxList  Selected Items to the database.

Here’s the code block below:

private void Update(string name, bool isSelected) {
            SqlConnection connection = new SqlConnection(GetConnectionString());
            SqlCommand cmd;

            string sqlStatement = string.Empty;
            try {
                connection.Open();
                sqlStatement = "UPDATE MyTable SET IsSelected = @IsSelected WHERE Employees = @Employees";
                cmd = new SqlCommand(sqlStatement, connection);
                cmd.Parameters.AddWithValue("@Employees", name);
                cmd.Parameters.AddWithValue("@IsSelected", isSelected);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException ex) {
                string msg = "Insert/Update Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
            finally {
                connection.Close();
            }
}

STEP6:  Calling the Method for Saving the state of  CheckBoxList  selected items.

Here’s the code block at Button_Click event :

     protected void Button1_Click(object sender, EventArgs e){

        string employeeName = string.Empty;
        for (int i = 0; i < CheckBoxList1.Items.Count; i++)
        {
            if (CheckBoxList1.Items[i].Selected)
            {
                employeeName = CheckBoxList1.Items[i].Text;
                Update(employeeName, CheckBoxList1.Items[i].Selected);
            }
        }

        //Rebind the List to retain the selected items on postbacks

        BindCheckBoxList();
    }

Here's the page output below:


That’s it! Hope you will find this example useful!


 

Technorati Tags: ,,

Posted on Wednesday, May 6, 2009 9:15 PM ADO.NET , ASP.NET , C# | Back to top


Comments on this post: Save CheckBoxList Selected Items and Retain them on PostBacks.

# re: Save CheckBoxList Selected Items and Retain them on PostBacks.
Requesting Gravatar...
Great article, I was just looking for this solution, this saved me a couple of hours of coding.
Left by Kalle on Jun 26, 2009 8:41 AM

# re: Save CheckBoxList Selected Items and Retain them on PostBacks.
Requesting Gravatar...
Excellent Code......
Left by Sunil on Nov 27, 2009 12:08 AM

# re: Save CheckBoxList Selected Items and Retain them on PostBacks.
Requesting Gravatar...
there is the tbl_profile table which has a pk
there is the tbl_lookup_music which has a pk
-----------------------------------------------------------
then tbl_profile_music has two FK
CREATE TABLE [dbo].[tbl_Profile_Music](
[ProfileID] [int] NOT NULL,
[MusicID] [int] NOT NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tbl_Profile_Music] WITH CHECK ADD CONSTRAINT [tbl_Profile_Music_fk] FOREIGN KEY([MusicID])
REFERENCES [dbo].[tbl_lookup_music] ([MusicID])
GO
ALTER TABLE [dbo].[tbl_Profile_Music] CHECK CONSTRAINT [tbl_Profile_Music_fk]
GO
ALTER TABLE [dbo].[tbl_Profile_Music] WITH CHECK ADD CONSTRAINT [tbl_Profile_Profile_fk] FOREIGN KEY([ProfileID])
REFERENCES [dbo].[tbl_Profile] ([ProfileID])
GO
ALTER TABLE [dbo].[tbl_Profile_Music] CHECK CONSTRAINT [tbl_Profile_Profile_fk]

USE [omegalove]
GO
/****** Object: Table [dbo].[tbl_lookup_music] Script Date: 01/11/2010 00:12:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_lookup_music](
[MusicID] [int] IDENTITY(1,1) NOT NULL,
[Music] [nvarchar](64) NOT NULL,
CONSTRAINT [PK_tbl_lookup_music] PRIMARY KEY CLUSTERED
(
[MusicID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
------------------

CREATE TABLE [dbo].[tbl_Profile](
[ProfileID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [nvarchar](50) NULL,
[UserName] [nvarchar](50) NULL,
[HeadLine] [nvarchar](255) NULL,
[GenderID] [int] NOT NULL CONSTRAINT [DF_tbl_Profile_GenderID] DEFAULT ((1)),
[Birthdate] [datetime] NULL,
[ZipCode] [varchar](15) NULL,
[SexualOrientationID] [int] NOT NULL CONSTRAINT [DF_tbl_Profile_SexualOrientationID] DEFAULT ((1)),
[CountryID] [char](2) NULL,
[RegionID] [char](2) NULL,
[CityID] [nvarchar](255) NULL,
CONSTRAINT [PK_tbl_Profile] PRIMARY KEY CLUSTERED
(
[ProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
-------------------------------

What should I do?
Left by matt cupryk on Jan 10, 2010 6:24 PM

# re: Save CheckBoxList Selected Items and Retain them on PostBacks.
Requesting Gravatar...
You literally saved me... :D
Left by Kush Dev on Jun 15, 2010 9:44 AM

# selected in the checkboxlist items are retained in list box
Requesting Gravatar...
please give me the code f
Left by priya on Jul 13, 2010 5:39 PM

# re: Save CheckBoxList Selected Items and Retain them on PostBacks.
Requesting Gravatar...
hi
Great Code
you save me time
i use checkboxlist in formview
and the checkboxlist not visible
how do it?
thanks
Left by moris on Jul 24, 2010 3:33 PM

# re: Save CheckBoxList Selected Items and Retain them on PostBacks.
Requesting Gravatar...
Great example! Thanks! You help me a lot.
Left by Ale on Nov 19, 2010 5:18 AM

# re: Save CheckBoxList Selected Items and Retain them on PostBacks.
Requesting Gravatar...
hola nesecito grabar los datoen en campos diferentes, ose un valor en un campo en una tabla solo para guardar. lo que seleccionen de la lista, ya se como funciona en cadena, pero necesito guardar cada valor por separado. por ayuda gracias lo necesito urgente, si me ponen un ejemplo con codigo mil grcias.
el punto practico para mis datos es el siguiente. tengo una lista de requeriemitnos de unas visitas y necesito guardar por separado los valores seleccionados en una tabla
Left by guillemo trujilllo on Mar 24, 2011 1:29 AM

# re: Save CheckBoxList Selected Items and Retain them on PostBacks.
Requesting Gravatar...
Thanx a lot.. Cheers to u...
Left by Sam on Jun 24, 2011 8:23 PM

# ME FUNCIONO
Requesting Gravatar...
Me funcionó, exactamente lo que buscaba......


salud2
Left by susana on Mar 01, 2012 6:27 AM

comments powered by Disqus

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