locked
Getting NullReferenceException on ADODB.ConnectionClass.Open after several iteration of connecting and disconnecting RRS feed

  • Question

  • In my program, I am running a loop and in the loop, I am connecting to a database (Excel as database), querying, and then disconnecting.

    I am using ADODB interop for this purpose.

    This works fine in most of the computer, but in one machine (Windows 7) it runs for several iterations while connecting it throws NullReferenceException

    System.NullReferenceException: Object reference not set to an instance of an object.
    at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)

    Below are the things which I have tried:

    • I tried to find ADODB.ConnectionClass documentation or code to get to know the possible causes of such an issue but count not find any.
    • I have also performed the suggestion provided in this article, but no luck: https://support.microsoft.com/en-in/help/810098/bug-an-object-reference-not-set-to-an-instance-of-an-object-error-occu
    • One thing I have noticed, in all normal iteration, disconnecting the database takes some time (around 30-50 milliseconds). But sometimes disconnection takes no time and in a subsequent iteration, while connecting to the database again, it gives the mentioned error.

    Here I am adding a sample code.

    Please note that in a real application, every time user fires a query, it creates a connection, fires a query, and then disconnects. That is a legacy flow of the application and it works for most of the users.

    connectionString = 
         "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=data.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=3\";"
                int count = 5000;
    
                for (int i = 0; i < count; i++)
                {
                    Connection connection = new Connection();
                    connection.Open(connectionString, string.Empty, string.Empty, 0); //Here it throws the error
    
                    ADODB.Command command = new ADODB.Command()
                    {
                        ActiveConnection = connection,
                        CommandText = "SELECT * FROM[Sheet1$] where col3 = 21 and col4 = 31",
                        CommandType = CommandTypeEnum.adCmdText
                    };
                    Recordset recordSet = new Recordset
                    {
                        CursorType = CursorTypeEnum.adOpenStatic,
                        CursorLocation = CursorLocationEnum.adUseClient,
                        LockType = LockTypeEnum.adLockReadOnly
                    };
                    recordSet.Open(command);
    
                    var dataTable = new DataTable();
                    OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter();
                    oleDbDataAdapter.Fill(dataTable, recordSet);
    
                    command = null;
    
                    if (connection.State.Equals(1))
                        connection.Close();
                }


    I want to understand the cause behind this error and how to resolve it.



    Wednesday, September 23, 2020 2:35 PM

All replies

  • Is it possible to close the connection unconditionally, without if? Also consider calling recordSet.Close().

    Do you have more call-stack information about the error?

    Wednesday, September 23, 2020 3:00 PM
  • this is the full call stack I am getting. So i think error in the method `Open` itself.

    While, I can check for the other two suggestions you have given.

    Thursday, September 24, 2020 2:35 AM
  • Hi AmitMMaheshwari,

    I make a test based on your code and it works fine in my computer.

    You can also post your question in Developer Community forum to get more help on this problem.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 24, 2020 8:45 AM
  • Check if it works in this case: add try-catch around connection.Open and intercept the NullReferenceException. When it happens, repeat the function, maybe after short delay. (Add a loop).

    Does it work if you retry the failed operation?

    Thursday, September 24, 2020 9:05 AM