How to Display data in datagridview from access database

Question

I have the codes below here in displaying data in datagridview from access database. I have different rows but in only display the last row of data in database. I dont know what's wrong in my code.

    dataGridView1.Columns.Add("UserID", "UserID");
    dataGridView1.Columns.Add("FirstName", "FirstName");
    dataGridView1.Columns.Add("MI", "MI");
    dataGridView1.Columns.Add("LastName", "LastName");
    dataGridView1.Columns.Add("Birthdate", "Birthdate");
    dataGridView1.Columns.Add("Address", "Address");
    dataGridView1.Columns.Add("UserName", "UserName");
    dataGridView1.Columns.Add("UserPassword", "UserPassword");
    dataGridView1.Columns.Add("Rights", "Rights");


    OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\SISC-STRONGHOLD\MIS!\wilbert.beltran\SEEDBucksDbase.accdb");
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;
    cmd.CommandText = "SELECT * From TableAcct";
    OleDbDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        dataGridView1.Rows.Add();

        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["UserID"].Value = reader[0].ToString();
        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["FirstName"].Value = reader[1].ToString();
        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["MI"].Value = reader[2].ToString();
        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["LastName"].Value = reader[3].ToString();
        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Birthdate"].Value = reader[4].ToString();
        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Address"].Value = reader[5].ToString();
        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["UserName"].Value = reader[7].ToString();
        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["UserPassword"].Value = reader[8].ToString();
        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Rights"].Value = reader[9].ToString();
    }
    conn.Close();
}

@JohnSaunders - I'm sorry but Im just a beginner in C#. The codes work but it only display the last row of data in my table but it should have display all the rows in the table. — bhert, Mar 01 '13 at 02:41

John Woo · Accepted Answer · 2013-03-01T02:57:28.337

7

You can directly bind dataGridView1 using OleDbDataAdapter. Make sure that the names of the columns in the datagridview matches with the field names return by the query so it will contain blank columns and create another column for every fields.

string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;
        Data Source=\\SISC-STRONGHOLD\MIS!\wilbert.beltran\SEEDBucksDbase.accdb";
string query = "SELECT * From TableAcct";
using(OleDbConnection conn = new OleDbConnection(connStr))
{
    using(OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
    {
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        DataGridView1.DataSource= ds.Tables[0];
    }
}

edited Mar 01 '13 at 02:57

answered Mar 01 '13 at 02:42

John Woo

258,903
69
498
492

1

in line "adapter.Fill(ds[0]);" the error says "Cannot apply indexing with [] to an expression of type 'System.Data.Dataset'". Thanks :) – bhert Mar 01 '13 at 02:56
oh it's a typo, it should only be `adapter.Fill(ds);`. – John Woo Mar 01 '13 at 02:57
1

Thanks a lot. It works perfectly :) Thank you. It's a short code and it's perfect :) thank you! – bhert Mar 01 '13 at 02:59
you're welcome. Actually it's not complete yet. You have to add exceptions to properly handle errors `:D` – John Woo Mar 01 '13 at 03:00
is it possible to only display the a specific row? for example. I have an employee table and when i type the "lastname" in the textbox. It will only display in the datagridview the related information about the "lastname" :) thanks, looking forward for your answers :) – bhert Mar 01 '13 at 05:56

score 1 · Answer 2 · answered Mar 13 '16 at 09:18

public partial class WebForm1 : System.Web.UI.Page
{
    public String name,type,rvw;


    public void Page_Load(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Uz!\Documents\Data_Ware.mdf;Integrated Security=True;Connect Timeout=30");
        SqlDataAdapter sda = new SqlDataAdapter("Select * From CoffeeDB  ", con);
        DataTable dt = new DataTable();
        sda.Fill(dt);

        GridView.DataSource = dt;
    }
}

Would you care to write some explanation to the provided code. Thanks — Anton Sarov, Mar 13 '16 at 09:43

Ramesh P · Answer 3 · 2016-10-03T09:49:59.263

0

Please use this code to display your data's to datagridview in c#.net

 OleDBConnection con=new      OleDBConnection("Copy your database database path and paste it");
    con.open();
    Dataset ds=new Dataset();
         OleDBDataAdapter sdr=new      OleDBDataAdapter("select * from table_name",con);
    sdr.Fill(ds);
    datagridview1.Datasource=ds.tables[0];
    con.close();

edited Oct 03 '16 at 09:49

answered Dec 15 '15 at 11:30

Ramesh P

81
4

score -1 · Answer 4 · answered Jan 23 '19 at 03:06

string con= "SERVER=localhost; user id=root; password=; database=dbname";

MySqlConnection connect = new MySqlConnection(con);
connect.Open();
try
{
string sqlQuery = "SELECT * FROM DATA WHERE date(date) = date(now())";
MySqlDataAdapter da = new MySqlDataAdapter(sqlQuery, connect);
DataTable ds = new DataTable();
da.Fill(ds);
Datagrid.DataSource = ds;
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}

How to Display data in datagridview from access database

4 Answers4