fetching data in datagridview in C#

Question

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;


namespace testdb
{
    public partial class Form1 : Form
    {
        private string constr =
            @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source =     C:/Users/Xprts_3/Documents/Database1.accdb";

        public Form1()
        {
            InitializeComponent();
            Bind();
        }

        private void Bind()
        {
            OleDbConnection con = new OleDbConnection(constr);
            OleDbCommand cmd = new OleDbCommand();

            cmd.Connection = con;
            cmd.CommandText = "select * from tb1";
            cmd.CommandType = CommandType.Text;
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                dataGridView1.Rows.Add(new DataGridViewRow());
                int j;
                for (j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    dataGridView1.Rows[i].Cells[j].Value = ds.Tables[0].Rows[i][j].ToString();
                }
            }

            con.Close();
        }
    }
}

This is my code for fetching data from database in a DataGridView, but it is showing this error:

Rows cannot be programmatically added to the DataGridView's rows collection when the control is data-bound at this line:

dataGridView1.Rows.Add(new DataGridViewRow());

Why is it titled 'php'? There's an unbelievable amount of relevant responses to this exact problem on the internet. Try google: https://www.google.co.uk/search?q=Rows+cannot+be+programmatically+added+to+the+DataGridView's+rows&oq=Rows+cannot+be+programmatically+added+to+the+DataGridView's+rows&aqs=chrome..69i57j0l3.160j0&sourceid=chrome&ie=UTF-8 — , Jan 10 '14 at 10:14
Have a look at the anser in http://stackoverflow.com/questions/8708057/rows-cannot-be-programmatically-added-to-the-datagridviews-row-collection-when — Joachim Rosskopf, Jan 10 '14 at 10:18

Lucian · Answer 1 · 2014-01-14T13:55:24.920

Updated answer:

First you should definitely separate your calls to database from the rest of the code. Let's create a GetData() method that is in charge of well... getting the data :)

private DataSet GetData(string constr) {
    //'using' constructs are always a good idea when dealing with database operations
    //your connection will automatically close
    using(OleDbConnection con = new OleDbConnection(constr)){
        using(OleDbCommand cmd = new OleDbCommand()){       
            cmd.Connection = con;
            cmd.CommandText = "select * from tb1";
            cmd.CommandType = CommandType.Text;
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
    }
}

Then to bind your DataGridView you have two options:

Using the DataBind() method
Programmatically create the grid columns and rows

Method 1 - The DataBind() way:

private void BindWithDataBind() {
    dataGridView1.DataSource = GetData();
    //call the databind method to bound the data to the grid
    //the grid structure will be created automatically from the datatable structure
    dataGridView1.DataBind(); 
}

Method 2 - The Programmatic way

private void BindProgramatically() {

    //obtain the data from your OleDB connection
    DataSet ds = GetData(constr);
    if(ds == null) {
        return;
    }
    DataTable dt = ds.Tables[0];

    //build the grid structure, add a new grid column for each datatable column
    for(int i = 0; i < dt.Columns.Count; i++) {
        DataGridViewColumn newColumn = new DataGridViewColumn();
        newColumn.Name = dt.Columns[i].ColumnName;
        dataGridView1.Columns.Add(newColumn);
    }   

    for (int i = 0; i < dt.Rows.Count; i++) {
        //call ToArray to pass a copy of the data from the datatable
        //(make sure you have 'using System.Linq;' at the top of your file
        dataGridView1.Rows.Add(dt.Rows[i].ItemArray.ToArray());
    }
}

On MSDN's official documentation about DataGridView.Columns property you can find more info about binding programmatically. Here is the link.

this method i know but the way i am doing could you please help me in that..if possible — user3181292, Jan 10 '14 at 10:33
in this case just comment the row `dataGridView1.DataSource = ds.Tables[0]`, and completely bind the grid programmatically in the for loop :) — Lucian, Jan 10 '14 at 10:35
it is showing this error ..Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index at line...dataGridView1.Rows[i].Cells[j].Value = ds.Tables[0].Rows[i][j].ToString(); — user3181292, Jan 10 '14 at 10:47
This is because you have not defined the column structure of the grid. Before the `for` loop, you will need to add a new `for` loop that iterates through the columns of the DataTable, and adds a new DataGridViewColumn object for each column in your data table — Lucian, Jan 10 '14 at 10:54
please tell me what to add..could you please write the code...i am new to this language..so dont know much about it..could you please write the code if possible..thanks — user3181292, Jan 10 '14 at 11:02

score 1 · Answer 2 · answered Jan 10 '14 at 14:28

The answer of Lucian is right you should use DataBind() in that case. But if you want to add rows in your DataGridView in the other way, here's a sample:

private void Bind()
{
    OleDbConnection con = new OleDbConnection(constr);
    OleDbCommand cmd = new OleDbCommand();

    cmd.Connection = con;
    cmd.CommandText = "select * from tb1";
    cmd.CommandType = CommandType.Text;
    OleDbDataAdapter da = new OleDbDataAdapter();
    da.SelectCommand = cmd;
    DataSet ds = new DataSet();
    da.Fill(ds);


    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
        var row = (DataGridViewRow)dataGridView1.Rows[0].Clone();

        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
        {
            row.Cells[j].Value = ds.Tables[0].Rows[i][j].ToString();
        }

        dataGridView1.Rows.Add(row);
    }

    con.Close();
}

Not tested but I think it should work. Just comment for the result.

fetching data in datagridview in C#

2 Answers2