Microsoft ACE OLEDB 12.0 syntax - F1 without .CSV header throws an exception
I am switching from using "Microsoft.Jet.OLEDB.4.0" to "Microsoft.ACE.OLEDB.12.0" as I am running my program on a 64-bit system and I realize that ACE has replaced Jet with 64-bit usage.
I didn't change the syntax of the request I passed to mine OleDbCommand
, but now it throws an exception:
"There is no value for one or more of the required parameters.
The code looks like this:
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 F1
and replace with *
; it works great. But my csv has no header, so I want to use the syntax F1
to take the first column.
Any idea why this doesn't work anymore?
source to share
In your choice, try the following change
"SELECT [F1] AS NameColumn FROM [myTable]"
Instead, []
you can also use ''
.
And I'm not sure if you just wrote this piece of code for a temporary question, but I would recommend that you manage your lines with something like
string sql = string.Format("SELECT [{0}] FROM [{1}]", myColumnVar, myTableVar);
and if more complex, consider parameterizing your query. See How do I parameterize complex OleDB queries? for example.
source to share