C# Datatable column name changing issue

Question

This is my first post here and I am new to programming. Right now, I am trying to changing column name in the DataTable which the data is fetch from the sql database. After I changing the column name using a foreach loop, it appears that after i change the column name of the datatable, the program crash right after I bind it to the gridview.

the error occur

Any idea what is causing the error?

C# code behind:

//custom query generator
Session["query"] = db.generate_query(parameter1, parameter2, parameter3, parameter4);
//connecting sql
SqlConnection conn = new SqlConnection("Data Source=USER-PC;Initial Catalog=ISMS;Integrated Security=True");
SqlCommand cmd1 = new SqlCommand(Session["query"].ToString(),conn);
SqlDataAdapter sdal = new SqlDataAdapter(cmd1);


//when search is needed
dt_table = new DataTable();
sdal.Fill(dt_table);
// dataTable.Columns["ExistingColumnName"].ColumnName = "regnum";
//renaming column in datatable

foreach (DataColumn dtclm in dt_table.Columns) {
    dt_table.Columns[dtclm.ToString()].ColumnName = dt_table.Columns[dtclm.ToString()].ColumnName.ToString().Replace("_"," ");

}

staff_attendance_gridview.Visible = true;
staff_attendance_gridview.DataSource = dt_table;
staff_attendance_gridview.DataBind();
result_message.Text = dt_table.Rows.Count.ToString() + " rows in the table";

the aspx file:

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="admin_attendance_.aspx.cs" Inherits="ISMS.admin_attendance" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
<!--start container box-->
<div id="container_box">  
<div id="text">
<asp:Label runat="server" ID="welcome_message" /><br />
<br />
<!--search bar-->
<p>
<br />
    Employee ID: <asp:TextBox runat="server" ID="employee_search" Text="" /><br />
    <br />
    Attendance ID: <asp:TextBox runat="server" ID="attendance_ID_text_box" Text="" /><br />
    <br />
    fill up both attendance ID and Employee ID and click delete to delete certain record.<br />
    <br />
<asp:Button ID="search_btn" runat="server" Text="search" OnClick="search_btn_Click" />
<asp:Button ID="clock_in_or_out_btn" runat="server" Text="clock in/out" OnClick="clock_in_or_out_btn_Click" />
<asp:Button ID="delete_button" runat="server" Text="delete" OnClick="delete_button_Click" />
    <br />
</p>
<!--gridview-->
   <hr />
    <br />
<div id="gridview">
<asp:GridView runat="server" ID="staff_attendance_gridview" OnPageIndexChanging="gridview_PageIndexChanging" AutoGenerateColumns="true" DataKeyNames="Attendance_id" >
</asp:GridView>
 


</div>
<!--end gridview-->
<p>
<!--validator-->
<asp:RegularExpressionValidator ID="employee_search_validator" runat="server"     
ErrorMessage="Invalid searching citeria. Only exactly 8 numbers is allowed." 
ControlToValidate="employee_search"
 ValidationExpression="^[0-9]{8}$" /><br />
<br />
</p>
<br />
<!--hyperlink to main menu-->
<asp:Label runat="server" ID="result_message" /><br />
<a href="admin.aspx" >Main menu</a>
</div>    
</div>
<!--end container box-->
</asp:Content>

Database table structure

You should avoid using DataTable, and learn how to properly handle an IDisposable. — mason
– mason, Commented Sep 14, 2017 at 19:55
ok noted. This is the first time i heard IDisposable Interface, will learn it — moja
– moja, Commented Sep 14, 2017 at 20:00

mason · Accepted Answer · 2017-09-14 20:12:54Z

2

You have an attribute in your GridView that refers to a column Attendance_id. That must match the updated name of your column Attendance id.

I've corrected your code below to properly handle IDisposable and remove overly complex bits.

Session["query"] = db.generate_query(parameter1, parameter2, parameter3, parameter4);

using(SqlConnection conn = new SqlConnection("Data Source=USER-PC;Initial Catalog=ISMS;Integrated Security=True"))
{
    using(SqlCommand cmd1 = new SqlCommand(Session["query"].ToString(), conn))
    {
        using(SqlDataAdapter sdal = new SqlDataAdapter(cmd1))
        {
            dt_table = new DataTable();
            sdal.Fill(dt_table);
        }
    }
}

foreach (DataColumn column in dt_table.Columns)
{
    column.ColumnName = column.ColumnName.Replace("_", " " );
}

staff_attendance_gridview.Visible = true;
staff_attendance_gridview.DataSource = dt_table;
staff_attendance_gridview.DataBind();
result_message.Text = dt_table.Rows.Count + " rows in the table";

edited Sep 14, 2017 at 20:12

answered Sep 14, 2017 at 19:56

mason

32.8k12 gold badges83 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mason Over a year ago

@moja If you get an error where something is referring to something you previously renamed, do yourself a favor and do a text search for the old value and see if it's referred to anywhere else.

moja Over a year ago

ok got it, will search for the old value text if I get the error after renaming. thanks for the advice

Collectives™ on Stack Overflow

C# Datatable column name changing issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related