Invalid cast error for float value in displaying sum of column total in GridView Footer in ASP.Net

Hi jqdesigner,

Instead of type casting to float value use double.

Refer the sample for your reference.

SQL

CREATE TABLE Test
(
Department NVARCHAR(20),	
Manager	 NVARCHAR(20),
Qty INT,
Amount Float
)

INSERT INTO Test VALUES('1','Name 1',10,20.5)
INSERT INTO Test VALUES('2','Name 2',11,50.3)
INSERT INTO Test VALUES('3','Name 3',12,24.8)
INSERT INTO Test VALUES('4','Name 4',13,5)

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" ShowFooter="true">
    <Columns>
    </Columns>
</asp:GridView>

C#

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

private void BindGrid()
{
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString);
    SqlDataAdapter oAdapter = new SqlDataAdapter("SELECT * FROM Test", sqlcon);
    DataSet ds = new DataSet();
    oAdapter.Fill(ds);
    DataTable dt = ds.Tables[0];
    if (dt.Rows.Count > 0)
    {
        GridView1.DataSource = dt;
        GridView1.DataBind();

        //here add code for column total sum and show in footer 
        GridView1.FooterRow.Cells[0].Text = "Total";
        GridView1.FooterRow.Cells[2].Font.Bold = true;
        GridView1.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Left;
        int totalQty = 0;
        for (int k = 2; k < dt.Columns.Count - 1; k++)
        {
            totalQty = dt.AsEnumerable().Sum(row => row.Field<int>(dt.Columns[k].ToString()));
            GridView1.FooterRow.Cells[k].Text = totalQty.ToString();
            GridView1.FooterRow.Cells[k].Font.Bold = true;
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige;
        }
        double totalAmount = 0;
        for (int k = 3; k < dt.Columns.Count; k++)
        {
            totalAmount = dt.AsEnumerable().Sum(row => row.Field<double>(dt.Columns[k].ToString()));
            GridView1.FooterRow.Cells[k].Text = totalAmount.ToString();
            GridView1.FooterRow.Cells[k].Font.Bold = true;
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige;
        }
    }
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGrid()
    End If
End Sub

Private Sub BindGrid()
    Dim sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings(1).ConnectionString)
    Dim oAdapter As New SqlDataAdapter("SELECT * FROM Test", sqlcon)
    Dim ds As New DataSet()
    oAdapter.Fill(ds)
    Dim dt As DataTable = ds.Tables(0)
    If dt.Rows.Count > 0 Then
        GridView1.DataSource = dt
        GridView1.DataBind()

        'here add code for column total sum and show in footer 
        GridView1.FooterRow.Cells(0).Text = "Total"
        GridView1.FooterRow.Cells(2).Font.Bold = True
        GridView1.FooterRow.Cells(2).HorizontalAlign = HorizontalAlign.Left
        Dim totalQty As Integer = 0
        For k As Integer = 2 To dt.Columns.Count - 2
            totalQty = dt.AsEnumerable().Sum(Function(row) row.Field(Of Integer)(dt.Columns(k).ToString()))
            GridView1.FooterRow.Cells(k).Text = totalQty.ToString()
            GridView1.FooterRow.Cells(k).Font.Bold = True
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige
        Next
        Dim totalAmount As Double = 0
        For k As Integer = 3 To dt.Columns.Count - 1
            totalAmount = dt.AsEnumerable().Sum(Function(row) row.Field(Of Double)(dt.Columns(k).ToString()))
            GridView1.FooterRow.Cells(k).Text = totalAmount.ToString()
            GridView1.FooterRow.Cells(k).Font.Bold = True
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige
        Next
    End If
End Sub

Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
    GridView1.PageIndex = e.NewPageIndex
    Me.BindGrid()
End Sub

Output