Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

VB.Net app reading contacts from SQL Server db into Outlook 2000 returns difficult-to-identify COM error

Hi all,

I have a VB.Net app which runs stored procs against an SQL Server db to write the contacts of the logged in user to their Outlook contacts folder. It all works fine except sometimes I get this error:
System.Runtime.InteropServices.COMException(OxAF6040005 although the specific number here varies): The operation failed
at Outlook._ContactItem.Close(OlInspectorClose SaveMode)
Some debugging I've done also gives me this information: Error Code: -936361979 (again this differs each time)
Message: The operation failed.
Source: Microsoft Outlook

The result is that some but not all of the person's contacts are written to Outlook, and the record on which it stops differs every time (so sometimes it will fail after 4 records, sometimes after 272 - no consistency so it's not as though there's a problem with a particular record, also it happens to <1 user, only common theme is that of 4 users tested it happens to those with >100 contacts but not to those with <100 contacts).

I can't find any really useful info on this on the web and I'm keen to get it sorted hence high number of points.

Thanks in advance,

Louise
0
louise001
Asked:
louise001
  • 4
  • 3
1 Solution
 
LacutahCommented:
Try adding        

    System.Threading.Thread.CurrentThread.Sleep(100) 'Pause adding records for 1/10 a second

between each iteration of adding a contact - Outlook is an unmanaged application, so perhaps it's throwing an exception because it hasn't had enough time  to close the resources from the last addition...

Also, could use a try / catch block around each contact insertion, if an error, repeat the last addition.
0
 
louise001Author Commented:
Thanks for both suggestions. Unfortunately the first doesn't work, I've tried increasing the length of time to as much as 1000 and the error still occurs. Could you tell me the syntax for the second suggestion? This is my code as at present:

******************************************************************************************
 Try
            'Delete contacts
            Do 'outer loop
                Do While Not olContactItem Is Nothing 'inner loop
                    olContactItem.Delete()
                    olContactItem = olFolder.FindNext
                    Exit Do 'exit inner loop
                Loop
            Loop Until olContactItem Is Nothing

            Dim drowFContact As DataRow = Me.DsFrontierContactsByUserSelect.SpFrontierContactsByUserSelect.Rows(0)

            'Re-write contacts
            Dim Counter As Integer = 0
            Do 'outer loop
                Do While Counter < Me.DsFrontierContactsByUserSelect.SpFrontierContactsByUserSelect.Rows.Count
                    For Each drowFContact In Me.DsFrontierContactsByUserSelect.SpFrontierContactsByUserSelect
                        Counter += 1
                        System.Threading.Thread.CurrentThread.Sleep(100)
                        OlContactItemNew = olApp.CreateItem(Outlook.OlItemType.olContactItem)
                        OlContactItemNew.Categories = "Frontier Contacts"
                        If Not drowFContact("Forenames") Is DBNull.Value Then
                            OlContactItemNew.FirstName = drowFContact("Forenames")
                        End If
                        If Not drowFContact("Surname") Is DBNull.Value Then
                            OlContactItemNew.LastName = drowFContact("Surname")
                        End If
                        If Not drowFContact("Forenames") Is DBNull.Value Then
                            If Not drowFContact("Surname") Is DBNull.Value Then
                                OlContactItemNew.FullName = drowFContact("Name")
                            End If
                        End If
                        If Not drowFContact("Email") Is DBNull.Value Then
                            OlContactItemNew.Email1Address = drowFContact("Email")
                        End If
                        If Not drowFContact("OrganisationName") Is DBNull.Value Then
                            OlContactItemNew.CompanyName = drowFContact("OrganisationName")
                        End If
                        If Not drowFContact("DirectTelephone") Is DBNull.Value Then
                            OlContactItemNew.BusinessTelephoneNumber = drowFContact("DirectTelephone")
                        End If
                        If Not drowFContact("Mobile") Is DBNull.Value Then
                            OlContactItemNew.Email1Address = drowFContact("Mobile")
                        End If
                        Try
                            OlContactItemNew.Close(Outlook.OlInspectorClose.olSave)
                        Catch ComEx1 As Exception
                        End Try

                    Next
                    Exit Do 'exit inner loop
                Loop
            Loop Until Counter = Me.DsFrontierContactsByUserSelect.SpFrontierContactsByUserSelect.Rows.Count 'exit outer loop
******************************************************************************************

Hopefully :-)

Louise
0
 
LacutahCommented:
I fixed up your do loops in this example as well.  This shows an example of how to use the Try / Catch block.  You'll want to decide how to handle an exception, ie, stop the entire process, skip the row, whatever.  Also, you may want to look at  the data you're using when it's having a problem.  Perhaps everything was null?

        'Delete contacts
        olContactItem = olFolder.FindNext
        Do While Not olContactItem Is Nothing
            'To catch an error when deleting...
            Try
                olContactItem.Delete()
            Catch 'Not trying to catch any specific error, just want to try again...
                'Wait for 1/2 second before trying again...
                System.Threading.Thread.CurrentThread.Sleep(500)
                Try
                    olContactItem.Delete()
                Catch ex As Exception
                    'Handle the problem here - perhaps just skip this one...
                    MessageBox.Show("There was a prolem deleting the contact." & vbCrLf & ex.Message, "Problem Deleting Contact", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    'Because we're in a Try / Catch block, the exception is handled, so the code continues without deleting in this case....
                End Try
            End Try
            olContactItem = olFolder.FindNext
        Loop

        Dim drowFContact As DataRow

        'Variable to hold number of errors encountered in a row.
        Dim numErrors As Integer = 0
        Dim hadError As Boolean = False

        'Re-write contacts
        Dim Counter As Integer = 0
        'The for loop will incriment counter by 1 each time it loops...
        For Counter = 0 To DsFrontierContactsByUserSelect.SpFrontierContactsByUserSelect.Rows.Count - 1
            Try
                OlContactItemNew = olApp.CreateItem(Outlook.OlItemType.olContactItem)
                OlContactItemNew.Categories = "Frontier Contacts"
                If Not drowFContact("Forenames") Is DBNull.Value Then
                    OlContactItemNew.FirstName = drowFContact("Forenames")
                End If
                If Not drowFContact("Surname") Is DBNull.Value Then
                    OlContactItemNew.LastName = drowFContact("Surname")
                End If
                If Not drowFContact("Forenames") Is DBNull.Value Then
                    If Not drowFContact("Surname") Is DBNull.Value Then
                        OlContactItemNew.FullName = drowFContact("Name")
                    End If
                End If
                If Not drowFContact("Email") Is DBNull.Value Then
                    OlContactItemNew.Email1Address = drowFContact("Email")
                End If
                If Not drowFContact("OrganisationName") Is DBNull.Value Then
                    OlContactItemNew.CompanyName = drowFContact("OrganisationName")
                End If
                If Not drowFContact("DirectTelephone") Is DBNull.Value Then
                    OlContactItemNew.BusinessTelephoneNumber = drowFContact("DirectTelephone")
                End If
                If Not drowFContact("Mobile") Is DBNull.Value Then
                    OlContactItemNew.Email1Address = drowFContact("Mobile")
                End If
                OlContactItemNew.Close(Outlook.OlInspectorClose.olSave)
            Catch ComEx1 As Exception
                numErrors += 1
                hadError = True
                'See if we've already had errors in this row...
                If numErrors > 1 Then
                    'Fine, don't try again with this data row...
                    MessageBox.Show("Unable to add contact for ..." & vbCrLf & comex1.Message, "Error adding contact.", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    numErrors = 0
                    hadError = False
                Else 'First time this row of data caused an error.
                    'OK, there was an error...  Wait 1/2 second, then try the loop again.
                    System.Threading.Thread.CurrentThread.Sleep(500)
                    'subtract 1 from the counter so that the row will be repeated again.
                    Counter -= 1
                End If
            End Try
            'Zero out the number of errors for the data row if no error reported.
            If Not hadError Then numErrors = 0
            'Set hadError to false regardless for next iteration.
            hadError = False
        Next
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
louise001Author Commented:
Thanks for this, I appreciate it. It's helpful to be sure that the error's coming from Outlook. Overall I haven't solved it though: for people with several hundred contacts the writing always hangs at a (seemingly) random point. It's not to do with nulls as we don't have any records in the db in which everything's null (input rules prevent that) and I've checked the records on which it hangs, plus obviously the code says if not null then write. So I've now got 3 questions:
1. In your suggestion you don't set drowFContact to anything so I've continued setting it as below. Do you think there's a better way, something else to which to set it?
2. When the com error occurs, the code doesn't move on to the next record, each following record has an error too ... that's more information than a question I guess as this isn't caused by your code as far as I can see
3. What do you think of this idea: given that I know it runs smoothly for users with fewer contacts, I could break the user's contacts into blocks of say 100 and write 100 at a time with a delay between each block to give Outlook breathing space

Best regards and thanks again for your input,

Louise


Try

  Dim drowFContact As DataRow = Me.DsFrontierContactsByUserSelect.SpFrontierContactsByUserSelect.Rows(0)

            'Re-write contacts
            Dim Counter As Integer = 0
            Dim numErrors As Integer = 0 'ee
            Dim hadError As Boolean = False 'ee

            Do 'outer loop
                Do While Counter < Me.DsFrontierContactsByUserSelect.SpFrontierContactsByUserSelect.Rows.Count
                    For Each drowFContact In Me.DsFrontierContactsByUserSelect.SpFrontierContactsByUserSelect
                        Counter += 1
                        OlContactItemNew = olApp.CreateItem(Outlook.OlItemType.olContactItem)
                        OlContactItemNew.Categories = "Frontier Contacts"
                        If Not drowFContact("Forenames") Is DBNull.Value Then
                            OlContactItemNew.FirstName = drowFContact("Forenames")
                        End If
                        If Not drowFContact("Surname") Is DBNull.Value Then
                            OlContactItemNew.LastName = drowFContact("Surname")
                        End If
                        If Not drowFContact("Forenames") Is DBNull.Value Then
                            If Not drowFContact("Surname") Is DBNull.Value Then
                                OlContactItemNew.FullName = drowFContact("Name")
                            End If
                        End If
                        If Not drowFContact("Email") Is DBNull.Value Then
                            OlContactItemNew.Email1Address = drowFContact("Email")
                        End If
                        If Not drowFContact("OrganisationName") Is DBNull.Value Then
                            OlContactItemNew.CompanyName = drowFContact("OrganisationName")
                        End If
                        If Not drowFContact("DirectTelephone") Is DBNull.Value Then
                            OlContactItemNew.BusinessTelephoneNumber = drowFContact("DirectTelephone")
                        End If
                        If Not drowFContact("Mobile") Is DBNull.Value Then
                            OlContactItemNew.Email1Address = drowFContact("Mobile")
                        End If
                        Try
                            OlContactItemNew.Close(Outlook.OlInspectorClose.olSave)
                        Catch ComEx1 As Exception
                            numErrors += 1
                            hadError = True
                            'See if there have already been errors in this row
                            If numErrors > 1 Then
                                'don't try again with this row
                                'MsgBox("Unable to write" & vbCrLf & ComEx1.ToString, MsgBoxStyle.OKCancel)
                                Debug.WriteLine(drowFContact("Surname"))
                                numErrors = 0
                                hadError = False
                            Else 'it is the first time this row has caused an error, wait 0.5 seconds then try loop again
                                System.Threading.Thread.CurrentThread.Sleep(1000)
                                'subtract 1 from counter in order to repeat row again
                                Counter -= 1
                            End If
                        End Try
                        'zero out the number of errors for drowfcontact if no errors
                        If Not hadError Then numErrors = 0
                        'set hadError to false regardless for next iteration
                        hadError = False
                    Next
                    Exit Do 'exit inner loop
                Loop
            Loop Until Counter = Me.DsFrontierContactsByUserSelect.SpFrontierContactsByUserSelect.Rows.Count 'exit outer loop

        Catch SqlEx As SqlClient.SqlException
            MsgBox(SqlEx.ToString, , "SQL error")
        Catch AdoEx As System.Data.DataException
            MsgBox(AdoEx.ToString, , "ADO error")
        Catch ComEx As System.Runtime.InteropServices.COMException
            Debug.WriteLine("Error Code: " & ComEx.ErrorCode)
            Debug.WriteLine("Message: " & ComEx.Message)
            Debug.WriteLine("Source: " & ComEx.Source.ToString)
            MsgBox(ComEx.ToString, , "COM error")
        Catch SysEx As System.Exception
            MsgBox(SysEx.ToString, , "System error")
0
 
LacutahCommented:
1.  In my example, (and in yours) there is no reason to assign drowFContact  with a value, merely need to declair the variable.  The "for each drowFContact in [table]" automatically assigns the value, so assigning it early is redundant.

2.  OK, errors continue after the first error - this would indicate that the underlying object (olApp) is throwing the error or at least is maintaining the error state even though your creating a new contact.  How about re-initializing the olApp oject in the exception code?  I.e.,
 olApp.Close
 olApp = new OutlookApplicationObject() (I'm not sure how you create a new instance, not included in your code...)

3.  If the error happens at random, but not as often with smaller amounts of additions, then I would address with the above #2, else your always going to still have a chance of the error occuring again.

Why do you have three nested loops?  Following my example, you see how it can be taken down to one "for" loop, it seems that your loops are redundant...
0
 
louise001Author Commented:
I think I see what you mean on all counts. Also I've noticed a typo in my code where I write a mobile number to Outlook's email address field which I guess won't help things. This is just to say though that I'll be back on to this Friday am UK time as not at work tomorrow & wanted to explain why I wasn't responding :-)
0
 
louise001Author Commented:
Well I've got this working up to a point (only problem now is if there's a very high number of contacts Outlook tends to crash) and it's clear that problems that arise always come from Outlook, so the points are yours cos your code got me to this point as well as teaching me a few things I didn't know. So thanks very much.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now