Querying DBF file with System.Data.Odbc.OdbcConnection when file path/name has space

Question

I'm trying to query a DBF file using System.Data.Odbc.OdbcConnection. It works correctly when the file doesn't have a space in it, but I get the following error "Error opening DBF File: ERROR [42000][Microsoft][ODBC dBase Driver]Syntax error in FROM clause" if the file path or name has a space in it.

I'm using the following code:

oConn = new System.Data.Odbc.OdbcConnection(); oConn.ConnectionString = "Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=NA;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";

oCmd.CommandText = "SELECT * FROM C:\test 2\12345678.dbf";

The command text isn't hard-coded. I just included it that way for simplicity. The application is setup to allow a user to pick a DBF file and have it display it. I don't have control over where the users store the DBF files and would rather not have to have them remember not to put spaces in the file name/path.

How do I escape the space in the file name/path?

Answer 1

Probable it's a problem related with the "MS-DOS 8.3 file name format" . You can review the next links:

• Microsoft Support: You receive an error message when importing dBASE, FoxPro, or Paradox file
• Wikipedia: 8.3 filename (Here it says that space was disallowed by convention)

Answer 2

I was running into this issue as well. This was my #1 hit in Google, so I wasn't very hopeful. However, we were able to get my code to work by changing the current directory to the problematic directory and then excluding the path in the CommandText:

//Save the current directory
string currentDir = System.IO.Directory.GetCurrentDirectory();

//Select the path that we need to use
System.IO.Directory.SetCurrentDirectory("C:\\test 2\\");

//Now the path isn't required:
oCmd.CommandText = @"SELECT * FROM 12345678.dbf";

//Restore the old directory
System.IO.Directory.SetCurrentDirectory(currentDir);

You're still limited in what the file's name can be (no spaces and <= 8 char I think), but that's something I can work with.