title: Updating SQL Database from DataGridView in C#
body: <p>There's a few tutorials out there on this, but I'm assuming I must have implemented something wrong, because the code I followed from combined tutorials is not working as it should.</p>
  
  <p>These are the tutorials:  <a href="https://youtu.be/_i4mYXSaD4w" rel="nofollow">https://youtu.be/_i4mYXSaD4w</a> , <a href="https://youtu.be/_sB0A6FIhUM" rel="nofollow">https://youtu.be/_sB0A6FIhUM</a></p>
  
  <p>I'm trying to create a DataGridView that displays some basic data, just 4 columns of information. I want the user to be able to add, update and delete rows of information.  I've manually created 1 row of information in the SQL database just to avoid in 'catches' when the program is loaded.</p>
  
  <p>I've got the 1 line of my SQL Database information loading just fine, but when I edit the information, and click my update button it doesn't seem to work on the SQL side, even though the program side works.  By this I mean, I have a messagebox that confirms it's been updated, but when I close the App and the run it again, it loads the old data, and when I double check the database, it hasn't been updated. If someone could help me adjust this so when I add rows of information to the DataGridView or edit the rows, and have the SQL file actually receive the changes/updates, I'd appreciate it. Here is my code:</p>
  
  <pre><code>public partial class Form1 : Form
  {
      SqlConnection con;
      SqlDataAdapter sda;
      DataTable dt;
      SqlCommandBuilder scb;
      private int rowIndex = 0;
  
      public Form1()
      {
          InitializeComponent();
      }
  
  
      private void Form1_Load(object sender, EventArgs e)
      {
          try
          {
              con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
              con.Open();
              sda = new SqlDataAdapter("SELECT * FROM School", con);
              dt = new DataTable();
              sda.Fill(dt);
              dataGridView1.DataSource = dt;
          }
          catch (Exception ex)
          {
              MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
      }
  
      private void UpButton_Click(object sender, EventArgs e)
      {
  
          try
          {
              scb = new SqlCommandBuilder(sda);
              sda.Update(dt);
              MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
          }
  
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
      }
      //button to refresh the data without need to close the app
      private void RefButton_Click(object sender, EventArgs e)
      {
          try
          {
              con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
              con.Open();
              sda = new SqlDataAdapter("SELECT * FROM School", con);
              dt = new DataTable();
              sda.Fill(dt);
              dataGridView1.DataSource = dt;
          }
          catch (Exception ex)
          {
              MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
      }
  
      private void CloseButton_Click(object sender, EventArgs e)
      {
          Application.Exit();
      }
  
      //BROKEN - supposed to bring up a menu item to delete the row
      private void dataGridView1_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
      {
          if (e.Button == MouseButtons.Right)
          {
              this.dataGridView1.Rows[e.RowIndex].Selected = true;
              this.rowIndex = e.RowIndex;
              this.dataGridView1.CurrentCell = this.dataGridView1.Rows[e.RowIndex].Cells[1];
              this.contextMenuStrip1.Show(this.dataGridView1, e.Location);
              contextMenuStrip1.Show(Cursor.Position);
          }
  
      }
  
      private void contextMenuStrip1_Click(object sender, CancelEventArgs e)
      {
          if (!this.dataGridView1.Rows[this.rowIndex].IsNewRow)
          {
              this.dataGridView1.Rows.RemoveAt(this.rowIndex);
          }
      }
  
  
  }
  </code></pre>
  
  <p>Now, what I'm not sure about is 1 thing I changed from one of the tutorials, was that they used a dataset rather than a datatable, but as I understand it, other than a dataset being able to hold multiple table structures, there's no differences in pulling the data or updating it.  For the fact that I didn't have mounds of data and different tables to use, I felt DataTable was sufficient for my use of this program.</p>
  
  <p>Additionally, in my database, I have a primary key that's an integer, and 4 columns of text.  I'd prefer to avoid the stupid column of integers like line numbers and just make my first text column the primary key, but when I try to do this and update the database, it throws errors.  If there's a way to do this, I'd appreciate the explanation of how, or if there's a way to hide the first line number column the pulls the integer value for the primary key, and have it automatically increment and/or adjust this value according to editing, changes and new rows being added that'd be great.  Just to clear up, if I add rows 2, 3 and 4, I want these values to be autogenerated and just make that column not visible.  As it stands, I have to manually type the integer in there. </p>
  
  <p>Thanks for any help and advice.</p>
  
  <p>Update #1:</p>
  
  <p>Okay, so taking some recommendations, I have tried using DataSet in the following format:</p>
  
  <pre><code>private void Form1_Load(object sender, EventArgs e)
      {
          try
          {
              con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
              con.Open();
              sda = new SqlDataAdapter("SELECT * FROM School", con);
              ds = new DataSet();
              sda.Fill(ds, "e");
              dataGridView1.DataSource = ds.Tables["e"];
            }
          catch (Exception ex)
          {
              MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
      }
  
      private void UpButton_Click(object sender, EventArgs e)
      {
  
          try
          {
              scb = new SqlCommandBuilder(sda);
              sda.Update(ds, "e");
              MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
          }
  
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
      }
  </code></pre>
  
  <p>I've tried using DataTable again in the format given in the first answer provided using the following format but in 2 ways: 1 without a new instance of the SqlCommandBuilder, and one with:
  WITH:</p>
  
  <pre><code>private void Form1_Load(object sender, EventArgs e)
      {
          try
          {
              con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
              con.Open();
              sda = new SqlDataAdapter("SELECT * FROM School", con);
              dt = new DataTable();
              sda.Fill(dt);
              dataGridView1.DataSource = dt;
          }
          catch (Exception ex)
          {
              MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
      }
  
      private void UpButton_Click(object sender, EventArgs e)
      {
  
          try
          {
              scb = new SqlCommandBuilder(sda);
              newDT = dt.GetChanges();
              if (newDT != null)
              {
                  sda.Update(newDT);
              }
              MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
          }
  
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
      }
  </code></pre>
  
  <p>WITHOUT:  This version produces an error that says: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."</p>
  
  <pre><code>private void Form1_Load(object sender, EventArgs e)
      {
          try
          {
              con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
              con.Open();
              sda = new SqlDataAdapter("SELECT * FROM School", con);
              dt = new DataTable();
              sda.Fill(dt);
              dataGridView1.DataSource = dt;
          }
          catch (Exception ex)
          {
              MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
      }
  
      private void UpButton_Click(object sender, EventArgs e)
      {
  
          try
          {
              newDT = dt.GetChanges();
              if (newDT != null)
              {
                  sda.Update(newDT);
              }
              MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
          }
  
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
      }
  </code></pre>
  
  <p>Sooooo, I'm stumped. All except the one without SqlCommandBuilder trigger the Updated Message Box, but none of them actually save the changes to the sql database.</p>