Error on a 'Add' in a Pivotcache in Excel

I make an Pivotcache in C# and later want to connect an Access-database to this pivotcache, but i get an error on the 'Add'.

The 'old' code in VB looks like this (and did work):

With WbkXL_Workbook.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlExternal)

.Connection = strConnection

.CommandType = Excel.XlCmdType.xlCmdSql

.CommandText = strCommand

.CreatePivotTable(TableDestination:=WstXL_Worksheet_Bst.Range("A3"), _

TableName:="Draaitabel1")

End With

The c# that i have created is as follows:

Excel.PivotCache PCache;

PCache = WbkXL_Workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, System.Type.Missing);

PCache.Connection = strConnection;

PCache.CommandType = Excel.XlCmdType.xlCmdSql;

PCache.CommandText = strCommand;

PCache.CreatePivotTable(WstXL_Worksheet_Bst.get_Range("A3", System.Type.Missing), "Draaitabel1", System.Type.Missing, System.Type.Missing);

The Error i get is:

AutoUpdateSaveChanges = '((Excel.WorkbookClass)(WbkXL_Workbook)).AutoUpdateSaveChanges' threw an exception of type 'System.Runtime.InteropServices.COMException'

Can anyone please give me a hint

Answer this question

Error on a 'Add' in a Pivotcache in Excel

Jeff Beehler
What it the exception message and full stacktrace
Meyyur

It does not rase an exception.

Only when i set a breakpoint and look into the values of WbkXL_Workbook, I see an exeption error as metioned before "AutoUpdateSaveChanges = '((Excel.WorkbookClass)(WbkXL_Workbook)).AutoUpdateSaveChanges' threw an exception of type 'System.Runtime.InteropServices.COMException'".

When i comment out the code

PCache.Connection = strConnection;

To

PCache.Connection = "";

My code moves on, only offcourse i get no result.

The value of strConnection is as follows and the path is ok because it is the same code as in VB where it did work:

string strConnection;

strConnection = "ODBC;DSN=MS Access-database;DBQ=" + ModPublicDef.StrDbPath + @"\"

+ ModPublicDef.StrDbName + ";DefaultDir=" + System.IO.Directory.GetCurrentDirectory() + @"\"

+ "database;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=rafael;UID=admin;";

Does this give you a better idea of the problem
Srivani V Eluri
You exception object provides you the needed stack-trace.

try
{
// TODO: Add your code here.
}
catch( Exception caught )
{
MessageBox.Show( this, "Exception details:\r\n" + caught.ToString() );
}
Mickael Provost 44
When you run in debug mode and an exception accored the IDE normally breakes and you see the statement that throws the Exception.

The MessageBox must be shown when the exception is thrown in the try-statement.

Because i don't know the full stack-trace and message it is hard to fix it, but as far as i can see the problem is in your connection. But you should try to get the exception details first, so you will learn to use this powerfull method and it will provide you all the information to fix the problem.
abhijit narvekar

Hello Mr. van de Sande,

How do i make a full stacktrace

Freakie.
Error on a 'Add' in a Pivotcache in Excel

Answer this question