Question DBF files with apostrophes in data

RadioKJ

Member
Joined
Dec 4, 2008
Messages
7
Programming Experience
3-5
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:

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.
 

RadioKJ

Member
Joined
Dec 4, 2008
Messages
7
Programming Experience
3-5
The only thing potentially useful in that entire page are the samples with "ParameterDirection.ReturnValue", but it's not exactly specific on how that works so I'll have to play around with it.

Or maybe I wasn't specific enough; I'm not the one entering any data into the database, I'm trying to read data from an already existing file created by another piece of software I have no control over. And I'm trying to retrieve everything (*); no WHERE statements or anything, just SELECT * FROM file. With nothing actually hard coded I fail to see where parameters become useful.
 

RadioKJ

Member
Joined
Dec 4, 2008
Messages
7
Programming Experience
3-5
Thanks anyway. Anyone else have suggestions? At this point I'm willing to try virtually anything to get it to work so I can put this behind me.

Interestingly, I can actually work with all the data up to the first stray apostrophe; all the rows prior do load into the datatable.
 

RadioKJ

Member
Joined
Dec 4, 2008
Messages
7
Programming Experience
3-5
Strike everything I previously wrote here. I got OleDb to work finally. This can be considered solved.
 
Last edited:

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Just out of curiousity, were you loading the DBFs using some sort of comma parsing routine? If not, I can't actually see what the problem would have been. I'll be working with paradox DBFs soon, so a fuller explanation may help me if you'd care to provide it :)
 

RadioKJ

Member
Joined
Dec 4, 2008
Messages
7
Programming Experience
3-5
What you see above is everything I've done regarding the DBF file minus some cookie cutter stuff (Command = Connection.CreateEtc) and the connection string, which I don't have in front of me right now but I basically just copied from ConnectionStrings.com:

VB.NET:
Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\mydbpath;

All the code around that is just managing the data on the form itself, none of which causes any exceptions. The problem appears to be linked to ODBC, since after I finally got the OLEDB connection working I didn't have to change a thing (other than OdbcConnection --> OleDbConnection and so forth), and it worked perfectly. Beyond that I'm not sure what I have that could be useful to you as an explanation.
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
So what I'm looking for is some way to get around the apostrophes.
The confusing aspect is that:

1) the SQL in your post contains no apostrophes
2) you imply that apostrophes in the DATA IN THE DATABASE are tripping you up

I now guess that it's more likely you were doing something like:

SELECT * FROM tblPerson WHERE lastName = 'o'brien'

Which, of course, is going to break.. That would be what you use parameterized queries for. See the PQ link in my signature
 

RadioKJ

Member
Joined
Dec 4, 2008
Messages
7
Programming Experience
3-5
...until I discovered the client has apostrophes in some of the columns.

The apostrophes are already in the database data, put there by their existing software. When I was retrieving the data with ODBC, the Table.Load line would explode because the apostrophe made it think there was another column, when it was just one column with an apostrophe in it. Using OleDB, which i finally managed to get working, it properly reads it as a single column.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,374
Location
Sydney, Australia
Programming Experience
10+
OK, code does not "explode". What it does is throw an exception. Every exception has an error message. That error message is provided as a description of the error. If you were to read that it might give you a clue as to what the actual issue was and how to correct it. If you gave us the error message it might do the same for us. If the error message isn't enough then you may be able to get more information out of the exception, depending on the circumstances. The .NET Framework doesn't simply fall in a heap and expect you to magically know what the issue is. It tries to help you fix the problem but if you ignore the information it gives you then what can you expect?
 

RadioKJ

Member
Joined
Dec 4, 2008
Messages
7
Programming Experience
3-5
No, my code is in fact highly volatile. It explodes. Blue smoke and all.

As for error messages, it's been a while since I cared about this (since the problem is now fixed as I've already mentioned), but I'm almost entirely certain the complete error message was "Near "'S": syntax error", which I mostly mentioned in the initial post and described that I know what is causing it but not why.

Personally I've found most of the error messages given to me by .NET to be useless until I step through the code to find the exact line. But in this case that didn't help me either.

And before you accuse me of ignoring information, how many responses in this thread were made before fully understanding things I'd already written? Just sayin'...
 
Last edited:

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Personally I've found most of the error messages given to me by .NET to be useless until I step through the code to find the exact line.
Choose Exceptions from the Debug menu (may have to Customize your menus to add this if your layout is basic vb) and tick "Thrown" CLR exceptions. Now VB will pause as soon as any exception is thrown regardless of whether you handled it in a Catch


And before you accuse me of ignoring information, how many responses in this thread were made before fully understanding things I'd already written? Just sayin'...
Usually an indicator that we have not clearly grasped what you are attempting to do ;) - IMHO something that can rarely be our fault. We all got there in the end though.. Forgive any peculierness[sic] on our part
 
Top Bottom