CODEWITHSUNDEEP
c#vbasqliteexcel-dna
jason m
jason m

Reputation: 6835

Excel DNA - Argument works from spraedsheet, but not from vba

This is a bit weird to me.

I have the following code:

      public static object[,] dbMultipleSingleQueries(string strDBPath, object[] oSQL)
      {
       //This is needed for attaching a DB.
       object[,] objOut;
       objOut = new Object[1, 1];
       try
       {

        //Prepare DB connection string      
        strDBPath = "Data Source=" +
              @strDBPath +
              ";Version=3";

        //SQL Objs
        SQLiteConnection dbConnection = new SQLiteConnection(strDBPath);
        SQLiteCommand dbCommand = null;
        SQLiteDataAdapter dbAdapter = null;
        DataTable table = new DataTable();
        //For looping
        int j = 0;
        //Conn str
        dbConnection.Open();
        dbCommand = new SQLiteCommand(dbConnection);
        while (j < oSQL.Length)
        {
         try
         {
          dbCommand.CommandText = oSQL[j].ToString();
          dbCommand.CommandType = System.Data.CommandType.Text;
          if (j == (oSQL.Length - 1))
          {
           dbAdapter = new SQLiteDataAdapter(dbCommand);
           dbAdapter.Fill(table);
           objOut = table2Array(table);
          }
          else
          {

           dbCommand.ExecuteNonQuery();
           objOut[0, 0] = "Success!";
          }
         }
         catch (Exception e2)
         {
          System.Console.WriteLine(e2.StackTrace.ToString());
          objOut[0, 0] = e2.StackTrace.ToString();
         }
         j++;
        };
        dbConnection.Close();
        dbConnection = null;

       }
       catch (Exception e)
       {
        objOut[0, 0] = e.StackTrace.ToString();
       }
       return objOut;

      }

which works perfectly if i feed my arguments from a spreadsheet like this:

    o = Application.Run("dbMultipleSingleQueries", "20130201.db", [test])

where [test] is a range in my workbook with queries that work and look something like this:

attach database 'a.db' as d1

select * from d1.main

now if i do the following in vba

    Option Explicit
    Sub Main()

    Dim sql(1 To 2, 1 To 1)

    sql(1, 1) = "attach database 'a.db' as d1"
    sql(2, 1) = "select * from d1.main"

    o = Application.Run("dbMultipleSingleQueries", "20130201.db", sql)

    End Sub

this does not work. i get a "Type Mismatch" error. Any input why would be a huge help this is driving me nuts!

Upvotes: 1

Views: 843

Answers (1)

Floris
Floris

Reputation: 46365

Your function prototype specifies that the second argument needs to be of type "Object[]" - but you are passing a variant that probably thinks it is a string array, based on the assignment.

Upvotes: 1

Related Questions