42601 : syntax error at or near "'Microsoft.ACE.OLEDB.12.0'"/

Question

Hello,

I am getting this error while I am running my code.(42601 : syntax error at or near "'Microsoft.ACE.OLEDB.12.0'"/)

using (var conn = new NpgsqlConnection(configuration.SqlServerConnectionString))
{
conn.Open();
var command = conn.CreateCommand();
command.CommandTimeOut = 600;
command.CommandType = System.Data.CommandType.Text;

command.CommandText = $@"{$"INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database = {ExportFile};')"}{$" 'SELECT *FROM[{Strings.ApprovalStartegyReport}]'"}

                        SELECT Country Code,
                        Legal Entity Name,
                        Type Name,
                        Subtype ID,
                        Subtype Name,
                        Template Barcode,
                        Template Name,
                        Approver Role,
                        Approver Roles's Order,
                        User First Name,
                        User Last Name,
                        User Position ID,
                        User Position Name,
                        Approver Line Manager First Name,
                        Approver Line Manager Last Name,
                        Approver Line Manager Position ID,
                        Approver Line Manager Position Name EN,
                        Approver Line Manager login


                        FROM public.ApprovalStartegyReport
                        WHERE Country Code IN(SELECT ELEMENT FROM public.func_Split(REPLACE(@country,'''',''),','))
                        ORDER BY Country Code, Legal Entity Name,Category Name,Type Name,Subtype Name,(CASE WHEN Template Barcode IS NULL THEN Flase ELSE True END),Template Barcode,Approver Role's Order;


                        command.Parameters.Add(new NpgsqlParameter("country",NpgsqlTypes.NpgsqlDbType.Varchar));
                        command.Parameters["country"].Value = countries;
                        command.ExecuteNonQuery();

}

this is my code, while I am running this code I am Getting the error : 42601 : syntax error at or near "'Microsoft.ACE.OLEDB.12.0'"/,
could you please explain me the issue and how can I resolve the error.

Thanks,

Answer

Hi anonymous user, I think this issue is more related to compatibility of Npgsql with OpenRowSet function. I was trying to search through the documentation of Npgsql package but could not find any details where in we can use it with OpenRowset.

However there is simple workaround to achieve this is firstly we can load the data from database to a dataset/datatable and then dump that data into excel. Below is one example on the same:

using (var conn = new NpgsqlConnection(connString))
{
        string szFilePath = "yourfilepath";
        string szConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + szFilePath + "';Extended Properties=\"Excel 12.0;HDR=YES;\"";
                var command = new NpgsqlCommand("SELECT * FROM yourtable", conn);
                NpgsqlDataAdapter npgsql = new NpgsqlDataAdapter(command);
                DataSet ds = new DataSet();
                npgsql.Fill(ds);
                conn.Close();
                using (OleDbConnection connOledb = new OleDbConnection(szConnectionString))
                {
                    connOledb.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = connOledb;
                    foreach(DataRow dr in ds.Tables[0].Rows)
                    {
                        cmd.CommandText = @"Insert into [Sheet1$] (id,name,quantity) VALUES ('" + dr[0] + "'," + dr[1] + "','" + dr[2] + "')";
                        cmd.ExecuteNonQuery();
                    }
                }
}

Reference Article: Read Write Excel file with OLEDB in C#

Please let me know if this helps or else we can discuss further.

if answer helps, please mark it as 'Accept Answer'

Share via

42601 : syntax error at or near "'Microsoft.ACE.OLEDB.12.0'"/

1 answer