session variable

[Trekk]

I have an access database and it also is used for some asp pages. There is a login page that was auto generated that create a session variable based on the login, again all set auto generated by the program.

I want to create a reports page for users but have it pass their user id in the SQL query as a parameter and display records or queries where it matches their userid.

But I don’t know what I am doing. I used a simple ASP program to create the asp pages which also created the login pages which references a login table in the access database.


How do capture the login session and base the query on the logged in user.
below is an example of my SQL query that has the user hard coded in it.

SELECT tblParolee.ParoleeID, tblParolee.LastName, tblParolee.FirstName, tblParolee.CareCoordinators, tblCaseNotes.ContactDate
FROM tblParolee LEFT JOIN tblCaseNotes ON tblParolee.ParoleeID = tblCaseNotes.ParoleeID
GROUP BY tblParolee.ParoleeID, tblParolee.LastName, tblParolee.FirstName, tblParolee.CareCoordinators, tblCaseNotes.ContactDate, Month([ContactDate])
HAVING (((tblParolee.CareCoordinators)="Craig Judd-ICS") AND ((Month([ContactDate]))=Month(Now())));

 

[Chopstik]

There are several different ways you can approach this but it rather depends on what you have done in some of your other pages. If the session variable contains their login ID, you can use that. If their login ID is stored in the database, you can retrieve it from the database. I'm not sure I totally follow the setup that you're using so it's difficult to provide a more detailed explanation (though it's a little late and has been a long day, so it may just be me).

An example, though, would be as follows where the session("loginID") represents your session variable that contains the login information (you will note I also slightly modified your code to use WHERE instead of HAVING):

[COLOR=green]'Please not that this is just sample code and will need to be tested and modified to suit your purposes.[/color]
SELECT tblParolee.ParoleeID, tblParolee.LastName, tblParolee.FirstName, tblParolee.CareCoordinators, tblCaseNotes.ContactDate
FROM tblParolee LEFT JOIN tblCaseNotes ON tblParolee.ParoleeID = tblCaseNotes.ParoleeID
GROUP BY tblParolee.ParoleeID, tblParolee.LastName, tblParolee.FirstName, tblParolee.CareCoordinators, tblCaseNotes.ContactDate, Month([ContactDate])
WHERE (((tblParolee.CareCoordinators)=[COLOR=red]'[/color]"[COLOR=red] & session("loginID") & [/color]"[COLOR=red]'[/color]) AND ((Month([ContactDate]))=Month(Now())));

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Airpan]

Trekk,
As chopstick said I am not sure how you need the session variable to function, but I was shown how to create a session variable from a recordset and how to store it as a variable and call it in a query... there again, don't know if that suits your needs but you can certainly view the thread here:
thread333-1394265

~E

 

[Trekk]

ASP runner already auto created the login page based on a table in my access database

The reports are a simple select information from table where name = name. There is a column in the access database that is assigned to a user via a populate dropdown field. So my query is just trying to display the records that are assigned to a certain user. But I am trying to avoid having to make 23 different Asp pages since there are 23 different users that need dashboards. So I though it would be easier to have SQL or access show the data by querying the database and using the logged on user as the criteria.

I may be going at this the wrong way??

 

[Chopstik]

I believe you're going at this the right way. You do not want to have to create a different page for each user so pulling the data you need from your database is the correct idea. If your login page creates a session variable that contains the login ID (after they've already logged in), then that is all you would need and you can use the information I posted earlier (or a semblance thereof). Are you having a more specific problem? If so, post the relevant code so that we can help you review it and find a solution for you.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Trekk]

I took the example of the code you gave me and tried to apply it on another query. The ASP program forces me to use the having function instead of where since it is a group by or at least that is what the program says. the error I get when I try to run the code is MS access error to few parameters expected 1

Here is my code

SELECT tblParolee.ParoleeID, tblParolee.LastName, tblParolee.FirstName, tblParolee.CareCoordinators, tblCaseNotes.ContactDate, tblSAAC.AuthUNits
FROM (tblParolee LEFT JOIN tblCaseNotes ON tblParolee.ParoleeID = tblCaseNotes.ParoleeID) LEFT JOIN tblSAAC ON tblParolee.ParoleeID = tblSAAC.ParoleeID
GROUP BY tblParolee.ParoleeID, tblParolee.LastName, tblParolee.FirstName, tblParolee.CareCoordinators, tblCaseNotes.ContactDate, tblSAAC.AuthUNits
HAVING (((tblParolee.CareCoordinators)='" & session("userID") & "')AND ((tblSAAC.AuthUNits)<"5"));

 

[Chopstik]

Try to take out the parentheses in your FROM statement. Your error messages indicates that you're missing a parameter but I'm not seeing it right off... But having those parentheses where they are may cause it since they're only around one of the joins and the second join has nothing against which to join (if that makes sense)...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[0407644526]

i want to ristrict the user input in textarea by visual basic sript so the code is some think like this which i have rite now but i need some script which should ristrict the input in the textarea and when the user input text it should check the length of the textarea and if the length is more then 250 character then it should display a text or something like message box.

<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY class=body>
<DIV align="center">
<BR><BR>
<BR>
<H1>Job Description Form</H1>
<DIV align=center>
<font face=Arial size=2><p>
<TABLE cellSpacing=0 cellPadding=3 width=550 border=0 align=center>
<TBODY>
<TR><TD class="text" colspan="2"><br>Job Description (max. 250 characters)<br>

<TEXTAREA name="jobdescription" ROWS="5" COLS="60" maxlength="250"></TEXTAREA>
<br><br>
Responsibilities (max. 500 characters):
<br>
<TEXTAREA name="responsibilities" ROWS="5" COLS="60" maxlength="500"></TEXTAREA>
</TD></TR>
</TBODY></TABLE>
<BR>
<INPUT type="submit" value="Continue"></DIV>&nbsp;</FORM>
</DIV>
</BODY>
</HTML>

 

[Chopstik]

You can put a client-side function (javascript) that will check the length of your textarea and have check either when you submit or when you enter the text (it's more processor-intensive that way as it will run with every new character added). Try something like the following script:

function checkLen()
{	if(frmNotes.Notes.value.length > 250)
	{	alert("Your note has more than 250 characters.  Please shorten your note.");
		return false;
	}
	else return true;
}

<INPUT type="submit" value="Continue" [COLOR=red]onsubmit="checkLen()"[/color]></

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill