ASP Mail not working with database

Hi,
I have created form with database connection and email. When I submit, data is adding in databse, but could not receive any value in email. If I use this form without database connection, i can receive datas in email.

I attached the code.


Please help.

regards
raj

ASP CODE:

form.asp
----------

'''--------------database Connection------

<%Set DBCnn = Server.CreateObject("ADODB.Connection")
rootPath = Server.MapPath("\")
dbPath = rootPath & "\db\test.mdb"
dbcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &dbPath

Set BrwType = Server.CreateObject("MSWC.BrowserType")
Set rstPersonDet = Server.CreateObject("ADODB.RecordSet")
If Err <> 0 Then
Err.Clear
Response.End
End if
%>

<%
sqlStr="SELECT max(refno) as txt_refno from dcr"

rstPersonDet.Open sqlStr,DBCnn %>

<% a= rstPersonDet("txt_refno")%>
<% a=a+1 %>

'' ASP Form Mail-----------------------------

<%
'- Customization of these values is required, see documentation. -----------


mailComp = "CDONTS"
smtpServer = ""
'- End required customization section. -------------------------------------

Response.Buffer = true
errorMsgs = Array()

'Check for form data.

if Request.ServerVariables("Content_Length") = 0 then
call AddErrorMsg("No form data submitted.")
end if

validReferer = true

if not validReferer then
if referer = "" then
call AddErrorMsg("No referer.")
else
call AddErrorMsg("Invalid referer: '" & referer & "'.")
end if
end if

'Check for the recipients field.

if Request.Form("_recipients") = "" then
call AddErrorMsg("Missing email recipient.")

end if

'Check all recipient email addresses.

recipients = Split(Request.Form("_recipients"), ",")
for each name in recipients
name = Trim(name)
if not IsValidEmail(name) then
call AddErrorMsg("Invalid email address in recipient list: " & name & ".")
end if
next
recipients = Join(recipients, ",")

'Get replyTo email address from specified field, if given, and check it.

name = Trim(Request.Form("_replyToField"))
if name <> "" then
replyTo = Request.Form(name)
else
replyTo = Request.Form("_replyTo")
end if
if replyTo <> "" then
if not IsValidEmail(replyTo) then
call AddErrorMsg("Invalid email address in reply-to field: " & replyTo & ".")
end if
end if

'Get subject text.

subject = Request.Form("_subject")

'If required fields are specified, check for them.

if Request.Form("_requiredFields") <> "" then
required = Split(Request.Form("_requiredFields"), ",")
for each name in required
name = Trim(name)
if Left(name, 1) <> "_" and Request.Form(name) = "" then
call AddErrorMsg("Missing value for " & name)
end if
next
end if

'Check for From Address

fromAddr = Trim(Request.Form("email"))
if fromAddr = "" then
fromAddr = "test@hotmail.com"
end if

'If a field order was given, use it. Otherwise use the order the fields were
'received in.

str = ""
if Request.Form("_fieldOrder") <> "" then
fieldOrder = Split(Request.Form("_fieldOrder"), ",")
for each name in fieldOrder
if str <> "" then
str = str & ","
end if
str = str & Trim(name)
next
fieldOrder = Split(str, ",")
else
fieldOrder = FormFieldList()
end if

'If there were no errors, build the email note and send it.

if UBound(errorMsgs) < 0 then

'Build table of form fields and values.

body = "<table border=0 cellpadding=2 cellspacing=0>" & vbCrLf
for each name in fieldOrder
body = body _
& "<tr valign=top>" _
& "<td>" _
& name _
& ":</font></td>" _
& "<td>" _
& Request.Form(name) _
& "</td>" _
& "</tr>" & vbCrLf
next
body = body & "</table>" & vbCrLf

'Add a table with any environmental variables.

if Request.Form("_envars") <> "" then
body = body _
& "

" _
& "<table border=0 cellpadding=2 cellspacing=0>" & vbCrLf
envars = Split(Request.Form("_envars"), ",")
for each name in envars
name = Trim(name)
body = body _
& "<tr valign=top>" _
& "<td>" _
& name _
& ":</td>" _
& "<td>" _
& Request.ServerVariables(name) _
& "</td>" _
& "</tr>" & vbCrLf
next
body = body & "</table>" & vbCrLf
end if

'Send it.

str = SendMail()
if str <> "" then
AddErrorMsg(str)
end if

'Redirect if a URL was given.

if Request.Form("_redirect") <> "" then
Response.Redirect(Request.Form("_redirect"))
end if

end if %>



<% '---------------------------------------------------------------------------
' Subroutines and functions.
'---------------------------------------------------------------------------

sub AddErrorMsg(msg)

dim n

'Add an error message to the list.

n = UBound(errorMsgs)
Redim Preserve errorMsgs(n + 1)
errorMsgs(n + 1) = msg

end sub

function GetHost(url)

dim i, s

GetHost = ""

'Strip down to host or IP address and port number, if any.

if Left(url, 7) = "http://" then
s = Mid(url, 8)
elseif Left(url, 8) = "https://" then
s = Mid(url, 9)
end if
i = InStr(s, "/")
if i > 1 then
s = Mid(s, 1, i - 1)
end if

getHost = s

end function

function IsValidEmail(email)

dim names, name, i, c

'Check for valid syntax in an email address.

IsValidEmail = true
names = Split(email, "@")
if UBound(names) <> 1 then
IsValidEmail = false
exit function
end if
for each name in names
if Len(name) <= 0 then
IsValidEmail = false
exit function
end if
for i = 1 to Len(name)
c = Lcase(Mid(name, i, 1))
if InStr("abcdefghijklmnopqrstuvwxyz_-.", c) <= 0 and not IsNumeric(c) then
IsValidEmail = false
exit function
end if
next
if Left(name, 1) = "." or Right(name, 1) = "." then
IsValidEmail = false
exit function
end if
next
if InStr(names(1), ".") <= 0 then
IsValidEmail = false
exit function
end if
i = Len(names(1)) - InStrRev(names(1), ".")
if i <> 2 and i <> 3 then
IsValidEmail = false
exit function
end if
if InStr(email, "..") > 0 then
IsValidEmail = false
end if

end function

function FormFieldList()

dim str, i, name

'Build an array of form field names ordered as they were received.

str = ""
for i = 1 to Request.Form.Count
for each name in Request.Form
if Left(name, 1) <> "_" and Request.Form(name) is Request.Form(i) then
if str <> "" then
str = str & ","
end if
str = str & name
exit for
end if
next
next
FormFieldList = Split(str, ",")

end function

function SendMail()

dim mailObj
dim addrList

'Send email based on mail component. Uses global variables for parameters
'because there are so many.

SendMail = ""

'Send email (CDONTS version), doesn't support reply-to address and has
'no error checking.

if mailComp = "CDONTS" then
set mailObj = Server.CreateObject("CDONTS.NewMail")
mailObj.BodyFormat = 0
mailObj.MailFormat = 0
mailObj.From = fromAddr
mailObj.To = recipients
mailObj.Subject = subject
mailObj.Body = body
mailObj.Send
end if

'Send email (JMail version).

if mailComp = "JMail" then
set mailObj = Server.CreateObject("JMail.SMTPMail")
mailObj.Silent = true
mailObj.ServerAddress = smtpServer
mailObj.Sender = fromAddr
mailObj.ReplyTo = replyTo
mailObj.Subject = subject
addrList = Split(recipients, ",")
for each addr in addrList
mailObj.AddRecipient Trim(addr)
next
mailObj.ContentType = "text/html"
mailObj.Body = body
if not mailObj.Execute then
SendMail = "Email send failed: " & mailObj.ErrorMessage & "."
end if
end if

'Send email (ASPMail version).

if mailComp = "ASPMail" then
set mailObj = Server.CreateObject("SMTPsvg.Mailer")
mailObj.FromAddress = fromAddr
mailObj.RemoteHost = smtpServer
mailObj.ReplyTo = replyTo
for each addr in Split(recipients, ",")
mailObj.AddRecipient "", Trim(addr)
next
mailObj.Subject = subject
mailObj.ContentType = "text/html"
mailObj.BodyText = body
if not mailObj.SendMail then
SendMail = "Email send failed: " & mailObj.Response & "."
end if
end if

end function %>

'-------------------------------------------------------------


<HTML>
<HEAD>
<TITLE></TITLE>

<SCRIPT LANGUAGE="javascript" SRC=""../scripts/formck.js">
</SCRIPT>

<SCRIPT LANGUAGE="javascript" SRC=""../scripts/addform.js">
</script>

</HEAD>

<SCRIPT LANGUAGE="VBScript">
Function postjob_onSubmit
dim b,c,d,na,dref
b=postjob.txt_refno.value
b=right("0000" & b,4)
na=postjob.txt_orgin.value
c=left(postjob.txt_type.value,1)
d=left(postjob.txt_to.value,1)
dref="A969090-B-" &ltrim(c)&"-" &d&"-" &b
postjob.txt_docref.value=dref
End Function
</SCRIPT>


<BODY BGCOLOR="#FFFFFf" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<FORM ACTION="posting_dcr.asp" NAME="postjob" ONSUBMIT="return readform()">


<INPUT TYPE="HIDDEN" NAME="_recipients" VALUE="raja0123@hotmail.com">
<INPUT TYPE="HIDDEN" NAME="_subject" VALUE="Mail">
<INPUT TYPE="HIDDEN" NAME="_fieldOrder" VALUE="txt_orgin,txt_type,txt_to">

<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top"></td>
</tr>
<tr>
<td valign="top">
<table width="78%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td ></td>
<td >
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
</font></div>
</td>
<td ><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<input type="hidden" readonly class= "frm" name="txt_refno" value="<%Response.Write(a)%>">
<input type="hidden" readonly class= "frm" name="txt_docref">
</font></td>
</tr>
<tr>
<td ><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Orgin</font></td>
<td >
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">:</font></div>
</td>
<td ><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<input type="text" readonly name="txt_orgin" value=<%=Request.QueryString("name")%>>
</font></td>
</tr>
<tr>
<td ><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Type</font></td>
<td >
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">:</font></div>
</td>
<td ><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<select name="txt_type">
<option value="Letter">Letter</option>
<option value="Fax">Fax</option>
<option value="Email">Email</option>
<option value="Document Transmittal">Document
Transmittal</option>
</select>
</font></td>
</tr>
<tr>
<td ><font face="Verdana, Arial, Helvetica, sans-serif" size="2">To</font></td>
<td >
<div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">:</font></div>
</td>
<td ><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<input type="text" readonly name="txt_to" value="TAPCO">
</font></td>
</tr>
<tr>
<td > <font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<input type="submit" name="Submit" value="Submit">
<input type="reset" name="Submit2" value="Reset">
</font></td>
</tr>
</table>

</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</FORM>
</BODY>
</HTML>


posting_dcr.asp
-----------------


<%@ Language=VBScript %>

<%

refno=Request.QueryString("txt_refno")
docref=Request.QueryString("txt_docref")
orgin=Request.QueryString("txt_orgin")
ttype=Request.QueryString("txt_type")
tto=Request.QueryString("txt_to")


Set cn = Server.CreateObject("ADODB.Connection")

rootPath = Server.MapPath("\")
dbPath = rootPath & "\db\test.mdb"

Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &dbPath

FUNCTION fixQuotes(theString)
fixQuotes = Replace(theString, "'", "''")
END FUNCTION

If err <> 0 Then
Err.Clear
strmessage = "Connection to database not established."
msgbox(strmessage)
Response.End
End If

dim squery1
squery1 = "INSERT INTO dcr (refno,docref,orgin,type,tto) VALUES ('" & refno & "','" & docref & "','" & orgin & "','" & ttype & "','" & tto & "')"

cn.Execute(squery1)
%>
<HTML><HEAD></HEAD>

<BODY BGCOLOR="#009999">

<table width=90% align="center" border="0" cellspacing="0" cellpadding="0" bgcolor="f5f5e9">
<tr>
<td align=center colspan="3">
<div align="center">

<font face="Verdana, Arial, Helvetica, sans-serif" size="2">Your Data
Added in Register &amp; Document Reference No. <%=(docref)%></font></div>
</td>
</tr>
</table></BODY></HTML>

Thanks & regards
Raj

hi

sorry. theres about 1000's lines there, and to be honest i dont wana think about unpicking that.

however, so your not stuck, why not use this: its what you need and with some obvious modifiaction it will do you fine.

Code: Select all

<% 
 dim Connect, RS
Set conn= Server.CreateObject("ADODB.Connection")
connect.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.MapPath("db.mdb"))
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM table", Connect, 2, 2 
RS.AddNew 
RS("name") = request("field1")
RS("email") = request("field2")

RS.Update


dim Mail
    Dim myMail
    Dim HTML
    Set myMail = CreateObject("CDONTS.NewMail")

    HTML = "<!DOCTYPE HTML PUBLIC""-//IETF//DTD HTML//EN"">"
    HTML = HTML & "<html>"
    HTML = HTML & "<head>"
    HTML = HTML & "</head>"
    HTML = HTML & "<body bgcolor=""ffffff"">"
    HTML = HTML & "

<font size =""2"" face=""Tahoma"">"
    HTML = HTML & "Dear " & RS.Fields("name").Value & ", Thankyou for signing up.
"
    HTML = HTML & "</body>"
    HTML = HTML & "</html>"

    myMail.From="email@whereever.com"
    myMail.To = RS.Fields("email").Value
    myMail.Subject="this is a subject for the email"
    myMail.BodyFormat=0
    myMail.MailFormat=0
    myMail.Body=HTML
    myMail.Send
    set mymail=nothing
RS.close
set conn= nothing

remember - the email type is CDONTS, and the db access. its only an examle. change it if yours is differnt!

with regards