Search code examples
oledbfoxprodbf

Update a column name that starts with a number in a dbf-file with oledb

I'm trying to update a column named value in a dbf-file. Since value is a reserved word I need to add the tablename to the query. So this works:

UPDATE "C:\TEMP\TEST_PARAM.DBF" SET TEST_PARAM.value='new value' WHERE Id='Some ID';

But now I have the problem that many of my dbf-files start with numbers in the filenames and following does not work:

UPDATE "C:\TEMP\0016_PARAM.DBF" SET 0016_PARAM.value='new value' WHERE Id='Some ID';

I've tried enclosing the table_name in single quotes, double quotes, [,... but nothing of that works. Is there something else I could try?

Solution

  • You don't say what language you're doing this in, but here we go in C#. The same approach should work in any language.

    You need to open the DBF under an alias, and you need to be able to send multiple commands through OLEDB.

    This should work.

    * -- Open the dbf in the first available work area under the alias 'param'. 
* -- Now you don't have to worry about the zeroes.
OleDbCommand myCommand = new OleDbCommand(@"execscript([use 'C:\TEMP\0016_PARAM.DBF' in 0 alias param])", myConnection);

var result = myExecuteNonQuery();

* -- Now do the update, referring to the alias, not the DBF filename.
myCommand = new OleDbCommand(@"execscript([update param set param.value='new' where id='some id'])", myConnection);

result = myCommand.ExecuteNonQuery();

    Regarding the square brackets, Visual FoxPro has three default string delimiters, namely the usual single and double quotes, but also square brackets.

    So we're using the double quotes for the C# string. The Visual Foxpro command we're running via ExecScript needs quotes too, around 'new' and 'some id', so that's using single quotes. But we need to pass that command to Execscript as a string, so that string is using the brackets.