Greetings
I have an ASP application to enter reservations. There are multiple user
security settings that require some users to have a restricted list of
client in a drop list on a form.
I constructed the following function:
CREATE FUNCTION [estudf_UserClie ntsList] (@pUserId int)
RETURNS @UserClientQ table
(
-- user information
UserId int,
FirstName varchar(50),
LastName varchar(50),
UserName varchar(25),
-- client information
ClientId int,
ClientName varchar(40),
UsageTypeId int,
-- event update rights
noSaveReq int, -- =1 disallow save of Request status events
for the client
noSaveConflict int, -- =1 disallow save of Conflict status
events for the client
noSaveApprov int, -- =1 disallow save of Approved status
events for the client
noSaveDenied int, -- =1 disallow save of Denied status events
for the client
-- debug trace
Stage int
)
AS
BEGIN
----------------------------------------------------------------------------
------------------------------
-- Marc Walgren - 07/14/2004
-- This routine will determine proper client list for user based on user
-- settings and user/contact/client relationships
----------------------------------------------------------------------------
------------------------------
-- local variables
declare @UserAdmin int
declare @UserFirstName varchar(50)
declare @UserLastName varchar(50)
declare @UserName varchar(25)
declare @UserActive int
declare @UserReadOnly int
declare @FacilityAdminC ount int
declare @UserClientCoun t int
-- Users with Admin rights get all clients
select @UserAdmin = Admin,
@UserFirstName = FirstName,
@UserLastName = LastName,
@UserName = UserName,
@UserActive = Active,
@UserReadOnly = ReadOnly
from Users where UserId = @pUserId
-- clear return q to be sure
delete @UserClientQ
if @UserAdmin = 1
begin
-- give all clients
insert @UserClientQ ( UserId, FirstName, LastName, UserName,
ClientId, ClientName, UsageTypeId, Stage, noSaveReq, noSaveConflict,
noSaveApprov, noSaveDenied )
Select @pUserId, @UserFirstName, @UserLastName, @UserName,
c.ClientId, c.ClientName, c.UsageTypeId, 1, 0, 0, 0, 0
from Client c
end
else
begin
-- check for Building Administrator
select @FacilityAdminC ount = count(UFLID) from UserFacilityLin k
where UserId = @pUserId
if @FacilityAdminC ount > 0
begin
-- give all clients
insert @UserClientQ ( UserId, FirstName, LastName, UserName,
ClientId, ClientName, UsageTypeId, Stage, noSaveReq, noSaveConflict,
noSaveApprov, noSaveDenied )
Select @pUserId, @UserFirstName, @UserLastName,
@UserName, c.ClientId, c.ClientName, c.UsageTypeId, 2, 0, 0, 0, 0
from Client c
end
else
begin
-- count the user's client
SELECT @UserClientCoun t = count(dbo.Users .UserId)
FROM dbo.Users
INNER JOIN dbo.Contacts ON dbo.Contacts.Us erId =
dbo.Users.UserI d
INNER JOIN dbo.ClientConta ctLink ON dbo.Contacts.Co ntactID =
dbo.ClientConta ctLink.ContactI D
INNER JOIN dbo.Client ON dbo.Client.Clie ntID =
dbo.ClientConta ctLink.ClientID
WHERE dbo.Users.UserI d = @pUserId
-- users with associated client get just their clients
if @UserClientCoun t > 0
begin
-- give user's clients
insert @UserClientQ ( UserId, FirstName, LastName,
UserName, ClientId, ClientName, UsageTypeId, Stage, noSaveReq,
noSaveConflict, noSaveApprov, noSaveDenied )
SELECT dbo.Users.UserI d,
dbo.Users.First Name, dbo.Users.LastN ame, dbo.Users.UserN ame,
dbo.Client.Clie ntId,
dbo.Client.Clie ntName, dbo.Client.Usag eTypeId , 3, 0, 0, 1,1
FROM dbo.Users
INNER JOIN dbo.Contacts ON
dbo.Contacts.Us erId = dbo.Users.UserI d
INNER JOIN dbo.ClientConta ctLink ON
dbo.Contacts.Co ntactID = dbo.ClientConta ctLink.ContactI D
INNER JOIN dbo.Client ON dbo.Client.Clie ntID
= dbo.ClientConta ctLink.ClientID
WHERE dbo.Users.UserI d = @pUserId
end
else
begin
-- Active and non-readonly users get full client
list
if @UserActive = 1 and @UserReadOnly = 0
begin
-- give all clients
insert @UserClientQ ( UserId, FirstName,
LastName, UserName, ClientId, ClientName, UsageTypeId, Stage, noSaveReq,
noSaveConflict, noSaveApprov, noSaveDenied )
Select @pUserId, @UserFirstName,
@UserLastName, @UserName, c.ClientId, c.ClientName, c.UsageTypeId, 4, 0, 0,
1, 1
from Client c
end
end
end
end
return
END
My administrative user is ID=1. I perform the following code to build the
drop list
strSQL = "SELECT ClientID, ClientName, UsageTypeId, Stage FROM
estudf_UserClie ntsList(" strSQL = strSQL & cstr(iUserId)
strSQL = strSQL & ") ORDER BY ClientName"
response.write "<select size=1 name=txtClient> "
response.write "<option value='' >--Choose--</option>"
pbIsClientListE xists = false
set rs=Server.Creat eObject("ADODB. RecordSet")
rs.open strSQL,CONNECTI ONSTRING
while rs.EOF=false
pbIsClientListE xists = true
stage = rs.fields("Stag e")
If Trim(rs.fields( "ClientID"))=Tr im(checkvalue)T hen
selected=" selected"
pClientsUsageTy peID = rs.fields("Usag eTypeId")
Else
selected=""
End If
response.write "<option value='"
response.write Trim(rs.fields( "ClientID") )
response.write "' " & selected & ">"
response.write rs.fields("Clie ntName")
response.write "</option>"
wend
I have used Profiler to be sure the parameter to the function is proper and
have never found it to be incorrect.
When the client list is not build properly, only one client name appears and
it is clientid=1. If I change the select sql in the function to exclude
clientid=1 then the name of the next lowest client id is displayed when the
list is improperly built.
Any ideas to resolve the inconsistency would be gratefully received.
Marc
I have an ASP application to enter reservations. There are multiple user
security settings that require some users to have a restricted list of
client in a drop list on a form.
I constructed the following function:
CREATE FUNCTION [estudf_UserClie ntsList] (@pUserId int)
RETURNS @UserClientQ table
(
-- user information
UserId int,
FirstName varchar(50),
LastName varchar(50),
UserName varchar(25),
-- client information
ClientId int,
ClientName varchar(40),
UsageTypeId int,
-- event update rights
noSaveReq int, -- =1 disallow save of Request status events
for the client
noSaveConflict int, -- =1 disallow save of Conflict status
events for the client
noSaveApprov int, -- =1 disallow save of Approved status
events for the client
noSaveDenied int, -- =1 disallow save of Denied status events
for the client
-- debug trace
Stage int
)
AS
BEGIN
----------------------------------------------------------------------------
------------------------------
-- Marc Walgren - 07/14/2004
-- This routine will determine proper client list for user based on user
-- settings and user/contact/client relationships
----------------------------------------------------------------------------
------------------------------
-- local variables
declare @UserAdmin int
declare @UserFirstName varchar(50)
declare @UserLastName varchar(50)
declare @UserName varchar(25)
declare @UserActive int
declare @UserReadOnly int
declare @FacilityAdminC ount int
declare @UserClientCoun t int
-- Users with Admin rights get all clients
select @UserAdmin = Admin,
@UserFirstName = FirstName,
@UserLastName = LastName,
@UserName = UserName,
@UserActive = Active,
@UserReadOnly = ReadOnly
from Users where UserId = @pUserId
-- clear return q to be sure
delete @UserClientQ
if @UserAdmin = 1
begin
-- give all clients
insert @UserClientQ ( UserId, FirstName, LastName, UserName,
ClientId, ClientName, UsageTypeId, Stage, noSaveReq, noSaveConflict,
noSaveApprov, noSaveDenied )
Select @pUserId, @UserFirstName, @UserLastName, @UserName,
c.ClientId, c.ClientName, c.UsageTypeId, 1, 0, 0, 0, 0
from Client c
end
else
begin
-- check for Building Administrator
select @FacilityAdminC ount = count(UFLID) from UserFacilityLin k
where UserId = @pUserId
if @FacilityAdminC ount > 0
begin
-- give all clients
insert @UserClientQ ( UserId, FirstName, LastName, UserName,
ClientId, ClientName, UsageTypeId, Stage, noSaveReq, noSaveConflict,
noSaveApprov, noSaveDenied )
Select @pUserId, @UserFirstName, @UserLastName,
@UserName, c.ClientId, c.ClientName, c.UsageTypeId, 2, 0, 0, 0, 0
from Client c
end
else
begin
-- count the user's client
SELECT @UserClientCoun t = count(dbo.Users .UserId)
FROM dbo.Users
INNER JOIN dbo.Contacts ON dbo.Contacts.Us erId =
dbo.Users.UserI d
INNER JOIN dbo.ClientConta ctLink ON dbo.Contacts.Co ntactID =
dbo.ClientConta ctLink.ContactI D
INNER JOIN dbo.Client ON dbo.Client.Clie ntID =
dbo.ClientConta ctLink.ClientID
WHERE dbo.Users.UserI d = @pUserId
-- users with associated client get just their clients
if @UserClientCoun t > 0
begin
-- give user's clients
insert @UserClientQ ( UserId, FirstName, LastName,
UserName, ClientId, ClientName, UsageTypeId, Stage, noSaveReq,
noSaveConflict, noSaveApprov, noSaveDenied )
SELECT dbo.Users.UserI d,
dbo.Users.First Name, dbo.Users.LastN ame, dbo.Users.UserN ame,
dbo.Client.Clie ntId,
dbo.Client.Clie ntName, dbo.Client.Usag eTypeId , 3, 0, 0, 1,1
FROM dbo.Users
INNER JOIN dbo.Contacts ON
dbo.Contacts.Us erId = dbo.Users.UserI d
INNER JOIN dbo.ClientConta ctLink ON
dbo.Contacts.Co ntactID = dbo.ClientConta ctLink.ContactI D
INNER JOIN dbo.Client ON dbo.Client.Clie ntID
= dbo.ClientConta ctLink.ClientID
WHERE dbo.Users.UserI d = @pUserId
end
else
begin
-- Active and non-readonly users get full client
list
if @UserActive = 1 and @UserReadOnly = 0
begin
-- give all clients
insert @UserClientQ ( UserId, FirstName,
LastName, UserName, ClientId, ClientName, UsageTypeId, Stage, noSaveReq,
noSaveConflict, noSaveApprov, noSaveDenied )
Select @pUserId, @UserFirstName,
@UserLastName, @UserName, c.ClientId, c.ClientName, c.UsageTypeId, 4, 0, 0,
1, 1
from Client c
end
end
end
end
return
END
My administrative user is ID=1. I perform the following code to build the
drop list
strSQL = "SELECT ClientID, ClientName, UsageTypeId, Stage FROM
estudf_UserClie ntsList(" strSQL = strSQL & cstr(iUserId)
strSQL = strSQL & ") ORDER BY ClientName"
response.write "<select size=1 name=txtClient> "
response.write "<option value='' >--Choose--</option>"
pbIsClientListE xists = false
set rs=Server.Creat eObject("ADODB. RecordSet")
rs.open strSQL,CONNECTI ONSTRING
while rs.EOF=false
pbIsClientListE xists = true
stage = rs.fields("Stag e")
If Trim(rs.fields( "ClientID"))=Tr im(checkvalue)T hen
selected=" selected"
pClientsUsageTy peID = rs.fields("Usag eTypeId")
Else
selected=""
End If
response.write "<option value='"
response.write Trim(rs.fields( "ClientID") )
response.write "' " & selected & ">"
response.write rs.fields("Clie ntName")
response.write "</option>"
wend
I have used Profiler to be sure the parameter to the function is proper and
have never found it to be incorrect.
When the client list is not build properly, only one client name appears and
it is clientid=1. If I change the select sql in the function to exclude
clientid=1 then the name of the next lowest client id is displayed when the
list is improperly built.
Any ideas to resolve the inconsistency would be gratefully received.
Marc
Comment