October 2, 2008 at 7:03 am
I have the following ingredients for a query:
CompanyId
AuthrolId
UserId
I don't know how to take care of the following query:
If the AuthrolId = 3 then the query must compare the companyId with the companyId stored in a session (I'm a ASP.Net developer).
If the AuthrolId = 1 then the query must compare the UserId with the UserId stored in a session
If the AutrolId <> 3 and <> 1 then no comparison is needed.
October 2, 2008 at 7:08 am
at a guess
Declare @UserId int
Declare @CompanyId int
Select CompanyId, AuthrolID, UserID
from YourTable
where (Aurhrolid = 3 and CompanyId = @CompanyId) or (Aurhrolid = 1 and UserId = @UserId)
October 2, 2008 at 7:19 am
This makes a little sense. Where do I put the statement in my Query? I assume PrINT does't mean 'print' as in MS Word?
October 2, 2008 at 8:10 am
a_bennen (10/2/2008)
This makes a little sense. Where do I put the statement in my Query?
What Steve gave is your query. Are you using stored procs (good practice) or ad-hoc SQL statements run from your ASP?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2008 at 8:22 am
I use SQL statements, because I'm not that familiar with stored procedures
October 2, 2008 at 8:46 am
You really should learn stored procedures. They make code more modular (T-SQL in the database and C#/VB in the client app) and they can prevent certain security problems that you can get with ad-hoc code in ASP.
Can you post the ASP.net code that you're using to fetch the data, and we can help you put the query in there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2008 at 9:05 am
Thanx! Here's my query sofar. This works as far as I can see now.
SELECT tblProjects.ProjectId, tblProjects.ProjectName, tblProjects.ProjectDescription, tblProjects.Remarks, tblProjects.CompanyId, tblProjects.ContractNummer, tblProjects.DataSupplierId,
tblUsers.UserId
FROM tblProjects LEFT OUTER JOIN
tblUsers ON tblProjects.CompanyId = tblUsers.CompanyId
WHERE (tblUsers.AuthRolId = 3) AND (tblUsers.CompanyId = @CompanyId)
October 2, 2008 at 9:11 am
Great
Do you need help getting it into ASP? Do you want help in turning it into a stored proc? Or is everything sorted now?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2008 at 9:27 am
GilaMonster (10/2/2008)
GreatDo you need help getting it into ASP? Do you want help in turning it into a stored proc? Or is everything sorted now?
I would like to have some help with getting it into ASP. Turning it into a stored proc is a bit too much at this point.
The first part works: If a user has AuthrolId 3 then only items of his company are shown. At this point I do not have the second and last part:
2nd: if a user has AuthrolId 1 then only items where he/she is user are shown. I have a tblUsers with a UserId and I have a tblTasks with a UserId.
last: if a user does not have AuthrolId 1 or 3 then he may see all items
October 2, 2008 at 9:39 am
Change the where clause to what Steve posted. That should sort the query out.
If you post your existing ASP code (just the data access portion) then we can help you there.
Turning it into a stored proc is not hard at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2008 at 11:47 pm
Ok. that would be nice. Here's my code:
"
DeleteCommand="DELETE FROM [tblProjects] WHERE [ProjectId] = @ProjectId"
InsertCommand="INSERT INTO [tblProjects] ([ProjectName], [ProjectDescription], [Remarks], [CompanyId], [ContractNummer], [DataSupplierId]) VALUES (@ProjectName, @ProjectDescription, @Remarks, @CompanyId, @ContractNummer, @DataSupplierId)"
SelectCommand="SELECT tblProjects.ProjectId, tblProjects.ProjectName, tblProjects.ProjectDescription, tblProjects.Remarks, tblProjects.CompanyId, tblProjects.ContractNummer, tblProjects.DataSupplierId, tblUsers.UserId FROM tblProjects LEFT OUTER JOIN tblTasks ON tblProjects.ProjectId = tblTasks.ProjectId LEFT OUTER JOIN tblUsers ON tblProjects.CompanyId = tblUsers.CompanyId WHERE (tblUsers.AuthRolId = 3) AND (tblUsers.CompanyId = @CompanyId)"
UpdateCommand="UPDATE [tblProjects] SET [ProjectName] = @ProjectName, [ProjectDescription] = @ProjectDescription, [Remarks] = @Remarks, [CompanyId] = @CompanyId, [ContractNummer] = @ContractNummer, [DataSupplierId] = @DataSupplierId WHERE [ProjectId] = @ProjectId">
<asp:SessionParameter DefaultValue="0" Name="CompanyId"
SessionField="CompanyId" Type="Int32" />
October 3, 2008 at 1:35 am
I think part of that got cut off. (The forum software doesn't like triangular brackets)
From what I can see, all you need to do is change the definition of the select statement. Change the where clause to what Steve posted. That should do what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply