Help! I am trying to use a SessionID obtained via a login to pull records

from a Tiemsheet type database. Basically the SQL query using the

sessionID for the user would pull only that user's records from the field

in the respective database. The code for the SQL string is as follows on

all one line:



...

<!--#include file="../auth/level1.asp"--> 

<% UN = session("UserName") %> 

...

SQL = "select ID, week, Project_Manager, Customer_Name, Customer_Number,

Service, New_Orders, Change_Orders, Cutover_Orders, Pending_Orders,

Status, Comments, Project_Phase, FRI, SAT, SUN, MON, TUE, WED, THU from

Timesheet WHERE Project_Manager= ' & UN & '"





Note I get neither an error, nor data. Also note the session("UserName")

variable would look like "Taylor, Scott" (no quotation marks but space and

comma are there). I use the sessionID in a response.Write okay earlier on

the page, but the SQL query returns no data.



Any thoughts on what I am doing wrong?

Yes, you are generating your SQL string incorrectly.  You need to put end quotes

before you print UN in the SQL string.  Like so:



SQL = "select ID, week, Project_Manager, Customer_Name, Customer_Number,

Service, New_Orders, Change_Orders, Cutover_Orders, Pending_Orders,

Status, Comments, Project_Phase, FRI, SAT, SUN, MON, TUE, WED, THU from

Timesheet WHERE Project_Manager= '" & UN & "'"



Also, for future reference, it may be helpful to print out your SQL string when

debugging so that you can see exactly what is being sent.  You could have done

a:



Response.Write(SQL)



to see the exact string that was being sent. Then you would see that an actual

user name was not being sent.



Hope this helps.

Peter











"Scott Taylor" <scott.taylor@e...> on 01/22/2001 03:37:19 PM



Please respond to "ASP Databases" <asp_databases@p...>



To:   "ASP Databases" <asp_databases@p...>

cc:    (bcc: Peter Foti)



Subject:  [asp_databases] SQL Queries to Access using Session IDs???







Help! I am trying to use a SessionID obtained via a login to pull records

from a Tiemsheet type database. Basically the SQL query using the

sessionID for the user would pull only that user's records from the field

in the respective database. The code for the SQL string is as follows on

all one line:



...

<!--#include file="../auth/level1.asp"-->

<% UN = session("UserName") %>

...

SQL = "select ID, week, Project_Manager, Customer_Name, Customer_Number,

Service, New_Orders, Change_Orders, Cutover_Orders, Pending_Orders,

Status, Comments, Project_Phase, FRI, SAT, SUN, MON, TUE, WED, THU from

Timesheet WHERE Project_Manager= ' & UN & '"





Note I get neither an error, nor data. Also note the session("UserName")

variable would look like "Taylor, Scott" (no quotation marks but space and

comma are there). I use the sessionID in a response.Write okay earlier on

the page, but the SQL query returns no data.



Any thoughts on what I am doing wrong?

Have you tried writing the SQL to the browser before you execute the SQL 

statement?? Always use Response.Write(SQL) right before you execute a 

command or open a recordset. That way you can easily see what code you are 

executing. If you still can't find the problem, copy and paste the SQL 

statement in the Query Analyzer / designer to see if it works directly in 

SQL server or Access.



Maybe session("UserName") does not contain any data. The fact that the 

session ID has data is no guarantee that the UserName is filled as well.



If that's not the problem, post the rest of your code as well so we can 

have a look.



HtH



Imar





At 08:37 PM 1/22/2001 +0000, you wrote:

>Help! I am trying to use a SessionID obtained via a login to pull records

>from a Tiemsheet type database. Basically the SQL query using the

>sessionID for the user would pull only that user's records from the field

>in the respective database. The code for the SQL string is as follows on

>all one line:

>

>...

><!--#include file="../auth/level1.asp"-->

><% UN = session("UserName") %>

>...

>SQL = "select ID, week, Project_Manager, Customer_Name, Customer_Number,

>Service, New_Orders, Change_Orders, Cutover_Orders, Pending_Orders,

>Status, Comments, Project_Phase, FRI, SAT, SUN, MON, TUE, WED, THU from

>Timesheet WHERE Project_Manager= ' & UN & '"

>

>

>Note I get neither an error, nor data. Also note the session("UserName")

>variable would look like "Taylor, Scott" (no quotation marks but space and

>comma are there). I use the sessionID in a response.Write okay earlier on

>the page, but the SQL query returns no data.

>

>Any thoughts on what I am doing wrong?

Scott,



You're statement will generate the SQL command:

	select ID ... from Timesheet WHERE Project_Manager= ' & UN & '"

SQL is trying to find Project Manager " & UN & "

You need to close the string before concatenating the variable and the rest

of the string

	SQL = "select ID ... from Timesheet WHERE Project_Manager= '" & UN & "'"

This will generate a command of:

	select ID ... from Timesheet WHERE Project_Manager= 'Taylor, Scott'



That should fix it.



You caught me with that one. Looked at it three times before I noticed the

error. Just a simple string problem. But, oh, so frustrating sometimes.



One BIG recommendation that I have is to use a User ID instead of a user

NAME for retreiving data. This will certainly eliminate any string

comparison problems that SQL might have in a where clause.  When your users

login, you are most likely pulling out a user record by username (from the

login) then comparing the password entered with the password from the

record. Here you might as well pull out a user ID as well from the record,

and use that as your user persistance in the ASP session variable.



Peter



-----Original Message-----

From: Scott Taylor [mailto:scott.taylor@e...]

Sent: Monday, January 22, 2001 3:37 PM

To: ASP Databases

Subject: [asp_databases] SQL Queries to Access using Session IDs???





Help! I am trying to use a SessionID obtained via a login to pull records

from a Tiemsheet type database. Basically the SQL query using the

sessionID for the user would pull only that user's records from the field

in the respective database. The code for the SQL string is as follows on

all one line:



...

<!--#include file="../auth/level1.asp"-->

<% UN = session("UserName") %>

...

SQL = "select ID, week, Project_Manager, Customer_Name, Customer_Number,

Service, New_Orders, Change_Orders, Cutover_Orders, Pending_Orders,

Status, Comments, Project_Phase, FRI, SAT, SUN, MON, TUE, WED, THU from

Timesheet WHERE Project_Manager= ' & UN & '"





Note I get neither an error, nor data. Also note the session("UserName")

variable would look like "Taylor, Scott" (no quotation marks but space and

comma are there). I use the sessionID in a response.Write okay earlier on

the page, but the SQL query returns no data.



Any thoughts on what I am doing wrong?

First take the where off the sql statement and run it to see if you are 

getting any data at all. The do a response.write "SQL: " & sql to check to 

see what the sql statement being passed to ADO is. Do you have on error ... 

if so, start by commenting that line out. It may be masking an error.





>From: "Scott Taylor" <scott.taylor@e...>

>Reply-To: "ASP Databases" <asp_databases@p...>

>To: "ASP Databases" <asp_databases@p...>

>Subject: [asp_databases] SQL Queries to Access using Session IDs???

>Date: Mon, 22 Jan 2001 20:37:19 -0000

>

>Help! I am trying to use a SessionID obtained via a login to pull records

>from a Tiemsheet type database. Basically the SQL query using the

>sessionID for the user would pull only that user's records from the field

>in the respective database. The code for the SQL string is as follows on

>all one line:

>

>...

><!--#include file="../auth/level1.asp"-->

><% UN = session("UserName") %>

>...

>SQL = "select ID, week, Project_Manager, Customer_Name, Customer_Number,

>Service, New_Orders, Change_Orders, Cutover_Orders, Pending_Orders,

>Status, Comments, Project_Phase, FRI, SAT, SUN, MON, TUE, WED, THU from

>Timesheet WHERE Project_Manager= ' & UN & '"

>

>

>Note I get neither an error, nor data. Also note the session("UserName")

>variable would look like "Taylor, Scott" (no quotation marks but space and

>comma are there). I use the sessionID in a response.Write okay earlier on

>the page, but the SQL query returns no data.

>

>Any thoughts on what I am doing wrong?

>

ofcourse response.write the sql, but perhaps you also have "text" as type

for comments...if so , you should have it selected as the last item in your

sql string...





-----Original Message-----

From: Wally Burfine [mailto:oopconsultant@h...]

Sent: Monday, January 22, 2001 9:45 PM

To: ASP Databases

Subject: [asp_databases] Re: SQL Queries to Access using Session IDs???





First take the where off the sql statement and run it to see if you are

getting any data at all. The do a response.write "SQL: " & sql to check to

see what the sql statement being passed to ADO is. Do you have on error ...

if so, start by commenting that line out. It may be masking an error.





>From: "Scott Taylor" <scott.taylor@e...>

>Reply-To: "ASP Databases" <asp_databases@p...>

>To: "ASP Databases" <asp_databases@p...>

>Subject: [asp_databases] SQL Queries to Access using Session IDs???

>Date: Mon, 22 Jan 2001 20:37:19 -0000

>

>Help! I am trying to use a SessionID obtained via a login to pull records

>from a Tiemsheet type database. Basically the SQL query using the

>sessionID for the user would pull only that user's records from the field

>in the respective database. The code for the SQL string is as follows on

>all one line:

>

>...

><!--#include file="../auth/level1.asp"-->

><% UN = session("UserName") %>

>...

>SQL = "select ID, week, Project_Manager, Customer_Name, Customer_Number,

>Service, New_Orders, Change_Orders, Cutover_Orders, Pending_Orders,

>Status, Comments, Project_Phase, FRI, SAT, SUN, MON, TUE, WED, THU from

>Timesheet WHERE Project_Manager= ' & UN & '"

>

>

>Note I get neither an error, nor data. Also note the session("UserName")

>variable would look like "Taylor, Scott" (no quotation marks but space and

>comma are there). I use the sessionID in a response.Write okay earlier on

>the page, but the SQL query returns no data.

>

>Any thoughts on what I am doing wrong?

>

Wow, out of all the combinations of characters I tried to make it work, I did

not try that one. Thanks sooo much - it worked - obviously you knew that ;)



Thanks for helping me over a hurdle. By chance is there a reference to when and

where you would use ' or " or &, etc? I am finishing up the "ASP in 21 Days" but

it leaves me a bit confused on some topics.



BRgds,

Scott Taylor









pdf@b... on 01/22/2001 03:59:40 PM



To:   "ASP Databases" <asp_databases@p...>

cc:   scott.taylor@e...



Subject:  Re: [asp_databases] SQL Queries to Access using Session IDs???









Yes, you are generating your SQL string incorrectly.  You need to put end quotes

before you print UN in the SQL string.  Like so:



SQL = "select ID, week, Project_Manager, Customer_Name, Customer_Number,

Service, New_Orders, Change_Orders, Cutover_Orders, Pending_Orders,

Status, Comments, Project_Phase, FRI, SAT, SUN, MON, TUE, WED, THU from

Timesheet WHERE Project_Manager= '" & UN & "'"



Also, for future reference, it may be helpful to print out your SQL string when

debugging so that you can see exactly what is being sent.  You could have done

a:



Response.Write(SQL)



to see the exact string that was being sent. Then you would see that an actual

user name was not being sent.



Hope this helps.

Peter











"Scott Taylor" <scott.taylor@e...> on 01/22/2001 03:37:19 PM



Please respond to "ASP Databases" <asp_databases@p...>



To:   "ASP Databases" <asp_databases@p...>

cc:    (bcc: Peter Foti)



Subject:  [asp_databases] SQL Queries to Access using Session IDs???







Help! I am trying to use a SessionID obtained via a login to pull records

from a Tiemsheet type database. Basically the SQL query using the

sessionID for the user would pull only that user's records from the field

in the respective database. The code for the SQL string is as follows on

all one line:



...

<!--#include file="../auth/level1.asp"-->

<% UN = session("UserName") %>

...

SQL = "select ID, week, Project_Manager, Customer_Name, Customer_Number,

Service, New_Orders, Change_Orders, Cutover_Orders, Pending_Orders,

Status, Comments, Project_Phase, FRI, SAT, SUN, MON, TUE, WED, THU from

Timesheet WHERE Project_Manager= ' & UN & '"





Note I get neither an error, nor data. Also note the session("UserName")

variable would look like "Taylor, Scott" (no quotation marks but space and

comma are there). I use the sessionID in a response.Write okay earlier on

the page, but the SQL query returns no data.



Any thoughts on what I am doing wrong?

Peter,



Thank you for the precise explanation. I have adjusted code in other areas

successfully using your suggestions. However, you suggest using a User ID

instead of the name. I would prefer to do this but have run into a

problem, as I use several different tables within the database.



For example, I have the user login with a UserName=TaylorS pass=xxx which

then matches that to the access database. The UserName is matched to the

users actual name ("Taylor, Scott" in this case), and assigned to a

session variable - along with a session variable for that user's

authorization level. The name and user level are always visible from the

banner of every page of the site.



If I use an ID from the first table, how would I match this to the other

tables, like the timesheet, the order tracking, etc, where the

"Project_Manager" field is the same, but the IDs now are for records, and

only that field matches another table's field? Does that make sense?



Brgds,

Scott

Scott,

 It sounds like your database could use some redesign.  Here is how I would have

done it.



Table1 (USERS)

---------------

UID       (autonumber) - Primary Key

USERNAME  (text)

PWD       (text)

FNAME          (text)

LNAME          (text)

LEVEL          (number)





Table2 (ORDERTRACKING)

------------------------

OID       (autonumber) - Primary Key

UID       (number) - Foreign Key for UID in USERS - This replaces your

"Project_Manager" field

...



Something along those lines is how I would have done it.  When the user logs in,

you do a query to find the record in the USERS table where the USERNAME and PWD

match the username and password that was entered.  From that you can get the

person's name, authority level, and UID value and store them in Session

variables (similar to what you are doing already, but using the unique UID field

instead of having to compare strings to match up the user in other tables).



Good luck,

Pete