rsorrells
Posts: 18
|
| Posted: 07/04/2006, 2:59 PM |
|
I'm trying to add 2 filter criterias to a list, both with the datasource visual query builder.
I was able to do the first one, which was comparing a database field to a session variable as an additional filter.
The 2nd one, I can't figure out. I'd like to include all records if the following statement is true:
CCGetGroupID() = 4
With the visual query builder, it sticks that "CCGetGroupID() = 4" into the SQL statement, and MS SQL doesn't know what to do with this function.
Any suggestions?
Thanks,
Bob
bold edited for clarity
|
 |
 |
WKempees
|
| Posted: 07/04/2006, 4:59 PM |
|
The second one:
In the VQB Where
on the left side of the equation the field in your table that you are
filtering on
on the right side of the equation UserID type Session
That will filter on the currently logged in User.
|
|
|
|
rsorrells
Posts: 18
|
| Posted: 07/04/2006, 5:41 PM |
|
The one that you described was actually my first filter which is working.
The problem that I'm having is with my second filter condition, which is connected to the first by an "OR".
With the 2nd condition, if the user has the security level of 4, as in CCGetGroupID() = 4, then I want to display all records.
I can't figure out how to do this with a parameter or expression, since none of the database's fields are referenced.
With Macromedia Dreamweaver, the "answer" to the CCGetGroupID function, which in my case, is a number between 1 and 4, is substituted into the SQL statement prior to the SQL statement being sent to the SQL server. I'm just trying to figure out how CCS does this.
Thanks,
Bob
|
|
|
|
rsorrells
Posts: 18
|
| Posted: 07/04/2006, 6:59 PM |
|
Fixed !  I quit using the VQB, because I can't figure out how to use it with an expression and a constant, such as CCGetGroupID() = 4, neither of which refers to any of the database fields.
Instead, I moved both filters to BeforeBuildSelect. Here's my code:
Principals.DataSource.Where = "(" & CCGetGroupID() & " = 4 OR [RepID] = " & session("svRepID") & ")"
***
I did notice that I seem to have broken the link between VBQ and the asp source code. My code that starts with
'BuildTableWhere Method @6-4CF03F09
no longer changes when I add and subtract Where parameters. I noticed the background text is white. I must have edited something and VBQ doesn't recognize it anymore.
Is there a way to fix / regenerate this?
Thanks,
Bob
|
|
|
|
rsorrells
Posts: 18
|
| Posted: 07/04/2006, 7:24 PM |
|
fixed that too !
I just deleted my .ASP file and it regenerated it.
Bob
|
|
|
|
WKempees
|
| Posted: 07/05/2006, 3:29 AM |
|
Well done.
Make good progress.
Just as an extra:
Suppose the point where you had the SQL in the VQB working, using the filter
on UserID
and trying to figure out how to filter for GroupID.
You can always go to the BeforeExecuteSelect
Do any logical test you like and as a result of that test
alter the Where.
SO: (in pseudo language)
if (some condition is true){
$Component->ds->Where .= " OR some extra where condition";
}
The $Compnent->ds->Where does initialy contain the original Where clause so
you can replace or expand upon that.
(Code based on PhP not ASP)
|
|
|
|
rsorrells
Posts: 18
|
| Posted: 07/05/2006, 4:20 AM |
|
Thank you. That's a very good point. I could have done that if it was an AND relationship. Since it was an OR relationship, I had to have the ( ) around the two conditions, because there was a built-in 3rd filter due to the built-in search field. I couldn't embed the extra "(" in the VQB.
Thanks,
Bob
|
|
|
|
WKempees
|
| Posted: 07/05/2006, 6:26 AM |
|
Good point, but there is a good solution too.
You can do anything you like using the $Component->ds->Where.
As long as you realize yourself that is holds the current Where clause.
$tempvar = $Component->ds->Where;
if ( some condition is true)
$Component->ds->Where = "(" . $tempvar . " OR some extra where
condition )";
you can use .= (dot equal) to append to the Where or = to replace the
content.
|
|
|
|
|
