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());

Answer 1

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:

1. Using the DataBind() method
2. 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.

Answer 2

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.