Hi,
Thank you for help.
Unfortunately it doesn't work.
I have removed Fill() and AcceptChanges.
Now after Update;
myOleDbDataAdapter.Update(myDataSet, "CustomersTable");
I got following error message:
Unhandled Exception: System.InvalidOperationException: Update requires a
valid InsertCommand when passed DataRow collection with new rows.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at ModifikacijePrekoDataTable.Example23_3.Main() in c:\documents and
settings
\mywork\c#\databases\modifikacijeprekodatatable\datatablemodifikacija.cs:
line 147
Press any key to continue
If I use INSERT (SQL) command it is not what I need. I know How I can do
that on that way.
I would like to make changes on my data using DataTable, not plain SQL
commands.
may be I need to do connection on some different way?
Here is complete code. This code is based on example from Mastering Visual
[email protected] book by Jason Price. Obviously there is lot of errors. Originaly Code
is prepared for SQL connection and I have adapted on OleDb.
Thanks.
Example:
using System;
using System.Data;
using System.Data.OleDb;
namespace ModifikacijePrekoDataTable
{
class Example23_3
{
public static void DisplayDataTable(DataTable myDataTable)
{
// Display the columns for each row in the DataTable using a
// DataRow object to access each row in the dataTable
foreach(DataRow myDataRow in myDataTable.Rows)
{
Console.WriteLine("CustomerID = " + myDataRow["CustomerID"]);
Console.WriteLine("CompanyName = " + myDataRow["CompanyName"]);
Console.WriteLine("ContactName = " + myDataRow["ContactName"]);
Console.WriteLine("Address = " + myDataRow["Address"]);
} // DisplayDataTable
}
public static void AddRow(DataTable myDataTable)
{
Console.WriteLine("\nAdding a new row with CustomerID of 'T1'");
// Step 1: Use the NewRow() method of the DataRow object to create a new row
in the DataTable
DataRow myNewDataRow = myDataTable.NewRow();
// Step 2: Set the values for the columns of the new row
myNewDataRow["CustomerID"] = "T1";
myNewDataRow["CompanyName"] = "T1 Company";
myNewDataRow["ContactName"] = "Jason Price";
myNewDataRow["Address"] = "1 Main Street";
// Step 3: Use the Add() method through the Rows property to add the new
DataRow to the DataTable
myDataTable.Rows.Add(myNewDataRow);
// Step 4: Use the AcceptChanges() method of the DataTable to commit
//myDataTable.AcceptChanges();
}
public static void ModifyRow(DataTable myDataTable)
{
Console.WriteLine("\nModifying a row with CustomerID of 'T1'");
// Step 1: Set the PrimaryKey property for DataTable object
DataColumn[] myPrimaryKey = new DataColumn[1];
myPrimaryKey[0] = myDataTable.Columns["CustomerID"];
myDataTable.PrimaryKey = myPrimaryKey;
// Step 2: Use the Find() method to locate the DataRow in the DataTable
using the primary key value
DataRow myEditDataRow = myDataTable.Rows.Find("T1");
// Step 3: Change the column values
myEditDataRow["CompanyName"] = "Widgets Inc.";
myEditDataRow["ContactName"] = "John Smith";
myEditDataRow["Address"] = "1 Any Street";
// Step 4; Use the AcceptChanges() method of the DataTable to commit the
changes
//myDataTable.AcceptChanges();
Console.WriteLine("myEditDataRow.RowState = " + myEditDataRow.RowState);
}
public static void RemoveRow(DataTable MyDataTable)
{
Console.WriteLine("\nRemoving a row with CustomerID of 'T1'");
// Step 1: Set the PrimaryKey property for the DataTable object
DataColumn[] myPrimaryKey = new DataColumn[1];
myPrimaryKey[0] = MyDataTable.Columns["CustomerID"];
MyDataTable.PrimaryKey = myPrimaryKey;
// Step2: Use the Find() method to locate the DataRow
DataRow myRemoveDataRow = MyDataTable.Rows.Find("T1");
// Step 3: Use the Delete() method to remove the DataRow
myRemoveDataRow.Delete();
// step 4: Use the AcceptingChanges() method of the DataTable to commit the
changes
//MyDataTable.AcceptChanges();
}
public static void Main()
{
string connectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=" + // Izvor podataka = putanja .mdb datoteke
@"C:\base\Customers.mdb";
// Create a OleDBConnection object to connect to the DB
OleDbConnection MyConn = new OleDbConnection(connectionString);
// Formulate a SELECT statement to retrieve the database, passing the
connection string to the constructor
string selectString =
"SELECT CustomerID, CompanyName, ContactName, Address " +
"FROM CustomersTable";
// Create OleDB Command to hold the SELECT statement
OleDbCommand myCommand = MyConn.CreateCommand();
myCommand.CommandText=selectString;
// Set the command text property of the Sqlcommand object to the SELECT
string
OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter();
// Set the SelectCommand property of the OleDbAdapter object ot the
OleDbCommand object
myOleDbDataAdapter.SelectCommand = myCommand;
// Create a DataSet object to store the results of the SELECT statement
DataSet myDataSet = new DataSet();
// Open the database connection using the Open() method of the SqlConnection
object
MyConn.Open();
// Use the Fill() method of the OleDbDataAdapter objekt to retreive the
// rows from the table, storing the rows localy in the DataTable
// of the dataSet object
Console.WriteLine("Retrieving a row from the CustomersTable...");
myOleDbDataAdapter.Fill(myDataSet, "CustomersTable");
// Get the DataTable object from the DataSet object
DataTable myDataTable = myDataSet.Tables["CustomersTable"];
// Display the rows in the DataTable object
DisplayDataTable(myDataTable);
// Add a new row
AddRow(myDataTable);
DisplayDataTable(myDataTable);
// Modify a row
ModifyRow(myDataTable);
DisplayDataTable(myDataTable);
// Removing the new row
//RemoveRow(myDataTable);
//DisplayDataTable(myDataTable);
// Use the Fill() to the OleDbDataAdapter object to synchronize the changes
with the data base
// myOleDbDataAdapter.Fill(myDataSet, "CustomersTable");
myOleDbDataAdapter.Update(myDataSet, "CustomersTable");
// myOleDbDataAdapter.Update(myDataTable);
// Close database connection
MyConn.Close();
}
}
}
message news:
[email protected]...
The problem seems to come from the fact that you add the row, and then
call fill again, which effectively gets rid of any changes that you had
before. Also, as Dmirty pointed out, you are calling AcceptChanges in the
wrong place. You shouldn't call it at all, the adapter will do it when you
call Update.
If you remove the call to Fill before the update, it should work.
Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)
mp said:
Unfortunately it doesn't work.
I have removed all AcceptChanges and I have used
myOleDbDataAdapter.Fill(myDataSet, "CustomersTable");
in message Hi,
How I can sysnchronize changes in DataTable with my Database???
What is wrong here ???
The call to AcceptChanges is wrong. It marks all the rows as "Unchanged"
and
that effectively prevents the DataAdapter to propagate any changes
to
the
database.
--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://www.x-unity.net/teststudio.aspx
Bring the power of unit testing to VS .NET IDE
I am working with OleDB Access DB and CSharp.
How I can sysnchronize changes in DataTable with my Database???
What is wrong here ???
============================================================================ create
a
new
row in the DataTable
DataRow myNewDataRow = myDataTable.NewRow();
// Step 2: Set the values for the columns of the new row
myNewDataRow["CustomerID"] = "T1";
myNewDataRow["CompanyName"] = "T1 Company";
myNewDataRow["ContactName"] = "Jason Price";
myNewDataRow["Address"] = "1 Main Street";
// Step 3: Use the Add() method through the Rows property to add the
new
DataRow to the DataTable
myDataTable.Rows.Add(myNewDataRow);
// Step 4: Use the AcceptChanges() method of the DataTable to commit
myDataTable.AcceptChanges();
}
In main
open etc...
// Call Method and add a new row
AddRow(myDataTable);
DisplayDataTable(myDataTable);
I have try to use the Fill() to the OleDbDataAdapter object to
synchronize
the changes with the data base but without success.
myOleDbDataAdapter.Fill(myDataSet, "CustomersTable");
I have try to use update as follows but without success.
myOleDbDataAdapter.Update(myDataSet, "CustomersTable");
// Close database connection
MyConn.Close();
