question

sivakrishnak-9752 avatar image
0 Votes"
sivakrishnak-9752 asked ·

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

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,

azure-database-postgresql
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @sivakrishnak-9752, welcome to Microsoft Q&A forum.

From the error message, it seems issue with syntax. Would it be possible for you to attach the excel file as well as code file to repro it as it is?

0 Votes 0 ·

Hi @AnuragSharma-MSFT,

Excel file is exporting from the strategy reports, but the issue is it is not inserting any data and the insert query also is not working, while execution of the insert query it is displaying the error @42601 : syntax error at or near "'Microsoft.ACE.OLEDB.12.0'"/

0 Votes 0 ·

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered ·

Hi @sivakrishnak-9752, 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'




· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @sivakrishnak-9752, wanted to follow up on this. Please let me know if the answer helps or else we can discuss more.

0 Votes 0 ·