VB Send Email in Table

Question

i need help in sending table in email after retrieving from database. Managed to retrieve the data but the table on show one row but the database have many rows.

The problem of the code is around Message.Body += sb.ToString. All the code above is working perfectly during debug.

I couldnt solve the problem. please help!

Imports System.Data.SqlClient
Imports System.Data
Imports System.Net.Mail
Imports System.Text

Module Module1
    Dim db As New Database

    Sub Main()

        Dim Message As MailMessage = New MailMessage()
        Dim Smtp As New SmtpClient()

        Dim emailTo As String = "email@email.com"
        Smtp.UseDefaultCredentials = True
        Smtp.Host = "server.com"
        Smtp.Port = 25

        Message.Subject = "Computers"
        Message.To.Add("email@email.com")
        Message.IsBodyHtml = True
        Message.Priority = MailPriority.High
        Message.From = New MailAddress("email@email.com")

        Dim sql As String
        Dim sql1 As String
        Dim dr As SqlDataReader
        Dim dr1 As SqlDataReader

        Dim count As Integer = 0

        Dim connstring As String = ("Server=QSD13\SQLEXPRESS; Initial Catalog = SoftwareManagementV2; UID=sa; Pwd=password;")
        Dim connection As New SqlConnection(connstring)

        db.OpenConn()

        'Select version and affected softwarename from both SCCM1 DB and affectedsoftware DB to check which are the affected computers
        sql = "SELECT SCCM1.DisplayName00, SCCM1.Version00, AffectedSoftware.Version00, AffectedSoftware.DisplayName00, SCCM1.Name00, SCCM1.InstallDate00, SCCM1.Publisher00, SCCM1.LastHWScan, SCCM1.DisplayName, SCCM1.email, SCCM1.Division, SCCM1.Dept, SCCM1.Brand, SCCM1.Model, SCCM1.OS from [SoftwareManagementV2].[dbo].AffectedSoftware, [CM_P01].[dbo].SCCM1 WHERE SCCM1.DisplayName00 = AffectedSoftware.DisplayName00 AND SCCM1.Version00 = AffectedSoftware.Version00"
        dr = db.ExecuteQuery(sql)

        'Select only values that appear once
        sql1 = "Select DISTINCT Name00, DisplayName00, Version00 FROM [SoftwareManagementV2].[dbo].[AffectedComputer] group by Name00, DisplayName00, Version00"
        dr1 = db.ExecuteQuery(sql1)

        'both condition must be satisfied to insert in affected computer db
        While dr.Read = True

            'Prevent duplicate values inserted into affected computer db
            If dr1.Read = False Then
                Console.WriteLine("Display Name:" + dr(0) + ", Version:" + dr(1) + ", Computer Name:" + dr(4))

                Dim cmd As SqlCommand = connection.CreateCommand
                connection.Open()

                'Insert affected computers and softwarenames into AffectedComputer DB 
                cmd.CommandText = "INSERT into AffectedComputer(Name00, DisplayName00, InstallDate00, Publisher00, Version00, LastHWScan, DisplayName, email, Division, Dept, Brand, Model, OS) VALUES (@Name00, @DisplayName00, @InstallDate00, @Publisher00, @Version00, @LastHWScan, @DisplayName, @email, @Division, @Dept, @Brand, @Model, @OS)"
                cmd.Parameters.AddWithValue("@Name00", dr(4))
                cmd.Parameters.AddWithValue("@DisplayName00", dr(0))
                cmd.Parameters.AddWithValue("@InstallDate00", dr(5))
                cmd.Parameters.AddWithValue("@Publisher00", dr(6))
                cmd.Parameters.AddWithValue("@Version00", dr(1))
                cmd.Parameters.AddWithValue("@LastHWScan", dr(7))
                cmd.Parameters.AddWithValue("@DisplayName", dr(8))
                cmd.Parameters.AddWithValue("@email", dr(9))
                cmd.Parameters.AddWithValue("@Division", dr(10))
                cmd.Parameters.AddWithValue("@Dept", dr(11))
                cmd.Parameters.AddWithValue("@Brand", dr(12))
                cmd.Parameters.AddWithValue("@Model", dr(13))
                cmd.Parameters.AddWithValue("@OS", dr(14))
                cmd.ExecuteNonQuery()
                connection.Close()


                Dim collection As New Collection
                Dim item1, item2, item3, item4, item5, item6, item7, item8, item9, item10, item11, item12, item13 As String

                item1 = dr(4)
                item2 = dr(0)
                item3 = dr(5)
                item4 = dr(6)
                item5 = dr(1)
                item6 = dr(7)
                item7 = dr(8)
                item8 = dr(9)
                item9 = dr(10)
                item10 = dr(11)
                item11 = dr(12)
                item12 = dr(13)
                item13 = dr(14)
                Dim all = item1.ToString + item2.ToString + item3.ToString + item4.ToString + item5.ToString + item6.ToString + item7.ToString + item8.ToString + item9.ToString + item10.ToString + item11.ToString + item12.ToString + item13.ToString
                collection.Add(all)

                Dim sb As New StringBuilder

                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")
                sb.Append("")

                For Each all In collection
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                    sb.Append("")
                Next

                sb.Append("Name00 DisplayName00 InstallDate00 Publisher00 Version00 LastHWScan Display Name email Divison Dept Brand Model OS
" + item1 + " " + item2 + " " + item3 + " " + item4 + " " + item5 + " " + item6 + " " + item7 + " " + item8 + " " + item9 + " " + item10 + " " + item11 + " " + item12 + " " + item13 + "")
                sb.Append("
")

                Message.Body += sb.ToString

                count = + 1

            End If

        End While

        dr.Close()
        dr1.Close()
        db.CloseConn()

        'Only send email when theres row(s)
        If count > 0 Then
            Smtp.Send(Message)
        End If

    End Sub

End Module

VB Send Email in Table

Answers (1)

Related Questions