none
How to bind Access DB table to DataGridView programmatically RRS feed

  • Question

  • I want to bind Access DB table to the DataGridView programmatically in WinForm.

    I want to set connection string, DB name and table name to the datagridview.

    And I want to sett each column in my code.

    How to do that?

    Wednesday, May 1, 2019 4:56 AM

Answers

  • Hi Jeff0803,

    Thank you for posting here.

    If you want to show Access database in datagridview, you could try the code below.

            private void Button1_Click(object sender, EventArgs e)
            {
                string strDSN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = Database1.accdb";
                dataGridView1.AllowUserToAddRows = false;
    
                string strSQL = "SELECT * FROM Table1";
                // create Objects of ADOConnection and ADOCommand  
                OleDbConnection myConn = new OleDbConnection(strDSN);
                OleDbDataAdapter myCmd = new OleDbDataAdapter(strSQL, myConn);
                //myConn.Open();
                DataSet dtSet = new DataSet();
                myCmd.Fill(dtSet, "Table1");
                DataTable dTable = dtSet.Tables[0];
                dataGridView1.DataSource = dtSet.Tables["Table1"].DefaultView;
                myConn.Close();
    
            }

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jeff0803 Wednesday, May 1, 2019 1:41 PM
    Wednesday, May 1, 2019 5:31 AM
    Moderator

All replies

  • Hi Jeff0803,

    Thank you for posting here.

    If you want to show Access database in datagridview, you could try the code below.

            private void Button1_Click(object sender, EventArgs e)
            {
                string strDSN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = Database1.accdb";
                dataGridView1.AllowUserToAddRows = false;
    
                string strSQL = "SELECT * FROM Table1";
                // create Objects of ADOConnection and ADOCommand  
                OleDbConnection myConn = new OleDbConnection(strDSN);
                OleDbDataAdapter myCmd = new OleDbDataAdapter(strSQL, myConn);
                //myConn.Open();
                DataSet dtSet = new DataSet();
                myCmd.Fill(dtSet, "Table1");
                DataTable dTable = dtSet.Tables[0];
                dataGridView1.DataSource = dtSet.Tables["Table1"].DefaultView;
                myConn.Close();
    
            }

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Jeff0803 Wednesday, May 1, 2019 1:41 PM
    Wednesday, May 1, 2019 5:31 AM
    Moderator
  • Thanks for detailed answer.

    BTW, is it possible to display each column with custom format?

    For example, 5'th colulmn has "1", "2" in the database's table.

    "1" means "Yes" and "2" means "No".

    I want to display "Yes" or "No" from the datagridview.

    How to do this?

    Wednesday, May 1, 2019 1:42 PM
  • Hi Jeff0803,

    My accessdb

    Please try the code below. Get the column named "5thColumn" to change the 1,2 value to Yes, No.

            private void Button1_Click(object sender, EventArgs e)
            {
                string strDSN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = Database1.accdb";
                dataGridView1.AllowUserToAddRows = false;
    
                string strSQL = "SELECT * FROM Table1";
                // create Objects of ADOConnection and ADOCommand  
                OleDbConnection myConn = new OleDbConnection(strDSN);
                OleDbDataAdapter myCmd = new OleDbDataAdapter(strSQL, myConn);
                //myConn.Open();
                DataSet dtSet = new DataSet();
                myCmd.Fill(dtSet, "Table1");
                DataTable dTable = dtSet.Tables[0];
                foreach (DataRow row in dTable.Rows)
                {
                    foreach (DataColumn column in dTable.Columns)
                    {
                        if (column.Caption == "5thColumn")
                        {
                            if (row.Field<string>(column) =="1")
                            {
                                row.SetField(column, "Yes");
                            }
                            else if (row.Field<string>(column) == "2")
                            {
                                row.SetField(column, "No");
                            }
                        }
    
                    }
                }
                dataGridView1.DataSource = dtSet.Tables["Table1"].DefaultView;
                myConn.Close();
    
            }

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 2, 2019 7:35 AM
    Moderator