Not showing record from database in selected date

Question

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 25-May-13 19:34pm

Zain ul abdeen

Add a Solution

Comments

Basmeh Awad 26-May-13 3:44am

pickReport.Text is your Textbox or DateTimePicker???

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

David_Wimbley · Answer 1 · 2013-05-25T19:52:00

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.

Basmeh Awad · Answer 2 · 2013-05-25T21:56:00

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") + "' ";

sivakumarganga · Answer 3 · 2013-05-25T22:00:00

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

Not showing record from database in selected date

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0