tags:

views:

35

answers:

0

I am reading in an excel file with an OleDBDataAdapter. I am using a select statement to UNPIVOT the data and insert into DataSet. However, the compiler is genereating this error: {"Syntax error in FROM clause."}

But the SQL Statement is correct as I have used it in other DB's

Here is the code:

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended     Properties=\"Excel 12.0 Xml;HDR=" + HDR + ";IMEX=1\"";
                OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();
string SQL = "select  Packhouse, Rm, Quantity , Product " +
                                " FROM " +
                                " ( " +
                                " SELECT Date,Packhouse, Rm,[FG XL], [FG L] " +
                                " FROM [" + xlSheet + "] " +
                                " ) Main " +
                                " UNPIVOT " +
                                " ( " +
                                " Quantity FOR Product in ([FG XL], [FG L]) " +
                                " ) Sub " +
                                " WHERE     (Date = '2010/03/08') and Quantity <> '0' and Packhouse = 'A' and Rm = '1' ";

OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(SQL, conn);
ds[sequencecounter] = new DataSet();
adapter.Fill(ds[sequencecounter], xlSheet);

If I copy and paste the excel data into a DB, then the select query works, but the data presented to me is in excel spreadsheets.

If anyone could provide help on this it will be much appreciated.

Regards, J