C# OleDbParameter with Access DateTime query

Question

I have the following query that works from inside Access or from C# as an OleDbCommand:

SELECT Table1.ProductType, Sum(Table1.ProductsSold)
FROM Table1
WHERE (Table1.DateTime Between #5/16/2013# And #5/17/2013#)
GROUP BY Table1.ProductType;

Table1.DateTime is Date/Time data type.

Now I want to pass the dates as OleDbParameters.

SELECT Table1.ProductType, Sum(Table1.ProductsSold)
FROM Table1
WHERE (Table1.DateTime Between #@StartDate# And #@StopDate#)
GROUP BY Table1.ProductType;

cmd.Parameters.Add(new OleDbParameter("@StartDate", OleDbType.Date));
cmd.Parameters["@StartDate"].Value = dateTimePicker1.Value.ToShortDateString();
cmd.Parameters.Add(new OleDbParameter("@StopDate", OleDbType.Date));
cmd.Parameters["@StopDate"].Value = dateTimePicker2.Value.ToShortDateString();

I have searched and tried numerous things (VarChar and strings, single quotes instead of hashtags, hashtags in command or in parameter, etc.) without luck. I want the dates to start at midnight (thus the ToShortDateString() and Date types.)

Gord Thompson · Accepted Answer · 2013-05-18 19:42:26Z

4

You need to get rid of the hash mark (#) delimiters in the query text. Delimiters like # for dates and ' for strings are required for literal SQL queries, but must be omitted in parameterized SQL queries. For reference, here is my working test code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace oledbTest1
{
    class Program
    {
        static void Main(string[] args)
        {
            var conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\__tmp\testData.accdb;");
            conn.Open();
            var cmd = new OleDbCommand(
                    "SELECT Table1.ProductType, SUM(Table1.ProductsSold) AS TotalSold " +
                    "FROM Table1 " +
                    "WHERE Table1.DateTime BETWEEN @StartDate AND @StopDate " +
                    "GROUP BY Table1.ProductType", 
                    conn);
            cmd.Parameters.AddWithValue("@StartDate", new DateTime(2013, 5, 16));
            cmd.Parameters.AddWithValue("@StopDate", new DateTime(2013, 5, 17));
            OleDbDataReader rdr = cmd.ExecuteReader();
            int rowCount = 0;
            while (rdr.Read())
            {
                rowCount++;
                Console.WriteLine("Row " + rowCount.ToString() + ":");
                for (int i = 0; i < rdr.FieldCount; i++)
                {
                    string colName = rdr.GetName(i);
                    Console.WriteLine("  " + colName + ": " + rdr[colName].ToString());
                }
            }
            rdr.Close();
            conn.Close();

            Console.WriteLine("Done.");
            Console.ReadKey();
        }
    }
}

Note that I included distinct names for the parameters (to more closely match what you did), but remember that for Access OLEDB the parameter names are ignored and the parameters must be defined in exactly the same order that they appear in the command text.

Edit

If you want to extract just the Date part of the DateTimePicker value then try something like this:

DateTime justTheDate = dateTimePicker1.Value.Date;
MessageBox.Show(justTheDate.ToString());

When I run that the MessageBox always displays something like 2013-05-01 00:00:00 (and not the current time).

edited May 18, 2013 at 19:42

answered May 18, 2013 at 9:51

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

3 Comments

Derek Johnson Over a year ago

My question might not have highlighted the problem. My original parameters were similar to cmd.Parameters.AddWithValue("@StartDate", dtpDailyStartDate.Value); However, the DateTimePicker's Value property returned the correct Date, but the CURRENT time.

Derek Johnson Over a year ago

This led me to try this: cmd.Parameters.AddWithValue("@StartDate", Convert.ToDateTime(dateTimePicker1.Value.ToShortDateString())); This approach did not return any records.

Derek Johnson Over a year ago

The original parameter will work if I set the DateTimePicker's Format property to Short. I had the DateTimePicker's Format set to Custom and the CustomeFormat property set to dddd, MMM dd, yyyy. The Property window states this controls how the value is displayed. It appears it also affects how the Value property is returned. I would really like the weekday displayed but get the value with Midnight as the time.

Collectives™ on Stack Overflow

C# OleDbParameter with Access DateTime query

1 Answer 1

Edit

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related