-
Jun 25th, 2013, 09:54 AM
#1
Thread Starter
Hyperactive Member
Last edited by Jo15765; Jun 25th, 2013 at 10:22 AM.
Reason: typo in SQL here not in my source coding
-
Jun 25th, 2013, 07:14 PM
#2
Hyperactive Member
Re: C# to Pull SQL Results based on day
Which part is erroring, the saleDate = dt.DayOfWeek.ToString() or the convert(datetime,saledate,101)?
My guess is the conversion. Why convert it to a datetime if it's already a datetime field? Can't you just use ORDER BY saleDate DESC?
-
Jun 25th, 2013, 07:56 PM
#3
Re: C# to Pull SQL Results based on day
You can't put C# code into a SQL query. That query is just a String so, in that context, 'dt' means nothing. That SQL query is sent to the database and it knows nothing about a local variable in your C# method. You have to get the actual date value in your C# code and pass that to the query, which you would do using a parameter. To learn how to do that, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.
Also, why are you ordering the data on a column that, by your WHERE clause, can only contain one value?
Finally, please tell that your 'saleDate' column is not a text data type. If it contains dates/times then it should be a data type specifically for dates/times. If you have control of the schema then you should change that immediately.
-
Jun 25th, 2013, 08:32 PM
#4
Hyperactive Member
Re: C# to Pull SQL Results based on day
You can't put C# code into a SQL query.
Of course, I didn't even notice it was part of the query and not a parameter or concatenated.
At the very least, concatenate the query string with the dt.DayOfWeek.ToString(). Really follow JMC's advice and use parameters.
-
Jun 26th, 2013, 06:55 AM
#5
Thread Starter
Hyperactive Member
Re: C# to Pull SQL Results based on day
Originally Posted by jmcilhinney
You can't put C# code into a SQL query. That query is just a String so, in that context, 'dt' means nothing. That SQL query is sent to the database and it knows nothing about a local variable in your C# method. You have to get the actual date value in your C# code and pass that to the query, which you would do using a parameter. To learn how to do that, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.
Ah, I didn't think of passing a parameter to it, I was just trying to use it all in the string.
Originally Posted by jmcilhinney
Also, why are you ordering the data on a column that, by your WHERE clause, can only contain one value?
I have it set-up that way as when I just used Order By Desc it wouldn't put the years together. For example, it would group all of Jan 2012 together, then Jan 2013. Was very peculiar result sets returned and this way it actually grouped date wise descending.
Originally Posted by jmcilhinney
Finally, please tell that your 'saleDate' column is not a text data type. If it contains dates/times then it should be a data type specifically for dates/times. If you have control of the schema then you should change that immediately.
Yes, this column is a datetime field. Thank you for checking
-
Jun 26th, 2013, 07:00 AM
#6
Thread Starter
Hyperactive Member
Re: C# to Pull SQL Results based on day
Thanks to the guidance offered here, I now have my code executing as needed. Below is my final result (and you guys may say there are more efficient ways
Code:
DateTime dt;
dt = DateTime.Now;
string queryString = "SELECT * FROM totalSales WHERE employee = @employee AND saleDate = @SD ORDER BY convert(datetime,saledate,101) DESC";
SqlConnection conn - new SqlConnection("Sql Connection Information Is here");
SqlCommand cmd = new SqlCommand(queryString, conn);
if (eID == "m1483")
{
cmd.Parameters.AddWithValue("@employee", "Michael");
cmd.Parameters.AddWithValue("@SD", dt.ToString());
}
//Additional if's to follow
-
Jun 26th, 2013, 07:31 AM
#7
Re: [RESOLVED] C# to Pull SQL Results based on day
Noooooooooo! You said that your 'saleDate' column is type 'datetime' so why are you comparing it to a text value? 'dt' is a DateTime so why are you calling ToString on it? Pass in the DateTime to compare to the datetime.
Also, your ordering still makes no sense at all. Firstly, your converting a datetime column to type datetime. If it's already a datetime then what's the conversion for? Secondly, you're filtering the data by a specific 'saleDate' value so how can ordering that data by that column be beneficial when every row contains the same value?
Finally, do you really want to get records where the 'saleDate' is equal to the current date and time? Would you even have any records with exactly that value? I suspect that what you really want is records with today's date irrespective of time. Is that correct? If so, does the database contain times as well or just dates?
-
Jun 26th, 2013, 07:40 AM
#8
Thread Starter
Hyperactive Member
Re: [RESOLVED] C# to Pull SQL Results based on day
Originally Posted by jmcilhinney
Noooooooooo! You said that your 'saleDate' column is type 'datetime' so why are you comparing it to a text value? 'dt' is a DateTime so why are you calling ToString on it? Pass in the DateTime to compare to the datetime.
You lost me there m8.
Originally Posted by jmcilhinney
Also, your ordering still makes no sense at all. Firstly, your converting a datetime column to type datetime. If it's already a datetime then what's the conversion for? Secondly, you're filtering the data by a specific 'saleDate' value so how can ordering that data by that column be beneficial when every row contains the same value?
Converting/ordering in this instance made it all fall sequential order. I don't remember what the exact issue was, but it was something to do with databases that had records from 2012 and 2013 they wouldn't order descending properly.
Originally Posted by jmcilhinney
Finally, do you really want to get records where the 'saleDate' is equal to the current date and time? Would you even have any records with exactly that value? I suspect that what you really want is records with today's date irrespective of time. Is that correct? If so, does the database contain times as well or just dates?
No, I actually just saw that was posing an issue (trying to match date and time). So I used this
Code:
string DateOnly = DateTime.Now.ToString("MM/dd/yyyy");
And passed that as the parameter to only display records with today's date.
-
Jun 26th, 2013, 07:50 AM
#9
Re: [RESOLVED] C# to Pull SQL Results based on day
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|