Post
Ask Question

How To Insert CheckBoxList Values Into a Single Column and Retrieve It From A Database When User Selects A Gridview Row

Introduction

 
In this article, I will explain how to insert CheckBoxList Values into a single column and retrieve it from a database when a user selects a GridView Row. Refer a following picture,
How To Insert CheckBoxList Values Into a Single Column and Retrieve It From A Database When User Selects A Gridview Row
 

Problem Statement

 
When a user selects multiple values from CheckBoxList and clicks on the “Submit” button, then all of the selected values should be inserted into a single column of the database table separated by commas. An inserted record should be displayed into the GridView. When a user selects any row from GridView, the comma-separated values should be displayed on to the label, and the corresponding CheckBox should be checked.

Let’s begin...

Step 1
 
We require two columns. In the first column, we store Candidate ID. In another column, we store Qualifications from the CheckBoxList. To create a table. write the following code using MS-SQL Server Management Studio.
  1. create table Qualf(  
  2. Cand_id int primary key identity(1,1),  
  3. Qulfns varchar(50)  
  4. );  
Step 2
 
After creating a table, open Visual Studio and create a new project by selecting the “ASP.NET Empty Web Site” template, as shown in the below image.

How To Insert CheckBoxList Values Into a Single Column and Retrieve It From A Database When User Selects A Gridview Row

Now right click on the project name and select “Add New Item.” From item templates select “Web Form” template and rename it or keep “Default.aspx” as it is. I am keeping it as it is. The following image shows the process,
 
How To Insert CheckBoxList Values Into a Single Column and Retrieve It From A Database When User Selects A Gridview Row 
 
Step 3
 
After adding a “Default.aspx” page, the design is as follows:

How To Insert CheckBoxList Values Into a Single Column and Retrieve It From A Database When User Selects A Gridview Row

To design the “Default.aspx” page, write/copy and paste the below code,

Default.aspx
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" EnableEventValidation="false" %>    
  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  3. <html xmlns="http://www.w3.org/1999/xhtml">  
  4. <head runat="server">  
  5.     <title></title>  
  6. </head>  
  7. <body>  
  8.     <form id="form1" runat="server">  
  9.     <div style="width: 860px">  
  10.                     Qualification</br>  
  11.         <asp:CheckBoxList ID="CheckBoxList1" runat="server" RepeatColumns="2" RepeatDirection="Horizontal">  
  12.             <asp:ListItem>SSC</asp:ListItem>  
  13.             <asp:ListItem>HSC</asp:ListItem>  
  14.             <asp:ListItem>Diploma</asp:ListItem>  
  15.             <asp:ListItem>BSC</asp:ListItem>  
  16.             <asp:ListItem>BE</asp:ListItem>  
  17.             <asp:ListItem>MSC</asp:ListItem>  
  18.         </asp:CheckBoxList>  
  19.         <br />              
  20.         <asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" />  
  21.         <br />  
  22.         <br />  
  23.         <asp:Label ID="Label2" runat="server" Text="Label" Font-Bold="True"></asp:Label>    
  24.         <asp:Label ID="Label1" runat="server" Text="qual" Font-Bold="True"></asp:Label>  
  25.         <br />  
  26.         <br />  
  27.         <asp:GridView ID="GridView1" runat="server" AutoGenerateSelectButton="true" OnSelectedIndexChanged="GridView1_SelectedIndexChanged"  
  28.             AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">  
  29.             <Columns>  
  30.                 <asp:BoundField DataField="Cand_id" HeaderText="Candidade ID" />  
  31.                 <asp:BoundField DataField="Qulfns" HeaderText="Qualfications" />  
  32.             </Columns>  
  33.             <SelectedRowStyle BackColor="#009999" ForeColor="#CCFF99" Font-Bold="true" />  
  34.         </asp:GridView>  
  35.     </div>  
  36.     </form>  
  37. </body>  
  38. </html>  
Step 4
 
To connect the database, add the following namespaces into the “Default.aspx.cs” code file. 
  1. using System.Data;  
  2. using System.Data.SqlClient;   
After adding the above namespaces, open the “Server Explorer” window and create a new database connection. To create a new database connection, from the “Data Source” select “Microsoft SQL Server” then select the server name which may be your local server and also select the Database name. Click on “Test Connection”, and if connection succeeds, go ahead. Refer to the following image.

How To Insert CheckBoxList Values Into a Single Column and Retrieve It From A Database When User Selects A Gridview Row

Now right-click on the newly created connection which is shown in the Server Explorer window and check the connection properties. From properties, copy the connection string and paste it into “Default.aspx.cs” code file while creating the connection object.

Step 5

Write or copy-paste the below code into the “Default.aspx.cs” code file:
 
Default.aspx.cs
  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;  
  8. using System.Data.SqlClient;  
  9.   
  10. public partial class _Default : System.Web.UI.Page  
  11. {  
  12.     SqlConnection cn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=E:\\ASP_BASIC\\0GLOBAL_MT_DB\\MT_DB.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True");  
  13.   
  14.     protected void Page_Load(object sender, EventArgs e)  
  15.     {  
  16.         DisplayData();  
  17.         Label1.Visible = false;  
  18.         Label2.Visible = false;  
  19.     }  
  20.     // To insert data into Qualf table  
  21.     public void INSERDATA()  
  22.     {  
  23.         String str = "";  
  24.         for (int i = 0; i <= CheckBoxList1.Items.Count - 1; i++)  
  25.         {  
  26.             if (CheckBoxList1.Items[i].Selected)  
  27.             {  
  28.                 if (str == "")  
  29.                 {  
  30.                     str = CheckBoxList1.Items[i].Text;  
  31.                 }  
  32.                 else  
  33.                 {  
  34.                     str += "," + CheckBoxList1.Items[i].Text;  
  35.                 }  
  36.             }  
  37.         }  
  38.         SqlCommand cmd = new SqlCommand("Insert into Qualf(Qulfns) values('" + str + "')", cn);  
  39.         cmd.ExecuteNonQuery();  
  40.     }  
  41.     // To display data from Qualf table  
  42.     public DataTable DisplayData()  
  43.     {  
  44.         SqlDataAdapter adpt = new SqlDataAdapter("select * from Qualf ", cn);  
  45.         DataTable dt = new DataTable();  
  46.         adpt.Fill(dt);  
  47.         GridView1.DataSource = dt;  
  48.         GridView1.DataBind();  
  49.         return dt;  
  50.     }  
  51.     // To retrives the values when user select the row from GridView   
  52.     protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)  
  53.     {  
  54.         if (IsPostBack)  
  55.         {  
  56.             CheckBoxList1.ClearSelection();  
  57.         }  
  58.             GridViewRow row = GridView1.SelectedRow;  
  59.             Label1.Text = row.Cells[2].Text;  
  60.             Label1.Visible = true;  
  61.             string str_split = Label1.Text;  
  62.             string[] qual = str_split.Split(',');  
  63.             foreach (string sub_str in qual)  
  64.             {  
  65.                 for (int i = 0; i <= CheckBoxList1.Items.Count - 1; i++)  
  66.                 {  
  67.                     if (sub_str == CheckBoxList1.Items[i].Text)  
  68.                     {  
  69.                         CheckBoxList1.Items[i].Selected = true;  
  70.                     }  
  71.                 }  
  72.             }  
  73.             Label2.Text = "Your Qualification is:";  
  74.             Label2.Visible = true;  
  75.     }  
  76.     protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)  
  77.     {  
  78.         if (e.Row.RowType == DataControlRowType.Header)  
  79.         {  
  80.             e.Row.Cells[0].Style.Add(HtmlTextWriterStyle.Display, "none");  
  81.         }  
  82.         if (e.Row.RowType == DataControlRowType.DataRow)  
  83.         {  
  84.             e.Row.Cells[0].Style.Add(HtmlTextWriterStyle.Display, "none");  
  85.             e.Row.Attributes["onclick"] = ClientScript.GetPostBackClientHyperlink(this.GridView1, "select$" + e.Row.RowIndex);  
  86.         }  
  87.     }  
  88.     //Calling the INSERDATA() function and after inserting data clear the CheckBoxList selected values and closing the connection  
  89.     protected void Button1_Click(object sender, EventArgs e)  
  90.     {  
  91.         cn.Open();  
  92.         INSERDATA();  
  93.         CheckBoxList1.ClearSelection();  
  94.         DisplayData();  
  95.         cn.Close();  
  96.     }  
  97. }  

Summary

 
In this article, we learned how to insert CheckBoxList Values into a single column and retrieve it from a database when a user selects a GridView Row.


Similar Articles