Retrieve dynamic ASP.Net GridView cell value through Column Name using C# and VB.Net

Last Reply one month ago By pandeyism

Posted one month ago

ExamDate is depending on the number of subjects. And Subjects may be 3 or 4 or even 9 for any specific class and all Student's datesheet for exam is being shown by using Dynamic Pivot Query.

Now I want to print details of each line of the gridview row. I have printed AdmissionNo,SName,FName and PhoneNo successfuuly. But As ExamDate is dynamic and based on No. of Subjects so i want to print it dynamically not manullay. how to do it?

<asp:GridView ID="GridView2" runat="server" Class="table table-striped table-bordered table-hover"
    AllowPaging="false" RowStyle-Wrap="false" HeaderStyle-Wrap="false" OnRowDataBound="GridView1_RowDataBound">
    <Columns>
        <asp:TemplateField>
            <HeaderTemplate>
                <asp:CheckBox ID="checkAll" runat="server" onclick="checkAll(this);" />
            </HeaderTemplate>
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" onclick="Check_Click(this)" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

 

    protected void btnSMS_Click(object sender, EventArgs e)
    {
        int success = 0;
        DataTable dt = GetPivotedDataTable();
        foreach (GridViewRow row in GridView1.Rows)
        {
            CheckBox chk = (row.Cells[0].FindControl("chkSelect") as CheckBox);
            if (chk.Checked)
            {
                DataRow dr = dt.Select("Row=" + row.Cells[1].Text)[0];
                string AdNo = dr["AdmissionNo"].ToString();
                string SName = dr["SName"].ToString();
                string FName = dr["FName"].ToString();
                string FPhone = dr["FPhone"].ToString();
                Response.Write(AdNo+"-"+SName+FName+ "\n");                
                con.conClose();
            }
        }
    }

 

Posted one month ago Modified on one month ago

Hey smile,

Please refer below sample.

HTML

<div>
    <asp:GridView ID="gvDetails" runat="server">
        <Columns>
            <asp:TemplateField>
                <HeaderTemplate>
                    <asp:CheckBox ID="checkAll" runat="server" />
                </HeaderTemplate>
                <ItemTemplate>
                    <asp:CheckBox ID="chkSelect" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <asp:Button Text="Send" runat="server" OnClick="btnSMS_Click" />
</div>

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;
using System.Data;

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = GetData();
        gvDetails.DataSource = dt;
        gvDetails.DataBind();
    }
}

private DataTable GetData()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = @"DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
                DECLARE @ColumnName AS NVARCHAR(MAX)
                SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(Date) FROM (SELECT DISTINCT [Date] FROM Pvt) AS [Date]
                SET @DynamicPivotQuery = '; WITH CTE AS
                                            (
                                                SELECT [Date],Gender,Participation FROM Pvt
                                            )
                                            SELECT Gender,'+@ColumnName+' FROM CTE
                                            PIVOT (MAX(Participation) FOR [Date] IN('+@ColumnName+')) p
                                            ORDER BY Gender DESC'
                EXEC(@DynamicPivotQuery)";

    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

protected void btnSMS_Click(object sender, EventArgs e)
{
    string dynamicVal = string.Empty;
    DataTable dt = GetData();
    foreach (GridViewRow row in gvDetails.Rows)
    {
        CheckBox chk = (row.Cells[0].FindControl("chkSelect") as CheckBox);
        if (chk.Checked)
        {
            DataRow[] dr = dt.Select("Gender='" + row.Cells[1].Text + "'");
            for (int i = 0; i < dr.Length; i++)
            {
                for (int k = 0; k < dt.Columns.Count - 1; k++)
                {
                    string columnName = dt.Columns[k + 1].ColumnName;
                    string value = dr[i].ItemArray[k].ToString() == "" ? "Null" : dr[i].ItemArray[k].ToString();
                    if (k >= 1)
                    {
                        dynamicVal += value + " on " + Convert.ToDateTime(columnName).ToString("MMM d yyyy") + ",";
                    }
                    else
                    {
                        dynamicVal += value + ",";
                    }
                }
                dynamicVal += "</br>";
            }
        }
    }
    Response.Write(dynamicVal);
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = GetData()
        gvDetails.DataSource = dt
        gvDetails.DataBind()
    End If
End Sub

Private Function GetData() As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(Date) FROM (SELECT DISTINCT [Date] FROM Pvt) AS [Date] SET @DynamicPivotQuery = '; WITH CTE AS(SELECT [Date],Gender,Participation FROM Pvt) SELECT Gender,'+@ColumnName+' FROM CTE PIVOT (MAX(Participation) FOR [Date] IN('+@ColumnName+')) p ORDER BY Gender DESC' EXEC(@DynamicPivotQuery)"
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Protected Sub btnSMS_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim dynamicVal As String = String.Empty
    Dim dt As DataTable = GetData()

    For Each row As GridViewRow In gvDetails.Rows
        Dim chk As CheckBox = (TryCast(row.Cells(0).FindControl("chkSelect"), CheckBox))

        If chk.Checked Then
            Dim dr As DataRow() = dt.[Select]("Gender='" & row.Cells(1).Text & "'")

            For i As Integer = 0 To dr.Length - 1

                For k As Integer = 0 To dt.Columns.Count - 1 - 1
                    Dim columnName As String = dt.Columns(k + 1).ColumnName
                    Dim value As String = If(dr(i).ItemArray(k).ToString() = "", "Null", dr(i).ItemArray(k).ToString())

                    If k >= 1 Then
                        dynamicVal += value & " on " & Convert.ToDateTime(columnName).ToString("MMM d yyyy") & ","
                    Else
                        dynamicVal += value & ","
                    End If
                Next

                dynamicVal += "</br>"
            Next
        End If
    Next

    Response.Write(dynamicVal)
End Sub

Screenshot