2. ASPRunnerPro General questions
  3. Sending e-mail in add page of child table with master table fields
This topic is locked

Sending e-mail in add page of child table with master table fields

 		3/16/2006 7:17:37 AM
ASPRunnerPro General questions
orit author

(ASPRunner version 3.2)

I'm using method published in this forum in order to send mail with the content of the new added record :

'send mail

Dim objCDOSYSCon

Set objCDOSYSMail = Server.CreateObject("CDO.Message")

Set objCDOSYSCon = Server.CreateObject ("CDO.Configuration")

objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver"); = "10.11.0.13"

objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport"); = 25

objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing"); = 2

objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"); = 60

objCDOSYSCon.Fields.Update

Set objCDOSYSMail.Configuration = objCDOSYSCon

objCDOSYSMail.From = rs("Owner")

objCDOSYSMail.To = "test@test.com"

objCDOSYSMail.Subject = "New comment #" & rs("AIR_No") & " was posted"

objCDOSYSMail.HTMLBody = "<table cellpadding=6 border=1 cellspacing=0 bordercolor=white bgcolor=efefef><tr><td><font face=arial size=2>Comment Date</td><td><font face=arial size=2>" & rs("Owner_Comment_Date") & "</td></tr><tr><td><font face=arial size=2>Comment</td><td><font face=arial size=2>" & rs("Owner_Comment") & "</td></tr></table>"
objCDOSYSMail.Send

Set objCDOSYSMail = Nothing

Set objCDOSYSCon = Nothing



The Added record is added to a child table. In the e-mail, I would like to send in the also content from the master table of this child table. how to do this?
Thanks
Admin 3/16/2006

Hi,
you can do it in the following way:

set rstemp = Server.CreateObject ("ADODB.Recordset")

rstemp.open "select * from mastertable where id=" & Session(strTableName & "_masterkey"), dbConnection
strMessage=" Field1: " & rs("Field1")



Now you can use rstemp("FieldName") to access master table field values.
orit author 3/16/2006

I added the code:

set rstemp = Server.CreateObject ("ADODB.Recordset")
rstemp.open "select * from AIR_All where id=" & Session(strTableName & "_masterkey"), dbConnection


and in my message:

objCDOSYSMail.HTMLBody = "<table cellpadding=6 border=1 cellspacing=0 bordercolor=white bgcolor=efefef><tr><td><font face=arial size=2>Comment Date</td><td><font face=arial size=2>" & rs("Owner_Comment_Date") & "</td></tr><tr><td><font face=arial size=2>Comment Date</td><td><font face=arial size=2>" & rstemp("Description") & "</td></tr><tr><td><font face=arial size=2>Comment</td><td><font face=arial size=2>" & rs("Owner_Comment") & "</td></tr></table>"



and I'm getting the error:

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'id='.
/Marketing/Applications/MAIR/Comments_add.asp, line 348


What is wrong?
Thanks a lot
Admin 3/17/2006

Hi,
you can try to replace

Session(strTableName & "_masterkey")



with

rs("ForeignKey")



where ForeignKey is field name of foriegn key in the child table.
orit author 3/17/2006

Now the code looks like:

set rstemp = Server.CreateObject ("ADODB.Recordset")
rstemp.open "select * from AIR_All where id=" & rs("AIR_No"), dbConnection


"AIR_NO" is the primary key in the master table and the foreign key in the child table.
and I'm still getting the same error...
What is the problem?
Thanks
Admin 3/19/2006

Make sure recordset RS is open when this code is called.
orit author 3/20/2006

How to do that (Make sure recordset RS is open when this code is called)?
Thanks
Admin 3/20/2006

Make sure recordset RS is open when this code is called.


This code snippet needs to be placed after rs.Open and before rs.Close.
Please note that we do not provide help on general programming questions.
orit author 3/22/2006

I made sure that the code is placed when RS is opened. I'm getting now the error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.
/Marketing/Applications/MAIR/Comments_add.asp, line 643


What can be the reason?
Thanks
Admin 3/23/2006

The error occurs because you try to access a field in the fields collection that is not in the Recordset.

Ensure that your field name is present in Recordset.
And please post Comments_add.asp, line 643 here.
orit author 3/23/2006

Line 643 was:

rstemp.open "select * from AIR_All where id=" & rs("AIR_No"), dbConnection


I added to the strSQL the field AIR_NO that wasn't there:

strSQL="select " & AddWrappers("CommentID") & ", " & AddWrappers("AIR_NO") & ", " & AddWrappers("Owner") & ", " & AddWrappers("Owner_Comment_Date") & ", " & AddWrappers("Owner_Comment") & ", " & AddWrappers("Send_Notification") & ", "


Now I'm getting the error:

error '80020009'

Exception occurred.
/Marketing/Applications/MAIR/Comments_add.asp, line 643


again, line 643 is:

rstemp.open "select * from AIR_All where id=" & rs("AIR_No"), dbConnection


What is the problem?
Thanks
Admin 3/24/2006

Orit,
this code won't work on the Add page.
orit author 3/26/2006

So no way to do that?
Thanks
Admin 3/26/2006

It impossible to tell you what's wrong without seeing the whole code.
I recommend you to switch to ASPRunnerPro 4.0 where you can use BeforeAdd event and Send email with new data record action that does exactly what you need.
orit author 6/27/2006

OK, I'm working with ASPRunner 4.0.

I got the default code after selecting the event: Before record added and the action: Send e-mail with new data.

I want that the e-mail will be sent only if a checkbox in the add form is checked and to add to the message fields from this table and from other table,
How to do that?
Thanks a lot

Default code below...

Function BeforeAdd(dict)
' Parameters:

' dict - Scripting.Dictionary object.

' Each field on the Add form represented as 'Field name'-'Field value' pair
'********** Send email with new data ************

' do not forget to setup email parameters like From, SMTP server etc

' on 'Security->User login settings' dialog
message =""
keys = dict.keys

For n = 0 To dict.Count-1

message = message & keys(n) & " : " & dict(keys(n)) & vbcrlf

Next



email="test@test.com"

subject="New data record"



sendmail email, subject, message
BeforeAdd = True
' set BeforeAdd to True if you like to proceed with adding new record

' set it to False in other case
End Function