C# and excel automation, how to use the CopyFromRecordset function

0

I am trying to populate an excel file with some data from a SQL server,

my code is as follows,

   public static void SQLToExcel(Excel.Workbook xlWorkBook, string SQL, string tFORMAT)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["ANSICConnection"].ConnectionString;
        SqlConnection conn;
        conn = new SqlConnection(connectionString);
        conn.Open();


        using (SqlCommand cmd = new SqlCommand(SQL, conn))
        {
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                reader.Read();

                ((Range)xlWorkBook.Sheets["COVER"].Range("D6")).CopyFromRecordset(reader);


            }
            reader.Close();
        }


        conn.Close();


    }

I get the following error

{"No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))"}

do I need to convert the reader to an array?

c#
.net
excel-automation
asked on Stack Overflow Jun 24, 2014 by user2206329

1 Answer

0

To be able to use CopyFromRecordset you need to replace SqlDataReader with ADODB.Recordset. Don't forget to add a reference to Microsoft ActiveX Data Objects 2.x library.

answered on Stack Overflow Apr 5, 2019 by Sujoy

User contributions licensed under CC BY-SA 3.0