VBA calling C# Routine

VBA calling C# Routine

Postby stephen » Tue Aug 26, 2008 11:27 pm

I have a VBA code attempting to call a C# routine and pass a string parameter to the C# routine
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;
        }


    }
}
stephen
 
Posts: 507
Joined: Thu Feb 09, 2006 9:37 am
Location: Brisbane

Return to Microsoft Office

Who is online

Users browsing this forum: No registered users and 1 guest

cron