email to DB via ASP and SQL......help!

[webboy1]

Hi,

I'm trying to send an email to all the email addresses in my database. Below is the code that i post my message to via a form. It looks as though it is working, but then a page comes up saying there is a problem, however it does not tell me what the problem is.

Can someone look through the code and tell me what im doing wrong please?

I have replaced all URL's/passwords/usernames etc (security :))

Please help!

<%
' Declaring variables
Dim rs, mail, subject, message, sql, no, cn

no = 0
subject = Request.Form("subject")
message = Request.Form("message")

' Adding a link to all messages by which users can delete their
' emails if they would want later

message = message & vbcrlf & vbcrlf
message = message & "To stop receiving emails click here :"
message = message & vbcrlf
message = message & "http://www.mydomain.com/db/email/del.asp?email="

'
'
'Connect to Database
'
'

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

cn.ConnectionString = "driver={SQL Server};server=serverIP;uid=userid;pwd=password;database=database"
cn.Open

sql="SELECT email from tbl_mail"

cn.execute(sql)

' Check to see if you have not pressed the 'send' button mistakenly
If Len(message) Then

' If you have written some message then lets send it
' You can use ASP Email component of your choice, here I will
' stick with CDO

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, cn.execute(sql)
While Not rs.EOF
Set mail = Server.CreateObject("CDONTS.NewMail")
mail.From = "[e-mail]"
mail.To = rs("email")
mail.Subject = subject
mail.Body = message & rs("email")
mail.Send
Set mail = Nothing%>
<%
no = no + 1
rs.MoveNext
wend%>

<%Response.Write "Emails sent to " & no & " users."%>

<%rs.Close
Set rs = Nothing
Set cn = Nothing

' Had you pressed the button mistakenly with text area empty, then
' redirect back to the HTML Form
Else
Response.Redirect "mail.htm"
End If
%>

[Xoc]

I don't see anything obvious. Without more info about what is failing, I can't help much. Can you narrow in on the problem by commenting out code until you find where it is? Is it the database operations that are failing or the email?

[Gizmare]

<%
' Declaring variables
Dim rs, mail, subject, message, sql, no, cn

no = 0
subject = Request.Form("subject")
message = Request.Form("message")

' Adding a link to all messages by which users can delete their
' emails if they would want later

message = message & vbcrlf & vbcrlf
message = message & "To stop receiving emails click here :"
message = message & vbcrlf
message = message & "http://www.mydomain.com/db/email/del.asp?email="

'
'
'Connect to Database
'
'

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

cn.ConnectionString = "driver={SQL Server};server=serverIP;uid=userid;pwd=password;database=database"
cn.Open

sql="SELECT email from tbl_mail"

cn.execute(sql)

' Check to see if you have not pressed the 'send' button mistakenly
If Len(message) Then

' If you have written some message then lets send it
' You can use ASP Email component of your choice, here I will
' stick with CDO

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, cn.execute(sql)
While Not rs.EOF
Set mail = Server.CreateObject("CDONTS.NewMail")
mail.From = "[e-mail]"
mail.To = rs("email")
mail.Subject = subject
mail.Body = message & rs("email")
mail.Send
Set mail = Nothing%>
<%
no = no + 1
rs.MoveNext
wend%>

<%Response.Write "Emails sent to " & no & " users."%>

<%rs.Close
Set rs = Nothing
Set cn = Nothing

' Had you pressed the button mistakenly with text area empty, then
' redirect back to the HTML Form
Else
Response.Redirect "mail.htm"
End If
%>

I would try the following. I have changed the way you are getting your records. I use the Do Loop rather then a While loop, as it provides a more structured and flexible way of looping. Also you are executing the Query Prior to putting it in the recordset. I am not sure if that is problematic. Give it a whirl and send me the error if this does not work. FYI you must go to your advanced internet options and turn off Give Friendly Error messages to get the error output.

<%
' Declaring variables
Dim rs, mail, subject, message, sql, no, cn

no = 0
subject = Request.Form("subject")
message = Request.Form("message")

' Adding a link to all messages by which users can delete their
' emails if they would want later

message = message & vbcrlf & vbcrlf
message = message & "To stop receiving emails click here :"
message = message & vbcrlf
message = message & "http://www.mydomain.com/db/email/del.asp?email="

'
'
'Connect to Database
'
'

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

cn.ConnectionString = "driver={SQL Server};server=serverIP;uid=userid;pwd=password;database=database"
cn.Open

sql="SELECT email from tbl_mail"

' Check to see if you have not pressed the 'send' button mistakenly
If Len(message) Then

' If you have written some message then lets send it
' You can use ASP Email component of your choice, here I will
' stick with CDO

Set rs = Server.CreateObject("ADODB.Recordset")
Set rs = cn.execute(sql)
Do While Not rs.EOF
Set mail = Server.CreateObject("CDONTS.NewMail")
mail.From = "[e-mail]"
mail.To = rs("email")
mail.Subject = subject
mail.Body = message & rs("email")
mail.Send
Set mail = Nothing%>
<%
no = no + 1
rs.MoveNext
Loop%>

<%Response.Write "Emails sent to " & no & " users."%>

<%rs.Close
Set rs = Nothing
Set cn = Nothing

' Had you pressed the button mistakenly with text area empty, then
' redirect back to the HTML Form
Else
Response.Redirect "mail.htm"
End If
%>

[txbakers]

If your database has too many email addresses you are running into a browser client problem.

I had to ask my users to break their emails into two or more sessions if their email string was over 2083 characters.

Take a look here:
[support.microsoft.com...]

[tomasz]

take yor set mail out of the loop.

...
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, cn.execute(sql)
Set mail = Server.CreateObject("CDONTS.NewMail")
While Not rs.EOF
....
loop
set mail=nothing

[webboy1]

Cheers guys,

I actially got the problem solved last week. I should have said.

I think the problem was that i was emailing to an Access Database which contained 1200 emails. I think it just couldn't handle it. I transffered all the details to an SQL server database and the same code worked first time.

Thanks for all the help anyway.

Regards
Webboy