Solved

Send email with dataset as email body

Posted on 2008-09-30
1
314 Views
Last Modified: 2008-10-01
I am trying to send an email with a dataset as the body. When the user clicks a button, my code loops through an asp datagrid on the webpage, and for all the rows that are checked on the datagrid, then an email is sent out with information from a dataset. How do I get the data from the dataset so that I can send the data as the body of my email?
' Variable which will send the mail

    Dim obj As System.Web.Mail.SmtpMail
 

    ''Variable to store the attachments 

    'Dim Attachment As System.Web.Mail.MailAttachment
 

    'Variable to create the message to send

    Dim Mailmsg As New System.Web.Mail.MailMessage()
 

    Protected Sub btnDCConfirmed_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDCConfirmed.Click

        Dim dgItem As DataGridItem

        Dim chkSelected As CheckBox

        Dim SRConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StikeData").ConnectionString)
 

        If SRConn.State <> Data.ConnectionState.Open Then

            SRConn.Open()

        End If
 

        Dim SRCommand1 As New System.Data.SqlClient.SqlCommand

        Dim SQL_Update As String
 

        'lblSelections.Text = "<br>Fooled Ya! The following rows were marked for deletion, "

        'lblSelections.Text += "but not actually deleted:<br><br>"

        For Each dgItem In dgDCDetails.Items

            chkSelected = dgItem.FindControl("chkSelection")

            If chkSelected.Checked Then

                SQL_Update = " UPDATE EnrollCustomer SET [Status] = 1, DCApproved = 1 " _

                & " WHERE DealCapID = " & CType(dgItem.FindControl("lblDealCapID"), Label).Text

                SRCommand1.Connection = SRConn

                SRCommand1.CommandType = Data.CommandType.Text

                SRCommand1.CommandText = SQL_Update

                'SRCommand1.Parameters.AddWithValue("@pDCID", CType(dgItem.FindControl("lblDealCapID"), Label).Text)

                SRCommand1.ExecuteNonQuery()
 

                'Pull Offer1Price data into datagrid

                Dim SQL_GetOffer1PriceData As String = " SELECT tbl_Offer_1_Price.OfferID, tbl_Offer_1_Price.ProspectID, " _

                & " tbl_Offer_1_Price.DealCapID, tbl_Offer_1_Price.Prospect, tbl_Offer_1_Price.ProdRenType, " _

                & " tbl_Offer_1_Price.ProductMix, tbl_Offer_1_Price.TDSPType, tbl_Offer_1_Price.QuoteType, " _

                & " tbl_Offer_1_Price.SwitchType, CONVERT(varchar(10), tbl_Offer_1_Price.SwitchDate, 101) as SwitchDate, tbl_Offer_1_Price.Term, " _

                & " tbl_Offer_1_Price.Plus1_Billing, tbl_Offer_1_Price.Balanced_Billing, tbl_Offer_1_Price.Price, " _

                & " tbl_Offer_1_Price.EMS_adder, tbl_Offer_1_Price.EMSVersion, tbl_Offer_1_Price.SerChg, " _

                & " tbl_Offer_1_Price.NumOfEsiids, tbl_Offer_1_Price.Annual_Kwh, tbl_Offer_6_COGS.Strip_MthYr, " _

                & " tbl_Offer_1_Price.EFL_ver, tbl_Offer_1_Price.TC_ver, tbl_Offer_1_Price.YRAC_ver " _

                & " FROM tbl_Offer_6_COGS RIGHT JOIN tbl_Offer_1_Price " _

                & " ON tbl_Offer_6_COGS.IDofOFFER = tbl_Offer_1_Price.IDofOFFER " _

                & " WHERE(((tbl_Offer_6_COGS.Fwd_Mth) = 1 Or (tbl_Offer_6_COGS.Fwd_Mth) Is Null)) " _

                & " and (((tbl_Offer_1_Price.OfferID)= @pOfferID))" _

                & " GROUP BY tbl_Offer_1_Price.OfferID, tbl_Offer_1_Price.ProspectID, tbl_Offer_1_Price.DealCapID, " _

                & " tbl_Offer_1_Price.Prospect, tbl_Offer_1_Price.ProdRenType, tbl_Offer_1_Price.ProductMix, " _

                & " tbl_Offer_1_Price.TDSPType, tbl_Offer_1_Price.QuoteType, tbl_Offer_1_Price.SwitchType, " _

                & " tbl_Offer_1_Price.SwitchDate, tbl_Offer_1_Price.Term, tbl_Offer_1_Price.Plus1_Billing, " _

                & " tbl_Offer_1_Price.Balanced_Billing, tbl_Offer_1_Price.Price, tbl_Offer_1_Price.EMS_adder, " _

                & " tbl_Offer_1_Price.EMSVersion, tbl_Offer_1_Price.SerChg, tbl_Offer_1_Price.NumOfEsiids, " _

                & " tbl_Offer_1_Price.Annual_Kwh, tbl_Offer_6_COGS.Strip_MthYr, tbl_Offer_1_Price.EFL_ver, " _

                & " tbl_Offer_1_Price.TC_ver, tbl_Offer_1_Price.YRAC_ver; "
 

                Dim SRCommand7 As New System.Data.SqlClient.SqlCommand(SQL_GetOffer1PriceData, SRConn)

                SRCommand7.Parameters.AddWithValue("@pOfferID", CType(dgItem.FindControl("lblOfferID"), Label).Text)

                Dim dsOffer1Price As New System.Data.DataSet()

                Dim da7 As New System.Data.SqlClient.SqlDataAdapter(SRCommand7)

                da7.Fill(dsOffer1Price, "Offer1Price")
 

                'Send email to analysts, ci transactions

                'Multiple recepients can be specified using ; as the delimeter

                'Address of the recipient

                Mailmsg.To = "ToEmailAddress"
 

                'Your From Address

                'You can also use a custom header Reply-To for a different replyto address

                Mailmsg.From = "FromEmailAddress"
 

                'Specify the body format
 

                Mailmsg.BodyFormat = MailFormat.Text

                'Send the mail in HTML Format
 

                'Mailmsg.BodyFormat = MailFormat.Text
 
 

                'If you want you can add a reply to header 

                'Mailmsg.Headers.Add("Reply-To", "sample@sample.net")

                'custom headersare added like this

                'Mailmsg.Headers.Add("Manoj", "TestHeader")
 

                'Mail Subject

                Mailmsg.Subject = "Test Email"
 

                'Mail Body

                Mailmsg.Body = dgItem.ToString()
 

                'Set the properties

                'Assign the SMTP server

                SmtpMail.SmtpServer = "MySMTPServer"
 

                'Call the send method to send the mail

                SmtpMail.Send(Mailmsg)
 

            Else

                SQL_Update = " UPDATE EnrollCustomer SET [Status] = 999, DCApproved = 1 " _

                & " WHERE DealCapID = " & CType(dgItem.FindControl("lblDealCapID"), Label).Text

                SRCommand1.Connection = SRConn

                SRCommand1.CommandType = Data.CommandType.Text

                SRCommand1.CommandText = SQL_Update

                'SRCommand1.Parameters.AddWithValue("@pDCID", 

                SRCommand1.ExecuteNonQuery()

            End If

        Next
 

        dgDCDetails.DataSource = Nothing

        dgDCDetails.DataBind()
 

        If SRConn.State <> Data.ConnectionState.Closed Then

            SRConn.Close()

        End If

    End Sub

Open in new window

0
Comment
Question by:utlonghornjulie
1 Comment
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 22612383
Hi,
You have to loop through the dataset and add each row to the email body using HTML tables.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Clone Visual studio 2013 solution 5 25
VB.Net - For Loop Error 5 37
Vb. Net application freezes 9 30
.NET tools for adding thread safety to a web app? 3 17
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now