.netms-accessjoinoledbdataadapter

OleDbDataAdapter throwing AccessViolationException when using statment including a Join


I have an old piece of code that has been working until last week. It uses a OleDb helper class to query a MS Access database. I've been able to pin point the error the a specific query. After a little experimentation, I can confirm that when the statement contains a JOIN clause the OleDataAdapter.Fill([DataSet]) line throws aa AccessViolationException. I've successfully run the same exact query from the Access file. As well as other simpler queries though the helper class.

What might cause this exception? My research has not provided me any insight, it appears to be a generic, "Something went wrong" The stack trace shows it originating so deep as to be unreachable

StackTrace: 
at System.Data.Common.UnsafeNativeMethods.ICommandText.Execute(IntPtrpUnkOuter, Guid& riid, tagDBPARAMS pDBParams, IntPtr& pcRowsAffected,Object& ppRowset)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at OleDatabase.hGetDS(String strSQL) in OleDatabase.cs:line 78
at GunTester.ServiceTest.getSalesOrder(String value) in ServiceTest.cs:line 53
at GunTester.ServiceTest.Manual() in ServiceTest.cs:line 29

GunTester is my test class, OleDatabase is the helper class.

I suspect an environment change of some kind, but as far as anyone here is aware, the server hosting this code has not been touched for days prior to the error surfacing. The code likewise has not been edited. The service running this code is called dozens of times a day.

EDIT:

Here is a striped down block of code, removing the helper class and all other layers. The query contains a single inner join. I had also seen it happen with left joins, but on review that query included this as a sub query. As mentioned, the connection string points to a MS Access database, and the error is thrown by da.Fill(A)

public void MinimumTest()
{
        string joinCommand = "SELECT CI_I.ItemCode, IM_I.BinLocation FROM CI_Item CI_I INNER JOIN IM_ItemWarehouse IM_I ON (CI_I.DefaultWarehouseCode = IM_I.WarehouseCode) AND(CI_I.ItemCode = IM_I.ItemCode)";

        OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["RTMAS90"].ConnectionString);
        OleDbDataAdapter da = new OleDbDataAdapter(joinCommand, conn);

        DataSet A = new DataSet();
        da.Fill(A);

        Assert.AreEqual(1, A.Tables.Count);
}

PLOT TWIST: I created some new simple tables with dummy data

Table A     Table B
ID | This   ID | That
 1 | A       1 | A
 2 | C       2 | B 
 3 | E       3 | C

and used a new query in the above Test Method

SELECT this,that FROM A INNER JOIN B ON A.id = b.id

This didn't throw an error. The difference between these new tables and the tables used in the first failing example is that they original tables are linked from MAS 200.


Solution

  • In MS Access I used the Linked Table Manager to refresh the linked tables in the query, this resolved the issue.