In my WinForms application, I am connecting to an Access (mdb) database. I have 2 tables in the database. MDB is not added to the project. It just avbl in the folder along the exe file of the app.
From the app, I am trying to add/edit records of the db. I could get connected to the DB, add records to DB but finding difficulty in editing the records. While the app is running, I find the db is updated with edited record, but on re-connecting to the DB the edited records doesn't exists i.e. records are not edited at all. The DB is just updated with edited records temporarily only & not permanently updated. This is my code :-
public static OleDbConnection SetupConnection()
{
mainCon = new OleDbConnection();
mainCon.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ServiceStn.mdb;";
try
{
mainCon.Open();
// Fill Data DS
FillAppointmentDS();
........
// Fill AppointmentDS with Appointment Table data
private static void FillAppointmentDs()
{
string todayDt = DateTime.Now.ToString("dd/MM/yyyy");
aptDs = new DataSet();
aptDa = new OleDbDataAdapter("Select * from Appointment where appointDate = #" + todayDt + "#", mainCon);
OleDbCommandBuilder aptCmdBuilder = new OleDbCommandBuilder(aptDa);
aptDa.Fill(aptDs, "Appointment");
aptDa.FillSchema(aptDs, SchemaType.Source, "Appointment");
return;
}
// Add new Appointment to the DB
public static bool SaveAppointment(Models.Appointment apt)
{
DataRow dr = null;
dr = aptDs.Tables[0].NewRow();
dr["custID"] = apt.CustomerId;
dr["appointDate"] = apt.AppointmentDate.Date; // "17/9/2014";
dr["appointTime"] = apt.AppointmentTime.TimeOfDay.ToString();
dr["companyName"] = apt.AC_CompanyName;
dr["model"] = apt.AC_Model;
dr["problem"] = apt.AC_Problem;
aptDs.Tables[0].Rows.Add(dr);
int updatedRows = aptDa.Update(aptDs, "Appointment");
Console.WriteLine("Updated Rows - " + updatedRows);
dr = null;
// Add Appointment to the AppointmentList
appoints.Add(apt);
if (updatedRows > 0)
return true;
else
return false;
}
// Update an existing Appointment in the DB
public static bool UpdateAppointment(Models.Appointment apt)
{
DataRow dr = null;
string filter = "aptID=" + apt.AppointmentId;
dr = aptDs.Tables[0].Select(filter)[0];
dr["appointDate"] = apt.AppointmentDate.Date;
dr["appointTime"] = apt.AppointmentTime.TimeOfDay.ToString();
dr["companyName"] = apt.AC_CompanyName;
dr["model"] = apt.AC_Model;
dr["problem"] = apt.AC_Problem;
// OleDbCommandBuilder aptCmdBuilder = new OleDbCommandBuilder(aptDa);
// dr.AcceptChanges();
aptDs.Tables[0].AcceptChanges();
// dr.SetModified();
int updatedRows = aptDa.Update(aptDs.Tables[0]); //(aptDs, "Appointment");
//int updatedRows = aptDa.Update(aptDs, "Appointment");
Console.WriteLine("Updated Rows - " + updatedRows);
dr = null;
if (updatedRows > 0)
return true;
else
return false;
}
SaveAppointment() works smoothly, but UpdateAppointment() doesn't show the updated the record in DB on re-Connecting the app to DB.
Can anyone help me know why I am not able to edit records to DB permanently !! Where am I going wrong in the code ? I don't get any error/exception in the code. Any help is highly appreciated. Thanks.
This line in the UpdateAppointments
aptDs.Tables[0].AcceptChanges();
should be removed, otherwise the status of the row changes to Unchanged and the subsequent call to Update doesn't find any row modified and thus it can't write your changes back to the database-
The meaning of AcceptChanges is always a source of confusion.
When you modify a row its RowState property becomes Modified.
When you call the OleDbDataAdapter.Update method, it searches all the rows in the table with a RowState like Added, Deleted or Modified and applies the OleDbCommands created by OleDbCommandBuilder appropriate for the state.
AcceptChanges works on these states changing them back to Unchanged (and removing the Rows with the state Deleted from the DataTable container).
In this way, an Update following an AcceptChanges call, will not find any row to be eligible for writing back to the database.