Click here to Skip to main content
14,699,840 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello. I want to create a search for my entry dates and i wrote this line of code.

private void History()
        {
            int i = 0;
            dataGridView1.Rows.Clear();
            cn.Open();
            cm = new SqlCommand("select * from vwStockIn where cast(sdate as date) between @sdate and @sdate1 and status like 'Bitti'", cn); 
            cm.Parameters.AddWithValue("@sdate", dt1.Value.ToShortDateString());
            cm.Parameters.AddWithValue("@sdate1", dt2.Value.ToShortDateString());

            dr = cm.ExecuteReader();
            while (dr.Read())
            {
                i++;
                dataGridView1.Rows.Add(i, dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString());
            }
            dr.Close();
            cn.Close();
        }


Now this is very wonky. Sometimes it shows me the time i want and sometimes it just shows everything, sometimes it gives an error because i put a day number higher than 12. Maybe its because i think of it as a europaen time thing because just when i click 13 and try to search it gives me the error following .

System.Data.SqlClient.SqlException: 'Conversion failed when converting date and/or time from character string.'



Even if i've gotten rid of this error there is still a huge problem.Its unreliable.It never shows me the thing i want but shows everything else.Is there a way that i can get consistent and accurate dates? I mean when i click 01:09:2020 - 10:09:2020 and search it i have a data in sql which is in 01:09:2020 but it doesnt show me that one but it shows me the one at 18:09:2020 like what the f.. is going on?

What I have tried:

I thought that maybe the problem was that i tried writing it with parameters and i tried writing it like this

cm = new SqlCommand("select * from vwStockIn where cast(sdate as date) between '" + dt1.Value.ToShortDateString() + "' and '" + dt2.Value.ToShortDateString() + "' and status like 'Done'", cn);


Also i've tried writing it without having cast(sdate as date) line and replaced it with just sdate but that made no difference aswell
Posted
Updated 9-Sep-20 21:03pm
Comments
mekenix 10-Sep-20 1:53am
   
I think the problem is that microsoft sql expects dd,MM,yyyy value while visual studio c# gives MM,dd,yyyy value but i am not sure. Even if that was the problem i have no idea how to fix it so i need help :D

1 solution

Never do this:
cm = new SqlCommand("select * from vwStockIn where cast(sdate as date) between '" + dt1.Value.ToShortDateString() + "' and '" + dt2.Value.ToShortDateString() + "' and status like 'Done'", cn);

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Don't do this:
cm.Parameters.AddWithValue("@sdate", dt1.Value.ToShortDateString());
cm.Parameters.AddWithValue("@sdate1", dt2.Value.ToShortDateString());
THat just introduces a layer of extra complexity as you convert to a string based on the your computer settings, and SQL converts back to a DATE based on its settings.
Instead, pass the DateTime value directly:
cm.Parameters.AddWithValue("@sdate", dt1.Value);
cm.Parameters.AddWithValue("@sdate1", dt2.Value);
And no conversions are required.
Unless of course your DB column is storing date information in a VARCHAR or NVARCHAR column instead of DATE, DATETIME, or DATETIME2 in which case you need to change that or your comparisons will not work correctly anyway.

Then, look at the rest of your SQL:
cm = new SqlCommand("select * from vwStockIn where cast(sdate as date) between @sdate and @sdate1 and status like 'Bitti'", cn);

LIKE is an operator that needs wildcards: without them it's identical to "=" but slower.
You could try:
...LIKE '%Bitti%
which will find anything with "Bitti" in it.

Then, this worries me:
...WHERE CAST(sdateAS DATE)...
which implies that you are storing date info in a VARCHAR or NVARCHAR column, and that's a bad idea, because when you try to use it you have to convert it to a date to do anything - if you don't then a string based comparison is used, and that gives a result solely based on the first pair of different characters. Which means that sorting and comparing does not use date order: 01-12-2020 is before 31-01-2000. And ... it relies on your data never getting "bad data", which is unlikely, and which includes every computer inserting date info using the same settings ...
If you are, change your DB
   
Comments
mekenix 10-Sep-20 2:10am
   
No, My date info is in a datetime type column. I thought somehow sql did not think that its a date so tried to convert it just to be safe but that made no difference.

what if i did it like this

cm = new SqlCommand("select * from vwStockIn where sdate between @sdate and @sdate1 and status like 'Bitti'", cn);
cm.Parameters.AddWithValue("@sdate" ,DateTime.Parse(dt1.Value.ToShortDateString()));
cm.Parameters.AddWithValue("@sdate1",DateTime.Parse(dt2.Value.ToShortDateString()));


what would be the problem if i wrote it like this? Outside of unnecessary use of toshortdatestring.


A follow up question, lets think of a hypothetical scenario. I am searching for a record which is in 10:09:2020. I search it by selecting the dates 09:09:2020 and 10:09:2020. But my programme doesnt show my record. Or if i pick 10:09:2020 and 10:09:2020 which it still shows nothing. But it works when i pick 10:09:2020 and 11:09:2020 How can i fix this issue?
OriginalGriff 10-Sep-20 3:18am
   
Depends: I was assuming that dt1 and dt2 were DateTimePickers - in which case the Value property is already a DateTime and can be passed directly to SQL as a parameter. If it isn't, then you need to use DateTime.TryParse to check and convert it at the top of your method and only continue if the user typed correctly.

And LIKE *still* needs wildcards!
mekenix 10-Sep-20 3:22am
   
Thank you for your help sir. :)

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




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