Add new column programmatically to DataGridView (DataGridview Filled with DataTable)

I have binded a datagridview with access database table. I want to add a new custom column to DataGridView when the program loads. This column contains the auto incremented number “Serial Number” for the rows in the DataTable. The code is below,

using (var conn = new OleDbConnection(DatabaseObjects.ConnectionString))
        {
            conn.Open();
            command = new OleDbCommand("SELECT s.RollNo, s.SName, s.FName, s.DOB, c.Class, s.[Section],s.Picture from students as s left outer join classes as c " +
                "on c.ClassID = s.ClassID", conn);
            dTable = new DataTable();
            dTable.Load(command.ExecuteReader());
            dgvDisplay.Columns.Add("SrNo", "Sr No"); // Adding "Serial No" Column to DataGridView
            dgvDisplay.DataSource = dTable;
            dgvDisplay.Sort(dgvDisplay.Columns["RollNo"], ListSortDirection.Ascending);

            // Change Column Headings
            dgvDisplay.Columns["RollNo"].HeaderText = "Roll No";
            dgvDisplay.Columns["SName"].HeaderText = "Student Name";
            dgvDisplay.Columns["FName"].HeaderText = "Father Name";
            dgvDisplay.Columns["DOB"].HeaderText = "Date Of Birth";

            // Serial Number code
            int srNo = 1;
            foreach(DataGridViewRow row in dgvDisplay.Rows)
            {
                row.Cells["SrNo"].Value = srNo;
                srNo++;
            }
        }

But When I run the program the Serial number Column is empty. This column have to show numbers like 1,2,3.. for rows in gridview.

Now I changed the code for new column addition. I try this code below

            dTable.Columns.Add(new DataColumn("SrNo", typeof(Int32))); // Adding "Serial No" Column to DataTable

            dgvDisplay.DataSource = dTable;
            dgvDisplay.Sort(dgvDisplay.Columns["RollNo"], ListSortDirection.Ascending);

            // Serial Number code
            int srNo = 1;
            foreach (DataRow row in dTable.Rows)
            {
                row["SrNo"] = srNo;
                srNo++;
            }

This generate the Serial No but according to the order of the DataTable as the Record Rows are inserted to it.

Answer

I am still unclear what you want. I will assume from the picture that you want the column “SrNo” to have the numbers 1, 2, 3… etc.… instead of the “pre-sorted” index order. This is doable; however, it appears there may be some confusion as to setting a cell value in the “grid” as opposed to setting a cell value in the data source.

Example, looking at the first code snippet the code gets a DataTable dTable from a data base. Then the code adds a column to the GRID. Then the data source is set to the grid. Then the GRID is sorted. Finally, a loop through all the rows in the GRID to set the SrNo values. This is straight forward and from my tests… this SHOULD WORK. You state that the column is empty however in my tests this code worked as expected with 1, 2, 3 … in the “SrNo” column. It is unclear where this code is called so I would conclude something else is going on after this code is called.

As other have commented, it may be better to put the column into the DataSource DataTable itself. This is doable, however, there is one problem from the approach you are using… you need to SORT the data table FIRST before you add the “SrNo” Numbers AND the table that we add the ”SrNo” column to CAN NOT be sorted. Therefore, you need to get a “new” table from the “sorted” table.

It is unclear why you do not do all this when you initially query the data base, however, below is an example of what I described above.

To start let us see “why” we can NOT use a “pre” sorted DataTable to add the numbers to. First, we get a data table. Then sort the data table, then use it as a data source to the grid. If we add the new column to the GRID, then… this works just fine. However, if we add the column to the DataTable and loop through all the rows in the data table like…

foreach (DataRow row in dTable.Rows) {
    row["SrNo"] = srNo;
    srNo++;
}

This is NOT going to work because the loop is NOT going to loop through the “sorted” order. This is what I meant earlier when I said. “it cannot be a sorted table.” With that said, it would appear that a simple solution would be to get a NEW table that is already sorted. THEN add the “SrNo” numbers to that table.

In the Example below, if you comment out the line…

dTable = dTable.DefaultView.ToTable();

you will see the items are not added in the proper order.

A global DataTable is used, however this is unnecessary.

DataTable dTable;

public Form1() {
  InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e) {
  //LoadDGV();
  LoadDGV2();
}

LoadDGV() is the first snippet of code adding the column to the GRID and it works as expected.

private void LoadDGV() {
  dTable = GetTable();
  dgvDisplay.Columns.Add("SrNo", "Sr No"); // Adding "Serial No" Column to DataGridView
  dgvDisplay.DataSource = dTable;
  dgvDisplay.Sort(dgvDisplay.Columns["ID"], ListSortDirection.Ascending);
  int srNo = 1;
  foreach (DataGridViewRow row in dgvDisplay.Rows) {
    row.Cells["SrNo"].Value = srNo++;
  }
}

LoadDGV2() adds the column to the data table. The key line of code to make it work is… dTable = dTable.DefaultView.ToTable();

private void LoadDGV2() {
  dTable = GetTable();
  dTable.DefaultView.Sort = "ID";
  dTable = dTable.DefaultView.ToTable();
  dTable.Columns.Add(new DataColumn("SrNo", typeof(Int32))); // Adding "Serial No" Column to DataTable
  int srNo = 1;
  foreach (DataRow row in dTable.Rows) {
    row["SrNo"] = srNo++;
  }
  dgvDisplay.DataSource = dTable;
}

A method to get some test data to complete the example.

private DataTable GetTable() {
  DataTable dt = new DataTable();
  dt.Columns.Add("ID", typeof(int));
  dt.Columns.Add("Name", typeof(string));
  Random rand = new Random();
  for (int i = 0; i < 20; i++) {
    dt.Rows.Add(rand.Next(1, 200), "Name_" + i);
  }
  return dt;
}

Hope that makes sense.