Adding a record using C#

[Ben S]

Can someone tell me what is wrong with this code? I am simply trying to add a record to a database. The error message is as follows:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Update unable to find TableMapping['Table'] or DataTable 'Table'.

**************************************************************

DataSet ds = new DataSet();

ds.Tables.Add("FundData");

// Create Access objects:

System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand("SELECT * FROM FundData", dbConn);

System.Data.OleDb.OleDbDataAdapter AdpFunds = new System.Data.OleDb.OleDbDataAdapter(cmd1);


AdpFunds.Fill(ds, "FundData");

DataRow dr = ds.Tables["FundData"].NewRow();

dr["Symbol"] = sym;

ds.Tables["FundData"].Rows.Add(dr);

ds.AcceptChanges();

AdpFunds.Update(ds);

 

[Chris Taylor]

Hi,

There are a number of problems with this code.

1) Not serious, but there is no real need to manually add the FundData DataTable to the DataSet as the OleDbDataAdapter.Fill() will do this if it does not already exist in the DataTable

2) To propogate the DataSet changes back to the database through the OleDbDataAdapter you must supply the insert, update and delete commands. You can either do this manually or for the very simple cases you can use the OleDbCommandBuilder to automatically generate the commands.
eg.
System.Data.OleDb.OleDbDataAdapter AdpFunds = new System.Data.OleDb.OleDbDataAdapter(cmd1);
new System.Data.OleDb.OleDbCommandBuilder( AdpFunds );

That will enable the OleDbDataAdapter to infer the update, delete and insert commands from the SelectCommand that you supplied and the database meta data.

3) You must not call AcceptChanges before calling OleDbDataAdapter.Update(), this causes the DataSet records to be accepted and there status is set to be as if the records were already updated to the datastore so the OleDbDataAdapter.Update() will do nothing.

4) Finally the reason for your exception message. Well the easy sollution is to just add the table name to the OleDbDataAdapter.Update() call.
eg.
OleDbDataAdapter.Update( ds, "FundData" );

OR

You can setup a table mapping for the OleDbDataAdapter, that way you will not need to pass the table name to either the Fill() or the Update().
eg.
AdpFunds.TableMappings.Add( "Table", "FundData" );

This maps the default DataTable name "Table" to "FundData". Do this after creating the OleDbDataAdapter but before calling Fill() or Update().

I would recommend that you set the Insert, Update and Delete commands of the OleDbDataAdapter manually using parameters as this allows you more control and better performance. You can find more info about this in the MSDN.

Hope this helps

Chris Taylor


Can someone tell me what is wrong with this code? I am simply trying to add a record to a database. The error message is as follows:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Update unable to find TableMapping['Table'] or DataTable 'Table'.

**************************************************************

DataSet ds = new DataSet();

ds.Tables.Add("FundData");

// Create Access objects:

System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand("SELECT * FROM FundData", dbConn);

System.Data.OleDb.OleDbDataAdapter AdpFunds = new System.Data.OleDb.OleDbDataAdapter(cmd1);


AdpFunds.Fill(ds, "FundData");

DataRow dr = ds.Tables["FundData"].NewRow();

dr["Symbol"] = sym;

ds.Tables["FundData"].Rows.Add(dr);

ds.AcceptChanges();

AdpFunds.Update(ds);