Sending mail through the loop sends multiple mails at same address-CodePudding

I am trying to send e-mail to candidates that applied for the job, sometimes I have few hundreds or even thousand mails to send, so code would be a help.

This is the one I wrote, but the problem is that mail is sent multiple times on same address (on some addresses once or twice and sometimes even more).

A bit of explanation for the code below. Code reads active job database and picks candidates that haven't been contaced (column FMZ in database, "X"), then reads candidate unique ID in another database for the e-mail address that mail is going to be sent. Even basic msgbox below SmtpServer.Send(mail) is activated for each candidate only once, but mail is sent multiple times. Here's the code.

'Step 1 - All candidates in DBAP that applied for ClientID/Job ID
For i As Integer = 0 To dsPoslovi.Tables(0).Rows.Count - 1
    If dsPoslovi.Tables(0).Rows(i)(1).ToString() = ATS_IDKlijent AndAlso dsPoslovi.Tables(0).Rows(i)(2).ToString() = ATS_IDPosao Then
        'Step 2 - Send mails to those not contacted yet (X in FMZ column)
        If dsPoslovi.Tables(0).Rows(i)(8).ToString() <> "X" Then
            IDKandidataAP = dsPoslovi.Tables(0).Rows(i)(0).ToString()
            IDKandidataAP2 = dsPoslovi.Tables(0).Rows(i)(3).ToString()
            'Read mail addresses
            For x As Integer = 0 To dsCL.Tables(0).Rows.Count - 1
                If dsPoslovi.Tables(0).Rows(i)(3).ToString() = dsCL.Tables(0).Rows(x)(1).ToString() Then

                    MailKandidata = dsCL.Tables(0).Rows(x)(7).ToString()

                    If MailKandidata.Contains("@") = True Then
                        SmtpServer.Send(mail)
                        MsgBox(MailKandidata)
                    End If
                End If
            Next

            'Update active job database
            Dim cmd As New OleDbCommand(query, myCONN)
            myCONN.Open()
            cmd.Parameters.AddWithValue("FMZ", "X")
            cmd.Parameters.AddWithValue("ID", IDKandidataAP)

            cmd.ExecuteNonQuery()
            myCONN.Close()

        End If

    End If
Next

CodePudding user response：

Since I don't like to type I resolved the DataSet.Tables to a DataTable and looped through the Rows collection. To me, it is more readable.

I think the problem is in the mail.To collection. Each iteration adds a recipient and sends the email to each recipient. I build the collection inside the loops and only mail once outside the loop. The mail will go to each recipient in the collection.

I am not sure what is going on with IDKandidataAP2. It doesn't appear to be used in this method and it is being overwritten on each iteration.

I corrected the database code. The connection and command should be declared in a Using block once outside the loops. I build the parameters collection outside the loop and only change the Value of the @ID parameter inside the loop. I guessed at what the Update string might look like. The Add method is preferred to AddWithValue. I guessed at the datatypes. Check your database for the actual types.

Private ATS_IDKlijent As String = "" '?
Private ATS_IDPosao As String = "" '?
Private dsCL As New DataSet
Private dsPoslovi As New DataSet
Private mail As New MailMessage
Private SmtpServer As New SmtpClient()
Private IDKandidataAP As String
Private IDKandidataAP2 As String

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dt As DataTable = dsPoslovi.Tables(0)
    'Step 1 - All candidates in DBAP that applied for ClientID/Job ID
    Dim query = "Update SomeTable Set FMZ = @FMZ Where ID = @ID;"
    Using myCONN As New OleDbConnection("Your connection string"),
            cmd As New OleDbCommand(query, myCONN)
        cmd.Parameters.Add("@FMZ", OleDbType.VarChar).Value = "X"
        cmd.Parameters.Add("@ID", OleDbType.VarChar)
        myCONN.Open()
        For Each row As DataRow In dt.Rows
            If row(1).ToString() = ATS_IDKlijent AndAlso row(2).ToString() = ATS_IDPosao Then
                'Step 2 - Send mails to those not contacted yet (X in FMZ column)
                If row(8).ToString() <> "X" Then
                    IDKandidataAP = row(0).ToString()
                    IDKandidataAP2 = row(3).ToString()
                    'Read mail addresses
                    Dim dt2 As DataTable = dsCL.Tables(0)
                    For Each row2 As DataRow In dt2.Rows
                        If row(3).ToString() = row2(1).ToString() Then
                            Dim MailKandidata = row2(7).ToString()
                            If MailKandidata.Contains("@") = True Then
                                Dim ToAddress As New MailAddress(MailKandidata)
                                mail.To.Add(ToAddress)
                                MsgBox(MailKandidata)
                            End If
                        End If
                    Next
                    'Update active job database
                    cmd.Parameters("@ID").Value = IDKandidataAP
                    cmd.ExecuteNonQuery()
                End If
            End If
        Next
    End Using 'Closes and disposes the connection and disposes the command.
    SmtpServer.Send(mail)
End Sub