Excel PivotTable C# problem

M

Markus

Hello to all,
can anybody help me with this problem:

I'm connecting an Excel PivotTable to a MySQL database. The PivotTable
is created, but when I open one of the item-list dialogs by pressing
the small button in the PivotField headers, select some items (even if
I leave them as they are) and confirm with OK, then Excel crashes. I
have no idea why. The only thing I noticed is that the connection
string could be a problem.

I tried to connect also by specifying the Connection and the
CommandText property of the PivotCache. The problem ocurred then, when
I put all in a loop and create more than one PivotTable (each in a new
Worksheet).

It would be great if anybody could tell me what is going on.
Thanks a lot,
Markus

PD:
The code I'm using is:

strConnection = "DSN=MyDSN;OPTION=3;UID=MyUID;PASSWORD=;"
+ "DRIVER={MySQL ODBC 3.51 Driver};DATABASE=MyDB;"
+ "SERVER=localhost;";

strSelectCommand = "SELECT ld.Name, ld.Date FROM MyDB ld WHERE"
+ " ld.Name='A Name' AND ld.Date>={d'2004-06-01'} AND "
+ "ld.Date<={d'2004-07-01'}"; // I tried it also without
the
// {d }
adoConnection = new ADODB.ConnectionClass();
adoConnection.ConnectionString = strConnection;
adoConnection.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
adoConnection.Open(null, null, null, 0);
adoRecordset = new ADODB.RecordsetClass();
adoRecordset.Open(strSelectCommand,adoConnection,
ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockOptimistic,0);

pcPivotCache = xlApp.ActiveWorkbook.PivotCaches().Add(Excel.
XlPivotTableSourceType.xlExternal,
Type.Missing);
pcPivotCache.Recordset = adoRecordset;
rngRange = activeSheet.get_Range("A5", Type.Missing);
pcPivotCache.CreatePivotTable(rngRange, "MyPivotTable", Type.Missing,
Type.Missing);
 
Top