M
Marc Walgren
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_UserClientsList] (@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 @FacilityAdminCount int
declare @UserClientCount 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 @FacilityAdminCount = count(UFLID) from UserFacilityLink
where UserId = @pUserId
if @FacilityAdminCount > 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 @UserClientCount = count(dbo.Users.UserId)
FROM dbo.Users
INNER JOIN dbo.Contacts ON dbo.Contacts.UserId =
dbo.Users.UserId
INNER JOIN dbo.ClientContactLink ON dbo.Contacts.ContactID =
dbo.ClientContactLink.ContactID
INNER JOIN dbo.Client ON dbo.Client.ClientID =
dbo.ClientContactLink.ClientID
WHERE dbo.Users.UserId = @pUserId
-- users with associated client get just their clients
if @UserClientCount > 0
begin
-- give user's clients
insert @UserClientQ ( UserId, FirstName, LastName,
UserName, ClientId, ClientName, UsageTypeId, Stage, noSaveReq,
noSaveConflict, noSaveApprov, noSaveDenied )
SELECT dbo.Users.UserId,
dbo.Users.FirstName, dbo.Users.LastName, dbo.Users.UserName,
dbo.Client.ClientId,
dbo.Client.ClientName, dbo.Client.UsageTypeId , 3, 0, 0, 1,1
FROM dbo.Users
INNER JOIN dbo.Contacts ON
dbo.Contacts.UserId = dbo.Users.UserId
INNER JOIN dbo.ClientContactLink ON
dbo.Contacts.ContactID = dbo.ClientContactLink.ContactID
INNER JOIN dbo.Client ON dbo.Client.ClientID
= dbo.ClientContactLink.ClientID
WHERE dbo.Users.UserId = @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_UserClientsList(" strSQL = strSQL & cstr(iUserId)
strSQL = strSQL & ") ORDER BY ClientName"
response.write "<select size=1 name=txtClient>"
response.write "<option value='' >--Choose--</option>"
pbIsClientListExists = false
set rs=Server.CreateObject("ADODB.RecordSet")
rs.open strSQL,CONNECTIONSTRING
while rs.EOF=false
pbIsClientListExists = true
stage = rs.fields("Stage")
If Trim(rs.fields("ClientID"))=Trim(checkvalue)Then
selected=" selected"
pClientsUsageTypeID = rs.fields("UsageTypeId")
Else
selected=""
End If
response.write "<option value='"
response.write Trim(rs.fields("ClientID"))
response.write "' " & selected & ">"
response.write rs.fields("ClientName")
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_UserClientsList] (@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 @FacilityAdminCount int
declare @UserClientCount 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 @FacilityAdminCount = count(UFLID) from UserFacilityLink
where UserId = @pUserId
if @FacilityAdminCount > 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 @UserClientCount = count(dbo.Users.UserId)
FROM dbo.Users
INNER JOIN dbo.Contacts ON dbo.Contacts.UserId =
dbo.Users.UserId
INNER JOIN dbo.ClientContactLink ON dbo.Contacts.ContactID =
dbo.ClientContactLink.ContactID
INNER JOIN dbo.Client ON dbo.Client.ClientID =
dbo.ClientContactLink.ClientID
WHERE dbo.Users.UserId = @pUserId
-- users with associated client get just their clients
if @UserClientCount > 0
begin
-- give user's clients
insert @UserClientQ ( UserId, FirstName, LastName,
UserName, ClientId, ClientName, UsageTypeId, Stage, noSaveReq,
noSaveConflict, noSaveApprov, noSaveDenied )
SELECT dbo.Users.UserId,
dbo.Users.FirstName, dbo.Users.LastName, dbo.Users.UserName,
dbo.Client.ClientId,
dbo.Client.ClientName, dbo.Client.UsageTypeId , 3, 0, 0, 1,1
FROM dbo.Users
INNER JOIN dbo.Contacts ON
dbo.Contacts.UserId = dbo.Users.UserId
INNER JOIN dbo.ClientContactLink ON
dbo.Contacts.ContactID = dbo.ClientContactLink.ContactID
INNER JOIN dbo.Client ON dbo.Client.ClientID
= dbo.ClientContactLink.ClientID
WHERE dbo.Users.UserId = @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_UserClientsList(" strSQL = strSQL & cstr(iUserId)
strSQL = strSQL & ") ORDER BY ClientName"
response.write "<select size=1 name=txtClient>"
response.write "<option value='' >--Choose--</option>"
pbIsClientListExists = false
set rs=Server.CreateObject("ADODB.RecordSet")
rs.open strSQL,CONNECTIONSTRING
while rs.EOF=false
pbIsClientListExists = true
stage = rs.fields("Stage")
If Trim(rs.fields("ClientID"))=Trim(checkvalue)Then
selected=" selected"
pClientsUsageTypeID = rs.fields("UsageTypeId")
Else
selected=""
End If
response.write "<option value='"
response.write Trim(rs.fields("ClientID"))
response.write "' " & selected & ">"
response.write rs.fields("ClientName")
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