By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,331 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Update Form and use RS to generate CDO Email

P: 5
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

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Set Conn = Server.CreateObject("ADODB.Connection")
  3. conn.Provider="Microsoft.Jet.OLEDB.4.0"
  4. conn.Open "c:/inetpub/wwwroot/change control.mdb"
  5.  
  6.  
  7.  
  8.  
  9. strSQL = "UPDATE tblform SET" & _
  10.     " PCM = " & "'" & Replace(Request.Form("PCM"),"'","''") & "'" & _
  11.     ", PCMAction = " & "'" & Request.Form("PCMAction") & "'" & _
  12.     ", PCMReason = " & "'" & Replace(Request.Form("PCMReason"),"'","''") & "'" & _
  13.     ", Approve1 = " & "'" & Request.Form("Approve1") & "'" & _
  14.     ", 1Action = " & "'" & Request.Form("1Action") & "'" & _
  15.     ", 1Reason = " & "'" & Replace(Request.Form("1Reason"),"'","''") & "'" & _
  16.     ", Approve2 = " & "'" & Request.Form("Approve2") & "'" & _
  17.     ", 2Action = " & "'" & Request.Form("2Action") & "'" & _
  18.     ", 2Reason = " & "'" & Replace(Request.Form("2Reason"),"'","''") & "'" 
  19.  
  20.     IF Request.Form("2Action") <> "" THEN
  21.     strSQL=strSQL & ", Status = '"& Request.Form("2Action") &"' "
  22.     End If
  23.     strSQL = strSQL & " WHERE ccf = " & Request.Form("ccf") & ";"
  24.  
  25. Conn.execute(strSQL)
  26.  
  27. idOfUpdatedRecord = Request.Form("CCF")
  28. %>
  29.  
  30. <CENTER><H2>
  31. <font face="InfoText Bd" color="#0388BB">Thank you, the status of Change <% = idOfUpdatedRecord %> has been successfully updated.</font></H2>
  32. <%Conn.Close%>
Expand|Select|Wrap|Line Numbers
  1. This is my code to the query the database to retrieve all the details from the database and then use this to email:
  2.  
  3.  
  4. Code:
  5. <% IF Request.Form("2Action")="Approved" or Request.Form("2Action")="Rejected" THEN
  6. Set Conn = Server.CreateObject("ADODB.Connection")
  7. conn.Provider="Microsoft.Jet.OLEDB.4.0"
  8. conn.Open "c:/inetpub/wwwroot/change control.mdb"
  9. set rs=Server.CreateObject("ADODB.recordset")
  10. ' Set SQL statement
  11. SQL = "SELECT * FROM tblform WHERE ccf = " & Request.Form("ccf") & " "
  12.  
  13. ' Open Recordset Object
  14. rs.Open strSQL,conn
  15.  
  16.  
  17.  
  18.  
  19.  
  20. DIM strEmail, strOriginator, strOriginEmail, StrRep, strStartDate, strCCF, strBody, strStatus
  21. strEmail=RS("Email")
  22. strOriginator=Request.Form("Originator")strOriginEmail=Request.Form("Originator_Email")
  23. strRep=Request.Form("Rep")
  24. strSummary=Request.Form("Summary")
  25. strImplementer=Request.Form("Implementer")
  26. strStartDate=Request.Form("StartDate")
  27. strStatus=Request.Form("2Action")
  28. strStart=Request.Form("Start")
  29. strRisk=Request.Form("Risks")
  30. strCCF= Request.Form("CCF")
  31. strBody="<font size=3 face=Helvetica Neue color=#0388BB>" & "<strong>" & "CCF " & strCCF & " has been " & strStatus & "<br>" & "<br>" 
  32. strBody=strBody & "<font size=2 face=Helvetica Neue color=#260063>" & "<strong>"& "Start Date: " & strStartDate & "<br>" 
  33. strBody=strBody & "Start Time: " & strStart & "<br>"  
  34. strBody=strBody & "Change Summary: "  & strSummary & "<br>"  
  35. strBody=strBody & "Implementer: " & strImplementer & "<br>" & "<br>" & "<br>" 
  36. strBody=strBody & "<font size=3 face=Helvetica Neue color=#0388BB>" & "<strong>" & "Please contact Change@xxxx.com if you require further information" & "<br>" 
  37. strBody=strBody & "or Click here to view full details " & "<a href=http://127.0.0.1/view.asp?qryid="& StrCCF & ">"& strCCF & "</a>"
  38.  
  39.  
  40. Set Mail=Server.CreateObject("CDO.Message")
  41. 'Next lines of code are for XP PWS only:
  42. Set Config = CreateObject("CDO.Configuration") 
  43.  
  44.  'Configuration: 
  45.  Config.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing")  = 1
  46.  Config.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory") _
  47.   = "c:\inetpub\mailroot\pickup" 
  48.  
  49.  
  50.  'Update configuration 
  51.  Config.Fields.Update 
  52.  Set Mail.Configuration = Config 
  53.  
  54. Mail.From="Change@xxxxx.com"    
  55. Mail.To="changex@xxxxx.com;"& strOriginEmail & ";" & strEmail
  56. Mail.CC=strRep
  57. Mail.Subject="CCF " & strCCF & " has been "  & strStatus 
  58. Mail.HTMLBody=strBody 
  59. Mail.Send
  60. Set Mail=Nothing
  61. End If
  62. Conn.Close
  63. %>
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:
Jul 24 '06 #1
Share this Question
Share on Google+
2 Replies


sashi
Expert 100+
P: 1,754
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.. :)
Jul 25 '06 #2

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. SQL = "SELECT * FROM tblform WHERE ccf = " & Request.Form("ccf") & " "
  2.  
  3. ' Open Recordset Object
  4. rs.Open strSQL,conn
I have changed StrSQL to match SQL and now everything works great!
Jul 25 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.