CDO to Database and Email [Archive]

Click to See Complete Forum and Search --> : CDO to Database and Email

Shona Smith

08-03-2006, 09:18 AM

Hi

I have the following code which submits the information to a database and sends the mail but doesn't include in the mail the information that would have been typed into it. The only thing in the mail is Name:

<%
' FP_ASP ASP Automatically generated by a Frontpage Component. Do not Edit.
On Error Resume Next

strErrorUrl = ""

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear

Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"

Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"

fp_conn.Open Application("name_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"

fp_rs.Open "Results", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"

fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to the database"
Dim arFormFields0(1)
Dim arFormDBFields0(1)
Dim arFormValues0(1)

arFormFields0(0) = "sName"
arFormDBFields0(0) = "sName"
arFormValues0(0) = Request("sName")

FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0

FP_SaveFieldToDB fp_rs, Now, "Timestamp"

fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"

fp_rs.Close
fp_conn.Close

FP_FormConfirmation "text/html; charset=windows-1252",_
"Form Confirmation",_
"Thank you for submitting the following information:",_
"noschema.asp",_
"Return to the form."

End If
End If

%>



<%

Dim sTextBody
Dim sName

Set cdoConfig = CreateObject("CDO.Configuration")

sTextBody = sTextBody & "Name: " & Request("sName") & VbCrLf

' sTextBody = sTextBody & "Name: " & FP_SavedFields("sName") & VbCrLf

With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "my.server.com"
.Update
End With

Dim objMail
'Create the mail object
Set objMail = Server.CreateObject("CDO.Message")
'Set key properties
objMail.From = "email@aol.com"
objMail.To = "email@aol.com"
objMail.Subject = "Software Required"
objMail.TextBody = sTextBody

objMail.Send

' End If

Set cdoMessage = Nothing
Set cdoConfig = Nothing
%>

<p> </p>
<p> </p>
<form method="POST" action="--WEBBOT-SELF--">
<input TYPE="hidden" NAME="VTI-GROUP" VALUE="0">
<p>
<input type="text" name="sName" size="20"></p>
<p> </p>
<p> </p>
<p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>

any help would be great thanks

kayaker411

08-04-2006, 03:10 PM

THat's because of this line:
sTextBody = sTextBody & "Name: " & Request("sName") & VbCrLf

You need to Resquest("theRestOfTheBody")
and concatenate.

Shona Smith

08-07-2006, 03:33 AM

Thanks

I only have one field in the form to simplify it. If I can get it to work with one field then hopefully it would be easy to do it with other fields.

I'm beginning to think it's not possible to use CDO with a database connection as well.

Cheers Shona

kayaker411

08-13-2006, 10:43 PM

Your original post says that the email is sent but only includes the name field. The solution is to add the rest of the firleds? What's the question?

Shona Smith

08-15-2006, 06:29 AM

Hi

I have now discovered why it is just the field names that are in the mail, which I hadn't before.

It's because as soon as I browse the file it sends a mail before I even hit submit.

Unfortunately I'm still stuck because even if I fill in the field and then hit submit the information still doesn't come through in the mail.

Still hoping someone can help

Cheers Shona

kayaker411

08-15-2006, 08:48 AM

Is it possible to include the ASP page you're working with?

Shona Smith

08-15-2006, 09:06 AM

Definitely thanks

<%
' FP_ASP ASP Automatically generated by a FrontPage Component. Do not Edit.

On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Session.CodePage = 1252
Err.Clear

strErrorUrl = ""

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear

Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"

Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"

fp_conn.Open Application("name_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"

fp_rs.Open "Results", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"

fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to the database"
Dim arFormFields0(2)
Dim arFormDBFields0(2)
Dim arFormValues0(2)

arFormFields0(0) = "Department"
arFormDBFields0(0) = "Department"
arFormValues0(0) = Request("Department")
arFormFields0(1) = "Name"
arFormDBFields0(1) = "Name"
arFormValues0(1) = Request("Name")

FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0

FP_SaveFieldToDB fp_rs, Now, "Timestamp"

fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"

fp_rs.Close
fp_conn.Close

FP_FormConfirmation "text/html; charset=windows-1252",_
"Form Confirmation",_
"Thank you for submitting the following information:",_
"cdoscript.asp",_
"Return to the form."

End If
End If

Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")

%>
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Software Requirements Form</title>
</head>

<body>


<%

Dim TextBody
Dim Name
Dim Department

Set cdoConfig = CreateObject("CDO.Configuration")

TextBody = TextBody & "Name " & Request("Name").Item & VbCrLf
TextBody = TextBody & "Department " & Request("Department").Item & VbCrLf

With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "my.server.name.com"
.Update
End With

Dim objMail
'Create the mail object
Set objMail = Server.CreateObject("CDO.Message")
'Set key properties
objMail.From = "email@aol.com"
objMail.To = "email@aol.com"
objMail.Subject = "Software Required"
objMail.TextBody = TextBody

objMail.Send

Set cdoMessage = Nothing
Set cdoConfig = Nothing

%>

<p align="center"><b><font size="6" face="Arial">Software Requirements Form</font></b><p> </p>
<p> </p>
<form method="POST" action="--WEBBOT-SELF--">
<input TYPE="hidden" NAME="VTI-GROUP" VALUE="0">

<table CELLSPACING="0" CELLPADDING="0" BORDER="0" WIDTH="530" id="table1">
<tr>
<td WIDTH="180" valign="top"><font face="Arial">Name</font></td>
<td WIDTH="350" colspan="3">
<input type="text" name="Name" size="20"></td>
</tr>
<tr>
<td WIDTH="180" valign="top"> </td>
<td WIDTH="80"> </td>
<td WIDTH="10"> </td>
<td WIDTH="80"> </td>
</tr>
<tr>
<td WIDTH="180" valign="top">Department</td>
<td WIDTH="350" colspan="3">
<input type="text" name="Department" size="20"></td>
</tr>
</table>
<p>                                            <input type="submit" value="Submit" name="B1"></p>
</form>

</body>

</html>

Cheers Shona

kayaker411

08-16-2006, 12:11 AM

Shona,
Just for giggles try this code. Save a backup copy of your existing code first. I just moved the email code up into the head and added a condition around it. It shouldn't be there. Lemme know what happens.

<%
' FP_ASP ASP Automatically generated by a FrontPage Component. Do not Edit.

On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Session.CodePage = 1252
Err.Clear

strErrorUrl = ""

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear
Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"

Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"

fp_conn.Open Application("name_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"

fp_rs.Open "Results", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"

fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to the database"
Dim arFormFields0(2)
Dim arFormDBFields0(2)
Dim arFormValues0(2)

arFormFields0(0) = "Department"
arFormDBFields0(0) = "Department"
arFormValues0(0) = Request("Department")
arFormFields0(1) = "Name"
arFormDBFields0(1) = "Name"
arFormValues0(1) = Request("Name")

FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0

FP_SaveFieldToDB fp_rs, Now, "Timestamp"

fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"

fp_rs.Close
fp_conn.Close

FP_FormConfirmation "text/html; charset=windows-1252",_
"Form Confirmation",_
"Thank you for submitting the following information:",_
"cdoscript.asp",_
"Return to the form."
End If
End If

Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")

if Len(request.form("Department")) > 0 then
Dim TextBody
Dim Name
Dim Department

Set cdoConfig = CreateObject("CDO.Configuration")

TextBody = TextBody & "Name " & Request.form("Name") & VbCrLf
TextBody = TextBody & "Department " & Request.form("Department") & VbCrLf

With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "my.server.name.com"
.Update
End With

Dim objMail
'Create the mail object
Set objMail = Server.CreateObject("CDO.Message")
'Set key properties
objMail.From = "email@aol.com"
objMail.To = "email@aol.com"
objMail.Subject = "Software Required"
objMail.TextBody = TextBody
objMail.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing
else
response.write "No value in Department field"
end if

%>
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Software Requirements Form</title>
</head>

<body>


<p align="center"><b><font size="6" face="Arial">Software Requirements Form</font></b><p> </p>
<p> </p>
<form method="POST" action="--WEBBOT-SELF--">

<input TYPE="hidden" NAME="VTI-GROUP" VALUE="0">



<table CELLSPACING="0" CELLPADDING="0" BORDER="0" WIDTH="530" id="table1">
<tr>
<td WIDTH="180" valign="top"><font face="Arial">Name</font></td>
<td WIDTH="350" colspan="3">
<input type="text" name="Name" size="20"></td>
</tr>
<tr>
<td WIDTH="180" valign="top"> </td>
<td WIDTH="80"> </td>
<td WIDTH="10"> </td>
<td WIDTH="80"> </td>
</tr>
<tr>
<td WIDTH="180" valign="top">Department</td>
<td WIDTH="350" colspan="3">
<input type="text" name="Department" size="20"></td>
</tr>
</table>
<p>                                            <input type="submit" value="Submit" name="B1"></p>
</form>

</body>

</html>

Shona Smith

08-16-2006, 02:08 AM

Thanks tried that no mail was received. The information was submitted to the database.

Cheers Shona

kayaker411

08-16-2006, 10:43 AM

Cool. Remove the conditional from around the CDO code and try it again.

Shona Smith

08-16-2006, 10:58 AM

I wish it was cool or even hot! Still no mail I'm afraid By conditional I think you mean " " so tried that and removing ()

<%

Dim TextBody
Dim Name
Dim Department

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then

Set cdoConfig = CreateObject(CDO.Configuration)

TextBody = TextBody & "Name " & Request.Form("Name") & VbCrLf
TextBody = TextBody & "Department " & Request.Form("Department") & VbCrLf

With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "rb-smtp-int.bosch.com"
.Update
End With

Dim objMail
'Create the mail object
Set objMail = Server.CreateObject(CDO.Message)
'Set key properties
objMail.From = "Shona.Smith1@uk.bosch.com"
objMail.To = "Shona.Smith1@uk.bosch.com"
objMail.Subject = "Software Required"
objMail.TextBody = TextBody

objMail.Send

End if
Set cdoMessage = Nothing
Set cdoConfig = Nothing

%>

Cheers Shona

kayaker411

08-16-2006, 12:56 PM

You've added :
"If Request.ServerVariables("REQUEST_METHOD") = "POST" Then"

You'll need to remove this end if too :

End if
Set cdoMessage = Nothing
Set cdoConfig = Nothing
(incidentally those Set statements should appear inside the end if statement or you'll get an error when the condition is false ie Not a Post)

If the above didn't give you an error it means that the CDO code is getting hit. I would remove this from the top of your page:

"On Error Resume Next"

and execute the page again. Post the error message if you need help interpreting it.

Rich

Shona Smith

08-17-2006, 03:30 AM

Hi

Cheers for that still no luck this is what I now have. No error message no mail. If I remove

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then

then a post arrives before hitting submit

<%
' FP_ASP ASP Automatically generated by a Frontpage Component. Do not Edit.
'On Error Resume Next

strErrorUrl = ""

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then

If Request.Form("VTI-GROUP") = "0" Then
Err.Clear

Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"

Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"

fp_conn.Open Application("name_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"

fp_rs.Open "Results", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"

fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to the database"
Dim arFormFields0(2)
Dim arFormDBFields0(2)
Dim arFormValues0(2)

arFormFields0(0) = "Department"
arFormDBFields0(0) = "Department"
arFormValues0(0) = Request("Department")
arFormFields0(1) = "Name"
arFormDBFields0(1) = "Name"
arFormValues0(1) = Request("Name")

FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0

FP_SaveFieldToDB fp_rs, Now, "Timestamp"

fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"

fp_rs.Close
fp_conn.Close

FP_FormConfirmation "text/html; charset=windows-1252",_
"Form Confirmation",_
"Thank you for submitting the following information:",_
"webdeveloper.asp",_
"Return to the form."

End If
End If

%>
<html>
<head>



<%

Dim TextBody
Dim Name
Dim Department

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then

Set cdoMessage = Nothing
Set cdoConfig = Nothing

Set cdoConfig = CreateObject("CDO.Configuration")

TextBody = TextBody & "Name " & Request.Form("Name") & VbCrLf
TextBody = TextBody & "Department " & Request.Form("Department") & VbCrLf

With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "my.server.com"
.Update
End With

Dim objMail
'Create the mail object
Set objMail = Server.CreateObject("CDO.Message")
'Set key properties
objMail.From = "myemail@com"
objMail.To = "myemail@.com"
objMail.Subject = "Software Required"
objMail.TextBody = TextBody

objMail.Send

Set cdoMessage = Nothing
Set cdoConfig = Nothing

End If

%></head>
<body>

<p align="center"><b><font size="6" face="Arial">Software Requirements Form</font></b><p> </p>
<p> </p>
<form method="POST" action="--WEBBOT-SELF--">
<input TYPE="hidden" NAME="VTI-GROUP" VALUE="0">

<table CELLSPACING="0" CELLPADDING="0" BORDER="0" WIDTH="530" id="table1">
<tr>
<td WIDTH="180" valign="top"><font face="Arial">Name</font></td>
<td WIDTH="350" colspan="3">
<input type="text" name="Name" size="20"></td>
</tr>
<tr>
<td WIDTH="180" valign="top"> </td>
<td WIDTH="80"> </td>
<td WIDTH="10"> </td>
<td WIDTH="80"> </td>
</tr>
<tr>
<td WIDTH="180" valign="top">Department</td>
<td WIDTH="350" colspan="3">
<input type="text" name="Department" size="20"></td>
</tr>
</table>
<p>                                           
<input type="submit" value="Submit" name="Submit"></p>
</form>
</body>

</html>

kayaker411

08-17-2006, 09:04 AM

No error message? Dang. Are you entering a real email address in place of "myemail@com" and a real web server in place of "my.server.com" in your CDO code when you execute it?

Try different browsers. You might check your IE settings. Check Tools/Internet Options/Advanced/ Under Browsing uncheck Disable Script Debugging (Internet Explorer) and the one below that. Then rerun your code.

I'm not familiar with the methods you're using to send email So I'm not sure I can be of much help. It looks overly complex to me. You might replace that CDO code with some asp you find on the internet to send email and see if that code is the problem. You could start at this great site that I've visited for years.(http://www.psacake.com/web/func/mailit_function.htm)

Clearly something is awry and it's usually something that makes you smack your forehead when it's discovered. Coding is really all about solving problems. So take your code apart and replace portions, execute pieces of it in isolation and eventually the issue will be resolved. Be sure to post your solution!!

Shona Smith

08-18-2006, 02:37 AM

Sorry I thought I had replied to this yesterday.

Thanks for the link but that is CDONTs which I don't have a problem with but need to change to CDO as Microsoft have deprecated CDONTS.

Cheers for your help Shona