Thank you Yang!

I got it working. I have a weird problem while using OleDb provider objects.
See if you guys experience this problem. I think it's a bug with OleDb provider
objects, but maybe I am doing something wrong. Please read on. I am trying
to explain this problem the best I can.

Thank you very much in advance!!

If I want to add a new row to my Access DB like so:

***********************************************************************
ERROR since OleDb needs an UpdateCommand FIRST, but for SqlClient, this
is CORRECT.
***********************************************************************

// Create a new row in DataSet.
DataTable dataTable = ds.Tables[0];
DataRow newRow = dataTable.NewRow();
newRow["CarID"] = "6";
newRow["Make"] = "Ford";
newRow["Color"] = "Black";
newRow["PetName"] = "Black Cat";

// Add the new row to our local DataSet table FIRST.
dataTable.Rows.Add( newRow );

// Update the Access DB
dAdapt.Update( ds, "Inventory" );

***********************************************************************
ERROR: I found this wierd problem. InsertCommand() DOES NOT add a new
row to my Access DB.
***********************************************************************

string insertString = "INSERT INTO Inventory VALUES ( 6, \"Ford\", \"Black\",
\"Black Cat\" )";

dAdapt.InsertCommand = new OleDbCommand( insertString, cn );

// Update the Access DB
dAdapt.Update( ds, "Inventory" ); /* Access DB DID NOT have new row inserted
*/

***********************************************************************
CORRECT: I need to add new row to DataSet first, then supply my UpdateCommand()
and then Update() in order to work.
Is there another way?? Am I doing something wrong!?
***********************************************************************

// Create a new row in DataSet.
DataTable dataTable = ds.Tables[0];
DataRow newRow = dataTable.NewRow();
newRow["CarID"] = "6";
newRow["Make"] = "Ford";
newRow["Color"] = "Black";
newRow["PetName"] = "Black Cat";

// Add the new row to our local DataSet table FIRST.
dataTable.Rows.Add( newRow );

string insertString = "INSERT INTO Inventory VALUES ( 6, \"Ford\", \"Black\",
\"Black Cat\" )";

dAdapt.InsertCommand = new OleDbCommand( insertString, cn );

// Update the Access DB
dAdapt.Update( ds, "Inventory" ); /* CORRECT, Access DB have new row */

***********************************************************************
Hence, I think this is a bug because one can have totally different
data in the DataSet and in the Access DB like so:
***********************************************************************

// Create a new row in DataSet.
DataTable dataTable = ds.Tables[0];
DataRow newRow = dataTable.NewRow();
newRow["CarID"] = "6";
newRow["Make"] = "Ford";
newRow["Color"] = "Black";
newRow["PetName"] = "Black Cat";

VERSUS...

string insertString = "INSERT INTO Inventory VALUES ( 6, \"ACURA\", \"DESERT
SILVER\", \"(null)\" )";
dAdapt.InsertCommand = new OleDbCommand( insertString, cn );


/************************************************************************/
// ENTIRE CODE...
/************************************************************************/
// Create data adapter using the following SELECT.
string sqlSELECT = "SELECT * FROM Inventory";

// Setting connection string for Access DB
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
@"data source=c:\car.mdb";

OleDbConnection cn = new OleDbConnection( connectionString );

try
{
cn.Open();
}
catch ( OleDbException ex )
{
MessageBox.Show( "OleDb Open exception: \n\n" + ex.Message );
}

OleDbDataAdapter dAdapt = new OleDbDataAdapter( sqlSELECT, cn );

// Create and fill the DataSet, close connection.
DataSet ds = new DataSet( "CarsDataSet" );

dAdapt.Fill( ds, "Inventory" );
PrintTable( ds );

/* Create a new row. */
DataTable dataTable = ds.Tables[0];
DataRow newRow = dataTable.NewRow();
newRow["CarID"] = "6";
newRow["Make"] = "Ford";
newRow["Color"] = "Black";
newRow["PetName"] = "Black Cat";

// Add the new row to our local DataSet table FIRST.
dataTable.Rows.Add( newRow );

// Modify data set row here FIRST.
dataTable.Rows[0]["Make"] = "Mazdaaa";

if ( ds.HasChanges() )
{
// Add the new row to Access DB, specified with the InsertCommand.
// NOTE: If DataSet was not INSERTed first, doing InsertCommand DOES NOT

// add new row
// to Access DB! The aforementioned procedure also applies to UPDATE
// and possibly DELETE.
// Hence, our local DataSet data can be different from our actually data
// in Access DB. I think this is a bug.
string insertString = "INSERT INTO Inventory VALUES ( 6, \"Ford\", \"Black\",
\"Black Cat\" )";

dAdapt.InsertCommand = new OleDbCommand( insertString, cn );

string updateString = "UPDATE Inventory SET Make = \"Mazdaaa\" WHERE Make
= \"BMW\"";

dAdapt.UpdateCommand = new OleDbCommand( updateString, cn );

// Update the Access DB
dAdapt.Update( ds, "Inventory" );

// Commit all changes
ds.AcceptChanges();

/* Store new data in DataGrid */
inventoryDataGrid.DataSource = ds;
inventoryDataGrid.DataMember = "Inventory";
...

"M. Yang" <myang@foxinternet.net> wrote:
>
>1) You do not need a DSN (registed via ODBC) since you specify the path
of
>your db, something like:
>
>String s = @"provider=microsoft.jet.oledb.4.0;data source=c:\temp\nwind.mdb")
>
>2) If path of Car.mdb is correct, open car.mdb with MS Access, select Tool
>| Security, clear password for admin and all users. Otherwise, add "user
>id=?;password=?;" in your connect string and it should work.
>
>3) You may get more info in the following page.
>
>M. Yang
>--------------------------------
>http://www.foxinternet.net/web2/myang/