How to handle an error when using DataGridView controls and Access database queries?

Question

I have two DataGridView controls.

For the second one, I just copy-pasted the code from the first and changed where the difference was. But I get an error at the second when I want to view the result of my SQL code.
Translated in English the error show something like that there was no value given to at least one required parameter.

private void button1_Click(object sender, EventArgs e)
{
    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=save.mdb";

    try
    {
        database = new OleDbConnection(connectionString);
        database.Open();
        date =  DateTime.Now.ToShortDateString();

        string queryString = "SELECT zivila.naziv,(obroki_save.skupaj_kalorij/zivila.kalorij)*100 as Kolicina_v_gramih "
               + "FROM (users LEFT JOIN obroki_save ON obroki_save.ID_uporabnika=users.ID) "
               + "LEFT JOIN zivila ON zivila.ID=obroki_save.ID_zivila"
               + "WHERE users.ID= " + a.ToString();

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

public void loadDataGrid(string sqlQueryString)
{
    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;
    dataGridView1.AllowUserToAddRows = false;
    dataGridView1.ReadOnly = true;
    dataGridView1.Columns[0].Visible = true;
}

private void button2_Click(object sender, EventArgs e)
{
    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=save.mdb";

    try
    {
        database = new OleDbConnection(connectionString);
        database.Open();
        date = DateTime.Now.ToShortDateString();

        string queryString = "SELECT skupaj_kalorij  "
                + "FROM obroki_save "
                + "WHERE users.ID= " + a.ToString();

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

public void loadDataGrid2(string sqlQueryString)
{
    OleDbCommand SQLQuery = new OleDbCommand();
    DataTable data = null;
    dataGridView2.DataSource = null;
    SQLQuery.Connection = null;
    OleDbDataAdapter dataAdapter = null;
    dataGridView2.Columns.Clear(); // <-- clear columns

    SQLQuery.CommandText = sqlQueryString;
    SQLQuery.Connection = database;
    data = new DataTable();
    dataAdapter = new OleDbDataAdapter(SQLQuery);
    dataAdapter.Fill(data);
    dataGridView2.DataSource = data;
    dataGridView2.AllowUserToAddRows = false;
    dataGridView2.ReadOnly = true;
    dataGridView2.Columns[0].Visible = true;
}

Have you tested your SQL string without the WHERE clause in the Access QBE grid? The parens don't look right to me, and Jet/ACE is very sensitive about that. — David-W-Fenton
– David-W-Fenton, Commented May 16, 2010 at 19:57

matt · Accepted Answer · 2010-06-09 12:37:13Z

You'd be far better off reading and doing this tutorial: http://msdn.microsoft.com/en-us/library/ms171884(v=VS.80).aspx It will probably take about 20 minutes to complete

And then (as needed) the rest of them (google for Data Walkthroughs)

They will teach you how to do your data access properly and, after becoming familiar with the advanced tools in Visual Studio for connecting to databases, you'll be able to generate an app that will show database contents in a grid, and save them back to the db, faster than it took you to even write your post here..

The code will be high performance, more secure and easier to write than what you have here - the code you have here is low quality, prone to SQL injection attacks, not modular or arranged in a way that is suited to the language youre using and takes a long time to write; all in, it has no good points at all (please don't be offended)

I thoroughly recommend scrapping the entire thing and starting again, doing things in the way advocated by Microsoft's tutorial. It might seem like the wrong thing to do after you've poured so many hours into this broken, bad way of doing things, but trust me.. You'll be glad you did; 20 minutes investment now will save you thousands of hours of coding in the future

One final point, if your database data doesnt seem to be saving changes when you do things the way the tutorial tells you to, click on the database in Solution Explorer and change "Copy to Output Directory" to "Copy if Newer" - c# is updating your database but youre either looking in the wrong DB to check whether it worked, or Visual Studio is replacing your edited database with a new one every time you start your app

Lost in Alabama · Accepted Answer · 2010-05-19 15:45:03Z

0

I think the problem is in private void button2_Click() with

a.ToString();

What's a?

answered May 19, 2010 at 15:45

Lost in Alabama

1,65310 silver badges16 bronze badges

2 Comments

Simon Over a year ago

it has a value for sure. It ain't that. I've actualy found the problem. So i'm cool now.

24x7Programmer Over a year ago

if you solved the problem then either accept the answer or delete the question

openshac · Accepted Answer · 2010-06-03 09:28:28Z

Try putting quotes around a.Tostring():

string queryString = "SELECT zivila.naziv,(obroki_save.skupaj_kalorij/zivila.kalorij)*100 as Kolicina_v_gramih "
               + "FROM (users LEFT JOIN obroki_save ON obroki_save.ID_uporabnika=users.ID)"
               + " LEFT JOIN zivila ON zivila.ID=obroki_save.ID_zivila "
               + " WHERE users.ID= '" + a.ToString() + "'";

or use string.format():

string queryString = string.format("SELECT zivila.naziv,(obroki_save.skupaj_kalorij/zivila.kalorij)*100 as Kolicina_v_gramih "
               + "FROM (users LEFT JOIN obroki_save ON obroki_save.ID_uporabnika=users.ID)"
               + " LEFT JOIN zivila ON zivila.ID=obroki_save.ID_zivila "
               + " WHERE users.ID= '{0}'", a);

Collectives™ on Stack Overflow

How to handle an error when using DataGridView controls and Access database queries?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related