Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

C# app sends multiple e-mails

Feb 1, 2007 10:38PM PST

I have a small executable, written in C# (Visual Studio 2003), that calls a SQL Server stored procedure which is simply a "SELECT * FROM [TableName]" query, and if the table has rows, sends an e-mail. The e-mail contains an Excel attachment which is an export file from the same SQL table (a DTS package exports the table to Excel, and then this process is triggered).

The problem is that if the table (and Excel file) contains more than one record, the app is sending one e-mail for each record in the spreadsheet. Today the spreadsheet had 21 rows and 21 messages went out. Each attachment contains the entire spreadsheet (all 21 rows).

The code I am using is below. Any ideas?

SqlConnection myConn = new SqlConnection("server=" + ServerName + "; uid=[logon id]; pwd=[password]; database=[DB Name]");
SqlCommand myCmd = new SqlCommand("sp_[ProcedureName]", myConn);
if( myConn.State == ConnectionState.Closed ) myConn.Open();
myCmd.CommandType = CommandType.StoredProcedure;
SqlDataReader myData = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
myData.Read();

do

{
MailBody = "";
myMsg = new MailMessage();
myMsg.From = "\"[name]\" <[e-mail address]>";
myMsg.To = "[e-mail address]";
myMsg.Bcc = "[e-mail address]";
myMsg.Subject = "[Message Subject]";
if(myData.HasRows)
{
MailBody = "This week's [report name] report is attached.";
myMsg.Attachments.Add(new MailAttachment("E:\\Data\\Reports\\Report_[ReportName].xls"));
}
else
{
MailBody = "There are no results to report this week.";
}
myMsg.BodyFormat = MailFormat.Html;
myMsg.Body = MailBody;
try
{
SmtpMail.Send( myMsg );
}
catch
{
for(int i = 0; i < 254; i++)
{
try
{
SmtpMail.Send( myMsg );
}
catch
{
Console.Write(myMsg.Subject + "//Error Contacting SMTP Server.");
}
}
}

}
while ( myData.Read() );
myData.Close();
}


I've tried reducing the number of retries but that had no effect.

Discussion is locked

- Collapse -
Could it be as simple as?
Feb 1, 2007 10:55PM PST

Removing the do and while ( myData.Read() ); ?

Then it would execute once.

Bob

- Collapse -
It could be and apparently it is
Feb 1, 2007 11:16PM PST

LOL, thanks!

- Collapse -
If that cured it, then here's what it was.
Feb 1, 2007 11:19PM PST

"Fresh eyes."

BTW, it's good code. I only had to read it twice to get a glimmer of what it was doing.

Bob

- Collapse -
(NT) As always, you are the man. Thanks again.
Feb 1, 2007 11:42PM PST