在选定日期未显示数据库中的记录 [英] Not showing record from database in selected date

问题描述

数据库中Date列的数据类型是日期，它不显示c＃中的日期记录。

pickReport是DateTimePicker，前端采用xaml设计，编码为c＃

Data type of Date column in Database is date and it is not showing records of date in c#.
pickReport is DateTimePicker, Front end designed in xaml and coding in c#

SqlConnection con = new SqlConnection(@"server=ZAIN\SQLEXPRESS;database=Project;user id=sa;pwd=abdeen");
            string str1 = "Select Rate,Crates,Weight,Hens,Discount,PreviousAmount,Payment,AmountLeft from ChickenPurchaseOrders C inner join Accounts A on C.Fid=A.Fid where C.Date='" + pickReport.Text + "' ";
            con.Open();
            SqlDataAdapter da= new SqlDataAdapter(str1,con);
            DataTable dt = new DataTable();
            da.Fill(dt);

            gridChickenPurchase.ItemsSource = dt.DefaultView;
            con.Close();

推荐答案

我假设你的pickReport.Text值只等于日期正确吗？ （2013年5月26日）。我的答案是基于限制信息/我可以从你的代码片段收集的内容提供的。



问题可能在于你试图比较 DateTime 到日期。



示例：05/26 / 2013 05：00：000不等于05/26/2013 ...知道为什么吗？因为DateTime比较默认的后者05/26/2013的时间部分是上午12点。所以你问的是05/26/2013 05：00：000是否等于05/26/2013 12：00：000它没有。



所以为了解决这个问题，你需要在你的SQL查询中将日期时间字段转换为日期。



示例：

Im assuming your pickReport.Text value would equal just a date correct? (05/26/2013). My answer is provided based on the limit information/what i can gather from your code snippet.

The problem probably lies in the fact that your trying to compare a DateTime to a Date.

Example: 05/26/2013 05:00:000 does not equal 05/26/2013...know why? because the Time portion of the latter 05/26/2013 by default for a DateTime comparison is 12 am. So you are asking if 05/26/2013 05:00:000 is equal to 05/26/2013 12:00:000 which it does not.

So to remedy this you would need to cast your date time field in your sql query as Date.

Example:

Select 
     Rate,
     Crates,
     Weight,
     Hens,
     Discount,
     PreviousAmount,
     Payment,
     AmountLeft 
from 
     ChickenPurchaseOrders C inner join Accounts A on C.Fid=A.Fid 
where 
     C.Date='05/26/2013'

然后会变成

Would then become

Select
     Rate,
     Crates,
     Weight,
     Hens,
     Discount,
     PreviousAmount,
     Payment,
     AmountLeft
from
     ChickenPurchaseOrders C inner join Accounts A on C.Fid=A.Fid
where
     CAST(C.Date AS Date) ='05/26/2013'

另外请注意，您使用的内联sql会让您打开sql注射。最好是参数化查询。



C＃Sql参数： http://www.dotnetperls.com/sqlparameter [ ^ ]



但是如果你必须做内联sql，至少要使用

Also another note, you are using inline sql which will leave you open to sql injections. It would be best if you parameterize your query.

C# Sql Params: http://www.dotnetperls.com/sqlparameter[^]

But if you must do inline sql, for the love of maintenance at least use

string.Format("SELECT * FROM Table Where Filed = '{0}'", fieldName.Text);

你会在复杂的查询中为自己寻找不匹配的单引号。

You will save yourself an aneurism looking for mis-matched single quotes in a complex query.

首先你应该看到哪个查询显示结果...在该查询字符串上添加一个断点当调试器出现在quey上时。通过点击小镜头在sql server编辑器中获取该查询并尝试多种日期格式，如20-05-2013​​，05/20/2013​​，2013年5月20日等。并检查哪个查询显示结果..任何人将显示结果然后更改您的日期表格到工作的那个

First you should see which query is displaying result...add a breakpoint on that query string when debugger comes on quey.by clicking on small lens take that query in sql server editor and try multiple date format like "20-05-2013","05/20/2013","20-May-2013"etc..and check which query is displaying result ..any one will display result then change your date format to the one which works

string str1 = "Select Rate,Crates,Weight,Hens,Discount,PreviousAmount,Payment,AmountLeft from ChickenPurchaseOrders C inner join Accounts A on C.Fid=A.Fid where C.Date='" + Strings.Format(pickReport.Text, "MM-dd-yyyy") + "' ";

Hello

Zain ul abdeen

，

您可以使用参数（使用下面的代码可以完成这项工作）。

,
You can use Parameter (use below code you can get this done).

SqlConnection con = new SqlConnection(@"server=ZAIN\SQLEXPRESS;database=Project;user id=sa;pwd=abdeen");
            string str1 = "Select Rate,Crates,Weight,Hens,Discount,PreviousAmount,Payment,AmountLeft from ChickenPurchaseOrders C inner join Accounts A on C.Fid=A.Fid where C.Date=@SelectedDate ";
            con.Open();
            SqlDataAdapter da= new SqlDataAdapter(str1,con);
	da.SelectCommand.Parameters.AddWithValue("@SelectedDate",DateTime.Parse(pickReport.Text));
            DataTable dt = new DataTable();
            da.Fill(dt);
            gridChickenPurchase.ItemsSource = dt.DefaultView;
            con.Close();

谢谢

Thanks

这篇关于在选定日期未显示数据库中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！