Jan van Dalen
|
| Posted: 07/01/2011, 3:18 PM |
|
Hi Everyone,
After switching from Table to SQL (Visual Query Builder) mode with no modification,
my select statement (on a grid) no longer works.
The reason is that my SQL Parameters (from the search panel) are now implicit
rather than optional. In the table mode, if the URL parameter is missing,
the condition is not considered. In SQL mode, for example I have a URL /
Intiger parameter that now has a default of 0 (zero) and obviously is trying
to filter based on that value.
Is there a way to make the SQL mode SQL Parameters act like the Table mode?
Thanks
BTW, I"m using ASP / MSSQL 2005
|
|
|
 |
jjrjr2
Posts: 131
|
| Posted: 07/04/2011, 2:18 PM |
|
Jan
This sounds like a bug..
Have U asked Yes Support???
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com
Real Web Development At: http://RealWebDevelopment.us |
 |
|
tfertil
Posts: 43
|
| Posted: 07/05/2011, 2:49 PM |
|
Hi Jan,
Maybe you can modify the WHERE statement of your SELECT to "omit" a NULL value, like this
SELECT <some fields> FROM mytable
WHERE ISNULL(myparam,my_int_field) = my_int_field AND <other_where_conditions>
This way, you can specify a default value of NULL for myparam, and the WHERE statement will act like WHERE (my_int_field = my_int_field), which is allway true, effectively ommiting the null parameter.
You can apply this technique to any number and type of fields...
Regards,
|
|
|
|
vandalen
Posts: 58
|
| Posted: 07/06/2011, 9:21 AM |
|
HI Everyone,
Thanks for the ideas. I went into a different direction but here is what support sent me:
Hello Jan,
the SQL data source type is different then Table type.
In this case all WHERe parameters are always added to sql query. If search parameters are not passed then default values are used.
The only way to build the WHERE dynamically is to do it with custom code in form's Before Execute Select event.
Please remember that if custom SQL is used then entire query is stored in SQL property, WHERE property is empty. So custom code in Before Execute Select event would look like
FormName.DataSource.SQL = FormName.DataSource.SQL & " WHERE id=" & CCgetParam("id_param_name", "default value")
regards,
Helen
|
|
|
|
|
