Access Database Handling in C#

[Arif15]

Has struggled a bit with a SQL query against the Access database in the last few days, I desperately need some help to get me on. In my access database I have a date format dd.MM.yyyy HH:mm and I would like to have a query like this:

string query = "SELECT * FROM GoodsSupplied WHERE Timesection > " + dateTimePicker1.Value.ToString() + " AND ´Timesection´ < " + dateTimePicker2.Value.ToString() + " ";

Here's the code:

private void button1_Click(object sender, EventArgs e)
        {

            string query = "SELECT * FROM GoodsSupplied WHERE Timesection > " + dateTimePicker1.Value.ToString() + " AND ´Timesection´ < " + dateTimePicker2.Value.ToString() + " ";
            OleDbConnection Con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Access\GoodsSupplied.mdb;Jet OLEDB:Database Password=PW;");

            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, Con);

            DataSet dSet = new DataSet();
            dAdapter.Fill(dSet);

            DataGridView grid = new DataGridView();
            BindingSource bSource = new BindingSource();

            bSource.DataSource = dSet;

            exportToExcel(dSet, @"C:\GoodsSupplied.xls");

        }

The result should be stored in a data set and on to the GridView. The error occurs when the data is in the dataset, with the message:

Syntax error (missing operator) in query expression 'Timesection > 15.11.2008 09:58:45 AND ´Timesection´ < 15.11.2010 09:58:45'.

[$tatic]

Have you tried to remove the apostrophes around the time of your query, assume that it is the name of a column in your table and it will probably be in the clear. Otherwise there is no date in the form of surface access for the ultimate in my experience, but try to format the date in datetimepicker so it blends in with what you have stored in the database. This is however just a guess and I am not sure 100% if it will work.

[Arif15]

Why can't we use apostrophes for a date? What's wrong with that? And if what you are telling is correct then what else should be used instead of apostrophe? It has been a long time that I have been working on SQL query and so I forgot some of its part and so I need your help. And if I do that I have the below message:

Syntax error (comma) in query expression 'Timesection > System.Windows.Forms.DateTimePicker, Value: 15.11.2007 08:12:48 AND ´Timesection´ < System.Windows.Forms.DateTimePicker, Value: 15.11.2010 08:12:48'.

[$tatic]

Yes, you should not use apostrophes. In such cases you must cut out the sentence that you think require to have apostrophes. A question on the side of the line starts but has the same theme. Is it better to use "Between" in between the two dates? See that the string is shorter but has otherwise no practical significance? Between takes the start date as a starting point while > yesterday the day after the start but let's say that it used >= and <=.

[Arif15]

I consulted this with someone else and it led me to another error. I know there should not be apostrophes, but the # around the date in OLEDB (not the same as with SQL).

string query = "SELECT * FROM " + (string)comboBox1.SelectedItem + " WHERE DATO > #" + dateTimePicker1.Value.ToString() + "# AND DATO < #" + dateTimePicker2.Value.ToString() +"# ";
            OleDbConnection Con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Access\" + (string)comboBox1.SelectedItem + ".mdb;" + sb.ToString() + "");
            try
            {

                OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, Con);
                OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
                DataSet dSet = new DataSet();
                dAdapter.Fill(dSet);

                BindingSource bSource = new BindingSource();

                bSource.DataSource = dSet;
                exportToExcel(dSet, @"C:\ " + comboBox1.SelectedValue.ToString() + ".xls");
            }
            catch (Exception ex)
            {
                textBox1.Text = ex.ToString();
                //MessageBox.Show(ex.ToString(), "ERROR!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                
            }

Now I get an error that looks like this:

System.Data.OleDb.OleDbException: Syntax error in date in query expression 'DATO > #17.11.2010 19:29:15# AND DATO < #17.11.2010 19:29:15#'.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Save_N_Delete.Form1.button1_Click(Object sender, EventArgs e) in C:\Data\Save_Delete\Save_N_Delete\Save_N_Delete\Form1.cs:line 44

So I do not know if I need to take advantage of OleDbParam and its formatting or whatever.

[Steyn]

I guess the error is that your DateTime is not formatted correctly. It will most likely be as follows: #2010-11-19 14:25:53#. If you use OleDbParameter, you can most likely think about such things. It is also best practice to use parameters to prevent SQL injection, among other things, etc.