ADO.NET ODBC FoxPro DataSet Update - why so painful?

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by John Tobl » Sun, 10 Mar 2002 09:59:38



I have spent *far* too much of our company's precious time trying to
figure out how to get ADO.NET to do one simple update of a FoxPro
database file.  I have no trouble accessing FoxPro files and making
the data viewable and changing the in-memory contents [AcceptChanges()
works fine].  But I have been completely unable to get the changes
back out to the underlying database file.  Any help would be *most*
appreciated!

I have the ODBC.NET provider up and working and can populate a DataSet
with multiple DataTable objects and can display and modify them in a
DataGrid just fine.

I am not able, however, to update the actual FoxPro .DBF file no
matter what I try.

I keep DataAdapters for each DataTable in a Hastable(m_AdapterDict).
Here is a fragment of C# code where I am trying to update one of the
tables:

public void UpdateDataSet(string inTableName)
{
    OdbcDataAdapter myAdapter =
        (OdbcDataAdapter)m_AdapterDict[inTableName];
    OdbcConnection cn = m_DConnection;
    try
    {
        string mySQL = "select * from " + inTableName;
        myAdapter.SelectCommand = new OdbcCommand(mySQL, cn);
        OdbcCommandBuilder custCB =
            new OdbcCommandBuilder(myAdapter);
        custCB.RefreshSchema();
        cn.Open();
        myAdapter.Update(m_dtSet, inTableName);
        myAdapter.Fill(m_dtSet, inTableName);
        cn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

Quote:}

m_dtSet is the DataSet maintained as a static in the helper class that
contains this code fragment.  It is successfully accessed by other
objects.

I have tried with and without an OdbcCommandBuilder with no joy.  BTW,
the OdbcCommandBuilder does not seem to build an updateCommand in its
cmdBuilder.

What am I doing wrong and what approaches will actually work?  Help!

John Tobler

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by David Scep » Sun, 10 Mar 2002 10:22:48


John,

     Are you getting an exception?  If so, what's the text of the
exception?  What sort of FoxPro table are you using?  A free
table or one that is referenced in a database container?  How is
the table indexed?

     The CommandBuilder has to fetch table and column names as
well as primary key information in order to build the INSERT,
UPDATE, and DELETE queries.  You'd get better performance and
control if you supply your logic in code rather than generating
it dynamically at run-time.

     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.

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by Kathleen Dollar » Sun, 10 Mar 2002 11:39:22


John,

I do not immediately see a problem with your code (although I suggest you
remove the explicit open and close of hte connection). However your comment
here

Quote:> I have spent *far* too much of our company's precious time trying to
> figure out how to get ADO.NET to do one simple update of a FoxPro
> database file.  I have no trouble accessing FoxPro files and making
> the data viewable and changing the in-memory contents [AcceptChanges()
> works fine].  But I have been completely unable to get the changes
> back out to the underlying database file.  Any help would be *most*
> appreciated!

Do not call AcceptChanges. This may be your entire problem

Kathleen

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by Jay B. Harlow [MVP - Outlook » Mon, 11 Mar 2002 00:57:55


John,
Having just read this in "Building Web Solutions with ASP.NET & ADO.NET" by
Dino Esposito, from MS Press.

Like Kathleen asked: Where are you calling AcceptChanges in relation to
UpdateDataSets?

Within UpdateDataSets you call OdbcDataAdapter.Update. Internally to
OdbcDataAdapter.Update, AcceptChanges is called for each row, as that row is
updated in the underlying database...

Calling AcceptChanges before UpdateDataSets will cause Update not to see any
changes! ;-)

See chapter 7 in the above book.

Hope this helps
Jay


> I have spent *far* too much of our company's precious time trying to
> figure out how to get ADO.NET to do one simple update of a FoxPro
> database file.  I have no trouble accessing FoxPro files and making
> the data viewable and changing the in-memory contents [AcceptChanges()
> works fine].  But I have been completely unable to get the changes
> back out to the underlying database file.  Any help would be *most*
> appreciated!

> I have the ODBC.NET provider up and working and can populate a DataSet
> with multiple DataTable objects and can display and modify them in a
> DataGrid just fine.

> I am not able, however, to update the actual FoxPro .DBF file no
> matter what I try.

> I keep DataAdapters for each DataTable in a Hastable(m_AdapterDict).
> Here is a fragment of C# code where I am trying to update one of the
> tables:

> public void UpdateDataSet(string inTableName)
> {
>     OdbcDataAdapter myAdapter =
>         (OdbcDataAdapter)m_AdapterDict[inTableName];
>     OdbcConnection cn = m_DConnection;
>     try
>     {
> string mySQL = "select * from " + inTableName;
> myAdapter.SelectCommand = new OdbcCommand(mySQL, cn);
> OdbcCommandBuilder custCB =
>             new OdbcCommandBuilder(myAdapter);
> custCB.RefreshSchema();
> cn.Open();
> myAdapter.Update(m_dtSet, inTableName);
> myAdapter.Fill(m_dtSet, inTableName);
> cn.Close();
>     }
>     catch (Exception ex)
>     {
> MessageBox.Show(ex.Message);
>     }
> }

> m_dtSet is the DataSet maintained as a static in the helper class that
> contains this code fragment.  It is successfully accessed by other
> objects.

> I have tried with and without an OdbcCommandBuilder with no joy.  BTW,
> the OdbcCommandBuilder does not seem to build an updateCommand in its
> cmdBuilder.

> What am I doing wrong and what approaches will actually work?  Help!

> John Tobler


 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by John Tobl » Tue, 12 Mar 2002 11:45:45



> Are you getting an exception?  If so, what's the text of the
> exception?  

No exception.  I tried to trap for one but .NET seemed to think
everything updated just fine.

Quote:> What sort of FoxPro table are you using?  A free table
> or one that is referenced in a database container?  
> How is the table indexed?

Free table, no index.

Quote:>      The CommandBuilder has to fetch table and column names as
> well as primary key information in order to build the INSERT,
> UPDATE, and DELETE queries.  You'd get better performance and
> control if you supply your logic in code rather than generating
> it dynamically at run-time.

How?

Thanks,

John Tobler

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by David Scep » Wed, 13 Mar 2002 05:00:18


Quote:> Free table, no index.

     The CommandBuilder requests table and column names for your
resultset, along with primary key information for the resultset.  
I haven't used the CommandBuilder with Fox tables, but I'm
assuming that it won't generate updating logic in your scenario
since it can't guarantee that UPDATE or DELETE queries would
update at most one row.

Quote:> How?

     The DataAdapter exposes UpdateCommand, InsertCommand, and
DeleteCommand properties.  Supply a parameterized Command object
in each of these properties.  You can "bind" the parameters to
columns in your result set by setting the SourceColumn and
SourceVersion properties on the parameter.  Then when you call
the DataAdapter's Update method and pass a structure with
modified rows into the method, the DataAdapter will walk through
the rows and submit the pending changes via the appropriate
command.

     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.

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by John Toble » Thu, 14 Mar 2002 07:29:46



> Within UpdateDataSets you call OdbcDataAdapter.Update. Internally to
> OdbcDataAdapter.Update, AcceptChanges is called for each row, as that row
is
> updated in the underlying database...

> Calling AcceptChanges before UpdateDataSets will cause Update not to see
any
> changes! ;-)

OK, I stopped using AcceptChanges and I knocked out the explicit open and
close,
as recommended by Kathleen.  Now I get further, but end up with an
exception:

"Dynamic SQL generation for the UpdateCommand is not supported against
a SelectCommand that does not return any key column information."

How do I get past this one?

Here is the relevant code fragment:

public void UpdateDataSet(string inTableName)

{

  try

  {

    OdbcDataAdapter myAdapter = (OdbcDataAdapter)m_AdapterDict[inTableName];

    OdbcConnection cn = m_DConnection;

    try

    {

      string mySQL = "select * from " + inTableName;

      myAdapter.SelectCommand = new OdbcCommand(mySQL, cn);

      OdbcCommandBuilder custCB = new OdbcCommandBuilder(myAdapter);

      custCB.RefreshSchema();

      myAdapter.Update(m_dtSet, inTableName);

      myAdapter.Fill(m_dtSet, inTableName);

    }

    catch (Exception ex)

    {

      MessageBox.Show(ex.Message);

    }

  }

  catch (Exception ex)

  {

    MessageBox.Show(ex.Message);

  }

Quote:}

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by John Toble » Thu, 14 Mar 2002 08:25:42


I wrote, not so long ago:

Quote:> OK, I stopped using AcceptChanges and I knocked out the explicit open and
> close, as recommended by Kathleen.  Now I get further, but end up with an
> exception:

> "Dynamic SQL generation for the UpdateCommand is not supported against
> a SelectCommand that does not return any key column information."

I just reproduced the exact same exception behavior using the Visual FoxPro
OleDb provider (VFPOleDB.dll).  Obviously, I need to do something in my code
to prepare for the Update.  Again, the test app from which the code fragment
came loads two tables plus a join of the two tables into a single DataSet as
DataTable objects.  I can display all three DataTable objects in a DataGrid.
When I change some values, I can see the changes via myDataSet.GetChanges()
just fine.  I just cannot seem to get the internal DataSet to update the
.DBF files. What am I missing and can someone please provide example code?

Not being able to write back out to a .DBF is a show-stopper, so your help
will be most appreciated!

John Tobler

----- Code fragment for OleDb version -----

public void UpdateDataSet(string inTableName)

{

    try

    {

        OleDbDataAdapter myAdapter =
(OleDbDataAdapter)m_AdapterDict[inTableName];

        OleDbConnection cn = m_DConnection;

        try

       {

            string mySQL = "select * from " + inTableName;

            myAdapter.SelectCommand = new OleDbCommand(mySQL, cn);

            OleDbCommandBuilder custCB = new OleDbCommandBuilder(myAdapter);

            custCB.RefreshSchema();

            myAdapter.Update(m_dtSet, inTableName);

            myAdapter.Fill(m_dtSet, inTableName);

        }

        catch (Exception ex)

        {

            MessageBox.Show(ex.Message);

        }

    }

    catch (Exception ex)

    {

        MessageBox.Show(ex.Message);

    }

Quote:}

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by David Scep » Thu, 14 Mar 2002 08:21:19


Quote:> OK, I stopped using AcceptChanges and I knocked out
> the explicit open and close, as recommended by Kathleen.
> Now I get further, but end up with an exception:
> "Dynamic SQL generation for the UpdateCommand is not
> supported against a SelectCommand that does not return
> any key column information."
> How do I get past this one?

     The CommandBuilder requests table and column names for your
resultset, along with primary key information for the resultset.  
If you don't have a primary key or unique index on your table
and/or the Fox ODBC driver won't report this information, the
CommandBuilder will not be able to generate your updating logic
for you.

     The DataAdapter exposes UpdateCommand, InsertCommand, and
DeleteCommand properties.  Supply a parameterized Command object
in each of these properties.  You can "bind" the parameters to
columns in your result set by setting the SourceColumn and
SourceVersion properties on the parameter.  Then when you call
the DataAdapter's Update method and pass a structure with
modified rows into the method, the DataAdapter will walk through
the rows and submit the pending changes via the appropriate
command.

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.

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by David Scep » Thu, 14 Mar 2002 09:20:22


     I built the following code, which successfully submits
updates against the Customers.dbf file that a recent version of
Office installed.  Obviously, you'd need to modify the code to
work against your tables.

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.

using System.Data;
using Microsoft.Data.Odbc;

static OdbcDataAdapter CreateDataAdapter()
{

CUSTOMER",

    System.Data.Common.DataTableMapping dtm;
    dtm = da.TableMappings.Add("Table", "CUSTOMER");
    dtm.ColumnMappings.Add("custmr_id", "custmr_id");
    dtm.ColumnMappings.Add("company", "company");
    dtm.ColumnMappings.Add("city", "city");
    dtm.ColumnMappings.Add("region", "region");

    OdbcCommand cmd;
    da.UpdateCommand =
da.SelectCommand.Connection.CreateCommand();
    cmd = da.UpdateCommand;

`company` = ?, `city` = ?, `region` = ? WHERE `custmr_id` = ? AND
`company` = ? AND `city` = ? AND `region` = ?";

OdbcType.Char, 11, ParameterDirection.Input, false, 11, 0,
"custmr_id", DataRowVersion.Current, null));

OdbcType.Char, 23, ParameterDirection.Input, false, 23, 0,
"company", DataRowVersion.Current, null));

OdbcType.Char, 12, ParameterDirection.Input, false, 12, 0,
"city", DataRowVersion.Current, null));

OdbcType.Char, 7, ParameterDirection.Input, false, 7, 0,
"region", DataRowVersion.Current, null));

OdbcType.Char, 11, ParameterDirection.Input, false, 11, 0,
"custmr_id", DataRowVersion.Original, null));

OdbcType.Char, 23, ParameterDirection.Input, false, 23, 0,
"company", DataRowVersion.Original, null));

OdbcType.Char, 12, ParameterDirection.Input, false, 12, 0,
"city", DataRowVersion.Original, null));

OdbcType.Char, 7, ParameterDirection.Input, false, 7, 0,
"region", DataRowVersion.Original, null));

    da.InsertCommand =
da.SelectCommand.Connection.CreateCommand();
    cmd = da.InsertCommand;

`company`, `city`, `region`) VALUES (?, ?, ?, ?)";

OdbcType.Char, 11, ParameterDirection.Input, false, 11, 0,
"custmr_id", DataRowVersion.Current, null));

OdbcType.Char, 23, ParameterDirection.Input, false, 23, 0,
"company", DataRowVersion.Current, null));

12, ParameterDirection.Input, false, 12, 0, "city",
DataRowVersion.Current, null));

OdbcType.Char, 7, ParameterDirection.Input, false, 7, 0,
"region", DataRowVersion.Current, null));

    da.DeleteCommand =
da.SelectCommand.Connection.CreateCommand();
    cmd = da.DeleteCommand;

= ? AND `company` = ? AND `city` = ? AND `region` = ?";

OdbcType.Char, 11, ParameterDirection.Input, false, 11, 0,
"custmr_id", DataRowVersion.Original, null));

OdbcType.Char, 23, ParameterDirection.Input, false, 23, 0,
"company", DataRowVersion.Original, null));

12, ParameterDirection.Input, false, 12, 0, "city",
DataRowVersion.Original, null));

OdbcType.Char, 7, ParameterDirection.Input, false, 7, 0,
"region", DataRowVersion.Original, null));

    return da;

Quote:}

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by John Toble » Thu, 14 Mar 2002 10:34:13



>      I built the following code, which successfully submits
> updates against the Customers.dbf file that a recent version of
> Office installed.  Obviously, you'd need to modify the code to
> work against your tables.

David, you're a prince!  I will attempt a test update following your example
code.  My first requirement is to simply prove that I can use ADO.NET to
update at least one FoxPro .DBF file with at least one change *somehow*.

In the long run, though, I hope we can find some more generic approach that
does not require intimate, advance knowledge of a data table's structure.  I
had hoped the CommandBuilder object would take care of these sorts of
details for us.  We have *lots* of tables and I would prefer to be able to
write general code that will infer the necessary structure and handle the
update for "any" DataSet or DataTable.  I hoped that was what CommandBuilder
would bring to the table.  Perhaps I just don't know how to use it properly,
yet.

Thanks so much for your rapid and specific help!

John Tobler

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by David Scep » Thu, 14 Mar 2002 11:35:53


John,

     I believe the CommandBuilder would be able to generate that
logic for you dynamically at run-time if it could identify a
primary/unique key for the table you're querying.  It's been a
while since I worked with xBase tables, so I don't know quite
what to tell you there.  My guess is that if you worked with a
Visual FoxPro database container and associated tables that
include primary/unique keys that you'll be able to utilize the
CommandBuilder.

     You'll get better performance by supplying the updating
logic in code, which is what the Visual Studio .NET DataAdapter
Configuration Wizard does.  However, the wizard currently only
works with the SQL Client and OLE DB .NET Data Providers, and it
requires the same meta-data that the CommandBuilder requires.  
So, unfortunately, the wizard will not be an option in your
specific scenario.

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.

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by Joy & Associates, CPA » Fri, 15 Mar 2002 08:47:24


I also have spent more time on this problem than I would like to admit. I
eventually got it to work using the method that David Sceppa has given you.
I don't think there is any way to make the CommandBuilder work, I tried
everything I could think of. I also developed some code that builds the
select statements by using the FillSchema() command, and providing the
KeyFld info, then going tough the table and creating the select command from
the schema info.

Hope this helps.

Myron Joy



> >      I built the following code, which successfully submits
> > updates against the Customers.dbf file that a recent version of
> > Office installed.  Obviously, you'd need to modify the code to
> > work against your tables.

> David, you're a prince!  I will attempt a test update following your
example
> code.  My first requirement is to simply prove that I can use ADO.NET to
> update at least one FoxPro .DBF file with at least one change *somehow*.

> In the long run, though, I hope we can find some more generic approach
that
> does not require intimate, advance knowledge of a data table's structure.
I
> had hoped the CommandBuilder object would take care of these sorts of
> details for us.  We have *lots* of tables and I would prefer to be able to
> write general code that will infer the necessary structure and handle the
> update for "any" DataSet or DataTable.  I hoped that was what
CommandBuilder
> would bring to the table.  Perhaps I just don't know how to use it
properly,
> yet.

> Thanks so much for your rapid and specific help!

> John Tobler


 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by John Toble » Fri, 15 Mar 2002 09:23:04



>      I believe the CommandBuilder would be able to generate that
> logic for you dynamically at run-time if it could identify a
> primary/unique key for the table you're querying.

I managed to get the update code to work perfectly from a SQL Server
connection and from an OleDB connection to a Microsoft Access .mdb file.  It
looks like the problem is indeed with getting a primary index or unique
column properly defined in FoxPro and then getting ADO.NET to recognize it.
I am experimenting and will post the results if I can get this to work.  The
DataAdapter Wizard worked *great* on my SQL Server and Access tests and it
does try to point out the primary key/unique field issues with the FoxPro
file.  I have not yet given up but other .NET issues acquired my foreground
processor today.

More later, and, again, thanks!

John Tobler

 
 
 

ADO.NET ODBC FoxPro DataSet Update - why so painful?

Post by John Toble » Sat, 16 Mar 2002 04:58:42



> I am experimenting and will post the results if I can get this to work.
The
> DataAdapter Wizard worked *great* on my SQL Server and Access tests and it
> does try to point out the primary key/unique field issues with the FoxPro
> file.  I have not yet given up but other .NET issues acquired my
foreground
> processor today.

I have tried several experiments, using Visual FoxPro 6.0 to establish a
primary key or unique field that will enable the ADO.NET OleDbDataAdapter
Wizard to automatically configure Update Delete statements.  I have had no
success.

When I create a primary key (wrapping my DBF file in a DBC container and
creating an OleDB connection to the DBC file), the wizard gives the
following error message for both the UPDATE and DELETE statement:

"There were errors configuring the data adapter."

When I create a "unique" or "candidate" field I get this error, instead:

'Could not determine which columns uniquely identify the rows for
"rzvalues46"'

The name of my DBF table is rzvalues46.

What am I doing wrong?

As I can successfully make the OleDbDataAdapter Wizard work with a Microsoft
Access (MDB) file, I can only suspect that Microsoft has a bug in the Wizard
code.  Visual FoxPro is a Microsoft product and one would hope that .NET
would work compatibly with Microsoft products.

Has anyone been able to make the OleDbDataAdapter Wizard work successfuly
with a DBC or DBF file?  Has anyone been able to successfully update a
Visual FoxPro database with ADO.NET without custom-coding or hand-crafting
the UPDATE and DELETE commands?

Cross-posted to:
   microsoft.public.dotnet.framework.adonet
   microsoft.public.fox.vfp.dbc
Main thread on
   microsoft.public.dotnet.framework.adonet

Thanks,

John Tobler

 
 
 

1. Howto update FoxPro DBF or DBC via ADO.NET?

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

2. Files corrupted when copying from HD to floppy

3. ADO.NET update using datasets

4. sendmail config

5. Performance Comparison of ADO vs ADO.NET vs ODBC

6. How do I do this? THIS IS DRIVING ME CRAZY!!!

7. ADO.NET Updating datasets using stored procedures

8. Pixel Resolution

9. Need help with FoxPro tables in ADO.NET

10. Can't connect to Foxpro DSN through odbc.net

11. Why do we have to learn ADO instead of ADO.net