Ceate Sessions from table data

[Illufox]

My question is: Can I create sessions from data that's stored in a table instead of data that the user enters?

I've build a web application where only certain users can update records.
What I now have to add is that those users all have their own data as well. So if they log in and click on a link it will only display the records that they are in charge of. I know that this can be controlled with sessions but so far I have not been able to plug the sessions correctly.

On the login page I have the following (after user name and password session variables):

Session("FirstName") = Recordset1.Fields.Item("FirstName").Value
Session("LastName") = Recordset1.Fields.Item("LastName").Value

What I'm trying to achieve is that I can pull all the data that contains the name of that particluar user. The data I want to display comes from a table that contains the full name of each user. The login table contains a field of "FirstName" and a field of "Last Name". I added this so I can create a filter that pulls all records with a particular First Name or Last Name or both.

Will "Request.QueryString" pull from the table or do I need a different code?

[buntine]

Request.QueryString will grab data from the QueryString, not the table. A table needs a RecordSet collection, which you have demonstrated above.

You should always work with primary keys instead of names. What if two users have the same name? It will be carnage.

The session need only be used to authenticate a user into the system. Once he/she is in, you can use the users primary key to only select the correct records.

Regards.

[Illufox]

Well, I try to pull data from a different table than the "user" table. What I'm trying to do is to say:

Show me all the data from the "events" table that contains the current users full name.

I added a "First Name" and a "Last Name" column to the "user" table which I want to store in a session, so that it can compare the name of the session with the name in the "events" table, and then display all records with that name.

The events table contains two colums, one for the support people and one for the project leads. So the name can be in both columns of the "events" database.

[buntine]

A better way to design your database would be to have a unique, numeric primary key in the users table. Then you could get rid of any referance to a particular name in your events table and avoid the possibility of two users having the same name and each seeing incorrect events.

You need some SQL to select the correct events:

"SELECT * FROM events WHERE events.usersFirstName = '" & Session("FirstName") & "' AND events.usersLastName = '" & Session("LastName") & "';"

This is assuming your events table has a column for the users first and last name.

Regards.

[Illufox]

The Access database has been designed by somebody else, I'm just helping with the web application part. The user table contains a primary key, but unfortunately it's for the initials which I think doesn't make sense.

I just combined "First Name" and "Last Name" columns in the "DbUser" table to one column called "FullName" so the data will match the data of the "Project Lead" column in the "Meeting" table.

On the login page I have the following:
Session("FullName") = Recordset1.Fields.Item("FullName").Value
On the "Show My Events" page I have the following SQL satement:
Recordset1.Source = "SELECT * FROM Meeting WHERE ProjectLead = '" + Session("FullName") + "' ORDER BY Date_S ASC"
For some reason it doesn't pull any records. I now wonder if the session gets set at all..... (?)

[Illufox]

Just added this to the "Show My Events" page for testing:
<% Response.Write(Session("FullName")) %>
Instead of displaying the full name of the user that I logged in as, it displays my full name(!?). I checked the "dbUser" database table, everthing is correct. Where does it get my name from? Now I'm really confused.

At least this explains why there are no events listed as I'm not one of the Project Leads. So something is working but not quite right....

[wmif]

whats the query for this rs?

Session("FullName") = Recordset1.Fields.Item("FullName").Value

[Illufox]

Recordset1.Source = "SELECT FullName, Password, UserName FROM DbUser"

[Illufox]

Got it! The query was pulling the first record instead of the one from the user that just logged in. I've changed the query like so:
Recordset1.Source = "SELECT FullName, Password, UserName FROM DbUser WHERE UserName = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"

This belongs to the code above:

<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.Form("username") <> "") Then
Recordset1__MMColParam = Request.Form("username")
End If
%>

And now it works! Thanks everybody for helping me getting this issue nailed downl! :D

[buntine]

You did most of the work yourself. ;)

[wmif]

sometimes it just helps for people to ask you questions about what youve done. then all of a sudden it slams you in the back of the head. :)