C# OleDbParameter with Access DateTime query

问题:

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.)

回答1:

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).