PDA

View Full Version : Newbie : ASP mail code and SQL

LouG
02-26-2009, 06:25 PM
Hi Folks,

This is my 1st posting here. And let me first say that I'm a newb when it comes to coding. I have taken on a job where I've been asked to do some web development and programming, even though that's not in my skill set. They knew this but still have tasked me with it anyways. So I'm just reading like crazy trying to figure this stuff out.

Background:

I have created a 2 field form (default2.asp). The form has 2 drop down boxes populated by values stored in SQL. The form is a make shift registration form. A trainee chooses their name from the 1st drop down, then chooses and date and time from the next drop down then click a 'register' button.

Those values, and some others are carried over to a confirmation page, and via some SQL, an update is made to to my trainees table. All of this works well. The code I'm sure needs cleaning but it works.

Now I need to email this information to a particular email address. I've got some code that is working on another of our web pages. I've tweaked it so it's passing the appropriate fields but it's not emailing.

I've tried many different ways and have experimented with the syntax but still no luck. Can somebody help me with this???

I would greatly appreciate your assistance. Thanks so much. Here is my code from the confirmation page:

<!--#Include File="functions.asp"-->
<%
Dim options
Dim ls_sql
Dim rstemp
Dim trainee
Dim classInfo
Dim classLoc

'Update the Class and Trainee tables logging their registration
open_calendar_connection()
'ls_sql = "UPDATE tblupgClass1 SET CurrentSeats = CurrentSeats + 1 WHERE UpgClassID = '" & Request("UpgClassID") & "'"
'connDb.execute( ls_sql )
ls_sql = "UPDATE tblupgClass1Trainees SET UpgClassId = '" & Request("UpgClassID") & "' WHERE traineeID = '" & Request("traineeID") & "'"
connDb.execute( ls_sql )

'Pull trainee name for confirmation page
set rstemp = Server.CreateObject( "ADODB.Recordset" )
ls_sql = "SELECT Trainee FROM tblupgClass1Trainees WHERE traineeID = '" & Request("traineeID") & "'"
set rstemp = connDb.execute( ls_sql )
do until rstemp.eof
trainee = rstemp("Trainee")
rstemp.movenext
Loop


'Pull Class Date and Time for confirmation page
set rstemp = Server.CreateObject( "ADODB.Recordset" )
ls_sql = "SELECT ClassDate FROM tblupgClass1 WHERE UpgClassID = '" & Request("UpgClassID") & "'"
set rstemp = connDb.execute( ls_sql )
do until rstemp.eof
ClassInfo = rstemp("ClassDate")
rstemp.movenext
Loop


'Pull Class Location for confirmation page
set rstemp = Server.CreateObject( "ADODB.Recordset" )
ls_sql = "SELECT Location FROM tblupgClass1 WHERE UpgClassID = '" & Request("UpgClassID") & "'"
set rstemp = connDb.execute( ls_sql )
do until rstemp.eof
classLoc = rstemp("Location")
rstemp.movenext
Loop

close_calendar_connection()
%>




<html><head><title>Training Sign-Up Confirmation</title>
<link href="default.css" rel="stylesheet" type="text/css">
</head><body>
<div class="largeText">
<p align="center">Training sign-up Confirmation</p>
</div>
<p align="center"><br>
Thank you.
You have successfully registered for Charge Correction Training!

<%
Dim Mailer, recipient, sender, subject, mailserver

'Beginning of code for mailing of information to ESD

'*** Record values from Form
strTraineeID = request.form("traineeID")

'**** Set up email object
Set Mailer = Server.CreateObject("ASPMail.ASPMailCtrl.1")
recipient = "lou.gonzalez@rochester.edu"
sender = "ESD_webforms@urmc.rochester.edu"
'subject = "Upgrade Training for (" & strTraineeID & ")"
subject = "Upgrade Training for"' (" & trainee & ")"
mailserver = "smtp.urmc.rochester.edu"

'******************* The Message *********************
message = message & "Trainee Name:" & vbTab & strTraineeID & vbCrLf
message = message & vbCrLf
message = message & now() & " " & Request.ServerVariables("REMOTE_ADDR")

'*** Override Header
Mailer.XHeader "Date", FormatDateTime(Date, 1) & " " & FormatDateTime(Time, 4) & " -0400"

%>


<table width="600" border="0" align="center">
<tr>
<td colspan="2"><hr></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td width="221"><p align="right"><font size="-1"><strong>Trainee Name:&nbsp;&nbsp;&nbsp;</strong></font></p></td>
<td width="379"><p><font size="-1">
<%Response.Write(trainee)%>
</font></p></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td><p><font size="-1"></font></p></td>
<td> <p><font size="-1"></font></p></td>
</tr>
<tr>
<td><p align="right"><font size="-1"><strong>Date / Time of Class:&nbsp;&nbsp;&nbsp;</strong></font></p></td>
<td><p><font size="-1">
<%Response.Write(ClassInfo)%>
</font></p></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td><div align="right"><font size="-1"><strong>Class Location:&nbsp;&nbsp;&nbsp;</strong></font></div></td>
<td><font size="-1">
<%Response.Write(ClassLoc)%>
</font></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td colspan="2"><hr></td>
</tr>
<tr>
<td colspan="2"><p><font size="-1">Please print this web page out for your records. If you need
to change the time and/or day of your training, please <a href="mailto:URMCTrainers@urmc.rochester.edu">contact
ESD</a> and
we will unregister you so that you may re-register for a new time and day. </font></p></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td colspan="2"><p><font size="-1"></font></p></td>
</tr>
<tr>
<td colspan="2"><p><font size="-1"></font></p></td>
</tr>
<tr>
<td colspan="2"><form name=exitForm action='http://intranet.urmc-sh.rochester.edu/depts/ESD/'>
<p><font size="-1">
<input name="submit" type="submit" value="Exit">
&nbsp;&nbsp;&nbsp;&nbsp;or&nbsp;&nbsp;&nbsp; <a href="http://intranet.urmc-sh.rochester.edu/depts/ESD">Schedule
another user</a>.</font></p>
</form></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td colspan="2"><font size="-1"><strong>Registration Date Stamp:</strong>
<%Response.Write(now())%>
</font></td>
</tr>
</table>
<p>&nbsp;</p>
<p align="center">&nbsp;</p>
<p><br>
</body></html>
LouG
02-26-2009, 08:46 PM
Looking back on my above post. Maybe I gave too much information. Let me put it this way. Is there some kind of conflict with the mail code and the SQL that's causing the mail code not to work? Or do I have some kind of reference or syntax problem?

Thanks again.
Old Pedant
02-26-2009, 09:23 PM
You have NO CODE at all in there that actually *sends* the email.

You create the mailer object, but you never do anything with it other than set the Mailer.XHeader value.

You don't send it to anyone or from anyone and you don't actually call "send" (or whatever the appropriate method name is for that component).

I've never seen that component before, so I don't know what its properties and methods are. If you could point us to the documentation for it, we could maybe help more.

*************

Your other code needs some work. I can see that it probably is basically okay, but it's badly inefficient.

Example:

'Pull Class Date and Time for confirmation page
ls_sql = "SELECT ClassDate, Location FROM tblupgClass1 WHERE UpgClassID = '" & Request("UpgClassID") & "'"
set rstemp = connDb.execute( ls_sql )
ClassInfo = rstemp("ClassDate")
classLoc = rstemp("Location")
rstemp.Close


See? ONE SQL statement to get BOTH values. And no need for a LOOP because there sure BETTER be one and only one record.

Other minor gotchas in there, but if it ain't too broke, don't fix it yet.
LouG
02-27-2009, 03:54 PM
Oh man I feel silly. Yeah I did a google search and found I was missing the following code:

Result = Mailer.SendMail(mailserver, recipient, sender, subject, message)

It's working like a charm now. Thanks for pointing it out!
Old Pedant
02-27-2009, 09:51 PM
Good. It seems like none of these various mail components can agree on a standardized way of sending. That method makes sense, but it's different than most all the others I've seen.