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
– bhert, Commented Mar 1, 2013 at 2:41

John Woo · Accepted Answer · 2013-03-01 02:57:28Z

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 1, 2013 at 2:57

answered Mar 1, 2013 at 2:42

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

bhert Over a year ago

in line "adapter.Fill(ds[0]);" the error says "Cannot apply indexing with [] to an expression of type 'System.Data.Dataset'". Thanks :)

John Woo Over a year ago

oh it's a typo, it should only be adapter.Fill(ds);.

bhert Over a year ago

Thanks a lot. It works perfectly :) Thank you. It's a short code and it's perfect :) thank you!

John Woo Over a year ago

you're welcome. Actually it's not complete yet. You have to add exceptions to properly handle errors :D

bhert Over a year ago

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 :)

Owaix Ansari · Accepted Answer · 2016-03-13 09:18:01Z

1

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;
    }
}

answered Mar 13, 2016 at 9:18

Owaix Ansari

679 bronze badges

1 Comment

Anton Sarov Over a year ago

Would you care to write some explanation to the provided code. Thanks

Ramesh P · Accepted Answer · 2016-10-03 09:49:59Z

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 3, 2016 at 9:49

answered Dec 15, 2015 at 11:30

Ramesh P

814 bronze badges

Comments

Miguel · Accepted Answer · 2019-01-23 03:06:50Z

-1

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

answered Jan 23, 2019 at 3:06

Miguel

264 bronze badges

Collectives™ on Stack Overflow

How to Display data in datagridview from access database

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related