We help IT Professionals succeed at work.

session variable from classic asp web app in sql server query

Medium Priority
1,181 Views
Last Modified: 2014-07-05
I have a classic asp web app using data from SQL Server. Now the customer wants to filter the employee records based on the location of the logged in user. I could go to every page and filter the records by the session variable I have to identify the user, but I don't want to do that. I want to make one query over in my SQL server database that will filter the records and then I can just automatically feed that to every page.

How do I do this? How do I capture a session variable from my web app to use in my SQL query? I just want a way to filter my records without having to edit every page of my application, but I don't know how to tell SQL which user is using the program. The SUSER_SNAME just returns the SQL login name and that's not what I need. I need the value I have stored in a session variable. Is this possible?
Comment
Watch Question

Peter ChanProblem resolver

Commented:
Hi,
Try these
Have a new location column to the relevant table
Have the change to the screen to only show the employee having the same location as the login user
Harish VargheseProject Leader

Commented:
Since web app and database are two different layers, there is no way to identify the real logged-in web user from database unless you use windows authentication to connect to the database (which is not, in your case). So the only way is to pass the web usserid or location to the database (queries or stored procs). Since, in web applications, database connections are shared (pooled) for many users, it is difficult to identify a connection (spid) with a specific user.
Developer & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
There are a multiple options here.  

>customer wants to filter the employee records based on the location of the logged in user
Can you define what you mean by this?  Are the users typing in a username/password that is then checked against the database?  Or are you using the Active Directory to log in?

I have done this a couple of ways in different applications.  

1) User Table
In your table of users and / or employees that is also used to test the login, add a location field.  I keep a separate table of locations and only store the the location id in the user table.  Once somebody is logged in, I will store the location id as either a session or cookie (feel free to use encryption)

As they are searching other employees, use the cookie or session to call up the filter
' set the location
response.cookies("location") = rsUser("location")
session("location") = rsUser("location")

'read the cookies
location = request.cookies("location")
location = session("location")

Open in new window


2) Using IP
This will only work for known locations of course.  
Keep a table of accepted IP's and their location id.

When somebody logs grab their ip
<b>Your IP address is:</b>
<%
ip = Request.ServerVariables("remote_addr")

'The DNS lookup of the IP address is:
ip = Request.ServerVariables("remote_host")
%>

Open in new window


Now how to make this painless as possible.
>I want to make one query over in my SQL server database that will filter the records and then I can just automatically feed that to every page.

At the log in, edit only the page that checks for the login/pass.

<%
'a)  Make your recordset to check logged in user.  Store that location in session("location")
'b)  Next create a list of employees at the location of the logged in user as described above
'c)  store the list in an array called arrEmployees  
' assume your filtered recordset is called rsEmployees

' Check the session variable
currentLocation = 0 ' just in case we can't get a good session id
if session("location")<> "" then
    if isnumeric(session("location")) then ' can't be too carful
             currentLocation = session("location")
    end if
end if
' if we have a session variable, and it is numeric, it is now stored in the variable currentLocation.  This is a little safer than just using the session variable.
    
end if
sql = "SELECT EmployeeID, FirstName, LastName from tblEmployees WHERE Location = "&currentLocation
' create your record from here called rsEmployees
' make sure there are records

if not rsEmployees.bof or not rsEmployees.eof then
     arrEmployees = rsEmployees.getrows()

     ' at this point we have an 2 dimensional array of employees and we can now store this to a session
     session("employeeList") = arrEmployees
      else
     session("employeeList") = ""
end if
%>

Open in new window

At this point, your list of employees is stored in a session variable as an array. This will of course be available on every page.  

Finally, how do you inject this to every page without editing every page?  Look for include files you are currently using.   You must have at least one for your connection?  It might look something like below. (If you are using include "file" rather than virtual, you have a potential security issue if parent paths are on manually in iis7)
<!--#include virtual="/includes/connection.asp"-->
<!--#include file="includes/connection.asp"-->

Open in new window

If you have other includes on your page that may help as well. You may have to bite the bullet and add another include. When I develop my pages now, I have includes at the top of my page, in my head section, just above the </body> tag and at the bottom of the page. This allows me to later on in the future add items just like this because I have run into this already.

There is another sneaky option.  Let's say you are using jquery.  Well, that file is loaded after the initial html is loaded on the page (including html generated from asp).

In one of your js files that is loaded after jquery (or the bottom of the jquery file) you can use asp to generate jquery that will inject or filter the table. The down side to this is the employee list will still be viewable if you viewsource or in the rare case the js is turned off.  If this is done for security reason this method may not be so good.

Notice what happens to this table with a jquery filter http://jsbin.com/riquge/1/edit
<table>
  <thead>
    <tr><th>Employee ID</th><th>Location</th><th>First</th><th>Last</th></tr>  
  </thead>
  <tbody>
    <tr><td>123</td><td>1</td><td>Mike</td><td>Apple</td></tr>
    <tr><td>124</td><td>2</td><td>Sue</td><td>Smith</td></tr>
    <tr><td>125</td><td>1</td><td>Bill</td><td>Johnson</td></tr>
     <tr><td>126</td><td>3</td><td>Lisa</td><td>Funk</td></tr>
    <tr><td>127</td><td>2</td><td>Beth</td><td>Sweet</td></tr>
    <tr><td>128</td><td>4</td><td>Jerry</td><td>Garcia</td></tr>
  </tbody>

Open in new window

$(function(){
var location_filter = 1;
$("td:nth-child(2)").filter(function() {
    return $(this).text().indexOf(location_filter) == -1;
}).parent().remove();
});    

Open in new window

I captured the id using nth child selector and got rid of those rows that are not equal to the location of 1 or equal to the location_filter

To make that work, you would need to just inject this to a js file that is at the bottom of the jquery file or in another js file that loads after jquery
$(function(){
var location_filter = <%=session("locationID")%>;
$("td:nth-child(2)").filter(function() {
    return $(this).text().indexOf(location_filter) == -1;
}).parent().remove();
}); 

Open in new window

One flaw with the above is it would filter any table.  If the table you want to target has an id or class that is unique to the employee list, here is an option http://jsbin.com/riquge/2/edit
<table id="employeeList">
  <thead>
    <tr><th>Employee ID</th><th>Location</th><th>First</th><th>Last</th></tr>  
  </thead>
  <tbody>
    <tr><td>123</td><td>1</td><td>Mike</td><td>Apple</td></tr>
    <tr><td>124</td><td>2</td><td>Sue</td><td>Smith</td></tr>
    <tr><td>125</td><td>1</td><td>Bill</td><td>Johnson</td></tr>
     <tr><td>126</td><td>3</td><td>Lisa</td><td>Funk</td></tr>
    <tr><td>127</td><td>2</td><td>Beth</td><td>Sweet</td></tr>
    <tr><td>128</td><td>4</td><td>Jerry</td><td>Garcia</td></tr>
  </tbody>
  </table>

Open in new window

$(function(){
var location_filter = 1;
$("#employeeList td:nth-child(2)").filter(function() {
    return $(this).text().indexOf(location_filter) == -1;
}).parent().remove();
}); 

Open in new window


There are a lot of possibilities and it would help to know more about your structure
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
if every page is already querying the database for the records, aren't you going to have to edit each page anyways to disable that query? I guess I'm a bit confused on what you mean by

I want to make one query over in my SQL server database that will filter the records and then I can just automatically feed that to every page.

I agree, so more details will be useful

Author

Commented:
The application is already programmed and running. The users type in a user name and password to log in, and then they see the list of employees. Each employee has a location ID. The customer now wants the users to only see the employees with the same location ID.

I can do this just fine in each web page by storing the login as a session variable and filtering by that. However, that means changing every single page. I also have an Access user interface for this application, and it was so simple, just filter the employee list once and now everyplace where the employees are displayed, they are already filtered.

I was hoping for the same thing in SQL Server. If I could just know which user was logged in, I could filter the records over there and not have to edit every single page.
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
I could filter the records over there and not have to edit every single page.

I guess I'm still not following you on this. On each of your pages, don't you have a sql query already in place, something like:

sql = "select employeeID from tblEmployees"

wouldn't you still have to go back and edit each page, since that is how you're  getting the data?

it's easy enough to filter out the records just once, you could do that in your
Global.asa file in the Session_OnStart event. You could even store the data locally in a cookie or session variable. but unless you're using a stored procedure to pull the data, you're still going to have to edit each page to change the original query
Scott FellDeveloper & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
Did it make sense that you can do this in javascript?  That would allow you to inject the js code needed to filter without having to change all of your pages.    As I said, the downside is somebody could simply view the source and see the entire list of employees.  Not that many people would understand to do that, but it is there.  

If your sql statement is on the page and you don't want to use the method outlined, then you will just need to bite the bullet and update every page.  

You can do a simple find and replace (back up files first).   What you may consider doing as long as you will update all the pages is to move that portion to a separate file and then inject it with an include file.   I know this is a little more work than you want, but down the road when there is another need to do something with this, you only have to update one page.    

After having to go through this myself, items that are common on every page I inject them to the page with an include file and when I am tasked with the type of situation you are in now, it is just updating one file.

Being able to directly affect sql server from a session variable is something that can't be done.  This is one of those questions where the answer is, you can't do that that and, you can't do that and that is ok.

Do you want to pursue one of the options already mentioned?
Harish VargheseProject Leader

Commented:
If this was a windows application where you can use a dedicated connection for each user, you could have set the location of current user in a table associating with the spid of the current connection. But that is not the way with web applications. Being aware that there is no option to know the logged in user from database without explicitly passing the location/user parameter to the queries/stored procs, you need to explore the ways of handling it in your current architecture.

Do you have a layer (service handler layer?) which accepts all calls to be made to your database, make appropriate calls to the database/app server, and pass the result back? If so, you could modify the methods in this layer to do the filter based on a session variable once the data is retrieved. Is that an option?

Author

Commented:
Thanks to the experts for your time and consideration on this matter, and your advice on better practices for next time. It looks like I will just have to edit every page, and no they don't all have the same query or the same set of data, they just all use the employee records in some way. If I could have created a filtered query in SQL and substituted that for the actual table, it would have been a lot easier, but no such luck this time. Thanks.
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
If the final solution is to go back and edit every page, then shouldn't my last post be considered as part of the final solution, where I essentially stated that you would need to edit every page?