C＃OleDbParameter与Access日期时间查询 [英] C# OleDbParameter with Access DateTime query

问题描述

我有以下查询，从内部访问或C＃作为一个OleDbCommand作品：

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是日期/时间数据类型。

Table1.DateTime is Date/Time data type.

现在我想通过的日期为OleDbParameters。

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();

我已经搜索并尝试众多事情（VARCHAR和字符串，单引号代替＃标签，在命令＃标签或者参数等），没有运气。我希望日期在午夜开始（因此ToShortDateString（）和Date类型。）

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

推荐答案

您需要摆脱井号（＃）中的查询文本分隔符。符如＃日期和 字符串所需的文字的SQL查询，但必须的参数的SQL查询被省略。作为参考，这是我工作的测试代码：

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();
        }
    }
}

请注意，我包括不同的名称为参数（更紧密地配合你做了什么），但要记住，对于Access OLEDB参数名称的忽略并参数的必须完全相同的顺序来定义它们出现在命令文本。

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.

如果您想只提取的DateTimePicker的日期部分值然后尝试这样的事：

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());

当我运行在MessageBox总是显示类似 2013年5月1日00:00:00 （而不是当前的时间）。

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

这篇关于C＃OleDbParameter与Access日期时间查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！