• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

Access database value passing and proper joining in ColdFusion

I am using Microsoft Access for my database. I have two tables, one for login info (username, password and roleCode), and another table that has roleCode, as well as other values that need to appear if the roleCode search upon matches. For example, the username is "John", the password is "x123z" and the roleCode is "Admin". In the second table, the roleCode will vary from "Admin" to "user" to "Editor", etc.

First - The access database is not joined properly (I have tried to make sure the the "roleCodes" from both tables are linked, but have not suceeded yet), so I think that is one area that is causes problems. Is it the only area?
Second - When a user enters his username and password, I need to have the roleCode variable information accessible in the display page. How do I do that?
0
msukow
Asked:
msukow
  • 3
  • 2
  • 2
2 Solutions
 
hartCommented:
is the rolecode table have unique rolecodes then create a primarykey on the role code...

and make a relationship btween rolecode in login table and rolecode in rolecodes table.
since the login table rolecode is a foriegn key, it will not allow any other values other that from the rolecode table..

then u won't have a datamismatch problem...

then when a user tries to log in i would write this in the checklogin page

<cfquery name="qry_checklogin" datasource="ur dsn name">
       select username,rolecode from login
       where username = '#form.username#'
                 and password = '#form.userpassword#'
</cfquery>

<cfif qry_checklogin.recordcount gt 0>
    <CFCOOKIE NAME="LoginCookie"
        VALUE="#qry_checklogin.Username#~#qry_checklogin.rolecode#"
        EXPIRES="NOW">
    <CFHEADER NAME="Refresh" VALUE="0; URL=DisplayPage.cfm">
<cfelse>
    <!--- invalid user redirect accordingly --->
</cfif>


--------------------------------------------------------
now in the displaypage.cfm

u can access the rolecode of the logged in user from the cookie...
<Cfoutput>#ListGetAt(Cookie.LoginCookie,2,'~')#</cfoutput>

also u can get the corresponding values from rolecode table using this cookie value

<cfquery name="qry_rolecodes" datasource="ur dsn name">
       select rolecode,whatever from rolecodes
       where rolecode = '#ListGetAt(Cookie.LoginCookie,2,'~')#'
</cfquery>

Regards
Hart
0
 
msukowAuthor Commented:
The roleCode tabel doesn't have unique values (there are multiple users that can be admins, as well as editors). What I need to do is check the username and password, and then display records that the user has access to. Example, there are 100 records, of which user1 can access only 45 (his roleCode matches 45 of the records). I want to beable to have the table displayed on the displayPage of the 45 records he can see and edit, without access to the other 55.
0
 
hartCommented:
ok u can do this
1. keep a unique id in the login table other than username [a numerical field]
2. now store this id in rolecode table as well. [thus this becomes a foreign key to login table]

or if u can't create a unique field now then store the username in the rolecode table..
this will help u to relte between the two tables easily..

so after login in the cookie store the username / unique id from login tabe

and in the display page hit a query like this
<cfquery name="qry_rolecodes" datasource="ur dsn name">
       select rolecode,whatever from rolecodes
       where username = '#ListGetAt(Cookie.LoginCookie,1,'~')#'
</cfquery>

this query will give u all the roles of that particluar user

hope u understand what i mean..

Regards
Hart
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
proceptCommented:
Hi,

hart is quite right, although I would suggest using session variables instead of cookies:

<cfquery name="qry_checklogin" datasource="ur dsn name">
       select username,rolecode from login
       where username = '#form.username#'
                 and password = '#form.userpassword#'
</cfquery>


<cfif qry_checkLogin.recordCount>
   <cfset session.username = qry_checklogin.username>
   <cfset session.rolecode = qry_checklogin.rolecode>
</cfif>

on the display page you can use the session vars to select the records:
<cfquery name="qry_rolecodes" datasource="ur dsn name">
       select rolecode,whatever from rolecodes
       where rolecode = '#session.rolecode#'
</cfquery>

The downside to this: if you are using CF 5 or earlier, you need to lock each and every use of session vars or you run the risk of loosing data! (With CF MX you don't need to lock shared scope variables any longer.)

One word of advice: make a proper relational database model, that's much better...

- make each table have a numerical primary key (can be a combined key of two values, too!)
- make aseparate table for joining users and roles

Could be something like this (always using fieldname: datatype, PK = Primary Key, unique value):
user-table:
userID: int  PK
username: text
roleID: int
...

roles-table
roleID: int  PK
rolecode: text

join-table (combination of userID and roleID would be unique)
userID: int
roleID: int

records-table:
recordID int PK
roleID int
field1 whatever

HTH,

Chris
0
 
hartCommented:
i didn't suggest sessions because sessions have a problem if u r accessing behind a firewall [proxy server]. the session values keep on interchanging..

where as cookies avoid this problem..

Regards
Hart
0
 
msukowAuthor Commented:
Thanks -

I am trying your suggestions now and hope to have this fixed later today or tomorrow. I will keep you posted.
0
 
proceptCommented:
Hi,

> i didn't suggest sessions because sessions have a problem if u r
> accessing behind a firewall [proxy server]. the session values keep on interchanging..
>
> where as cookies avoid this problem..

Per default sessions are identified by one or two cookies (depending on the type of session you have), and the reason that sessions sometimes get lost is that firewalls or proxies either filter out the cookies (not the sessions!) or cache the pages.

If you use cookie-less sessions, your session vars are safe. (From this point of view... not talking about missing locks with session vars in CF 5 and earlier. ;-))

Chris



0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now