Solved

Send email with dataset as email body

Posted on 2008-09-30
1
315 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SLMGR Switches Are Not Working On KMS Host 3 92
VB.net Open Folder in Windows Explorer 3 29
Write to a printer using vb.net 9 25
vb.net class 3 12
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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