but the C# receives an empty string
This is my VBA code in an excel 2007 workbooh
- Code: Select all
Private Sub TestDotNetCall()
Dim testClass As New DotNetClass
' MsgBox testClass.DotNetMethod("World")
Dim psDBConnet As String
Dim riDBHandle As Long
Dim plDBConnectTimeout As Long
Dim plDBCommandTimeout As Long
psDBConnet = "Provider=SQLNCLI.1;Data Source=PC-12OCT07\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=AdventureWorks"
'Provider=SQLNCLI.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW
plDVConnectTimeout = 10
plDBCommandTimeout = 20
MsgBox testClass.ADOMDConnectionOpen1(riDBHandle, psDBConnect, plDBConnectTimeout, plDBCommandTimeout)
'MsgBox testClass.ADOMDConnectionOpen(riDBHandle, psDBConnect, plDBConnectTimeout, plDBCommandTimeout)
End Sub
This is the code in the Visual Studio 2008 C# program
- Code: Select all
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.AnalysisServices.AdomdClient;
using System.Runtime.InteropServices;
namespace DotNetLibrary
{
// Allows for intellisense (early bounding)
[ClassInterface(ClassInterfaceType.AutoDual)]
public class DotNetClass
{
uint MAX_CONNECTIONS = 100;
int[] ADOConnections;
public DotNetClass() // default Constructor
{
// Initialise the connection array
ADOConnections = new int[MAX_CONNECTIONS];
for (int i = 0; i < MAX_CONNECTIONS; i++)
{
ADOConnections[i] = 0;
}
}
public string ADOMDConnectionOpen1(ref int riDBHandle, ref string psDBConnect, int plDBConnectTimeout, int plDBCommandTimeout)
{
// Returns 0 and Handle >=0 if all OK
// Returns -1 if any errors, iHandle >= 0 if connection object created OK (but some other error)
// Returns -1 if any errors iHandle = -1 if connection object not created
uint iDBHandle = 0;
AdomdConnection objconADOMDs;
string bstrAccessConnect;
try
{
if (GetNextConnectionHandle(ref iDBHandle))
{
objconADOMDs = new AdomdConnection();
bstrAccessConnect = psDBConnect;
objconADOMDs.ConnectionString = bstrAccessConnect;
return "3";
}
}
catch (Exception ex) {
Type t = psDBConnect.GetType();
Console.WriteLine("Error accessing the database: " + ex.Message+ psDBConnect);
Console.ReadLine();
return "Error accessing the database: " + ex.Message+ "Type= "+t+" ConnectString= "+psDBConnect;
}
return "2";
}
public bool ADOMDConnectionOpen(ref uint riDBHandle, [MarshalAs(UnmanagedType.LPStr)] string psDBConnect, int plDBConnectTimeout, int plDBCommandTimeout)
//public bool ADOMDConnectionOpen(ref uint riDBHandle, ref string psDBConnect, long plDBConnectTimeout, long plDBCommandTimeout)
{
// Returns 0 and Handle >=0 if all OK
// Returns -1 if any errors, iHandle >= 0 if connection object created OK (but some other error)
// Returns -1 if any errors iHandle = -1 if connection object not created
uint iDBHandle = 0;
AdomdConnection objconADOMDs;
string bstrAccessConnect;
try
{
if (GetNextConnectionHandle(ref iDBHandle))
{
objconADOMDs = new AdomdConnection();
bstrAccessConnect = psDBConnect;
objconADOMDs.ConnectionString = bstrAccessConnect;
/***
objconADOMDs[iDBHandle].Open();
catch (Exception ex) {
//' The connection could not be opened or was disconnected.
//' This error can occur at any time, if the provider is
//' disconnected from the server.
Console.WriteLine(ex);// sjk remove after testing
} }
objconADOMDs[iDBHandle].ChangeDatabase();
ADOMDCatalog[iDBHandle].CreateInstance( __uuidof(ADOMD::Catalog)); //creating connection to database
ADOMDCatalog[iDBHandle]->ActiveConnection = ADOConnection[iDBHandle];
riDBHandle = iDBHandle;
return 0;
*****/
return true;
}
return false;
}
catch (Exception ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return false;
}
}
bool GetNextConnectionHandle(ref uint iHandle)
{
uint i;
i = 0;
do
{
if (ADOConnections[i] == 0)
{
iHandle = i;
return true;
}
i++;
} while (i < MAX_CONNECTIONS);
return false;
}
}
}