[shill]

I'm trying to create a WindowsApp that emails the results of a stored procedure. I'm new to all of this, so please bear with me, and if you must laugh, please don't tell me about it.

So far I have this. Eventually the form will go away and the "SendEmail()" will go in the form load event so that when the task scheduler runs this app the email is automatically sent. Right now the email I get just has my test text in it.

I would like to know how to export the sproc results to Excel so I can attach that to the email:

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

Public Class Form1

Sub SendMail()
		Dim MailObj As New SmtpClient("mail.mckissock.com")  
		Dim Msg As MailMessage = New MailMessage()
		Dim conn As New SqlConnection("Data Source=devserver;Initial Catalog=MLPPROJECT;Integrated Security=True")
		Dim cmd As New SqlCommand
		cmd.Connection = conn
		cmd.CommandType = CommandType.StoredProcedure
		cmd.CommandText = "sp_SelPaymentDue"
			 
		 Dim sMsg As String = ""

		Try
			' Open connection 
			conn.Open()

			Msg.To.Add(New MailAddress("shill@mckissock.com", "Skye Hill"))
			Msg.FROM =new MailAddress("shill@mckissock.com", "Skye Hill")   
			Msg.Subject = "Test Email"
			Msg.Body = "This is a test e-mail at " & DateTime.Now & "!" & vbcrlf & vbcrlf
	 

			cmd.CommandType = CommandType.StoredProcedure
			cmd.CommandText = "sp_SelPaymentDue"

			cmd.ExecuteNonQuery()
			
			sMsg += vbcrlf
		 
			cmd.ExecuteReader

			 ' Send the email
			MailObj.Send(Msg)
				 'Inform the user if email is successful
			statusLabel.Text = "Email was successful"
			'	Else
 
		Catch ex As SqlException
			' Display Sql Exception message 
			statusLabel.Text = ex.Message
		Catch ex As Exception
			' Display Exception message
			statusLabel.Text = ex.Message
		Finally
			' Close the connection
			If conn.State = ConnectionState.Open Then
				conn.Close()
			End If
		End Try

	End Sub

Private Sub btnSendEmail_Click( ByVal sender As System.Object,  ByVal e As System.EventArgs) Handles btnSendEmail.Click
		Me.SendMail()
End Sub

Private Sub Form1_Load( ByVal sender As System.Object,  ByVal e As System.EventArgs) Handles MyBase.Load
		'TODO: This line of code loads data into the 'MLPPROJECTDataSet.sp_SelPaymentDue' table. You can move, or remove it, as needed.
		Me.Sp_SelPaymentDueTableAdapter.Fill(Me.MLPPROJECTDataSet.sp_SelPaymentDue)

End Sub
End Class

This post has been edited by shill: 08 October 2008 - 12:06 PM

[magicmonkey]

The cmd.ExecuteReader returns a datareader which you need to loop through to get your data. Maybe this will get you started...

Oh and get rid of the cmd.executenonquery, as you are just executing the proc twice.

            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader
            If dr.HasRows Then
                Do While dr.Read
                    For index As Integer = 0 To dr.FieldCount - 1
                        sMsg &= dr.GetValue(index).ToString
                        If index < dr.FieldCount - 1 Then
                            sMsg &= ","
                        End If
                    Next
                    sMsg &= Environment.NewLine
                Loop
            End If

[shill]

Thanks so much! that worked perfectly!