Microsoft ACE OLEDB 12.0 - F1 syntax for headerless .CSV causes exception

Question

I am switching from using the 'Microsoft.Jet.OLEDB.4.0' provider to 'Microsoft.ACE.OLEDB.12.0' as I am launching my program on a 64-bit system, and I understand ACE has superseded Jet for 64-bit use.

I haven't changed the syntax of the query I pass to my OleDbCommand, but it now causes an exception:

"No value given for one or more required parameters."

Code is as follows:

static void Main(string[] args)
{
    const string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\PimsImport\\PreciousStore\\imports;Extended Properties='Text;HDR=NO;IMEX=1'";
    const string selectStatement = "SELECT F1 AS NameColumn FROM C:\\PimsImport\\PreciousStore\\imports\\PIMSPreciousbradypxx.csv";

    using (var connection = new OleDbConnection(connectionString))
    {
        using (var cmd = new OleDbCommand(selectStatement, connection))
        {
            cmd.Connection.Open();
            OleDbDataReader workbookReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            workbookReader.Close();
        }
    }
}

If I remove the F1, and replace with *; it works fine. But my csv has no header, so I want to use the F1 syntax to take the first column.

Any idea's why this no longer works?

Answer 1

In your select try making the following change

"SELECT [F1] AS NameColumn FROM [myTable]"

Instead of [] you can also use '' as well.

And I am not sure if you just written this code snippet for the sake of a temporary question but I would recommend you to manage your strings with something like

string sql = string.Format("SELECT [{0}] FROM [{1}]", myColumnVar, myTableVar);

and if more complex consider parametrizing your query. Refer to How to parameterize complex OleDB queries? for an example.