Howto update FoxPro DBF or DBC via ADO.NET?

John Tobler

unread,

Mar 15, 2002, 11:39:04 AM3/15/02

to

Has anyone in this group been able to use the OleDb .NET Provider or ODBC
.NET Provider to do either of the following?

1. Successfully modify the contents of a FoxPro 6.0 DBF or DBC and update
the results actually to the files?

2. Successfully create a new DataTable in memory and persist it out to
either a FoxPro 6.0 DBF or DBC.

If you answered "yes" to either question, please offer some details or
sample code as to how you made it work.

Thanks,

John Tobler
grep...@yahoo.com

David Sceppa

unread,

Mar 15, 2002, 5:38:51 PM3/15/02

to

John,

I just ran a quick test using the TasTrade sample database
using the Visual FoxPro ODBC driver and was able to submit
updates using an OdbcDataAdapter and an OdbcCommandBuilder. The
sample tables have primary keys and reside in a DBC.

C#:
string strConn, strSQL;
strConn = @"Driver={Microsoft FoxPro VFP Driver (*.dbf)};" +

@"SourceDB=E:\FoxData\TasTrade\TASTRADE.DBC;SourceType=DBC;";
strSQL = "SELECT Customer_ID, Company_Name, FROM Customer " +
"WHERE Customer_ID = 'ANTON'";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, strConn);
OdbcCommandBuilder cb = new OdbcCommandBuilder(da);
DataTable tbl = new DataTable();
da.Fill(tbl);
DataRow row = tbl.Rows[0];
string strOrigValue = row["Company_Name"];
row["Company_Name"] = "Modified";
da.Update(tbl);
row["Company_Name"] = strOrigValue;
da.Update(tbl);

VB:
Dim strConn, strSQL As String
strConn = "Driver={Microsoft FoxPro VFP Driver (*.dbf)};" & _

"SourceDB=E:\FoxData\TasTrade\TASTRADE.DBC;SourceType=DBC;"
strSQL = "SELECT Customer_ID, Company_Name FROM Customer " & _
"WHERE Customer_ID = 'ANTON'"
Dim da As New OdbcDataAdapter(strSQL, strConn)
Dim cb As New OdbcCommandBuilder(da)
Dim tbl As New DataTable()
da.Fill(tbl)
Dim row As DataRow = tbl.Rows(0)
Dim strOrigValue As String = row("Company_Name")
row("Company_Name") = "Modified"
da.Update(tbl)
row("Company_Name") = strOrigValue
da.Update(tbl)

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2002 Microsoft Corporation. All rights reserved.

Joy & Associates, CPAs

unread,

Mar 16, 2002, 11:22:42 AM3/16/02

to

Can you get the same thing to work with the FoxPro OLE-DB. I have tried but
couldn't get it to work.

Thanks
Myron Joy

"David Sceppa" <dav...@online.microsoft.com> wrote in message
news:AFduiIHzBHA.1532@cpmsftngxa09...

David Sceppa

unread,

Mar 18, 2002, 3:12:01 PM3/18/02

to

Myron,

The Visual FoxPro OLE DB provider does not return the schema
information necessary for the OleDbCommandBuilder to generate
updating logic.

John Tobler

unread,

Mar 18, 2002, 3:40:39 PM3/18/02

to

David Sceppa wrote:
> I just ran a quick test using the TasTrade sample database
> using the Visual FoxPro ODBC driver and was able to submit
> updates using an OdbcDataAdapter and an OdbcCommandBuilder. The
> sample tables have primary keys and reside in a DBC.

I was able to run your sample and it worked. Now, however, try the same
thing but use "*" in the strSQL. Trap for an OdbcException (see below) and
tell me what you get. I get

"ERROR [22018] [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type
mismatch."}

There are other problems I'll outline in a separate post.

John Tobler
johnt...@yahoo.com

---------- Altered example code below ----------

string strConn, strSQL;
strConn = @"Driver={Microsoft FoxPro VFP Driver (*.dbf)};" +

@"SourceDB=E:\FoxData\TasTrade\TASTRADE.DBC;SourceType=DBC;";

strSQL = "SELECT * FROM Customer " +

"WHERE Customer_ID = 'ANTON'";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, strConn);
OdbcCommandBuilder cb = new OdbcCommandBuilder(da);
DataTable tbl = new DataTable();

try
{

da.Fill(tbl);
DataRow row = tbl.Rows[0];

string strOrigValue = (string)row["Company_Name"];

row["Company_Name"] = "Modified";
da.Update(tbl);
row["Company_Name"] = strOrigValue;
da.Update(tbl);
}

catch(OdbcException oe)
{
MessageBox.Show(oe.Message);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}

John Tobler

unread,

Mar 18, 2002, 4:05:45 PM3/18/02

to

David Sceppa wrote:
> I just ran a quick test using the TasTrade sample database
> using the Visual FoxPro ODBC driver and was able to submit
> updates using an OdbcDataAdapter and an OdbcCommandBuilder. The
> sample tables have primary keys and reside in a DBC.

David, thank you so much for all of your efforts to help me resolve this
database problem. After getting your example to work, I did the same thing,
using one of our "real" database tables. It worked fine, when I selected a
single record and modified a single field. However, when I altered my SQL
string to use "*" in the query,

string strSQL = "SELECT * FROM Rzvalues46 WHERE respzone='1247'";

ADO.NET blew up with this exception message:

"ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too
long."

I revised my SQL string to actually use field names. Same problem. I then
commented out groups of field names until I could make my single-cell update
work again. When the length of my SQL string was 275, the update worked;
when it was 285, the update failed.

Am I missing something important, here?

In real-world database programming, it seems to me that a small and
arbitrary limit of this type is completely unacceptable. I assume many
companies have database tables much larger than the paltry little thing I am
trying to update right now. Again, FoxPro is a Microsoft product and I
would hope to see it fully supported, particularly when Microsoft's
marketing rhetoric indicates FoxPro is an important part of the .NET and
Visual Studio family
(http://msdn.microsoft.com/vfoxpro/prodinfo/ballmer.asp).

It does not seem that we are asking too much of the ADO.NET and the .NET
Framework to be able to make a modest update to a modestly-sized database
table created with one of Microsoft's own database tools.

Any help appreciated!

John Tobler
grep...@yahoo.com

John Tobler

unread,

Mar 18, 2002, 4:17:30 PM3/18/02

to

David Sceppa wrote:
> The Visual FoxPro OLE DB provider does not return the schema
> information necessary for the OleDbCommandBuilder to generate
> updating logic.

I tried to modify David's ODBC .NET Provider example to use the OleDB .NET
provider and it failed for me with the following message:

"Dynamic SQL generation is not supported against a
SelectCommand that does not return any base table information."

While David's comment, above, explains the failure, I hope Microsoft will
elect to try to resolve such FoxPro integration problems in the very near
future. Farms are being bet on the hope that the .NET framework and ADO.NET
can interact with simple database tables created with Microsoft products!

I cannot speak with pride to any of my respected peers about the crufty,
hideous, blecherous workaround we are currently contemplating because FoxPro
table updates are so unbelievably difficult for .NET to handle!

John Tobler
grep...@yahoo.com

David Sceppa

unread,

Mar 18, 2002, 4:44:37 PM3/18/02

to

John,

If you build and execute a query using the Visual FoxPro
ODBC driver whose length is 285 or greater, you get the error you
described. Is that correct? It sounds like we'd see the same
problem using tools like ODBCTest. Let me know if I understand
the problem correctly and I'll see if I can reproduce it from
this end.

I definitely understand your frustration and I'll gladly
pass along feedback to the Visual FoxPro team. They're the group
who owns both the Visual FoxPro ODBC driver and the Visual FoxPro
OLE DB provider. Hopefully, they will be able to improve these
components so developers can work with FoxPro databases more
easily with ADO.NET.

I'll also pass along feedback to the ADO.NET and Visual
Studio .NET development teams so they are aware of developers'
frustrations when working with ODBC drivers and OLE DB providers
that don't return the schema information that the tools expect.

Joy & Associates, CPAs

unread,

Mar 18, 2002, 6:48:03 PM3/18/02

to

I would like to add my name to the list of FoxPro programmers who are very
disappointed with the support for FoxPro tables in .NET. I have had the same
problems that John has experienced. I hope something can be done.

Thanks
Myron Joy

"David Sceppa" <dav...@online.microsoft.com> wrote in message

news:uK95KYszBHA.1420@cpmsftngxa08...

David Sceppa

unread,

Mar 18, 2002, 7:47:28 PM3/18/02

to

Myron,

As I noted earlier, the problems you're experiencing are the
result of limitations of the FoxPro OLE DB provider. I'll gladly
pass along your feedback regarding the OLE DB provider to the
FoxPro team.

John Tobler

unread,

Mar 19, 2002, 2:38:46 PM3/19/02

to

David Sceppa wrote:
> If you build and execute a query using the Visual FoxPro
> ODBC driver whose length is 285 or greater, you get the error you
> described. Is that correct? It sounds like we'd see the same
> problem using tools like ODBCTest. Let me know if I understand
> the problem correctly and I'll see if I can reproduce it from
> this end.

I have emailed David privately with a copy of one of the tables we are
trying to update. I have asked him to see if he and his colleagues at
Microsoft can figure out how to update this table either directly (as a DBF)
or indirectly (within a DBC container I have provided that establishes a
primary key).

I want to take a moment to applaud David for being so helpful. My
frustration is with my inability to use ADO.NET to accomplish our mission.
I am excited about .NET and *really* want to see .NET work for us!

John Tobler
grep...@yahoo.com

John Tobler

unread,

Mar 22, 2002, 11:42:04 AM3/22/02

to

I wrote:
> I have emailed David privately with a copy of one of the tables we are
> trying to update. I have asked him to see if he and his colleagues at
> Microsoft can figure out how to update this table either directly (as a
DBF)
> or indirectly (within a DBC container I have provided that establishes a
> primary key).

David, please privately email me your real email address. I have tried
several times to email the zip file mentioned in my post to
dav...@online.microsoft.com and keep getting "undeliverable" messages. I
would really like to be able to have your FoxPro people be able to look at
and resolve this problem.

Thanks,

John Tobler
grep...@yahoo.com

John Tobler

unread,

Mar 22, 2002, 4:43:14 PM3/22/02

to

Is it possible to specify or discover the defined width (or length) of a
database field at runtime through *any* ADO.NET mechanism?

Example: In the Northwind database, the Customers table has a ContactTitle
field that has a length of 30. How can I get to that number 30 through
ADO.NET?

John Tobler
grep...@yahoo.com

John Tobler

unread,

Mar 22, 2002, 5:15:24 PM3/22/02

to

John Tobler

unread,

Mar 22, 2002, 5:22:44 PM3/22/02

to

Sorry, I meant to post this as a new topic and have done so. Please reply
there.

Bob Beauchemin

unread,

Mar 23, 2002, 12:58:14 AM3/23/02

to

You can use the INFORMATION_SCHEMA views in SQL Server, through a regular
Command, if your database is SQL Server. Or use
OleDbConnection.GetOleDbSchemaTable with an OLE DB provider that supports
it.

Or you can do a DataAdapter.Fill with a MissingSchemaAction of
MissingSchemaAction.AddWithKey. Then refer to DataColumn.MaxLength. Or use
CommandBehavior.KeyInfo or SchemaOnly and look at the DataReader's
SchemaTable.

Hope this helps,
Bob Beauchemin
bo...@develop.com

"John Tobler" <grep...@yahoo.com> wrote in message
news:OaBhB8e0BHA.2588@tkmsftngp07...