Update Form and use RS to generate CDO Email

[devine]

Hi All,
I am trying to send an automatic email when an update has been made.
My update statement will updates 6 fields, and dependant on one of the fields, I would like to send an email using CDO.
Once the update is made, I am trying to re-query the database to retrieve all the fields that need to be included in the email, but it's just not working for me!!

This my code to update

<%
Set Conn = Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/inetpub/wwwroot/change control.mdb"
 
 
 
 
strSQL = "UPDATE tblform SET" & _
    " PCM = " & "'" & Replace(Request.Form("PCM"),"'","''") & "'" & _
    ", PCMAction = " & "'" & Request.Form("PCMAction") & "'" & _
    ", PCMReason = " & "'" & Replace(Request.Form("PCMReason"),"'","''") & "'" & _
    ", Approve1 = " & "'" & Request.Form("Approve1") & "'" & _
    ", 1Action = " & "'" & Request.Form("1Action") & "'" & _
    ", 1Reason = " & "'" & Replace(Request.Form("1Reason"),"'","''") & "'" & _
    ", Approve2 = " & "'" & Request.Form("Approve2") & "'" & _
    ", 2Action = " & "'" & Request.Form("2Action") & "'" & _
    ", 2Reason = " & "'" & Replace(Request.Form("2Reason"),"'","''") & "'" 
 
    IF Request.Form("2Action") <> "" THEN
    strSQL=strSQL & ", Status = '"& Request.Form("2Action") &"' "
    End If
    strSQL = strSQL & " WHERE ccf = " & Request.Form("ccf") & ";"
 
Conn.execute(strSQL)
 
idOfUpdatedRecord = Request.Form("CCF")
%>
 
<CENTER><H2>
<font face="InfoText Bd" color="#0388BB">Thank you, the status of Change <% = idOfUpdatedRecord %> has been successfully updated.</font></H2>
<%Conn.Close%>

This is my code to the query the database to retrieve all the details from the database and then use this to email:
 
 
Code:
<% IF Request.Form("2Action")="Approved" or Request.Form("2Action")="Rejected" THEN
Set Conn = Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/inetpub/wwwroot/change control.mdb"
set rs=Server.CreateObject("ADODB.recordset")
' Set SQL statement
SQL = "SELECT * FROM tblform WHERE ccf = " & Request.Form("ccf") & " "
 
' Open Recordset Object
rs.Open strSQL,conn
 
 
 
 
 
DIM strEmail, strOriginator, strOriginEmail, StrRep, strStartDate, strCCF, strBody, strStatus
strEmail=RS("Email")
strOriginator=Request.Form("Originator")strOriginEmail=Request.Form("Originator_Email")
strRep=Request.Form("Rep")
strSummary=Request.Form("Summary")
strImplementer=Request.Form("Implementer")
strStartDate=Request.Form("StartDate")
strStatus=Request.Form("2Action")
strStart=Request.Form("Start")
strRisk=Request.Form("Risks")
strCCF= Request.Form("CCF")
strBody="<font size=3 face=Helvetica Neue color=#0388BB>" & "<strong>" & "CCF " & strCCF & " has been " & strStatus & "<br>" & "<br>" 
strBody=strBody & "<font size=2 face=Helvetica Neue color=#260063>" & "<strong>"& "Start Date: " & strStartDate & "<br>" 
strBody=strBody & "Start Time: " & strStart & "<br>"  
strBody=strBody & "Change Summary: "  & strSummary & "<br>"  
strBody=strBody & "Implementer: " & strImplementer & "<br>" & "<br>" & "<br>" 
strBody=strBody & "<font size=3 face=Helvetica Neue color=#0388BB>" & "<strong>" & "Please contact [e-mail] if you require further information" & "<br>" 
strBody=strBody & "or Click here to view full details " & "<a href=http://127.0.0.1/view.asp?qryid="& StrCCF & ">"& strCCF & "</a>"
 
 
Set Mail=Server.CreateObject("CDO.Message")
'Next lines of code are for XP PWS only:
Set Config = CreateObject("CDO.Configuration") 
 
 'Configuration: 
 Config.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing")  = 1
 Config.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory") _
  = "c:\inetpub\mailroot\pickup" 
 
 
 'Update configuration 
 Config.Fields.Update 
 Set Mail.Configuration = Config 
 
Mail.From="[e-mail]"    
Mail.To="[e-mail];"& strOriginEmail & ";" & strEmail
Mail.CC=strRep
Mail.Subject="CCF " & strCCF & " has been "  & strStatus 
Mail.HTMLBody=strBody 
Mail.Send
Set Mail=Nothing
End If
Conn.Close
%>

If I query the database and try to use RS, I get the ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/approval.asp, line 46 error
I do not get an error if I use Request.Form, but then there is no data populated for the email with the exception of the status and the ID number because they were request.form update fields!

I have checked for spelling errors when using RS, but still receive the
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/approval.asp, line 46 error
Thanks in advance for any help! :confused:

[sashi]

Hi there,

"ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal"

this error message means that one ore more field name is not found or not same in your table.. check your field names once again.. good luck my fren.. take care.. :)

[devine]

Hi there,

"ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal"

this error message means that one ore more field name is not found or not same in your table.. check your field names once again.. good luck my fren.. take care.. :)

Many thanks Sashi,

The problem was with this line of code:

SQL = "SELECT * FROM tblform WHERE ccf = " & Request.Form("ccf") & " "
 
' Open Recordset Object
rs.Open strSQL,conn

I have changed StrSQL to match SQL and now everything works great!