troubleshooting Question

Asp.Net email attachment issues

Avatar of Jerry Miller
Jerry MillerFlag for United States of America asked on
ASP.NETVisual Basic.NETMicrosoft Applications
5 Comments1 Solution621 ViewsLast Modified:
I am using a stored procedure to pull in the correct data to create my email attachment. I found code on this site to use a stream for the data. 

It seems to work except that I am not getting the entire dataset in the attachment. It is not a size issue as far as I can tell, since I have pulled in only the Top 50 for the stored procedure and it only loads 35 of them. When it is pulling in the entire dataset, there is currently 354 rows, I get 351, and only part of the last row. Something in my code is cutting off the last of the data. I need a fresh set of eyes to see what I have that is screwed up.

Here is the function to create the stream:
Public Function createCSVAttachment(ByVal spName As String) As Stream
Dim conn As SqlConnection = New SqlConnection(commonFunctions.getDSN)
        Dim cmd = New SqlCommand()
        Dim ad As SqlDataAdapter
        Dim tempdata As DataTable = New DataTable

        cmd.Connection = conn
        cmd.CommandText = spName
        cmd.CommandType = CommandType.StoredProcedure
        ad = New SqlDataAdapter(cmd)

        Dim stream As New MemoryStream()
        Dim sw As New StreamWriter(stream)
        Dim sbColumns As New StringBuilder()
        Dim sbRows As New StringBuilder()
        Dim col As DataColumn
        Dim row As DataRow

        'Format the csv file
        For Each col In tempdata.Columns

        For Each row In tempdata.Rows
            For Each col In tempdata.Columns
                sbRows.Append(If(row(col.ColumnName) Is DBNull.Value, "", row(col.ColumnName).ToString())).Append(",")
            sbRows.Remove(0, sbRows.ToString().Length)

        stream.Position = 0

        Return stream


End function

And the one to send the email:

Public Sub SendEmail()
        'General Message
        Dim mailSubject As String = "Test File"
        Dim message As String = String.Empty
        Dim messageBuilder As New StringBuilder
        Dim fileName As String = createFileName()
        Dim path As String = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority)

        l_from = HttpContext.Current.Session("adid") & ""

        messageBuilder.Append("Test Body")
        message = messageBuilder.ToString()

        If getEnvironment() = "Localhost" Then
            l_to = ""
            l_to = Session.SessionManager.ADID
        End If
        l_cc = ""

        Dim Mail As New MailMessage()
        Dim attachment As New EmailAttachments
        Dim attachmentStream As System.IO.MemoryStream = attachment.createCSVAttachment("storedProcName")
        'Dim data As New Attachment(attachmentStream, fileName, "text/csv")

        Mail.From = New MailAddress(l_from)

        'Send failure back to sender
        Mail.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure

        'set the content
        Mail.Subject = mailSubject
        Mail.IsBodyHtml = True
        Mail.Body = message

        Mail.Attachments.Add(New Attachment(attachmentStream, fileName, "text/csv"))

        'send the message
        Dim smtp As New SmtpClient(commonFunctions.getSMTPserver())


        'release resources
    End Sub
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros