Avatar of Jerry Miller
Jerry Miller
Flag for United States of America asked on

Asp.Net email attachment issues

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.

http://weblogs.asp.net/rrobbins/archive/2008/03/01/send-email-attachment-using-a-memory-stream.aspx 

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)
        ad.Fill(tempdata)

        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
            sbColumns.Append(col.ColumnName).Append(",")
        Next
        sw.WriteLine(sbColumns.ToString().TrimEnd(","))

        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(",")
            Next
            sw.WriteLine(sbRows.ToString().TrimEnd(","))
            sbRows.Remove(0, sbRows.ToString().Length)
        Next

        stream.Position = 0

        Return stream

        sw.Flush()
        sw.Close()

        stream.Flush()
        stream.Close()
        stream.Dispose()
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") & "@test.com"

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

        If getEnvironment() = "Localhost" Then
            l_to = "test@test.com"
        Else
            l_to = Session.SessionManager.ADID
        End If
        l_cc = "test@test.com"

        'email
        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)
        Mail.To.Add(l_to)
        Mail.CC.Add(l_cc)

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

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

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

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

        smtp.Send(Mail)

        insertNewSequenceNumber(_sequenceNumber)
        'release resources
        'Mail.Dispose()
    End Sub
ASP.NETVisual Basic.NETMicrosoft Applications

Avatar of undefined
Last Comment
Jerry Miller

8/22/2022 - Mon
Nasir Razzaq

I think you would be better off creating a temp csv file and then attaching that to the email.
Jerry Miller

ASKER
I have been swamped and not able to research this approach or try this yet. I will test in the next couple of days and get back with you.
Jerry Miller

ASKER
I still haven't had a chance to try this. The stream approach mostly works, but it is cutting off the last part of the data no matter how large (or small) the stream is. I would really like to figure out what is wrong with the above code to make it work.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Nasir Razzaq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jerry Miller

ASKER
For anyone reading this thread, I finally got back to this project after a few months. I found the answer on this forum:

http://forums.asp.net/t/1451590.aspx/1

I only had to move sw.Flush() after all of the data is written, but before I set the stream.position = 0

Apparently flushing the streamwriter pushes the last of the data into the stream. I knew that I was getting all of the data from the database, but puzzled as to why it wasn't writing the entire datatable to the stream.