0

I have searched around the internet but I couldn't find a solution. I want to do this:

if (Convert.ToString(form1.searchBox.SelectedItem) == "Date")
{
    val = (sender as TextBox).Text;
    sqlCmd = new SqlCommand(
        "SELECT * FROM ["TableName"] WHERE ["Date"] LIKE '" + 
         filterFunc().Trim() + "%'", connection);
}

So I have a Searchbox (Textbox) where one can type something in and it is shown in the database. That works fine for integers and strings but somehow he got problems with the date value above (it's typed as "date" in my SQL database). When I want to grab values I get errors. I tried to Convert and Cast it within the SQL string and in C# but nothing helps!

Mikko Viitala
  • 8,344
  • 4
  • 37
  • 62
MKX2015
  • 155
  • 1
  • 2
  • 13
  • What kind of error ? – DeadlyJesus Jan 30 '15 at 10:50
  • System.InvalidCastException – MKX2015 Jan 30 '15 at 10:54
  • What is the value of `filterFunc()`? Also I note that you're not using `val`. For date queries, I'd suggest using `BETWEEN`, and you should *definitely* be parameterizing your queries... – Jon Skeet Jan 30 '15 at 10:55
  • 1
    Is that a direct copy & paste from your code? If so, you don't want the quotes around `["TableName"]` and `["Date"]`, just `[TableName]` and `[Date]`. The bigger picture though, is that you shouldn't be constructing queries by concatenating pieces of SQL. – paul Jan 30 '15 at 10:58
  • Read http://stackoverflow.com/questions/1629050/sql-server-datetime-like-select – Paul Zahra Jan 30 '15 at 10:58
  • Do you really need `LIKE` with dates? Why not use `month`, `year` and other date-time functions? – Dmitry Bychenko Jan 30 '15 at 10:59
  • Yes I know the code is not that clear but it works with all other columns and elements in there. I need to use date. So he just has a problem to convert it. How can I convert date values so that C# recognizes SQL values and vise versa? He does not even recognize it when I write "=" and not "Like" – MKX2015 Jan 30 '15 at 11:02
  • Ok when I put "=" in and not "Like" I get a SQL Exception: "Conversion failed when converting date and/or time from character string." – MKX2015 Jan 30 '15 at 11:09

1 Answers1

0

The problem is that you cannot do a LIKE with dates. You could use a parameterized query for clarity, and convert your date datatype like this :

using (SqlCommand command = new SqlCommand(
  "SELECT * FROM ["+TableName+"] WHERE convert(varchar(10),[Date],112) like @Date", connection))
{
    command.Parameters.Add(new SqlParameter("Date", filterFunc().Trim() + "%"));
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        string val1 = reader.GetString(0);
        string val2 = reader.GetString(1);
        Console.WriteLine("Val 1= {0}, Val2 = {1}", val1, val2);
    }
}

Note also, that for performance reason, you should not do a LIKE at all, but use BETWEEN for your date lookup.

Nicolas M
  • 81
  • 5
  • Ok I try it with "=" now. When I use the convert method I don't get an Exception anymore but he doesn't find values. – MKX2015 Jan 30 '15 at 11:17
  • Ok I found out. "Like" is ok. I just had to write "convert(varchar(10),["Date"],112)" and now it works fine – MKX2015 Jan 30 '15 at 12:04
  • Writing in SQL convert(varchar(10),["Date"],112) = 'somedate' is bad practice because it requires SQL to convert the variable to string first instead of doing a range lookup. – Ako Feb 20 '15 at 08:14