title: DataTable Modified row count reverting to 0 after adapter fill
body: <p>Apologies if this is a daft error - but have been struggling a few weeks with this and I am no further on.</p>
  
  <p>The problem is - my update call from a DT based DS to an SQL Db is only inserting nerw records, not updating modifed ones.</p>
  
  <p>I have a datasource (httpWebRequested html table) which I call periodically and update a DataTable belonging to a DataSet with it. Initially I parsed it all into a second DataTable and merged it with my DataSet table - but this wasnt working, so currently I have one DataTable only and either insert new rows into table or update existing values by iterating through the source and adding a row at a time to an array - checking for existance of current row, and either updating or inserting ([Session ID] is a PK, and Data[0] is corresponding unique value):-</p>
  
  <pre><code>                foreach (var row in rows.Skip(1))
              {
                  //create new list collection
                  var data = new List&lt;string&gt;();
                  //interate through rows
                  foreach (var column in row.Descendants("td"))
                  {
                      //add data to list from Table
                      data.Add(column.InnerText);
                  }
                      string strSelect = "[Session ID] = '"+ data[0] +"'";
                      DataRow[] myRow = dt.Select(strSelect);
                      if (myRow.Length == 1)
                      {
                          if (myRow[0][2].ToString() != data[2].ToString())
                          {
                              myRow[0][2] = data[2];
                          }
                          if (myRow[0][3].ToString() != data[3].ToString())
                          {
                              myRow[0][3] = data[3];
                          }
                          if (myRow[0][4].ToString() != data[4].ToString())
                          {
                              myRow[0][4] = data[4];
                          }
                          if (myRow[0][5].ToString() != data[5].ToString())
                          {
                              myRow[0][5] = data[5];
                          }
                          if (myRow[0][7].ToString() != data[7].ToString())
                          {
                              myRow[0][7] = data[7];
                          }
                      }
                      else
                      {
                          dt.Rows.Add(data.ToArray());
                      }
  </code></pre>
  
  <p>I put a bit on the end also which counts the Added and Modified:-</p>
  
  <pre><code>            int modified= 0;
              int added = 0;
              foreach (DataRow dr in pca.chatDataSetG.Tables[0].Rows)
              {
                  if (dr.RowState == DataRowState.Modified)
                  {
                      modified++;
                  }
                  if (dr.RowState == DataRowState.Added)
                  {
                      added++;
                  }
              }
  </code></pre>
  
  <p>At this point - the counts are fine - modified pick up the rows where attributes have changed and added is correct.</p>
  
  <p>When I call my update Db method - things start to go wrong:-</p>
  
  <pre><code>    public static void updateSqlTable()
      {
  
          string connectionString = "Connection String here";
          string qry = @"select * from chatData";
  
          SqlConnection conn = new SqlConnection(connectionString);
  
          try
          {
              SqlDataAdapter da = new SqlDataAdapter(qry, conn);
              SqlCommandBuilder sb = new SqlCommandBuilder(da);
              log.Info("Building Queries...");
              da.UpdateCommand = sb.GetUpdateCommand();
              da.InsertCommand = sb.GetInsertCommand();
              da.DeleteCommand = sb.GetDeleteCommand();
  
              log.Info("Filling Data into Adapter...");
              int modified = 0;
              int added = 0;
              foreach (DataRow dr in pca.chatDataSetG.Tables[0].Rows)
              {
                  if (dr.RowState == DataRowState.Modified)
                  {
                      modified++;
                  }
                  if (dr.RowState == DataRowState.Added)
                  {
                      added++;
                  }
              }
              //This is where the modified count reverts
              da.Fill(pca.chatDataSetG, "Chat");
              modified = 0;
              added = 0;
              foreach (DataRow dr in pca.chatDataSetG.Tables[0].Rows)
              {
                  if (dr.RowState == DataRowState.Modified)
                  {
                      modified ++;
                  }
                  if (dr.RowState == DataRowState.Added)
                  {
                      added++;
                  }
              }
              conn.Open();
              log.Info("Calling Update to DB...");
              int rowseffected = da.Update(pca.chatDataSetG, "Chat");
              log.Info("Update Complete - " + rowseffected + " rows effected........");
  
          }
          catch (Exception ex)
          {
              log.Error("Error Updating Db with chat Data", ex);
          }
          finally
          {
              conn.Close();
          }
  
      }
  </code></pre>
  
  <p>The counts are right before the fill, but after - the added count remains the same - good, but the modified count goes to 0 - bad :( . I have tried all shapes to try and see what is going on here - but honestly -  am stumped. Any help would be very, very, very much appreciated. The rows in the Db are not reflecting new values.</p>
  
  <p>Peter</p>