2. ASPRunnerPro General questions
  3. Lookup table question
This topic is locked

Lookup table question

 		12/17/2007 10:30:47 AM
ASPRunnerPro General questions
E
 Eric author

Hello,
I have a table that has user names assigned to districts. I would like to have a lookup table that displays only the user names that are in the same district as the user that is currently logged in. I'm sure this requires a Where statement, but I can't figure out how to code it. Any help is appreciated. If it helps, I'm using ASPRunner Pro 4.1.
Also, I've had an issue in the past where I've entered in values from a lookup table but when I go to edit the record, it wants me to re-select the value from the lookup table. Is there a way to prevent this from happening?
Thanks,

Eric
Sergey Kornilov admin 12/17/2007

This is a two-step process

  1. In AfterSuccessfullLogin event you need to write a SQL query that retrieves a district value from Users table.
    set rstmp = Server.CreateObject("ADODB.Recordset")

    rstmp.Open "select District from LoginTable where UserIDField ='" & Session("UserID") & "'"

    if not rstmp.eof then Session("District") = rstmp("District")

    rstmp.close : set rstmp = Nothing


Replace field/table names in bold with the actual field names.
2. When you setup a Lookup wizard add a WHERE clause:

" District = " & Session("District")


This code assumes that District is a numeric field.
E
 		Eric author 12/18/2007

Thanks Sergey. District is a text (varchar) field. What change do I have to make to the code to account for that?
Thanks,

Eric
Sergey Kornilov admin 12/18/2007
" District = '" & Session("District") & "'"


The rest of code should be left intact.
E
 		Eric author 12/19/2007

Thanks. I plugged that in and got this error:
Error number 3709

Error description The connection cannot be used to perform this operation. It is either closed or invalid in this context.

URL /drm2000/infotech/safecount/safecount_store/login.asp

SQL query select * from [dbo].[Logons] where [Username]='**' and [Password]='**'