使用C＃到MS Access数据库选择查询中的日期格式 [英] Date Format in Select Query With C# To MS Access Database

问题描述

亲爱的所有人，

我正在用MS Access数据库编写C＃。当我用条件作为日期编写选择查询时。

访问不知道日期格式的数据库当我选择当前2013年的日期。例如今天是2013年9月28日，我输入日期14/09/2013或15/09/2013或25/09/2013超过13.在数据库中，它知道日期像那个13/09/2014或13/09/2015或13/09/2025。所以我怎样才能确切地将日期作为我的参数。这是我的写作代码。

  string  vFDate，vTDate，vStr; 
 vFDate = Convert.ToDateTime（txtFDate.Text）.ToShortDateString（）; 
 vTDate = Convert.ToDateTime（txtTDate.Text）.ToShortDateString（）; 
 
  //   vFDate = 01/09/2013或12/09/2013 and vTDate = 28/09/2013没关系，它正常运行。 
  //  但是 
  //   vFDate = 18/09/2013和vTDate = 28/09/2013不行。所以日期已更改。例如 
  //   vFDate = 13/09/2018 and vTDate = 13/09/2028  
 
 vStr =   Selec *来自tbl其中R_Date格式之间（＃ + vFDate +  ＃，'dd / mm / yyyy'）和格式（ ＃ + vTDate +  ＃，'dd / mm / yyyy'）; 
 OleDbCommand cmd =  new  OleDbCommand（vStr，OleDBComm）; 
 OleDBConn.Open（）; 
 cmd.ExecuteNonQuery（）; 
 OleDBConn.Close（）; 

解决方案

不要这样做！你为什么要取一个字符串，将它转换为DateTime，然后再返回一个字符串，添加到另一个字符串，传递到SQL，在那里它必须转换回DateTime来比较它？使用参数化查询（无论如何应该避免SQL注入攻击）

  string  vFDate，vTDate， VSTR; 
 DateTime vFDate = Convert.ToDateTime（txtFDate.Text）; 
 DateTime vTDate = Convert.ToDateTime（txtTDate.Text）; 
 vStr =   SELECT * FROM tbl WHERE R_Date BETWEEN @FD AND @TD; 
 OleDbCommand cmd =  new  OleDbCommand（vStr，OleDBComm）; 
 cmd.Parameters.AddWithValue（  @ FD，vFDate）; 
 cmd.Parameters.AddWithValue（  @ TD，vTDate）; 
 OleDBConn.Open（）; 
 cmd.ExecuteNonQuery（）; 

很有可能，你的问题会在同一时间消失！



但如果你必须使用日期条目的文本框（我会使用DateTimePicker），然后你应该使用DateTime.TryParse而不是Convert.ToDateTime。

感谢您的帮助。但我可以解决自己。通过使用以下内容。

  string  vFDate，vTDate; 
 
 vFDate = txtFDate.Value.ToString（  yyyy / MM / dd）; 
 vTDate = txtTDate.Value.ToString（  yyyy / MM / dd）; 

和我的选择查询一样

 选择 * 从 tbl 其中​​  date   ＃  + vFDate + ＃和＃  + vTDate + ＃   

谢谢大家。







更正格式问题。

[/编辑] 的

Dear All,
I am writing C# with MS Access database.When I write select query with criteria as date.
Access database that isn't know date format when I select date over current year 2013.Example today is 28/09/2013,I enter date 14/09/2013 or 15/09/2013 or 25/09/2013 over 13.In the database,It know the date like that 13/09/2014 or 13/09/2015 or 13/09/2025.So how can I try to get exactly date as my parameter.Here is my writing code.

string vFDate, vTDate, vStr;
vFDate = Convert.ToDateTime(txtFDate.Text).ToShortDateString();
vTDate = Convert.ToDateTime(txtTDate.Text).ToShortDateString();

//vFDate = 01/09/2013 or 12/09/2013 and vTDate = 28/09/2013 is ok,it's working.
//But
//vFDate = 18/09/2013 and vTDate = 28/09/2013 is not ok.So Date is changed.Like that
//vFDate = 13/09/2018 and vTDate = 13/09/2028

vStr = "Selec * From tbl Where R_Date Between  Format(#" + vFDate + "#, 'dd/mm/yyyy') And   Format(#" + vTDate + "#, 'dd/mm/yyyy')";
OleDbCommand cmd = new OleDbCommand(vStr, OleDBComm);
OleDBConn.Open();
cmd.ExecuteNonQuery();
OleDBConn.Close();

解决方案

Don't do it like that! Why are you taking a string, converting it to a DateTime, then back to a string again, to add to another string, to pass to SQL where it has to be converted back to a DateTime to compare it? Use parametrized queries instead (You should be anyway to avoid SQL injection attacks)

string vFDate, vTDate, vStr;
DateTime vFDate = Convert.ToDateTime(txtFDate.Text);
DateTime vTDate = Convert.ToDateTime(txtTDate.Text);
vStr = "SELECT * FROM tbl WHERE R_Date BETWEEN @FD AND @TD";
OleDbCommand cmd = new OleDbCommand(vStr, OleDBComm);
cmd.Parameters.AddWithValue ("@FD", vFDate); 
cmd.Parameters.AddWithValue ("@TD", vTDate);
OleDBConn.Open();
cmd.ExecuteNonQuery();

Chances are, you problem will disappear at the same time!

But if you must use a text box for date entry (and I'd use a DateTimePicker instead), then you should use DateTime.TryParse instead of Convert.ToDateTime.

Thanks you for your help. But I can Solve myself. By using like that following.

string vFDate, vTDate;

vFDate = txtFDate.Value.ToString("yyyy/MM/dd");
vTDate = txtTDate.Value.ToString("yyyy/MM/dd");

And my select query like that

Select * From tbl where date between #" + vFDate + "# And #" + vTDate + "#"

Thanks everyone.


[Edit member="Tadit"]
Corrected formatting issues.
[/Edit]

这篇关于使用C＃到MS Access数据库选择查询中的日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！