smi
Posts: 11
|
| Posted: 08/08/2004, 6:10 PM |
|
Searched everything with no luck on this very specific issue.
When entering an Integer-typed SQL Parameter for a 'SQL' Datasource on a form, a null value is not permitted for the SQL value and no wildcards (% , *) are permitted.
For some reason, CCS 2.3.1 will allow you to create a SQL Parameter and leave the "Default Value" field blank if the Datasource Type is 'Table'. It will not let you leave the "Default Value" field blank if the Datasource Type is 'SQL', as I need it to be because I require a CONCAT() SQL statement.
The problem is that if I put a value (let's say 0) in that default field (let's say for a "priority" search parameter) to satisfy this wierd behavior, then only items with Priority 0 will show up. No good!
I have also tried using a Text-typed SQL Parameter for a 'SQL' Datasource, in which case I can have a null value in that Default Value field, but the SQL then breaks.
I also went into the Bookstore example and checked out their search function... they use a blank value their "Category" drop-down search parameter, but their Datasource on that search field is 'Table'. I could not find an example of a wildcard or null default value when Datasource type = SQL.
Besides the fact that it seems bizarro to me that the data type of the parameter does makes a difference in SQL datasources but not in Table datasources, I need a fix that will make my search pages work!
Any assistance is appreciated.
_________________
Security Methods Inc.
http://www.securitymethods.com |
 |
 |
peterr
Posts: 5971
|
| Posted: 08/08/2004, 9:36 PM |
|
Hi,
Quote :For some reason, CCS 2.3.1 will allow you to create a SQL Parameter and leave the "Default Value" field blank if the Datasource Type is 'Table'.
This is not "for some reason"  , but it's because the SQL is created at run-time, therefore if the value is blank then the corresponding part of the SQL/WHERE can be removed. If all parameters are blank then the WHERE statement may not even be created at all.
Quote :It will not let you leave the "Default Value" field blank if the Datasource Type is 'SQL'
In the situation the SQL is created at design-time (manually entered by you) and all necessary values must be present, because the program cannot modify such SQL later. For example a statement "SELECT * FROM table WHERE field=" would be invaild. Wildcards don't work with numeric fields either.
CONCAT may be an exception here, however I'm not sure how or why you are using it in this case. If you really need to use it then you could modify the WHERE statement at run-time, as shown here:
http://docs.codecharge.com/studio/html/ProgrammingTechn...HEREClause.html
http://docs.codecharge.com/studio/html/ProgrammingTechn...hereClause.html
It may be a separate question about how to return search results when a numeric parameter is blank. The answer could be found here:
http://forums.codecharge.com/posts.php?post_id=49615
http://forums.codecharge.com/posts.php?post_id=27344
http://forums.codecharge.com/posts.php?post_id=27344
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
smi
Posts: 11
|
| Posted: 08/12/2004, 3:00 PM |
|
I'd say that run-time constraints qualify as 'some reason'. 
I ended up just using DLookups+hiddenfields+IF statements and some custom code.
Thanks for the clarifications, they are helpful.
_________________
Security Methods Inc.
http://www.securitymethods.com |
|
|
|
|
