Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Tue, 22-Jul-2014 Authors
Sheonarayan
250
All Time Authors
Sourav.Kayal
39750
Sheonarayan
37300
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads

How to generate GridView columns dynamically based on user selection?

SheoNarayan
Posted by under ASP.NET category on for Advance level | Points: 250 | Views : 66996 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
Rating: 4 out of 5
3 vote(s)
In case we have to give ability to the user to select the columns that should appear in the GridView, we can follow this approach.

Sometimes due to large number of columns to be displayed on the page in the GridView or give a precise look to the data in GridView, we need to show only those columns in the GridView that is selected by the user. In this article we are going to learn how to dynamically generate GridView columns based on user selection. 


This article has been written based on the question asked in this forum thread.


ASPX Code

<div>

Select column to show in the GridView:

<asp:CheckBoxList runat="server" ID="chkFields" DataTextField="Column_name" DataValueField="Column_name" RepeatDirection="Horizontal" RepeatLayout="Flow" />

<p>

<asp:Button ID="btnSub" runat="server" Text="Show" OnClick="ShowGrid" />

</p>

<asp:GridView ID="GridView1" runat="server" EnableViewState="false" AutoGenerateColumns="false" />

</div>

In the above code snippet, we have a CheckBoxList control that lists all the columns of my database table. Apart from this, we have a Show button and a GridView. After selecting the columns from the CheckBoxList control user click on the Show button and accordingly the GridView columns are displayed on the page. Notice that AutoGenerateColumns property is set to false in the GridView.

.NET How to Tips and Tricks comes with hundreds of ASP.NET, ASP.NET AJAX, jQuery,  HTML, CSS and JavaScript Tips and Tricks.


Code behind

    

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindTableColumns();

}

}

private void BindTableColumns()

{

DataTable table = new DataTable();

using (SqlConnection conn = new SqlConnection(_connStr))

{

using (SqlCommand cmd = new SqlCommand("sp_columns", conn))

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@table_name", "PersonalDetail");

// get the adapter object and attach the command object to it

using (SqlDataAdapter ad = new SqlDataAdapter(cmd))

{

// fire Fill method to fetch the data and fill into DataTable

ad.Fill(table);

}

chkFields.DataSource = table;

chkFields.DataBind();

}

}

}

private void GetData()

{

DataTable table = new DataTable();

// get the connection

using (SqlConnection conn = new SqlConnection(_connStr))

{

// write the sql statement to execute

string sql = "SELECT AutoId, FirstName, LastName, Age, Active FROM PersonalDetail ORDER By AutoId";

// instantiate the command object to fire

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

// get the adapter object and attach the command object to it

using (SqlDataAdapter ad = new SqlDataAdapter(cmd))

{

// fire Fill method to fetch the data and fill into DataTable

ad.Fill(table);

}

}

}

// specify the data source for the GridView

GridView1.DataSource = table;

// bind the data now

GridView1.DataBind();

}


protected void ShowGrid(object sender, EventArgs e)

{

foreach (ListItem item in chkFields.Items)

{

if (item.Selected)

{

BoundField b = new BoundField();

b.DataField = item.Value;

b.HeaderText = item.Value;

GridView1.Columns.Add(b);

}

}

this.GetData();

}


In the above code snippet, we have called BindTableColumns() method that uses sp_columns stored procedure (no need to create it, it comes by default with SQL Server) to retrieve the column names of the database table and populates into the CheckBoxList control (In case you have column names coming from more than one database table, you will need to add them one by one into the CheckBoxList control).

When user click on Show button after selecting the columns from the CheckBoxList control, we are calling ShowGrid server side method. In this method we are iterating through the columns of the GridView and if selected, adding into the GridView column collection. At last we are calling the GetData method that fetches the records from the database and populates into the GridView.

As we have specified AutoGenerateColumns to false so whatever columns will be added to the GridView will only be displayed on the page.

Hope this article was useful. Thanks for reading and subscribe for the RSS feed to get forthcoming articles alert directly into your inbox.

Page copy protected against web site content infringement by Copyscape
About the Author

SheoNarayan

Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Microsoft_MVP,Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan The Founder http://www.dotnetfunda.com
http://www.snarayan.com

Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan
Login to vote for this post.
Found interesting? Add this to:


Comments or Responses

Posted by: Susanthampy on: 6/13/2011 | Points: 25

Nice

Posted by: A4u6178 on: 6/14/2011 | Points: 25

Hi Sir,
Nice article ..very helpful one.
I need a litle explanation on following code line u have used..
cmd.Parameters.AddWithValue("@table_name", "PersonalDetail"); 

pls mention the use of @table_name in ur code.

Posted by: SheoNarayan on: 6/15/2011 | Points: 25

@table_name is used to provide the name of the database table whose fields I am going to populate into the the CheckBoxList control. In this case "PersonalDetail" is the name of database table whose columns have been populated into the CheckBoxList control.

Hope this helps.

Thanks

Posted by: Kibocko on: 12/12/2011 | Points: 25

Very useful article !!
It works good with one table. However, it is more likely to use different columns from different database tables.

I have 3 different tables. Please, do you mean we should add in the CheckBoxList control like:

DataTextField="Column_name1, Column_name2, Column_name3"
DataValueField="Column_name1, Column_name2, Column_name3"

Please, I am having probleme connecting the 3 tables. When I tried to add:

cmd.Parameters.AddWithValue("@table_name", "Table1, Table2, Table3");

I get errors on sp_columns. Can only take 1 table.

Please, How to resolve this issue?
Thanks


Posted by: Sunny2011 on: 12/25/2011 | Points: 25

thanks for nice article

when i select fields in checkboxlist then it will display in gridview but columns are repeated everytime

for example if i select employeeid from checkboxlist it will display in gridview then again i will select another datafield like ename then

it will display two employeeid with same data and ename.
how to avoid reapeated columns .plzz clarify this one





Posted by: Kika on: 4/18/2012 | Points: 25

please can u do paging and sorting aswell on this,i really need this,its urgentttt,thanks

Posted by: Bobjml on: 1/19/2013 | Points: 25

Thanks for the article. I am having difficulty in getting the checklistbox to populate, nothing shows up. I modifed the connection string for our connection and the table name for our database. Any help would be appreciated. Here is the code that I am using:

Imports System.Data

Imports System.Data.SqlClient
Imports System.Net.Mail
Imports System.Configuration

Partial Class TempDynamicColumns
Inherits System.Web.UI.Page

Private _connStr As String = ConfigurationManager.ConnectionStrings("memberaccountsConnectionString").ConnectionString


Protected Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
Response.Write("hello")
BindTableColumns()
End If
End Sub
Private Sub BindTableColumns()
Dim table As New DataTable()
Using conn As New SqlConnection(_connStr)
Using cmd As New SqlCommand("sp_columns", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@table_name", "Mem")
Response.Write(cmd)
' get the adapter object and attach the command object to it
Using ad As New SqlDataAdapter(cmd)
' fire Fill method to fetch the data and fill into DataTable
ad.Fill(table)
End Using
chkFields.DataSource = table
chkFields.DataBind()
End Using
End Using
End Sub
Private Sub GetData()
Dim table As New DataTable()
' get the connection
Using conn As New SqlConnection(_connStr)
' write the sql statement to execute
Dim sql As String = "SELECT * FROM Mem ORDER By [Last Name]"
' instantiate the command object to fire
Using cmd As New SqlCommand(sql, conn)
' get the adapter object and attach the command object to it
Using ad As New SqlDataAdapter(cmd)
' fire Fill method to fetch the data and fill into DataTable
ad.Fill(table)
End Using
End Using
End Using
' specify the data source for the GridView
GridView1.DataSource = table
' bind the data now
GridView1.DataBind()
End Sub

Protected Sub ShowGrid(sender As Object, e As EventArgs)
For Each item As ListItem In chkFields.Items
If item.Selected Then
Dim b As New BoundField()
b.DataField = item.Value
b.HeaderText = item.Value
GridView1.Columns.Add(b)
End If
Next
Me.GetData()
End Sub


End Class



<%@ Page Language="VB" AutoEventWireup="false" CodeFile="TempDynamicColumns.aspx.vb" Inherits="TempDynamicColumns" %>


<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
Select column to show in the GridView:
<asp:CheckBoxList runat="server" ID="chkFields" DataTextField="Column_name" DataValueField="Column_name" RepeatDirection="Horizontal" RepeatLayout="Flow" AutoPostBack="False" />
<p>
<asp:Button ID="btnSub" runat="server" Text="Show" OnClick="ShowGrid" />
</p>
<asp:GridView ID="GridView1" runat="server" EnableViewState="false" AutoGenerateColumns="false" />
</div>

</form>
</body>
</html>


Posted by: Naninoor on: 6/20/2013 | Points: 25

hi Sheo Narayan
this is a good article .For the resultant gridview i tried to set pageindexchanging event.when i click on the page index which is visible beneath the gridview the gridview is disappearing. Please help me in this regard.
This is the code i am trying.
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" EnableModelValidation="True"
AllowPaging="true" onpageindexchanging="GridView1_PageIndexChanging"
EnableViewState="False"/>


Login to post response

Comment using Facebook