Update method not working

[Grober]

Hi,
The code below is working all the way. The problem is that the Update method
of the adapter doesn't update the mdb database. What am I doing wrong,
anyone?

string path = Path.GetDirectoryName(Application.ExecutablePath);
string sConn = @"provider=Microsoft.Jet.OLEDB.4.0;data
source=c:\temp\testxp.mdb";
OleDbConnection conn = new OleDbConnection(sConn);

OleDbDataAdapter adp = new OleDbDataAdapter();
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Customer";
adp.SelectCommand = cmd;
OleDbCommandBuilder ocb = new OleDbCommandBuilder(adp);

conn.Open();

DataSet ds = new DataSet();
string sTabell = "Customer";
adp.Fill(ds, sTabell);
DataTable dt = ds.Tables[sTabell];

DataRow dr = dt.NewRow();
dr["Number"] = 111;
dr["Name"] = "Acme Inc";
dr["Phone"] = "12345";

dt.Rows.Add(dr);

dt.AcceptChanges();

adp.UpdateCommand = ocb.GetUpdateCommand();
adp.Update(ds, sTabell);
conn.Close();

 

[Manoj G [MVP]]

Hi,

The problem is that you are calling AcceptChanges after adding a new
DataRow. So, all DataRows will have the RowState as "unchanged" and when you
call the Update method on the DataAdapter, there is nothing to update. Just
remove the call to AcceptChanges.
One more thing. Are you planning to Add or Update rows in the Database? If
it is adding then, you need to set the InsertCommand. You seem to be setting
the UpdateCommand of the DataAdapter here after adding a new DataRow.

 

[Grober]

Removing the AcceptChanges line did it! Thanks!

It seems the UpdateCommand method line wasn't neccessary either.

I haven't quite figured out how ADO.NET works (obviously). I don't feel I
get that much help from the online help either. Do you know of any good,
easy to understand site?

Regards
/Grober

Hi,

The problem is that you are calling AcceptChanges after adding a new
DataRow. So, all DataRows will have the RowState as "unchanged" and when you
call the Update method on the DataAdapter, there is nothing to update. Just
remove the call to AcceptChanges.
One more thing. Are you planning to Add or Update rows in the Database? If
it is adding then, you need to set the InsertCommand. You seem to be setting
the UpdateCommand of the DataAdapter here after adding a new DataRow.

--
HTH,
Manoj G [.NET MVP]
http://www15.brinkster.com/manoj4dotnet

Hi,
The code below is working all the way. The problem is that the Update method
of the adapter doesn't update the mdb database. What am I doing wrong,
anyone?

string path = Path.GetDirectoryName(Application.ExecutablePath);
string sConn = @"provider=Microsoft.Jet.OLEDB.4.0;data
source=c:\temp\testxp.mdb";
OleDbConnection conn = new OleDbConnection(sConn);

OleDbDataAdapter adp = new OleDbDataAdapter();
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Customer";
adp.SelectCommand = cmd;
OleDbCommandBuilder ocb = new OleDbCommandBuilder(adp);

conn.Open();

DataSet ds = new DataSet();
string sTabell = "Customer";
adp.Fill(ds, sTabell);
DataTable dt = ds.Tables[sTabell];

DataRow dr = dt.NewRow();
dr["Number"] = 111;
dr["Name"] = "Acme Inc";
dr["Phone"] = "12345";

dt.Rows.Add(dr);

dt.AcceptChanges();

adp.UpdateCommand = ocb.GetUpdateCommand();
adp.Update(ds, sTabell);
conn.Close();

 

[Madhu [MVP.NET]]

Hi,

The problem in your code is the AcceptChanges method.
AcceptChanges method resets the RowState of each row.
since RowState is used internally by the adapter to call
various actions like insert, update or delete. Since the
RowState is reset, it doesn't do anything and the
database is not updated.

I hope it answers your question.

Regards,
Madhu

MCSD.NET | MVP