Problem with query builder for 'date' type

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 1388 Views - Last Post: 19 June 2015 - 05:50 PM Rate Topic: -----

#1 oljko   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-May 15

Problem with query builder for 'date' type

Posted 19 June 2015 - 04:43 AM

Hi. I'm having a problem with my query builder. I have a datagridview which I want to filter through. I want to allow search by "NAME, SURNAME, DATE". Here is my SQL QUERY

SELECT exDate, exName, exSurname FROM examines
WHERE (exDate = @Param1) AND (exName = @Param2) AND (exSurname = @Param3)



And this is my C# code where i 'call' the query

rivate void button1_Click(object sender, EventArgs e)
        {
            try
            {
                this.examinesTableAdapter.FillBy(this.dbSpinDataSet.examines, dateTimePicker1.Value, textBox2.Text, textBox3.Text);
            }

            catch (SystemException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
        }



in VS I get 2 ERRORS saying:

"...Project1.dbSpinDataSetTableAdapters.examinesTableAdapter, string, string, string)' has some invalid arguments."

"...cannot convert from 'System.Windows.Forms.DateTimePicker' to 'string'."


I understand where is the error but I don't know how to solve it. My query was wrote in 'query builder' in projects DataSet. I went to FILTER columns for exDate, exName and exSurname and i wrote ? (question mark). The query builder automaticaly transfered it into above mentioned form "= @Param1", "= @Param2" and "= @Param3".

I know I can't treate the DateTime as string but don't know what to write into FILTER column of my query builder for my code to work. Thank you very much.

Is This A Good Question/Topic? 0
  • +

Replies To: Problem with query builder for 'date' type

#2 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6553
  • View blog
  • Posts: 26,566
  • Joined: 12-December 12

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 06:02 AM

As a quick test, what happens with:
dateTimePicker1.Value.ToString()

Was This Post Helpful? 1
  • +
  • -

#3 oljko   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-May 15

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 06:13 AM

The errors are gonne but when I run my app and enter the date in search (through my dateTimePicker1) and click the 'FILTER' button - I don't get nothing.

For example, I have 3 examines in my database on date 01/01/2015 and I should get 3 rows selected when i click the FILTER button (with 01.01.2015. selected) but I get, as I said - nothing, empty datagridview
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6553
  • View blog
  • Posts: 26,566
  • Joined: 12-December 12

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 06:17 AM

What about the Name and Surname? These would need to be supplied, and match, as well.
Was This Post Helpful? 0
  • +
  • -

#5 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6220
  • View blog
  • Posts: 21,469
  • Joined: 05-May 12

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 06:31 AM

Hack solution: Try dateTimePicker1.Value.ToString("o") or dateTimePicker1.Value.ToString("yyyyMMdd")

I don't use any of the Visual Studio wizards to create table adapters for me, so this may sound naive: Is there a way to tell VS that the first parameter is a DATETIME, and not a NVARCHAR ?
Was This Post Helpful? 0
  • +
  • -

#6 oljko   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-May 15

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 06:37 AM

I've changed the SQL code into this one:

WHERE (exDate LIKE @Param1 + '%') AND (exName LIKE @Param2 + '%') AND (exSurname LIKE @Param3 + '%')




when I try to search through name or surname it gives me the wright results. It doesn't need to fill all of the parameters (date, name, surname). I can search only with one parameter.

Now, as I said, when I enter name or surname I get wright results, but when I enter the date I get this message:

"The data types date and varchar are incompatible in the add operator."

I've added the 'ToString()' on my dateTimePicker1.Value
Was This Post Helpful? 0
  • +
  • -

#7 oljko   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-May 15

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 06:43 AM

View PostSkydiver, on 19 June 2015 - 06:31 AM, said:

Hack solution: Try dateTimePicker1.Value.ToString("o") or dateTimePicker1.Value.ToString("yyyyMMdd")

I don't use any of the Visual Studio wizards to create table adapters for me, so this may sound naive: Is there a way to tell VS that the first parameter is a DATETIME, and not a NVARCHAR ?


I've tryed you solution and I get message: "Failed to convert parameter value from a String to a DateTime".

I don't get why does it recognizes my variable as DateTime when it is only Date...

And the last, answer to your question - you can't tell VS that first parameter is DATE because you import tables and I should asume that it should recognize that my variable is DATE. There is nowhere to enter it's value.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6553
  • View blog
  • Posts: 26,566
  • Joined: 12-December 12

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 06:49 AM

Quote

"The data types date and varchar are incompatible in the add operator."

I suspect that this error relates to exDate LIKE @Param1 + '%'. You shouldn't apply LIKE and '%' to a date value. Remove LIKE from this parameter.
Was This Post Helpful? 0
  • +
  • -

#9 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6220
  • View blog
  • Posts: 21,469
  • Joined: 05-May 12

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 06:55 AM

View Postoljko, on 19 June 2015 - 09:43 AM, said:

I don't get why does it recognizes my variable as DateTime when it is only Date...

Because the DatePicker.Value is a DateTime.
Was This Post Helpful? 1
  • +
  • -

#10 oljko   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-May 15

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 06:56 AM

View Postandrewsw, on 19 June 2015 - 06:49 AM, said:

Quote

"The data types date and varchar are incompatible in the add operator."

I suspect that this error relates to exDate LIKE @Param1 + '%'. You shouldn't apply LIKE and '%' to a date value. Remove LIKE from this parameter.


Still nothing ... "Failed to convert parameter value from String to DateTime" ... as I first said ... Problem is what to write into query builders FILTER COLUMN for my exDate ... It doesn't work when I write ?, it doesn't work when I write LIKE ? + '%' ...
Was This Post Helpful? 0
  • +
  • -

#11 oljko   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-May 15

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 07:02 AM

View PostSkydiver, on 19 June 2015 - 06:55 AM, said:

View Postoljko, on 19 June 2015 - 09:43 AM, said:

I don't get why does it recognizes my variable as DateTime when it is only Date...

Because the DatePicker.Value is a DateTime.


Can it be that problem lies in the difference between DATETIME and DATE, because my variable is set to DATE in SQL SERVER database ...
Was This Post Helpful? 0
  • +
  • -

#12 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5103
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 07:04 AM

Part of the problem is this shotgun debugging approach. Go back to your original approach when you were using strings and you were passing the DateTime as a string.

Then set up a trace on your SQL server. Capture the incoming query, and actually look at it to see what values are being passed.

For one thing, is there time information being passed to your query? DateTimes aren't "fuzzy", if you have a stored date of "1/1/2015" that means "Jan 1 2015 00:00:00", and "Jan 1 2015 00:00:01" isn't going to match. You might have to truncate the time (or call .Date on the DateTime before you convert to string), or use it to define a range of times (from 00:00:00 to 23:59:59, for example).

Either way, the best way to solve these issues is to understand what exactly is being passed to SQL. Everyone assumes that their code is doing what they think, sending what they think. Make sure it's sending the right stuff.
Was This Post Helpful? 1
  • +
  • -

#13 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 449
  • View blog
  • Posts: 2,186
  • Joined: 07-April 08

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 07:10 AM

The problem with the Parameters is the use of the AND. Basically your SQL Query is saying that in order to return rows exDate has to be LIKE @Param1 (Refer to andrews response on using like with a date) <AND> exName LIKE @Param2 <AND> exSurname LIKE @Param3.

Basically at this point you must supply all 3 in order to get returned rows. If you want to be able to supply only one then use the OR keyword.

With that the other option is to do something like this:
WHERE (exDate = @Param1 OR ISNULL(@Param1,'') = '') AND (exName = @Param2 OR ISNULL(@Param2,'') = '')...


This post has been edited by rgfirefly24: 19 June 2015 - 08:36 AM

Was This Post Helpful? 0
  • +
  • -

#14 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5103
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 07:52 AM

Quote

If you want to be able to supply only one then use the OR keyword


That's not exactly true. Or at least, it won't produce the behavior most people would expect.

Typically, in a "query builder", you would expect each additional field of information you supply to further restrict the search results to more and more specific.

However, if you simply OR all the parameters together, you still only need to match one value to get returned. Example table:

FN   LN
----|-----
JOHN SMITH
JANE SMITH
JANE DOE


If you give FN = "JANE", you get Jane Smith and Jane Doe back. If you give LN = "SMITH", you get John Smith and Jane Smith. If you give both (FN = "JANE" LN = "SMITH"), you'd expect a single result, Jane Smith. However, you'd get all the rows, because each row only has to match a single condition.

It actually takes some effort to dynamically add to your WHERE clause. Because the logic should probably still be AND, it just needs to only include those conditions when values are present.
Was This Post Helpful? 1
  • +
  • -

#15 oljko   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-May 15

Re: Problem with query builder for 'date' type

Posted 19 June 2015 - 07:58 AM

There is no problem with "AND" in query builder. My app filters with only one parameter without any problem, but thank you all for your time and effort. I will try to find another way (directly through SQL lines in my code).
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2