C#OleDbParameter与Access日期时间查询(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.)
解决方案
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:__tmptestData.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).
我有以下查询,从内部访问或C#作为一个OleDbCommand作品:
SELECT Table1.ProductType,SUM (Table1.ProductsSold) FROM表1 WHERE(Table1.DateTime之间的第5/16/2013年#和#5/17/2013年#) GROUP BY Table1.ProductType;
Table1.DateTime是日期/时间数据类型。
现在我想通过的日期为OleDbParameters。
SELECT Table1.ProductType,SUM(Table1.ProductsSold) 从表1 WHERE(#之间开始日期@#和#@#StopDate Table1.DateTime) GROUP BY Table1.ProductType; cmd.Parameters.Add(新OleDbParameter(@起始日期,OleDbType.Date)); cmd.Parameters [@起始日期]值= dateTimePicker1.Value.ToShortDateString()。 cmd.Parameters.Add(新OleDbParameter(@ StopDate,OleDbType.Date)); cmd.Parameters [@ StopDate]值= dateTimePicker2.Value.ToShortDateString()。
我已经搜索并尝试众多事情(VARCHAR和字符串,单引号代替#标签,在命令#标签或者参数等),没有运气。我希望日期在午夜开始(因此ToShortDateString()和Date类型。)
解决方案
您需要摆脱井号(#)中的查询文本分隔符。符如#日期和 字符串所需的文字的SQL查询,但必须的参数的SQL查询被省略。作为参考,这是我工作的测试代码:
使用系统; 使用System.Collections.Generic; 使用System.Linq的; 使用System.Text; 使用System.Data.OleDb; 命名空间oledbTest1 { 类节目 { 静态无效的主要(字串[] args) { 变种康恩=新的OleDbConnection(@供应商= Microsoft.ACE.OLEDB.12.0;数据源= C:__tmptestData.accdb;); conn.Open(); 变种CMD =新的OleDbCommand( 选择Table1.ProductType,SUM(Table1.ProductsSold)AS TotalSold+ FROM表1+ 表示Table1.DateTime BETWEEN @起始日期和@StopDate+ GROUP BY Table1.ProductType, 康恩); cmd.Parameters.AddWithValue(@起始日期,新的datetime(2013,5,16)); cmd.Parameters.AddWithValue(@ StopDate,新的日期时间(2013,5,17)); OleDbDataReader RDR = cmd.ExecuteReader(); INT rowCount时= 0; ,而(rdr.Read()) { rowCount等++; Console.WriteLine(行+ rowCount.ToString()+:); 的for(int i = 0; I< rdr.FieldCount;我++) { 串COLNAME = rdr.GetName(I) Console.WriteLine(+ COLNAME +:+ RDR [COLNAME]的ToString()); } } rdr.Close(); conn.Close(); Console.WriteLine(完成。); Console.ReadKey(); } } }
请注意,我包括不同的名称为参数(更紧密地配合你做了什么),但要记住,对于Access OLEDB参数名称的忽略并参数的必须完全相同的顺序来定义它们出现在命令文本。
修改
如果您想只提取的DateTimePicker的日期部分值然后尝试这样的事:
的DateTime justTheDate = dateTimePicker1.Value.Date; MessageBox.Show(justTheDate.ToString());
当我运行在MessageBox总是显示类似 2013年5月1日00:00:00 (而不是当前的时间)。