session variables

Hi there, I am VERY new to ASP and have set up an internet based user login system where the user enters thier username and password. What I am trying to do after this is query the MS SQL Database to return all records based on the "originatorid" that the logged in user has within the "userstable" table, remembering that the user did not enter in thier "originatorid" only ther "username" and "userpwd".

Here is my code thus far for my login page.

LOGIN.ASP

<%
End Sub

Sub CheckLogin
Dim Conn, cStr, sql, RS, username, userpwd
username = Request.Form("username")
userpwd = Request.Form("userpwd")
Set Conn = Server.CreateObject("ADODB.Connection")
cStr = "DRIVER={SQL Server};SERVER=ipaddress;DATABASE=databasename;UID =userid;PWD=password" & ";"
Conn.Open(cStr)
sql = "select username,originatorid from UserTable where username = '" & LCase(username) & "'"
sql = sql & " and userpwd = '" & LCase(userpwd) & "'"
Set RS = Conn.Execute(sql)
If RS.BOF And RS.EOF Then
Error_Msg = "Login Failed. Try Again."
ShowLogin
Else
Session("UserLoggedIn") = "true"
Response.Redirect "originators.asp"
End If
End Sub
%>

Then after authentication the user is directed to the following page

APPLICATIONS_IN_PROCESS.ASP

This is where I come unstuck. I think I need to extract the "originatorid" from the "usertable" then show all records based on the "originatorid" within the APPLICATIONS_IN_PROCESS.ASP form.

I really hope you can help me here, questions are most welcome many thanks in advance.

SEAN.

Ok - when the user gets a successfiul login, store their UserID in the session vairable. That way anytime u need to do a query on the user's details, you'll have their ID handy.

After you redirect them to the "originators.asp" file - do the query for their originator ID's here, not on the login page. Keep your logic clean and seperate.

The query will be similar to what you have on the login page.
"SELECT originatorid FROM users WHERE userid = " & session("userid")

then, once u have your recordset, loop through displaying the originator ID's.

HTH,
stumpy

Hey thanks for the reply. I am still struggeling as I have modified the code I got to suite my DB but still cannot get it to work. Here is the code for my login page.

<link rel="stylesheet" type="text/css" href="snet1.css">
<%
Response.Expires = -1000 'Makes the browser not cache this page
Response.Buffer = True 'Buffers the content so our Response.Redirect will work

Dim Error_Msg

login = Request.Form("login")
If login = "login_again" Then
Session("UserLoggedIn") = ""

ShowLogin
Else
If Session("UserLoggedIn") = "true" Then
AlreadyLoggedIn
Else
If login = "true" Then
CheckLogin
Else
ShowLogin
End If
End If
End If

Sub ShowLogin
Response.Write(Error_Msg & "<br>")
%>
<form name=form1 action=login_copy(1).asp method=post>
<input type=hidden name=login value=true>
<h1>Originators Login</h1>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" width="100%" id="AutoNumber1">
<tr>
<td width="11%"><b><font size="2">User Name : </font></b></td>
<td width="89%"> <input type=text name=username size="20"></td>
</tr>
<tr>
<td width="11%"><b><font size="2">Password :</font></b></td>
<td width="89%"> <input type=password name=userpwd size="20"></td>
</tr>
<tr>
<td width="11%">&nbsp;</td>
<td width="89%">
<input type=submit value="Login"></td>
</tr>
</table>
</form>
<%
End Sub

Sub AlreadyLoggedIn
%>
&nbsp;<p>You are already logged in.
Do you want to logout or login as a different user? </p>
<form name=form2 action=login_copy(1).asp method=post>
<input type=submit name=button1 value='Yes'>
<input type=hidden name=login value='login_again'>
</form>
<%
End Sub

Sub CheckLogin
Dim Conn, cStr, sql, RS, username, userpwd
username = Request.Form("username")
userpwd = Request.Form("userpwd")
Set Conn = Server.CreateObject("ADODB.Connection")
cStr = "DRIVER={SQL Server};SERVER=ipaddress;DATABASE=databasename;UID =userid;PWD=password" & ";"
Conn.Open(cStr)
sql = "select username, originatorid from UserTable where username = '" & LCase(username) & "'"
sql = sql & " and userpwd = '" & LCase(userpwd) & "'"
Set RS = Conn.Execute(sql)
If RS.BOF And RS.EOF Then
Error_Msg = "Login Failed. Try Again."
ShowLogin
Else
Session("UserLoggedIn") = "true"
Session("originatorid") = RS("originatorid")
Response.Redirect "applications_in_process.asp"
End If
End Sub
%>

Now here is the code for my results page (using FrontPage 2002) the page is called "applications_in_process

<%Stroriginatorid = Session("originatorid")%>

<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Applications In Process</title>
<link rel="stylesheet" type="text/css" href="../snet1.css">
</head>

<body>

<!--webbot bot="DatabaseRegionStart" s-columnnames="appid,originatorid,loannumber,status" s-columntypes="129,129,129,129" s-dataconnection="nzmortgagefunds" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice="appid" s-menuvalue="appid" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource s-displaycolumns="appid,originatorid,loannumber,status" s-criteria s-order s-sql="SELECT * FROM loans WHERE (originatorid = '&quot;&amp; session(&quot;originatorid&quot&quot;')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="5" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00" align="left"><font color="#000000">This is the start of a Database Results region.</font></td></tr></table>" startspan b-WasTableFormat="FALSE" --><!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM loans WHERE (originatorid = '""& session(""originatorid"")""')"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="nzmortgagefunds"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="appid"
fp_sMenuValue="appid"
fp_iDisplayCols=4
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="3184" --><p><b>appid:</b>&nbsp;<!--webbot bot="DatabaseResultColumn" s-columnnames="appid,originatorid,loannumber,status" s-column="appid" b-tableformat="FALSE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>appid<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"appid")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="10487" --></p>
<p><b>originatorid:</b>&nbsp;<!--webbot bot="DatabaseResultColumn" s-columnnames="appid,originatorid,loannumber,status" s-column="originatorid" b-tableformat="FALSE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>originatorid<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"originatorid")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="36753" --></p>
<p><b>loannumber:</b>&nbsp;<!--webbot bot="DatabaseResultColumn" s-columnnames="appid,originatorid,loannumber,status" s-column="loannumber" b-tableformat="FALSE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>loannumber<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"loannumber")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="32650" --></p>
<p><b>status:</b>&nbsp;<!--webbot bot="DatabaseResultColumn" s-columnnames="appid,originatorid,loannumber,status" s-column="status" b-tableformat="FALSE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>status<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"status")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="19226" --></p>
<hr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="5" clientside tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00" align="left"><font color="#000000">This is the end of a Database Results region.</font></td></tr><TR><TD ALIGN=LEFT VALIGN=MIDDLE><NOBR><INPUT TYPE=Button VALUE=" |< "><INPUT TYPE=Button VALUE=" < "><INPUT TYPE=Button VALUE=" > "><INPUT TYPE=Button VALUE=" >| "> [1/5]</NOBR><BR></td></tr></table>" startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="56926" --></body></html>

The user logging is enters thier username and password, then the results page queries the database based on the username and password then shows all records according to the users "originatorid" I hope you can help, many thanks in advance.

SEAN.

Hey Sean,

I'm not sure exactly what you've got going on in that SQL Statement. It should be more like:

<CODE>
sql = "SELECT * FROM loans WHERE (originatorid='" & Session("originatorid") & "')"
</CODE>

That may work for you this time but it seems to me like you've outgrown the FrontPage database wizards. They're bloated and difficult to customize. I think you're ready to move on to true programming. Congratulations!

http://www.4guysfromrolla.com/webtech/042599-1.shtml

Yup - seand - you're just missing the closing ampersand (&) in your originatorID query.