skayred skayred - 26 days ago 15
C# Question

Querying MS Access from C#

I'm new in C#. I'm trying to query an

*.accdb
database, but having 0 row in response and datagrid stays clear. Query works from MS Access. What's wrong?

Main form class

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
OleDbConnection database;

public Form1()
{
InitializeComponent();
}

private void filterButton_Click(object sender, EventArgs e)
{
MessageBox.Show(nameFilter.Text);

InitializeComponent();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\qwe.accdb;";
try
{
database = new OleDbConnection(connectionString);
database.Open();

string queryString = "SELECT id FROM table1";
loadDataGrid(queryString);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}

public void loadDataGrid(string sqlQueryString) {

OleDbCommand comm = new OleDbCommand();
comm.CommandText = sqlQueryString;
comm.CommandType = CommandType.Text;
comm.Connection = database;

Int32 returnValue = comm.ExecuteNonQuery();

MessageBox.Show(returnValue.ToString());



OleDbCommand SQLQuery = new OleDbCommand();
DataTable data = null;
dataGridView1.DataSource = null;
SQLQuery.Connection = null;
OleDbDataAdapter dataAdapter = null;
dataGridView1.Columns.Clear(); // <-- clear columns

SQLQuery.CommandText = sqlQueryString;
SQLQuery.Connection = database;
data = new DataTable();
dataAdapter = new OleDbDataAdapter(SQLQuery);
dataAdapter.Fill(data);
dataGridView1.DataSource = data;

MessageBox.Show(data.ToString());

dataGridView1.AllowUserToAddRows = false; // <-- remove the null line
dataGridView1.ReadOnly = true; // <-- so the user cannot type
}
}
}

Answer

Why not something along the lines of:

private void filterButton_Click(object sender, EventArgs e)
{
    dataGridView1.Columns.Clear();
    MessageBox.Show(nameFilter.Text);
    InitializeComponent();
    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\qwe.accdb;";
    try
    {
        using (DataTable dt = new DataTable())
            {
                using (OleDbDataAdapter da = new OleDbDataAdapter(new SqlCommand("SELECT id FROM table1",new OleDbConnection(connectionString))))
                {
                    da.Fill(dt);
                    MessageBox.Show(dt.Rows.Count.ToString());
                    dataGridView1.DataSource = dt;
                    dataGridView1.Update();
                }
            }       

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        return;
    }
}
Comments