Syntax while trying to include The multi-part identifier [°C] in command text

Question

I am trying to copy data directly from a Microsoft Excel file into the database using Asp.net. The problem is that the spreadsheet has a column named Product[°C], which, when I include in the query, fails to parse as valid SQL.

I'm using the following code from an example on how to connect to and query an Excel file via OleDb and Ado.net.

var conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0},HDR={1}";
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
connExcel.Open();
var SheetName = "Sheet1$";

// ...
cmdExcel.CommandText = "SELECT [Date/time] as [Date/time], [Product[°C]]] as [Product] From [" + SheetName + "] ";

Here the problem bit is [Product[°C]]]. The debugger gives an error saying:

Additional information: Syntax error in query expression '[Product[°C]]]'.

Where as if I want to do query a table with [Product[°C]]] in SQL I can do it via using:

Select id, [Product[°C]]] from TableName

This query parses fine in SQL but not when I query Excel. Why not?

What do you mean, "doesn't work in ASP.NET"? That's ridiculous. Please show the code you're using. — John Saunders, Dec 31 '14 at 03:37
I am getting the following error Additional information: Syntax error in query expression '[Product[°C]]]'. — b k, Dec 31 '14 at 03:40
Are you querying the excel spreadsheet directly with the JET driver, or did you import this into SQL server? The reason is that the SQL parser for each engine is different. — codekaizen, Dec 31 '14 at 03:48
I have updated the question with more code if this helps thanks — b k, Dec 31 '14 at 03:57
Through the code I am trying to copy the data in the spreadsheet into the database. The connection and everything worked perfectly fine but after I included column names instead of * I have started getting errors — b k, Dec 31 '14 at 04:03

codekaizen · Accepted Answer · 2014-12-31T20:23:40.120

1

Ok, I've finally deducted that you are using the Excel Jet driver to connect directly to the Excel spreadsheet. This driver uses a SQL engine which has some... unique features.

One feature is to convert square brackets in a column name to parentheses. What you want is likely this:

 cmdExcel.CommandText = "select [Date/Time], [Product(°C)] from [Sheet1$]";

Note the use of '(' in place of '['.

edited Dec 31 '14 at 20:23

answered Dec 31 '14 at 03:41

codekaizen

26,990
7
84
140

I tried this and I am getting following error Error 1 Unrecognized escape sequence Error 2 Unrecognized escape sequence – b k Dec 31 '14 at 03:44
Did you add the "Escape" clause? – codekaizen Dec 31 '14 at 03:44
Yes this is what I wrote '[Product\[°C\]]' – b k Dec 31 '14 at 03:45
No, you need to also add "ESCAPE '\'" at the end. – codekaizen Dec 31 '14 at 03:45
Thanks I just copied and pasted the code. I am still getting the error unrecognised escape sequence – b k Dec 31 '14 at 03:50
And I wrote [Product[°C]]] with in an extra bracket as it multi-part-identifier for SQL and its works fine if I write the command in SQL – b k Dec 31 '14 at 03:54
Ok, I followed the link which you slapped on the end of your question. Please, next time, just start by explaining exactly what you are doing. – codekaizen Dec 31 '14 at 08:53
1

Thank you for your help and your time. I changed the code to Select * and then mapping the columns. Your solution is acceptable and correct as well. Thank you – b k Jan 05 '15 at 07:44

Syntax while trying to include The multi-part identifier [°C] in command text

1 Answers1