Iron Speed Technical Forums
Register Latest Topics
 
 
 


Reply
  Author   Comment  
des

Registered:
Posts: 126
Reply with quote  #1 
Hi

I need some help please. I am doing the processing of incoming emails and saving it on the SQL database based on the webinar that was presented by James Whistler. 

I am having an error. "Could not find stored procedure 'SelectAttachments'. Below is the code given...

Code:

Dim DeleteMessages As Boolean = True
        Dim LogPath As String = "pop3.log"
        'Dim LogPath As String
        Dim AttachmentDirectory As String = "c:\temp\" 'used for saving attachments out of the database
    Public Overrides Sub EmailMessagesRefreshButton_Click(ByVal sender As Object, ByVal args As ImageClickEventArgs)
        
            Try
                'Download messages from a pop3 server
                DownloadMessages(0, 50)
                'SelectAttachments( int ) shows how to get attachments out of the DataBase
                SelectAttachments(1)
            Catch ex As Exception
                Me.Page.ErrorOnPage = True
                Utils.MiscUtils.RegisterJScriptAlert(Me, "BUTTON_CLICK_MESSAGE", ex.Message)
            Finally
            End Try
    
            Try
                
            Dim EmailMessagesTableControlObj as EmailMessagesTableControl = DirectCast(Me.Page.FindControlRecursively("EmailMessagesTableControl"), EmailMessagesTableControl)
            EmailMessagesTableControlObj.ResetData = True
                        
            EmailMessagesTableControlObj.RemoveFromSession(EmailMessagesTableControlObj, "DeletedRecordIds")
            EmailMessagesTableControlObj.DeletedRecordIds = Nothing
            
            Catch ex As Exception
                Me.Page.ErrorOnPage = True
    
                ' Report the error message to the end user
                Utils.MiscUtils.RegisterJScriptAlert(Me, "BUTTON_CLICK_MESSAGE", ex.Message)
            Finally
    
            End Try
                  
        End Sub
  
        Private Sub DownloadMessages(ByVal startAt As Integer, ByVal numberOfMessages As Integer)
            Dim pop As New POP3(True, False)
            'check for logging
            If Not (LogPath Is Nothing) Then
                pop.LogPath = LogPath
            End If
            pop.Connect()
            Dim messageCount As Integer = pop.MessageCount()
            'check to make sure we don't download more messages than we have available
            If numberOfMessages > messageCount Then
                numberOfMessages = messageCount
            End If
            'grab the messages
            Dim i As Integer
            For i = startAt To (numberOfMessages + startAt) - 1
                Dim [text] As String = pop.GetMessageAsText(i)
                InsertMessage([text])
                If DeleteMessages Then
                    pop.Delete(i)
                End If
            Next i
            'if we are deleted messages, purge them now
            If DeleteMessages Then
                pop.CommitDeletes()
            End If
            'disconnect
            pop.Disconnect()
        End Sub
        Private Sub InsertMessage(ByVal messageText As String)
            Dim message As New MimeMessage(messageText)
            'grab the various message parts
            Dim subject As String = Nothing
            If Not (message.Subject Is Nothing) Then
                subject = message.Subject.Value
            End If
            Dim fromAddress As String = Nothing
            If Not (message.From Is Nothing) Then
                fromAddress = message.From.ToString()
            End If
            Dim toAddresses As String = Nothing
            If Not (message.To Is Nothing) Then
                If message.To.Count > 0 Then
                    toAddresses = message.To.ToString()
                End If
            End If
            Dim ccAddresses As String = Nothing
            If Not (message.CC Is Nothing) Then
                If message.CC.Count > 0 Then
                    ccAddresses = message.CC.ToString()
                End If
            End If
            Dim messageId As String = Nothing
            If Not (message.MessageId Is Nothing) Then
                messageId = message.MessageId.Value
            End If
            Dim contentType As String = Nothing
            If Not (message.ContentType Is Nothing) Then
                contentType = message.ContentType.Value
            End If
            Dim emailDate As DateTime = DateTime.Now
            If Not (message.Date Is Nothing) Then
                Try
                    emailDate = Utility.FromEmailDate(message.Date)
                Catch
                End Try
            End If
            Dim id As Integer = DataEngine.InsertEmailMessage(subject, fromAddress, toAddresses, ccAddresses, messageId, contentType, emailDate, messageText)
            'insert the message bodies
            InsertMessageBodies(message, id)
            'insert the attachments
            InsertAttachments(message, id)
        End Sub 'InsertMessage

        Private Sub InsertAttachments(ByVal message As MimeMessage, ByVal emailId As Integer)
            If Not (message.Attachments Is Nothing) And message.Attachments.Count > 0 Then
                Dim i As Integer
                For i = 0 To message.Attachments.Count - 1
                    Dim attachment As MimePart = message.Attachments(i)
                    Dim contentType As String = attachment.ContentTypeString
                    Dim name As String = attachment.Name
                    Dim filename As String = attachment.Filename
                    If filename = Nothing Then
                        filename = "No attachment name"
                    End If
                    Dim data As Byte() = attachment.Data()
                    DataEngine.InsertAttachment(emailId, contentType, name, filename, data)
          
                    ' store as TIFF
                    Try
                        DbUtils.StartTransaction()
                        Dim myAttachment As AttachmentRecord = New AttachmentRecord
                        myAttachment.AttachmentFileName = Left(filename, InStr(filename, ".") - 1) & ".Tiff"
                        myAttachment.Attachment = data
                        myAttachment.Description = name
                        Dim myInStream As MemoryStream = New MemoryStream(data)
                        Dim myImage As System.Drawing.Image = System.Drawing.Image.FromStream(myInStream)
                        Dim myOutStream As MemoryStream = New MemoryStream()
                        myImage.Save(myOutStream, System.Drawing.Imaging.ImageFormat.Tiff)
                        myAttachment.Attachment = myOutStream.GetBuffer()
                        myAttachment.Save()
                        DbUtils.CommitTransaction()
                    Catch ex As Exception
                        DbUtils.RollBackTransaction()
                    Finally
                        DbUtils.EndTransaction()
                    End Try
          
                Next i
            End If
        End Sub 'InsertAttachments
        Private Sub InsertMessageBodies(ByVal message As MimeMessage, ByVal emailId As Integer)
            'check for a text part
            If Not (message.TextMimePart Is Nothing) Then
                Dim [text] As String = message.TextMimePart.DecodedText()
                Dim charset As String = "us-ascii"
                If Not (message.TextMimePart.CharSet Is Nothing) Then
                    charset = message.TextMimePart.CharSet
                Else
                    'check the parent
                    If Not (message.CharSet Is Nothing) Then
                        charset = message.CharSet
                    End If
                End If
                Dim contentType As String = "text/plain"
                'insert it
                DataEngine.InsertBody(emailId, [text], contentType, charset)
            End If
            'check for a HTML part
            If Not (message.HtmlMimePart Is Nothing) Then
                Dim [text] As String = message.HtmlMimePart.DecodedText()
                Dim charset As String = "us-ascii"
                If Not (message.HtmlMimePart.CharSet Is Nothing) Then
                    charset = message.HtmlMimePart.CharSet
                Else
                    'check the parent
                    If Not (message.CharSet Is Nothing) Then
                        charset = message.CharSet
                    End If
                End If
                Dim contentType As String = "text/html"
                'insert it
                DataEngine.InsertBody(emailId, [text], contentType, charset)
            End If
        End Sub 'InsertMessageBodies

        Private Sub SelectAttachments(ByVal emailId As Integer)
            Dim dt As DataTable = DataEngine.SelectAttachments(emailId)
            Dim i As Integer
            For i = 0 To dt.Rows.Count - 1
                Dim dr As DataRow = dt.Rows(i)
                Dim data As Byte() = CType(dr("data"), Byte())
                If data.Length > 0 Then
                    Dim attachmentName As String = String.Format("attachment{0}.dat", i) 'default attachment name
                    'grab the filename
                    If Not (dr("filename") Is System.DBNull.Value) Then
                        attachmentName = dr("filename").ToString()
                    Else
                        If Not (dr("name ") Is System.DBNull.Value) Then
                            attachmentName = dr("name").ToString()
                        End If
                    End If
                    'save the attachments
                    Dim fs As FileStream = File.Create(Path.Combine(AttachmentDirectory, attachmentName))
                    fs.Write(data, 0, data.Length)
                    fs.Close()
                End If
            Next i
        End Sub 'SelectAttachments
miles

Avatar / Picture

MVP Consultant
Registered:
Posts: 1,659
Reply with quote  #2 
 at what point in this code are you getting this error?
__________________
Miles Gibson, MScIS
Iron Speed MVP
Senior Consultant, Principal
Milestone Software Inc.

http://www.ironspeedmvp.com\localizer 
Email: miles@milestone.ca
Milestone Localizer: Full Localization for your Iron Speed applications!
des

Registered:
Posts: 126
Reply with quote  #3 
Hi Miles

I get it when I click the EmailMessagesRefreshButton_Click
des

Registered:
Posts: 126
Reply with quote  #4 
Miles I got it working. I got a SQL Script from James that included the SelectAttachments stored procedure.

It no longer gives me that error but now I have a new problem, it is not retrieving any of the emails from my gmail account.
Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.

Download Iron Speed Designer

Terms of Service Privacy Statement