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: google.co.uk/… — user1017882
– user1017882, Commented Jan 10, 2014 at 10:14
Have a look at the anser in stackoverflow.com/questions/8708057/… — Joachim Rosskopf
– Joachim Rosskopf, Commented Jan 10, 2014 at 10:18

jomsk1e · Accepted Answer · 2014-01-10 14:28:48Z

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.

Lucian · Accepted Answer · 2014-01-14 13:55:24Z

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:

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.

edited Jan 14, 2014 at 13:55

answered Jan 10, 2014 at 10:27

Lucian

4,0015 gold badges32 silver badges34 bronze badges

6 Comments

user3181292 Over a year ago

this method i know but the way i am doing could you please help me in that..if possible

Lucian Over a year ago

in this case just comment the row dataGridView1.DataSource = ds.Tables[0], and completely bind the grid programmatically in the for loop :)

user3181292 Over a year ago

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 Over a year ago

what to do for this??

Lucian Over a year ago

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

|

Collectives™ on Stack Overflow

fetching data in datagridview in C#

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related