utlonghornjulie
asked on
Send email with dataset as email body
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.