• Registered: 2015-11-13
  • Posts: 652

Topic: Possible Bug in Sql Query Action

I may have come upon a bug in SQL Query Action. The following Sql works fine under 2.4 Beta, but not under 2.4 Release nor 2.5 Beta.

Select strftime('%m/%d/%Y',Due), Task, id
From Tasks where Due = {DateTimePicker1} Order By Due

It does not bring in the rows equal to the DateTimePicker. It has something to do with the equal condition. If I change the Sql Query to <= condition, it will bring in the less than rows, but not the equal rows.

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Possible Bug in Sql Query Action

Hello,


Please attach your project, I will test it.


Thanks.

Dmitry.
  • Registered: 2015-11-13
  • Posts: 652

Re: Possible Bug in Sql Query Action

I put together a test project for you.

Post's attachments

Attachment icon Sql Query Test.zip 580.74 kb, 653 downloads since 2016-03-21 

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Possible Bug in Sql Query Action

ehwagner wrote:

I put together a test project for you.

Check out this query

SELECT strftime('%m/%d/%Y',Due), Task, id
FROM Tasks
WHERE date(Due) = date({DateTimePicker1}) Order By Due
Dmitry.
  • Registered: 2015-11-13
  • Posts: 652

Re: Possible Bug in Sql Query Action

Thanks Dimitry. That worked.  Just curious. Any explanation as to why the way I had it worked in previous versions of MVD?

  • Registered: 2015-11-13
  • Posts: 652

Re: Possible Bug in Sql Query Action

Dimitry,
Your solution to this query stopped working in 2.6. It works fine under 2.5.

  • Registered: 2015-11-13
  • Posts: 652

Re: Possible Bug in Sql Query Action

Dimitry, a little more info on this. The datetimepicker in the SELECT is hidden on the form. In other words the VISIBLE checkbox is unchecked. However, when I make it visible (checkbox checked), the query works. So when the datatimepicker is visible on the form, the query works. When it is hidden, it does not work. It brings back nothing. Hope this helps in solving this issue.

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Possible Bug in Sql Query Action

Hi EHWagner,
I've also noticed this sort of behaviour in odd circumstances (though not in sqlquery).
I know it's not a solution, as such, but as a work-around (and apologies if you've already tried it and it hasn't worked for you) is to make the field visible, set the TABSTOP checkbox to unchecked, reduce the visible size of the field and then hide it behind another element on the form.  It's always done the trick for me.
Derek.

  • Registered: 2015-11-13
  • Posts: 652

Re: Possible Bug in Sql Query Action

Thanks Derek,
This workaround does work and I'll use that approach for the time being. Hopefully Dimitry will fix this in a short order.

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Possible Bug in Sql Query Action

ehwagner wrote:

Dimitry, a little more info on this. The datetimepicker in the SELECT is hidden on the form. In other words the VISIBLE checkbox is unchecked. However, when I make it visible (checkbox checked), the query works. So when the datatimepicker is visible on the form, the query works. When it is hidden, it does not work. It brings back nothing. Hope this helps in solving this issue.

I tested it, but can't reproduce it, please attach your project, where I can see this bug.
Thanks.

Dmitry.
  • Registered: 2015-11-13
  • Posts: 652

Re: Possible Bug in Sql Query Action

Just following up on this. In an effort to duplicate this situation using the previous example in this post, I discovered some more info on this situation. I'm not sure if there really is a problem. I actually think that the 2.6 version of MVD may have fixed something that shouldn't have worked before. Here is the situation that causes it not to work. In the object inspector for the DateTimePicker field used in the query, if you have the "DefaultChecked" property unchecked and you also have the "Visible" checkbox unchecked, then the query will not work. If either one of them are checked or both are checked, then the query works fine. Like I said, I'm not really sure there is a problem. I'll let Dimitry comment on that. All I had to do is go into all my projects that had this situation and check the "DefaultChecked" checkbox and everything was back to normal.

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Possible Bug in Sql Query Action

ehwagner wrote:

Just following up on this. In an effort to duplicate this situation using the previous example in this post, I discovered some more info on this situation. I'm not sure if there really is a problem. I actually think that the 2.6 version of MVD may have fixed something that shouldn't have worked before. Here is the situation that causes it not to work. In the object inspector for the DateTimePicker field used in the query, if you have the "DefaultChecked" property unchecked and you also have the "Visible" checkbox unchecked, then the query will not work. If either one of them are checked or both are checked, then the query works fine. Like I said, I'm not really sure there is a problem. I'll let Dimitry comment on that. All I had to do is go into all my projects that had this situation and check the "DefaultChecked" checkbox and everything was back to normal.

When your DateTimePicker is Unchecked, it's mean that component have empty value. What do you mean when query don't work? Any error messages? or something else?

Dmitry.