Inline SQL problem

[Guest]

Hello,

I'm developing a APP that reads an Excel spreadsheet, does data
manipulation, and writes to a SQL Server table. I working with C#, Visual
Studio 2003, and Framework 1.1. When I connect to the Excel workbook I'm
using OLEDB.

The problem I'm have is with my In-line SQL having spaces in the Column
Headings. Here's a code snippet of my Sql:

string sqlCommand = "Select 'Invoice #','Invoice Date','Equipment
ID','Invoice Line Item Amount','Authorization #' " +
"From [InvoiceTemplate$]";

It is failing when I execute the sqlCommand because our vendors are sending
us Column headings with spaces and # signs. I created a dummy workbook with
normal column headings, and no spaces or special characters and I connected
fine.

Has anyone ran into this problem? If so, how did you handle it?

Thanks,

 

[Marc Gravell]

SELECT [Invoice #], [Invoice Date], [Equipment ID] etc

Watch out for columns with ] in them ;-p (blah blah SQL injection blah blah
concatenating blah parameters blah - not always possible if the schema is
dynamic...)

Of course, if you want to read all the columns from a single table, then
SELECT * may be a reasonable choice *in this case* (only). I wouldn't
normally recommend this, but it could save you from a lot of problems...

Marc

 

[Marc Gravell]

Sorry - on first glance I thought the problem was in reading from the
uploaded table (in SQL Server) - not sure if this syntax will be friendly to
Excel - worth a try, though!

Marc

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

you can use [] to enclose the columns. Another possibility is to simply do a
SELECT *

 

[Guest]

Nope, the [] did not work. I even tried qualifing the table name with an
alias and that did not work.

 

[Guest]

Another issue I'm running into is when PROTECT SHEET & PROTECT WORKBOOK are
turned on.

When working with OLEDB & C#, are there additional OLEDB parameters my C#
program needs to pass when these switches are turned on?

Thanks,