Click here to Skip to main content
14,929,864 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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#


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();
Posted
Comments
Basmeh Awad 26-May-13 3:44am
   
pickReport.Text is your Textbox or DateTimePicker???

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:
SQL
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
SQL
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'

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
C#
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.
   
v3
Comments
Zain ul abdeen 26-May-13 2:17am
   
Changed my line to
string str1 = "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) ='" + pickReport.Text + "' ";

but still not working
David_Wimbley 26-May-13 2:25am
   
Can you open sql management studio or some reader and show exact value of what is in your C.Date column. Then show the exact value of pickReport.Text.

If the values are what i mentioned before, it may not be your where clause/date compare issue rather an issue with your join possibly.

But without seeing your database/contents of it im just shooting in the dark here.
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
C#
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
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900