2. ASPRunnerPro General questions
  3. allowing a user to edit multiple areas
This topic is locked

allowing a user to edit multiple areas

 		5/5/2009 5:19:50 PM
ASPRunnerPro General questions
W
 wfcentral author

I have multiple tables...
Colleges > Departments > Degrees
Users
Access
I want a user to be assigned to many departments and only be able to add degrees in those assigned departments.
So, I created a table called "Access" that has id,user_ID,dept_ID
so, with this data
Users

id, name

1, Mary

2, John
Departments

id, dept_name

1, Nursing

2, Art
Access

id, user_ID, dept_ID

1, 1, 1

2, 2, 1

3, 2, 2
would mean

Mary can add/edit in Nursing

John can add/edit in Nursing and Art
So, how do I get AspRunner to honor this type of access?
I can't do it in the misc security area because I am not linking a field in user to a field in dept
I tried strWhereClause = whereAdd(strWhereClause,"your where clause here") as an event, but not sure how to use it...
J
 		Jane 5/6/2009

Hi,
you need to select dept_ID from Access table and use it in your where clause.

Here is just a sample:

where = ""

str = "select dept_ID from Access where user_id=" & Session("user_id")

Set rstmp = server.CreateObject("ADODB.Recordset")

rstmp.open str,dbConnection
while not rstmp.eof

where = where & rstmp("dept_ID") & ","

rstmp.movenext

wend
rstmp.close

set rstmp=nothing

where = Left(where, Len(where)-1)
strWhereClause = whereAdd(strWhereClause,"id in (" & where & ")")
W
 		wfcentral author 5/6/2009

Hi,

you need to select dept_ID from Access table and use it in your where clause.

Here is just a sample:


do I put this in as an event?
J
 		Jane 5/7/2009

Yes, use List page: Before SQL queryevent for this purpose.
W
 		wfcentral author 5/7/2009

Yes, use List page: Before SQL queryevent for this purpose.


not sure what I did wrong, but that causes the department list page to error 500
W
 		wfcentral author 5/7/2009

got it working now - thanks
where = ""

'str = "select dept_ID from Access where user_id=" & Session("userID")

str = "SELECT dept_ID FROM Access WHERE (user_ID = (SELECT id FROM users WHERE (login = '" & Session ("userID") & "')))"

Set rstmp = server.CreateObject("ADODB.Recordset")

rstmp.open str,dbConnection
while not rstmp.eof

where = where & rstmp("dept_ID") & ","

rstmp.movenext

wend
rstmp.close

set rstmp=nothing

where = Left(where, Len(where)-1)
strWhereClause = whereAdd(strWhereClause,"id in (" & where & ")")