带有访问日期时间查询的 C# OleDbParameter [英] C# OleDbParameter with Access DateTime query

问题描述

我有以下查询，可在 Access 内部或 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	estData.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-05-01 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！