Execute SQL command and output result to DataGridView in C#

Question

I am trying to execute an SQL command on an Access Database over an OleDbConnection using C# and use that information to fill a DataGridView on a windows form. I have opened the connection, stated the query, and executed it, but I cannot find how to output the results to the DataGridView on the windows form (named dataOutput).

    private void Query()
    {
        string cmdText = "SELECT * FROM RetentionTable " +
            "WHERE [DateTime] BETWEEN '" + getDateTimeFrom("") + "' AND '" + getDateTimeTo("") + "'";

        string ConnectionPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=RetentionDB.mdb";

        try
        {
            OleDbConnection cn = new OleDbConnection(ConnectionPath);
            DataSet objDataSet = new DataSet();
            OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();

            if (cn.State.Equals(ConnectionState.Closed))
            {
                cn.Open();
            }

            OleDbCommand OleDbSearch = new OleDbCommand(cmdText, cn);
            OleDbSearch.ExecuteNonQuery();

            objDataAdapter.Fill(objDataSet);
            dataOutput.DataSource = objDataSet;
            cn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString());
            MessageBox.Show(ex.StackTrace.ToString());
        }

    }

From what I can see, the query is being executed correctly, but the issue comes when trying to use the objDataAdapter.Fill. I guess I am not understanding how to fill the DataSet with the output from the query. Any help would be much appreciated. Thanks!

Aghilas Yakoub · Accepted Answer · 2013-07-17 18:20:27Z

3

Replace with ExecuteDataSet method

..
OleDbSearch.ExecuteDataSet();
objDataAdapter.Fill(objDataSet);
dataOutput.DataSource = objDataSet;
...

I suggest you to set your close connection in using blok or try with Finally of your try catch

Best practise

 using( var cn = new OleDbConnection(ConnectionPath))
 {
    ... 
 }

answered Jul 17, 2013 at 18:20

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

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

Comments

Richard Deeming · Accepted Answer · 2013-07-17 18:29:11Z

1

A few comments:

Your query is subject to SQL injection. Use a parameterised query instead.
You don't need to open/close the connection; the DataAdapter will do that for you.
You should wrap the OleDbConnection and OleDbCommand objects in a using block to ensure that their resources are cleaned up.
You don't need to call ExecuteNonQuery, or any other Execute... method on the command;
You need to assign the command to the SelectCommand property of the OleDbDataAdapter, or pass it to the constructor.

Try something like this:

private void Query()
{
   const string ConnectionPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=RetentionDB.mdb";

   try
   {
      using (var cn = new OleDbConnection(ConnectionPath))
      using (var cmd = new OleDbCommand("SELECT * FROM RetentionTable WHERE [DateTime] BETWEEN ? And ?"))
      {
         // Parameter names don't matter; OleDb uses positional parameters.
         cmd.Parameters.AddWithValue("@p0", getDateTimeFrom(""));
         cmd.Parameters.AddWithValue("@p1", getDateTimeTo(""));

         var objDataSet = new DataSet();
         var objDataAdapter = new OleDbDataAdapter(cmd);
         objDataAdapter.Fill(objDataSet);

         dataOutput.DataSource = objDataSet;
      }
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.Message.ToString());
      MessageBox.Show(ex.StackTrace.ToString());
   }
}

answered Jul 17, 2013 at 18:29

Richard Deeming

31.5k11 gold badges86 silver badges167 bronze badges

1 Comment

1337Atreyu Over a year ago

SO I did this and it seems to be executing correctly, but there is no data being shown in the grid view. It just shows no entries. I have AutoGenerateColuns = true, but there is no data. Any ideas?

Axarydax · Accepted Answer · 2013-07-17 18:22:38Z

0

I think you can also load the data into a DataTable and point the DataGridView to the table:

DataTable dt = new DataTable();
dt.Fill(OleDbSearch.ExecuteReader());
dataOutput.DataSource = dt;

answered Jul 17, 2013 at 18:22

Axarydax

16.7k22 gold badges95 silver badges157 bronze badges

Comments

Comfortably Numb · Accepted Answer · 2013-07-17 18:22:57Z

0

You should not execute OleDbSearch.ExecuteNonQuery this is used only for queries that modify data (INSERT/UPDATE). Instead assign you command to dataadatper:

objDataAdapter.SelectCommand = OleDbSearch;

And then do your Fill and DataSource assignment.

answered Jul 17, 2013 at 18:22

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Comments

Steve · Accepted Answer · 2013-07-17 18:27:00Z

0

The correct way to retrieve a DataSet using an OleDbDataAdapter is to associate the OleDbCommand to the SelectCommand property of the OleDbDataAdapter. You simply pass the OleDbCommand to the constructor of the OleDbDataAdapter.

Also your code should use a parameterized query for the text of the command, not a string concatenation. Using parameterized query allows you to avoid Sql Injections and the correct parsing of your text, date, decimals variable is done by the framework code.

   string cmdText = "SELECT * FROM RetentionTable WHERE [DateTime] BETWEEN ? AND ?";
   string ConnectionPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=RetentionDB.mdb";
   try
   {
        using(OleDbConnection cn = new OleDbConnection(ConnectionPath))
        using(OleDbCommand OleDbSearch = new OleDbCommand(cmdText, cn))
        using(OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(OleDbSearch))
        {
            OleDbSearch.Parameters.AddWithValue("@p1", getDateTimeFrom(""));
            OleDbSearch.Parameters.AddWithValue("@p2", getDateTimeTo(""));
            DataSet objDataSet = new DataSet();
            cn.Open();
            objDataAdapter.Fill(objDataSet);
            dataOutput.DataSource = objDataSet;
        }
    }
    catch (Exception ex)
    {
         MessageBox.Show(ex.Message.ToString());
         MessageBox.Show(ex.StackTrace.ToString());
    }

The using statement is another point to take note. This will ensure a correct closing and disposing of the connection, command and adapter also in case of exceptions.

answered Jul 17, 2013 at 18:27

Steve

217k22 gold badges242 silver badges296 bronze badges

2 Comments

1337Atreyu Over a year ago

This is then giving me the error showing "The SelectCommand property has not been initialized before calling "Fill".

Steve Over a year ago

That's really strange. As you can see from the docs here msdn.microsoft.com/en-us/library/0wz82t73.aspx passing the OleDbCommand to the constructor of the OleDbDataAdapter assigns the SelectCommand property

Collectives™ on Stack Overflow

Execute SQL command and output result to DataGridView in C#

5 Answers 5

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related