0

I'm creating a small program to read from an SQL server, loading result into an array, pivoting the array to my liking and mailing it out to selected members of a group.

It's going to be run once a day with Windows schedule.

I have the connection, query and mail sorted, but not the datareader to array bit. Anyone know how it's done (line 26)?

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

Module Module1
    Sub Main()
        'Connection to SQL
        Dim con As New SqlConnection
        con.ConnectionString = "Data Source=localhost\SQLEXPRESS;Initial Catalog=db;integrated security=SSIP;Persist Security Info=False;Trusted_Connection=Yes"
        con.Open()

        'Query SQL
        Dim cmd As New SqlCommand("SELECT DISTINCT REG,K.NAME,L.LOCATION,L.LOCATION_NAME,A.INSTRUCTION, " & _
             "SUM(CASE SAMPLE WHEN 'POS' THEN 1 WHEN 'OK' THEN 1 ELSE 0 END) AS Pos,COUNT(CODE) as 'Sum' " & _
                "FROM PT_SAMPLE P " & _
                "INNER JOIN PT_SAMPLE_PARTS PP ON P.SAMPLE = PP.SAMPLE " & _
                "INNER JOIN KT_CUSTOMER K ON P.CUSTOMER = K.CUSTOMER " & _
                "INNER JOIN LT_LOCATION L ON L.LOCATION = P.LOCATION " & _
                "INNER JOIN AT_CODE A ON A.CODE = PP.CODE " & _
                "WHERE DATE LIKE '20111104%' AND CODE <> 'NA' AND CODE NOT LIKE 'PREVIOUS%' AND P.ZT <> 'W' " & _
                "GROUP BY REG,K.NAME,L.LOCATIONL,L.LOCATION_NAME,CODE,A.INSTRUCTION", con)
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        con.Close()

        'Convert to array
        ?

        'Mail setup
        Dim mMailMessage As New MailMessage()
        mMailMessage.From = New MailAddress("Sender")
        mMailMessage.To.Add(New MailAddress("Reciever"))
        mMailMessage.Subject = "Subject"

        'CSS setup for presentation of array (work in progress)
        Dim Body As String
        Body = "<!DOCTYPE html PUBLIC " & Chr(34) & "-//W3C//DTD XHTML 1.0 Transitional//EN" & Chr(34) & " " & Chr(34) & "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" & Chr(34) & ">" & _
            "<html><head><style type=" & Chr(34) & "text/css" & Chr(34) & ">table{border-collapse:collapse;} table, td, th {border:1px solid black;}</style></head><body><table>" & _
        "<tr><th>Name</th><th>Name</th></tr><tr><td>Peter</td><td>Griffin</td></tr><tr><td>Lois</td><td>Griffin</td>" & _
        "</tr></table>" & _
        "<p><b>Note:</b> If a !DOCTYPE is not specified, the border-collapse property can produce unexpected results" & _
        "in IE8 and earlier versions.</p></body></html>"

        mMailMessage.Body = Body
        mMailMessage.IsBodyHtml = True
        mMailMessage.Priority = MailPriority.Normal
        Dim mSmtpClient As New SmtpClient()
        mSmtpClient.Send(mMailMessage)
    End Sub
End Module
2
Contributors
1
Reply
3
Views
5 Years
Discussion Span
Last Post by hericles
0

I'd use a dataAdapter to pull out the information and a dataTable to hold it. Must easier to work with and insert to the email. Probably faster execution than a data reader too.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.