Try this: Immediately after the line var builder = new OleDbCommandBuilder(dbAdapter); add the two lines builder.QuotePrefix = “[“; builder.QuoteSuffix = “]”; That will tell the OleDbCommandBuilder to wrap table and column names in square brackets, producing an INSERT command like INSERT INTO [TableName] … instead of the default form INSERT INTO TableName … The square … Read more
To expand on Marc’s answer, I need to create a text file called Schema.ini and put it in the same directory as the CSV file. As well as column types, this file can specify the file format, date time format, regional settings, and the column names if they’re not included in the file. To make … Read more
Simply ask google, I guess more than 10000 hits is quite impressive. Your argument “I don’t think that…” is not valid until you proved it. This is what MSDN says: The OLE DB.NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when … Read more
I had the same error, but none of the suggestions above worked. In my case all I had to do was to change my connection string to this: string connStr = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + FilePath + “;Extended Properties=”Excel 12.0;IMEX=1;””; Note the Single Quote around the Extended Properties attribute (‘Excel 12.0;IMEX=1;’). Once I added those single … Read more
The problem of the mismatch in criteria expression is due to the OleDbType assigned to the parameter used to represent the DateTime.Now value when you call AddWithValue. The OleDbType choosen by AddWithValue is DBTimeStamp, but Access wants a OleDbType.Date. http://support.microsoft.com/kb/320435 Searching on the NET I have found another intersting tip. The core problem lies in … Read more
From ConnectionStrings “If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash. To always use IMEX=1 is a safer way to retrieve data for mixed data columns. ..” Please note that the IMEX value … Read more
This annoyed me, screwy little OleDB, so I’ll post my solution here for posterity. It’s an old post but seems like a good place. OleDB doesn’t recognize named parameters, but it apparently does recognize that you’re trying to convey a named parameter, so you can use that to your advantage and make your SQL semantic … Read more
Take a look at Linq-to-Excel. It’s pretty neat. var book = new LinqToExcel.ExcelQueryFactory(@”File.xlsx”); var query = from row in book.Worksheet(“Stock Entry”) let item = new { Code = row[“Code”].Cast<string>(), Supplier = row[“Supplier”].Cast<string>(), Ref = row[“Ref”].Cast<string>(), } where item.Supplier == “Walmart” select item; It also allows for strongly-typed row access too.
You are getting in your own way by creating New DB Objects over and over. If the DataAdapter was a form level variables, you would have to write a lot less code: Public Class Form1 ‘ declare some persistant DB objects Private myDT As DataTable Private myDA As OleDbDataAdapter Private myStudentsDataView As DataView Private dbConnStr … Read more
You should indicate only the directory name in your connection string. The file name will be used to query: var filename = @”c:\work\test.csv”; var connString = string.Format( @”Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=””Text;HDR=YES;FMT=Delimited”””, Path.GetDirectoryName(filename) ); using (var conn = new OleDbConnection(connString)) { conn.Open(); var query = “SELECT * FROM [” + Path.GetFileName(filename) + “]”; using (var adapter … Read more