Good day. I'm working on a project involving reading data from DBF files. Everything was going on just fine until I discovered the client has apostrophes in some of the columns. Due to some 15,000 records already existing, I've been asked to try to work around it rather than make them fix up their data, which works with their existing system anyway though I can't look at their source to find out how.
Anyway, I've been accessing the data through an ODBC connection with Microsoft's FoxPro driver. It all goes smoothly up until the first column with the apostrophe at which point I get a "syntax error". Here's a bit of what I'm doing:
I'm using a wildcard because the data file contains some 100 or so columns. But even calling the specific columns doesn't help.
So what I'm looking for is some way to get around the apostrophes. I've selected from Excel spreadsheets with apostrophes in columns using OLEDB without difficulty, so I'm hoping it's somehow possible to do so with DBF files with ODBC as well. It's been suggested elsewhere that I used OLEDB instead, but for the life of me I could not get it to work that way for reasons I can no longer recall.
Anyway, I've been accessing the data through an ODBC connection with Microsoft's FoxPro driver. It all goes smoothly up until the first column with the apostrophe at which point I get a "syntax error". Here's a bit of what I'm doing:
VB.NET:
FoxCommand.CommandText = "SELECT" & vbNewLine
FoxCommand.CommandText &= "*" & vbNewLine
FoxCommand.CommandText &= "FROM (productfile).DBF;"
'The multiple line thing is force of habit
ProductDB = FoxCommand.ExecuteReader() 'OdbcDataReader
Dim InventoryData As New DataTable
InventoryData.Load(ProductDB) 'KABOOM!
I'm using a wildcard because the data file contains some 100 or so columns. But even calling the specific columns doesn't help.
So what I'm looking for is some way to get around the apostrophes. I've selected from Excel spreadsheets with apostrophes in columns using OLEDB without difficulty, so I'm hoping it's somehow possible to do so with DBF files with ODBC as well. It's been suggested elsewhere that I used OLEDB instead, but for the life of me I could not get it to work that way for reasons I can no longer recall.