CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> .NET

 SOLVED - Tricky Customized WHERE condition

Print topic Send  topic

Author Message
kescott


Posts: 49
Posted: 09/01/2008, 7:03 AM

I was wondering if there is someway to write an exception to a sql where condition.

For example, Bob is in NY, Amy is in VA, Mike is in CA, and Tom is an Administrator on the eastcoast of the US. Bob is only able to view data in NY, Amy is only able to view data in VA, and Mike is only able to view data in CA. However, Tom is an Administrator and is able to see NY, VA, and CA data.

I have created a custom session that looks up the location of the logged in user when the user logs in. The logged in user location session is called the "theLoggedInUserLocation". This prevents the logged in user from viewing information that doesn't belong to his/hers location.

So my SQL Statement is the following:

SELECT * FROM Employees WHERE Location = {theLoggedInUserLocation}

However, I want the Tom to be able to view all user data since he is the Administrator for all locations.

Any help with this would be great. Thnaks in advanced.
_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
Tuong Do
Posted: 09/01/2008, 8:29 PM

Hi Kescott

You only need this statement

If Session("UserLogin") = "Tom" Then
Session("theLoggedInUserLocation") = Nothing
End If

And also do not set the default value for the "theLoggedInUserLocation"

NOTE:

CCS will remove the Where condition if the Session is empty and there is no
default value


"kescott" <kescott@forum.codecharge> wrote in message
news:848bbf645c1b58@news.codecharge.com...
>I was wondering if there is someway to write an exception to a sql where
> condition.
>
> For example, Bob is in NY, Amy is in VA, Mike is in CA, and Tom is an
> Administrator on the eastcoast of the US. Bob is only able to view data
> in NY,
> Amy is only able to view data in VA, and Mike is only able to view data in
> CA.
> However, Tom is an Administrator and is able to see NY, VA, and CA data.
>
> I have created a custom session that looks up the location of the logged
> in
> user when the user logs in. The logged in user location session is called
> the
> "theLoggedInUserLocation". This prevents the logged in user from viewing
> information that doesn't belong to his/hers location.
>
> So my SQL Statement is the following:
>
> SELECT * FROM Employees WHERE Location = {theLoggedInUserLocation}
>
> However, I want the Tom to be able to view all user data since he is the
> Administrator for all locations.
>
> Any help with this would be great. Thnaks in advanced.
> _________________
> B.S. of Computer Science
> Class of 2005
> Norfolk State University
> Norfolk, VA
> http://www.nsu.edu
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>

kescott


Posts: 49
Posted: 09/02/2008, 7:04 AM

Tuong

Would the statement
You only need this statement

If Session("UserLogin") = "Tom" Then
Session("theLoggedInUserLocation") = Nothing
End If


Go into the Before Show Row Event

_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
E43509

Posts: 283
Posted: 09/02/2008, 8:09 AM

I'd just add this to the sql statement. (assuming you are using CC security) to eliminate hardcoding of a username.

SELECT * FROM Employees WHERE Location = {theLoggedInUserLocation}
OR Session.UserLevel = <Administrator>

Not sure what # you set up for administrator but substitute in that value.
use the QBE to build the where clause as I'm not sure what language you use.
View profile  Send private message
kescott


Posts: 49
Posted: 09/02/2008, 8:50 AM

Thanks for replying.

I gave it a shot but I got a datetime type conversion error.

I'm still looking into the issue.
_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
Tuong Do
Posted: 09/02/2008, 4:28 PM

Hi kescott,

That statement is in the before page initialize

The best way is in where you defined that session variable

eg in your login page (after login has been sucessfully)
If Session("UserLogin") = "Tom" Then
Session("theLoggedInUserLocation") = Nothing
ElseIf Session("UserLogin") = "John" then
Session("theLoggedInUserLocation") = "NY"
End If




"kescott" <kescott@forum.codecharge> wrote in message
news:848bd47de21e04@news.codecharge.com...
> Tuong
>
> Would the statement
> You only need this statement
>
> If Session("UserLogin") = "Tom" Then
> Session("theLoggedInUserLocation") = Nothing
> End If
>
>
> Go into the Before Show Row Event
>
> _________________
> B.S. of Computer Science
> Class of 2005
> Norfolk State University
> Norfolk, VA
> http://www.nsu.edu
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>

wkempees


Posts: 1679
Posted: 09/05/2008, 4:07 PM

At the risk that this has been solved. O:)
Using the internet example database, and a database query tool:
  
Select * from store_products  
order by category_id  
would display all 26 or so rows sorted by category_id

  
Select * from store_products  
where category_id = 1  
Would only display the 15 or so records that have the value 1 in category_id

  
Select * from store_products  
where ( 9=2 or category_id = 1)  
Still displays the 15 or so from the previous code block
  
Select * from store_products  
where ( 9=9 or category_id = 1)  
Now displays all 26 or so total rows.

What is the significance of 9=2: suppose your Tom has a GroupID of 9 indicating he is an administrator a simple CCGetGroupID() would get the GroupID of the current logged in user.
If the logged in user is Tom the 9=CCGetGroupID() would be TRUE, if it is another user than it would be FALSE.
As it is an OR condition we are using here, one of the condition should evaluate to TRUE for the SQL to give result.

How to do this in VQB: using your own table as described in your post
Prerequisites:
You know the set value for Administrator in Security Setting (assume 9);
You have the session var for the users location working and it is 'theLoggedInUserLocation'

Open your page containing the grid, select the grid and go to it's properties, select the DataSource[...], you are now in the VisualQuery
Assuming you already have entered the fields and table and VQB is in TABLE mode (not SQL mode)
Start of with selecting the WHERE.
Lower right pane should now show the current where condition, delete all if any.
Press the '+' sign to add the first condition
Condition Type: Parameter
Name: <select the field containing the state>
Type: <se4lect the correct type>
Condition: equals (=)
Parameter: theLoggedInUserLocation
Type: Session
Condition: OR
press OK

Press '+' again to add another condition
condition Type: Expression
Expression: CCGetGroupID() = 9
Press OK

Now select (control + click or shift click) both conditions and click the '()' button in VQB
it will show the '( ) ' around the conditions you entered.
Press OK and optionaly press the Show SQL icon to see the generated SQL.

Press OK to leave the VQB, and publish test your page.


Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)

if you liked this info PAYPAL me: http://donate.consultair.eu
View profile  Send private message
kescott


Posts: 49
Posted: 09/07/2008, 1:54 PM

Thanks for the reply to the post. Unfortunately, I get the following error message:

CCGetGroupID is not a recognized built-in function name.

System.Data.OleDb.OleDbException:  'CCGetGroupID' is not a recognized built-in function name.

I'm using C#.NET, so I'll check though the .NET 2.0 Framework maybe.
_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
GabrielAkoh

Posts: 9
Posted: 09/08/2008, 4:33 AM

I enjoyed your tips. However, I have a need to pass "UserLoggedOn" as a query field. Better still, I want to tied the emp_login to currentuser right in the query where clause and not as function.

Thanks in advance.
View profile  Send private message
wkempees


Posts: 1679
Posted: 09/08/2008, 8:05 AM

Quote GabrielAkoh:
tied the emp_login to currentuser right in the query where clause
Press the '+' sign to add the first condition or doubleclick one to alter:
Condition Type: Parameter
Name: emp_id
Type: Integer
Condition: equals (=)
Parameter: UserID
Type: Session

Preregs:
Page should be restricted, Login is implemented using the default session variables (out-of-the-box)


Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)

if you liked this info PAYPAL me: http://donate.consultair.eu
View profile  Send private message
wkempees


Posts: 1679
Posted: 09/08/2008, 8:09 AM

@Kescott
I am closing in on solving your missing CCGetGroupID() function.
apparently .NET C# has different function or way of retreival.

Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)

if you liked this info PAYPAL me: http://donate.consultair.eu
View profile  Send private message
kescott


Posts: 49
Posted: 09/08/2008, 4:40 PM

:-)

Unfortunately, no luck.
_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
wkempees


Posts: 1679
Posted: 09/08/2008, 7:03 PM

Solved it, although in a unusual way.
At least you made me do something I did not want to do, but had to do for a while now.
As a PhP/MySQL XAMPP LAMPP addict, I did not want to go the ASP .NET road.
Inmotion triggered my imagination a while back, and you actualy made me do it!
Installed IIS on a XP Virtual Box and did some tampering with .Net C# and . Net InMotion.
The suspense must be killing you, hold on......

I did find it quite annoying that basic function like CCGetGroupID() is not available.
However, after doing some testing and experimenting I came up with the following nice little trick.

Instead of trying to read a session variable in the VQB Expression, as described earlier
Quote :
Press '+' again to add another condition
condition Type: Expression
Expression: CCGetGroupID() = 9
which without the function being available should evolve to:
Quote :
Press '+' again to add another condition
condition Type: Expression
Expression: Session["GroupID"] = 9
But gave error: expect ')'
That would probably be because of the " messing up the SQL.

However, I did the following trick and it works ok:
  
Press the '+' sign to add the first condition  
Condition Type: Parameter  
Name: <select the field containing the state>  
Type: <se4lect the correct type>  
Condition: equals (=)  
Parameter: theLoggedInUserLocation  
Type: Session  
Condition: OR  
press OK  
  
Press '+' again to add another condition  
Condition Type: Parameter  
Name: 9                        JUST THE NUMBER 9 IF THAT IS YOUR ADMIN GROUP  
Type: Integer  
Condition: equals (=)  
Parameter: GroupID  
Type: Session  
Condition: OR  

Like this


To all listeners/viewers, as I am now entering the .Net world (as some of you wanted me to do) please give me any correct way of obtaining the GroupID in a VQB expression or any pointer as to CCGetGroupID()........ missing.

.Net/InMotion looks good.

Off to bed....

Walter
(Class of 1958)
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)

if you liked this info PAYPAL me: http://donate.consultair.eu
View profile  Send private message
GabrielAkoh

Posts: 9
Posted: 09/08/2008, 10:56 PM


Thank you.

I solved it. But I had to first create the userid session on the login page. In my query, I tied the emp_id to parameter userid and selected session as the Type.

Next is to change user password. I am creating an editable grid, then to use before build update to set emp_password to the new one after passing the validation rules for both oldpassword and verfynewpassword. Hope I am on the right track ? Any better clue.

Regards.
View profile  Send private message
kescott


Posts: 49
Posted: 09/09/2008, 5:09 AM

Walter, you are the best. The solution works and the steps to perform the task are very simple. I will definitely make sure that CodeCharge Support Team gets this information so that maybe they will update their documentation on the method. Maybe they will even use my example and your solution to better service their many clients.

I will definitely donate to your paypal account.
@PAYPAL: http://donate.consultair.eu :-D :-D :-D :-D :-D :-D :-D :-D :-D :-D :-D
_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
wkempees


Posts: 1679
Posted: 09/09/2008, 5:14 AM

Makes me happy, you're happy!

You might change title to [Solved] or [Tip].

BTW, have you seen the change in my signature, you caused that! (.Net)

Walter


_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)

if you liked this info PAYPAL me: http://donate.consultair.eu
View profile  Send private message
kescott


Posts: 49
Posted: 09/09/2008, 6:19 AM

@wkempees

I'm changing this [SOLVED - Tricky Customized WHERE condition]

O:)

I suggested to the CodeCharge Support Team to maybe update their documentation in the future to help others like myself to resolve issues like this. Hopefully, they will use my example, your solution, and pay you a lot of MONEY. :-D :-D :-D :-D :-D :-D :-D :-D :-D
_________________
B.S. of Computer Science
Class of 2005
Norfolk State University
Norfolk, VA
http://www.nsu.edu
View profile  Send private message
wkempees


Posts: 1679
Posted: 09/09/2008, 6:24 AM

O:)
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)

if you liked this info PAYPAL me: http://donate.consultair.eu
View profile  Send private message
wkempees


Posts: 1679
Posted: 09/09/2008, 2:07 PM

Quote GabrielAkoh:
I solved it. But I had to first create the userid session on the login page. In my query, I tied the emp_id to parameter userid and selected session as the Type.

The Login creates a number of standard Session variables one of them is UserID
You should not have had to create a userid session variable.
Just be sure you type UserID with the capitals as typed here or check your project settings->Security->Advanced.

Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)

if you liked this info PAYPAL me: http://donate.consultair.eu
View profile  Send private message
GabrielAkoh

Posts: 9
Posted: 09/10/2008, 3:56 AM

Walter, you're right I used lower case. However, I am done with that.

To change user password. I created an editable grid, then to use before_build_update to set emp_password to the new one after passing the validation rules for both oldpassword and verfynewpassword. This is still a problem, Could you assit me?
View profile  Send private message
wkempees


Posts: 1679
Posted: 09/10/2008, 4:54 PM

Quote GabrielAkoh:
Walter, you're right I used lower case. However, I am done with that.

To change user password. I created an editable grid, then to use before_build_update to set emp_password to the new one after passing the validation rules for both oldpassword and verfynewpassword. This is still a problem, Could you assit me?

Topic is asked and answered, both kescott and you stated that, so consider it closed.
As to the last part (To change user pas....), I suggest you search forum and if not satisfied open new topic.

Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)

if you liked this info PAYPAL me: http://donate.consultair.eu
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.