2

I'm working on a project which uses an Access database. First of all I copy the information that i need from db and put it in a DataTable (with combining 2 tables). Over this datatable I search items; update, delete, add rows using a GridView and all of that happens in an ajax update panel. Problem occurs when i try to apply the changes back to the db. What i want to do is get certain rows of the DataTable and update/insert/delete them to certain rows of certain tables. Important thing is database table has to contain the same information as the DataTable. İ actually don't know if I can use the OledbDataAdapter.Update this way.

Here is the exeption that i get : Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

My Code:

void load_dtTable()
{
    OleDbConnection bag = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\\Profiles\\StjBugraO\\Desktop\\data.accdb");

    DataTable dtResult = new DataTable();
    if (Session["dtResult"] != null)
    {
        dtResult = (DataTable)Session["dtResult"];
    }
    else
    {
        bag.Open();
        try
        {
            OleDbDataAdapter adtr = new OleDbDataAdapter("select gt.NodeID, gt.unit_num,gt.unit_name_text,gt2.ip_id ,gt2.server_name_text ,gt.call_flow_type  From gt inner join gt2 on gt.cm_id=gt2.cm_id", bag);
            dtResult.Clear();
            adtr.Fill(dtResult);
            adtr.Dispose();
            dtResult.Columns[0].ColumnName = "NodeID";
            dtResult.Columns[1].ColumnName = "Şube Kodu";
            dtResult.Columns[2].ColumnName = "Şube ADI";
            dtResult.Columns[3].ColumnName = "IP Adresi";
            dtResult.Columns[4].ColumnName = "Call Manager";
            dtResult.Columns[5].ColumnName = "Santral Tipi";
            foreach (DataRow dr in dtResult.Rows)
            {
                if ((string)dr[5] == "P")
                {
                    dr[5] = "PRI";
                }
                else if ((string)dr[5] == "A") dr[5] = "ANALOG";


            }

        }
        catch (Exception e)
        {




        }
        Session["dtResult"] = dtResult as DataTable;


            bag.Close();
    }



protected void Button3_Click(object sender, EventArgs e)
{
    DataTable dtResult = Session["dtResult"] as DataTable;
    //changing the names of the columns (that i need) to be the same with the db
    dtResult.Columns[0].ColumnName = "NodeID";
    dtResult.Columns[1].ColumnName = "unit_num";
    dtResult.Columns[2].ColumnName = "unit_name_text";
    dtResult.Columns[3].ColumnName = "ip_id";    
    dtResult.Columns[5].ColumnName = "call_flow_type";


    OleDbConnection bag = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\\Profiles\\StjBugraO\\Desktop\\data.accdb");
    OleDbDataAdapter adtr2 = new OleDbDataAdapter("select NodeID ,unit_num ,unit_name_text , ip_id , call_flow_type from gt", bag);
    OleDbCommandBuilder Ocmd = new OleDbCommandBuilder(adtr2);
    adtr2.DeleteCommand=Ocmd.GetDeleteCommand();
    adtr2.InsertCommand=Ocmd.GetInsertCommand();
    adtr2.UpdateCommand=Ocmd.GetUpdateCommand();
    DataTable dt = dtResult.GetChanges();
    int updates = 0;
    try
    {
        if(dt != null)
      updates=adtr2.Update(dtResult);
    }

    catch (Exception ex)
    {
        Label1.Text = ex.Message.ToString();
    }
    //changing them back

    dtResult.Columns[0].ColumnName = "NodeID";
    dtResult.Columns[1].ColumnName = "Şube Kodu";
    dtResult.Columns[2].ColumnName = "Şube ADI";
    dtResult.Columns[3].ColumnName = "IP Adresi";
    dtResult.Columns[4].ColumnName = "Call Manager";
    dtResult.Columns[5].ColumnName = "Santral Tipi";
    Label2.Text = updates + "changes applied.";
    adtr2.Dispose();
    bag.Close();





}
Chris
  • 2,955
  • 1
  • 30
  • 43
Bugra Oral
  • 21
  • 2
  • 1
    Do your columns really have spaces in them (i.e. `Şube Kodu`)? Not particular good practise (better to use the underscore `_` character) – freefaller Jul 24 '12 at 09:10
  • See http://stackoverflow.com/questions/4306619/concurrency-violation-the-updatecommand-affected-0-of-the-expected-1-records-d – Fionnuala Jul 24 '12 at 09:11
  • Yeap they do have spaces but i'am not getting any erreurs elsewhere they work perfectly fine and as you can see i'am changing them back to original colomn names with no spaces and that's the part doesn't work.I had already read the link but couldn't find any solution perhaps i'am not seeing it. Thanks for the quick reply – Bugra Oral Jul 24 '12 at 10:00

0 Answers0