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

Solution 1

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

Solution 2

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

Comments

[bhert]

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

[John Woo]

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

[bhert]

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

[John Woo]

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

[bhert]

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

[Anton]

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