title: DataAdapter not updating source
body: <p>I have come across a problem in using the DataAdapter, which I hope someone can help with.  Basically I am creating a system, which is as follows:</p>
  
  <ol>
  <li>Data is read in from a data source (MS-Access, SQL Server or Excel), converted to data tables and inserted into a local SQL Server database, using DataAdapters.  This bit works fine.  The SQL server table has a PK, which is an identity field with auto increment set to on.</li>
  <li>Subsequent data loads read in the data from the source and compare it to what we already have.  If the record is missing then it is added (this works fine).  If the record is different then it needs to be updated (this doesn't work).</li>
  <li>When doing the differential data load I create a data table which reads in the schema from the destination table (SQL server) and ensures it has the same columns etc.</li>
  <li>The PK in the destination table is column 0, so when a record is inserted all of the values from column 1 onwards are set (as mentioned this works perfectly.).  I don't change the row status for items I am adding.  The PK in the data table is set correctly and I can confirm this.</li>
  <li>When updating data I set column 0 (the PK column) to be the value of the record I am updating and set all of the columns to be the same as the source data.</li>
  <li>For updated records I call AcceptChanges and SetModified on the row to ensure (I thought) that the application calls the correct method.</li>
  <li>The DataAdapter is set with SelectCommand and UpdateCommand using the command builder.</li>
  </ol>
  
  <p>When I run, I have traced it using SQL profiler and can see that the insert command is being ran correctly, but the update command isn't being ran at all, which is the crux of the problem.  For reference an insert table will look something like the following </p>
  
  <pre><code>PK   Value1    Value 2    Row State
  ==   ======    =======    =========
  124   Test1     Test 2    Added
  123   Test3     Test4     Updated
  </code></pre>
  
  <p>Couple of things to be aware of....</p>
  
  <ul>
  <li>I have tested this by loading the row to be changed into the datatable, changing some column fields and running update and this works.  However, this is impractical for my solution because the data is HUGE >1Gb so I can't simply load it into a datatable without taking a huge performance hit.  What I am doing is creating the data table with a max of 500 rows and the running the Update.  Testing during the initial data load showed this to be the most efficient in terms of memory useage and performance.  The data table is cleared after each batch is ran.</li>
  </ul>
  
  <p>Anyone any ideas on where I am going wrong here?</p>
  
  <p>Thanks in advance</p>
  
  <p>Andrew</p>
  
  <p>==========Update==============</p>
  
  <p>Following is the code to create the insert/update rows</p>
  
  <pre><code>    private static void AddNewRecordToDataTable(DbDataReader pReader, ref DataTable pUpdateDataTable)
      {
  
          // create a new row in the table
  
          DataRow pUpdateRow = pUpdateDataTable.NewRow();
  
          // loop through each item in the data reader - setting all the columns apart from the PK
  
          for (int addCount = 0; addCount &lt; pReader.FieldCount; addCount++)
          {
              pUpdateRow[addCount + 1] = pReader[addCount];
          }
  
          // add the row to the update table
  
          pUpdateDataTable.Rows.Add(pUpdateRow);
  
      }
  
      private static void AddUpdateRecordToDataTable(DbDataReader pReader, int pKeyValue,
                                                     ref DataTable pUpdateDataTable)
      {
          DataRow pUpdateRow = pUpdateDataTable.NewRow();
  
          // set the first column (PK) to the value passed in
  
          pUpdateRow[0] = pKeyValue;
  
          // loop for each row apart from the PK row
  
          for (int addCount = 0; addCount &lt; pReader.FieldCount; addCount++)
          {
              pUpdateRow[addCount + 1] = pReader[addCount];
          }
  
          // add the row to the table and then update it
  
          pUpdateDataTable.Rows.Add(pUpdateRow);
          pUpdateRow.AcceptChanges();
          pUpdateRow.SetModified();
      }
  </code></pre>
  
  <p>The following code is used to actually do the update:</p>
  
  <pre><code>    updateAdapter.Fill(UpdateTable);
      updateAdapter.Update(UpdateTable);
      UpdateTable.AcceptChanges();
  </code></pre>
  
  <p>The following is used to create the data table to ensure it has the same fields/data types as the source data</p>
  
  <pre><code>    private static DataTable CreateDataTable(DbDataReader pReader)
      {
          DataTable schemaTable = pReader.GetSchemaTable();
          DataTable resultTable = new DataTable(&lt;tableName&gt;); // edited out personal info
          // loop for each row in the schema table
  
          try
          {
  
              foreach (DataRow dataRow in schemaTable.Rows)
              {
  
                  // create a new DataColumn object and set values depending
                  // on the current DataRows values
  
                  DataColumn dataColumn = new DataColumn();
                  dataColumn.ColumnName = dataRow["ColumnName"].ToString();
                  dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());
                  dataColumn.ReadOnly = (bool)dataRow["IsReadOnly"];
                  dataColumn.AutoIncrement = (bool)dataRow["IsAutoIncrement"];
                  dataColumn.Unique = (bool)dataRow["IsUnique"];
  
                  resultTable.Columns.Add(dataColumn);
              }
          }
          catch (Exception ex)
          {
              message = "Unable to create data table " + ex.Message;
              throw new Exception(message, ex);
          }
  
          return resultTable;
      }
  </code></pre>