ASP: Personal Messaging

[Lancaster]

Could someone tell me how I can let only users messages stored in a database which were sent to specific members can pull them out of the database without them reading messages not meant for them.

I think its something to do with session variables?

[rdove]

If you are storing a Session, yes, you can do this.

Session("User") = User

Then you would use the session in your SQL statement:

SQL = "Select * from Messages Where User = '" & Session("User") & "';"

[Lancaster]

So, just to make sure this works.

If I insert that code into the page, how would I make the dynamic text pull out information from the database that was meant for that specific user?

I use Ultradev, and its quite confusing because it gives you options to create a log in page and pull information out of a database, but I want only to pull authorised information out of the database for the ability of users to read their personal messages and to edit their profiles.

Unfortunately, ultradev doesn't make it simple. I have noticed things like request. and session variables but i don't know how to use them in ultradev.

more info on this would be appreciated.

[putts]

So, it sounds like your quite the beginner to the wonderful world of ASP. So, I'll try to give you a few of the basics and I'm sure Ryan can help you out with this too.

First, request variables (as they are the heart and soul of ALL Server Side Languages)
Let's say you have your login screen that needs two input boxes (user name/password) so you'd set that up like so:

<form name="loginForm" action="runLogin.asp" method="post">
User Name: <input type="text" name="userName">
Password: <input type="password" name="pwd">
<input type="submit" value="Login">
</form>


Now, when that form gets submitted it sends the information in those two input boxes to runLogin.asp as request variables which can be used, for instance, to query your SQL Server like so:


<%
dim db
dim recSet
dim strQuery
'Assumed instantiation of your database variable here

strQuery = "SELECT USER_ID FROM USER_TABLE WHERE USER_NAME = '" & request("UserName") & "' AND PASSWORD = '" & request("pwd") & "'"
set recSet = db.execute(strQuery)

'so, using the request collection we're able to get what the user typed into those boxes
'and query our SQL tables by them

'Now, we check to see if that username/pwd combo was found in the table or not
'Note: this has changed in .NET which Ryan could tell you about
if not(recSet.eof) then
'if the recordset isn't at the End of File yet, that means there's gotta be a record
'that matched our username/pwd combo, so we'll now set a Session variable
'now that we've verified that the user is valid

Session("User_ID") = recSet("USER_ID")
else
'if it's not a valid combo, we boot them back to the login screen
'In this case, with no clue what they've done wrong
Response.redirect("login.html")
end if
%>


So, there's a quick intro into using request variables and then setting Session variables.
In this case, if all has gone well, the Session("User_ID") variable now contains the primary key value from the USER_TABLE which is what you should use in the rest of your tables to assign messages to certain users.

Now, what Ryan said earlier may make more sense.

[Lancaster]

A big thanks for you help!!!!!!!!!!

I finally solved it! The only problem now is with my personal messaging script. It says theres a error in the script but I can't figure out whats causing it.

I'll post it up later, if you can help. I'd be ever so grateful.

[rdove]

hmm..let me take a guess

Error 500
Internal Server Error

To get rid of this:

Tools
--->Internet Options
------>Click the Advanced Tab
-------->Uncheck "Show Friendly http errors"
---------->OK & Refresh

It will now tell you the error and line number.

Hope thats helps fix that problem

[Lancaster]

Hi thanks for that, but there's an actual error I can't fix.

Could someone help me with my ASP problem, I keep getting an error with a page:

The error says:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

/aldridgeschool/pmwriteteacher.asp, line 112

---------------------------------------
THIS IS THE PAGE WITH THE ERROR:

<%@LANGUAGE="VBSCRIPT"%><%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers="admin,teacher,pupil"
MM_authFailedURL="login.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
If (false Or CStr(Session("MM_UserAuthorization"))="") Or _
(InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
MM_grantAccess = true
End If
End If
If Not MM_grantAccess Then
MM_qsChar = "?"
If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
MM_referrer = Request.ServerVariables("URL")
if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
Response.Redirect(MM_authFailedURL)
End If
%>
<!--#include file="Connections/Members.asp" -->

<%
' *** Edit Operations: declare variables

MM_editAction = CStr(Request("URL"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>

<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) <> "") Then

MM_editConnection = MM_Members_STRING
MM_editTable = "PM"
MM_editRedirectUrl = "completed.asp"
MM_fieldsStr = "To|value|Subject|value|Message|value|Account|value|Username|value"
MM_columnsStr = "Username|',none,''|Subject|',none,''|Message|',none,''|Account|',none,''|By|',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>

<%
' *** Insert Record: construct a sql insert statement and execute it

If (CStr(Request("MM_insert")) <> "") Then

' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),",")
Delim = MM_typeArray(0)
If (Delim = "none") Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none") Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none") Then EmptyVal = ""
If (FormVal = "") Then
FormVal = EmptyVal
Else
If (AltVal <> "") Then
FormVal = AltVal
ElseIf (Delim = "'") Then ' escape quotes
FormVal = "'" & Replace(FormVal,"'","''") & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>

<HTML>
<HEAD>
<STYLE type="text/css"><!--BODY {SCROLLBAR-FACE-COLOR: #FF0000; SCROLLBAR-HIGHLIGHT-COLOR: ; SCROLLBAR-SHADOW-COLOR: #000000; SCROLLBAR-3DLIGHT-COLOR: #000000; SCROLLBAR-ARROW-COLOR: #000000; SCROLLBAR-TRACK-COLOR: #FFFFFF; SCROLLBAR-DARKSHADOW-COLOR: #000000; SCROLLBAR-BASE-COLOR: #FFFFFF}
--></STYLE>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="description" content="Aldridge School, Tynings Lane website for pupils to come and reunite with oneanother">
<meta name="keywords" content="aldridge school, aldridge school united, tynings lane, school, senior school, friends reunited, claaa, chat, message boards, walsall, aldridge, england, eductation, contact, email">
<TITLE>Aldridge School United</TITLE>
<style fprolloverstyle>A:hover {color: #0000FF; font-family: Verdana}
</style>
</HEAD>
<BODY BGCOLOR=#FFFFFF LEFTMARGIN=0 TOPMARGIN=0 MARGINWIDTH=0 MARGINHEIGHT=0 text="#000000" link="#000000" vlink="#000000" alink="#000000">
<!-- ImageReady Slices (Header2.bmp) -->
<div align="center">
<center>
<TABLE WIDTH=700 BORDER=0 CELLPADDING=0 CELLSPACING=0>

<TR>

<TD COLSPAN=3>
<IMG SRC="images/index_01.gif" width=700 height=101 ALT=""></TD>
</TR>

<TR>

<TD>
<img border="0" src="images/left.gif" width="86" height="15"></TD>
<TD>
<IMG SRC="images/index_03.gif" WIDTH=526 HEIGHT=9 ALT=""></TD>
<TD background="images/right.gif">
<IMG SRC="images/index_04.gif" width=88 height=9 ALT=""></TD>
</TR>

<TR>

<TD background="images/left.gif">
<!--#include file="navigation.asp" -->
</TD></center>
<TD>

<p align="center"><img border="0" src="images/pm.gif"><br>
-- <font face="Verdana" size="2">To Teacher Account --</font><br>
<!--#include file="pmnav.asp" --> </p>

<p align="center"><font face="Verdana" size="2">Please write Personal
Message and click send:</font></p>
<div align="center">

<table border="0" width="74%">

<tr>

<td width="100%" style="background-color: #F2F2F2; border: 1 solid #000080">

<form ACTION="<%=MM_editAction%>" method="POST" name="PmWrite">

<div align="center">

<table border="0" width="100%">

<tr>

<td width="2%">

<p align="right"><font face="Verdana" size="2"><b>To:</b></font>
</td><center>
<td width="98%">
<input type="text" name="To" size="20" style="font-family: verdana; font-size: 10pt; border-style: inset; border-color: #000080">
<font face="Verdana" size="2"><a href="helpsearch.asp">Help</a></font></td>
</tr>
</center>

<tr>

<td width="30%">

<p align="right"><font face="Verdana" size="2"><b>Subject:</b></font>
</td><center>
<td width="70%">
<input type="text" name="Subject" size="20" style="font-family: verdana; font-size: 10pt; border-style: inset; border-color: #000080">
</td>
</tr>

<tr>

<td width="100%" colspan="2">

<p align="center"><b><font face="Verdana" size="2">Message:</font></b>
</td>
</tr>

<tr>

<td width="100%" colspan="2">

<p align="center">
<textarea rows="6" name="Message" cols="47" style="font-family: verdana; font-size: 10pt; border-style: inset; border-color: #000080"></textarea>
</td>
</tr>
</center>

<tr>

<td width="100%" colspan="2">

<p align="center">
<input type="submit" value="Send Personal Message" name="Send" style="font-family: verdana; font-size: 10pt; font-weight: bold; background-color: #FFFFFF; border-style: outset; border-color: #000080">
</td>
</tr>

</table>
</div>
<input type="hidden" name="Account" value="<%= Session("MM_UserAuthorization") %>">
<input type="hidden" name="Username" value="<%= Session("MM_Username") %>">
<input type="hidden" name="MM_insert" value="true">


</form>
</td>
</tr>

</table>
</div>
<p align="center">&nbsp;</p>
<center>
<p align="center"><a href="chat.asp"><img border="0" src="images/chathere.GIF" alt="Click Here To Chat" width="261" height="72"></a></p>
</center>
</TD><center>
<TD background="images/right.gif">

<p align="center">&nbsp;</p>
</TD>
</TR>
</TABLE></center>
</div>
<!-- End ImageReady Slices -->
<p align="center"><!--#include file="disclaimer.asp" --></p>
</BODY>
</HTML>

Help, very much appreciated :)

[putts]

Haven't had the time to sit and look at your whole piece of code yet, but whenever I get an error based off a SQL statement I use the following to try to figure it out:


'just before the database execute statement, put this
Response.write(sqlStatement)
Response.end


The response.end will stop the rest of the page from processing, thus your sql statement won't get the chance to cause the error and the last thing outputed to your page should be that sql statement so you can look it over and verify that it doesn't have any serious faults.

Also, if you get that sql statement and still dont know what's wrong, post it out here and then we can look at it too.

[Lancaster]

Thanks for helping,

I've been spending some time on it and figured out that I want the form to enter it into the database but I'm executing session variables into hidden objects i nthe form and thats causing an error.

I don't understand why but it is cos when i remove them, it works

[rdove]

I think I may see your problem:

"insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"


Are MM_dbValues string values?

you may need to surround your values in single quotes like this:

"insert into " & MM_editTable & " (" & MM_tableValues & ") values ('" & MM_dbValues & "')"

[Lancaster]

They look the same but my eyeseight's poop,
I'll give it ago thanks!

Thank you very much for helping!
(I hope this works!)

[Lancaster]

I figured that the scrripting conflicted with my form names and table names.

Its sorted now but it took a while.

thanks for your help.
i need to know more about variables though :)

[putts]

You need anything information in particular?
Like:
Session Variables
Variable Scopes
Variable Cleanup
Request Variables (aka, Form Variables)
Server Variables

Let us know what you're looking for.