How to delete row datagrid and update SQL database C#

Question

I am really stuck and non of the books or tread here are user-friendly enough to explain how to delete data from data grid object using C#

from varies of books and fourm threads I have managed to get this coded but it fails to executes the sql command , I have One table call 'SpellingList' and two columns one is ID and the other is Words, all I want to do is delete a row from the datagrid.

Can someone point me to the correct direction , once I solved this , I shall upload some basic easy to follow tutorial so the rest of the newbie can follow this example.

I have used this thread How to delete a selected DataGridViewRow and update a connected database table?

and MCSD Certificaiton Toolkit Exam 70-483 book Page 392 , all attempts have failed.

there is a catch 22 here if I comment out if(dataGridView1.SelectedRows.Count > 0) line , I get the index out of range error .

private void btnDelete_Click(object sender, EventArgs e)
{
     // My SQL connectionString 
         SqlConnection myConnection = new SqlConnection("Data Source=Epic-LaptopWR;Initial Catalog=words;Integrated Security=True");
        myConnection.Open();


        foreach (DataGridViewCell oneCell in dataGridView1.SelectedCells)
        {

            if(dataGridView1.SelectedRows.Count > 0)
            {
                 int selectedIndex = dataGridView1.SelectedRows[0].Index;
                // gets the RowID from the first column in the grid
                int rowID = int.Parse(dataGridView1[0, selectedIndex].Value.ToString());

                 // your code for deleting it from the database
                string sql = "DELETE FROM SpellingList WHERE Words =" + selectedIndex;
                SqlCommand deleteRecord = new SqlCommand();
                 deleteRecord.Connection = myConnection;
                 deleteRecord.CommandType = CommandType.Text;
                 deleteRecord.CommandText = sql;

                 SqlParameter RowParameter = new SqlParameter();
                 RowParameter.ParameterName = "@Words";
                 RowParameter.SqlDbType = SqlDbType.Int;
                 RowParameter.IsNullable = false;
                 RowParameter.Value = rowID;

                deleteRecord.Parameters.Add(RowParameter);

                deleteRecord.Connection.Open();

                deleteRecord.ExecuteNonQuery();
               if (oneCell.Selected)
                 dataGridView1.Rows.RemoveAt(oneCell.RowIndex);
                deleteRecord.Connection.Close();

                wordsDataSet.GetChanges();
                // booksDataset1.GetChanges();
                spellingListTableAdapter.Fill(wordsDataSet.SpellingList);
            }// end of if 

            // then your code for refreshing the DataGridView
        }// end of for each statement 
        myConnection.Close();

}// end of delete Button 

Answer 1

I would humbly like to thank Steve for his contribution , without guru's like him us newbies would have spent hours to end in trying to solve a simple yet complex problem.

Here is the working code with few modification that by passes the index out of bound error:

private void btnDelete_Click(object sender, EventArgs e)
    {

            // No row selected no delete....
           if(dataGridView1.SelectedRows.Count == 0)
           {

               MessageBox.Show("No row selected !");// show a message here to inform
           }


            // Prepare the command text with the parameter placeholder

           string sql = "DELETE FROM SpellingList WHERE ID = @rowID";


            // Create the connection and the command inside a using block
         using(SqlConnection myConnection = new SqlConnection("Data Source=Epic-LaptopWR;Initial Catalog=words;Integrated Security=True"))
         using (SqlCommand deleteRecord = new SqlCommand(sql, myConnection))
         {
             myConnection.Open();

             MessageBox.Show("The SQL Connection is Open");

             // this overcomes  the out of bound error message 
             // if the selectedRow is greater than 0 then exectute the code below.
             if(dataGridView1.CurrentCell.RowIndex > 0)
             {

                int selectedIndex = dataGridView1.SelectedRows[0].Index;
                // gets the RowID from the first column in the grid
          int rowID = Convert.ToInt32(dataGridView1[0, selectedIndex].Value);

                 // Add the parameter to the command collection
          deleteRecord.Parameters.Add("@rowID", SqlDbType.Int).Value = rowID;
          deleteRecord.ExecuteNonQuery();

                // Remove the row from the grid
                dataGridView1.Rows.RemoveAt(selectedIndex);
             }

         }

    }// end of delete Button 

Please ensure that your datagrid property for SelectionMode is set to 'FullRowSelect' this as mention by Steve will populated with selected rows.

In additional to the above this is for the newbies , please ensure you have a Primary key set as ID with integer values ,in my first error I did not have any data in the primary key it was NULL with one field { Colum called Words} .

Within a week or so I shall post a step by step guide on how to create a simple database watch out for this space.

Answer 2

We could simplify a lot. And fix some evident bugs.

The ID field is the key field of your table and thus you should use it to find the row to delete, then, a parameter, to be useful, needs a parameter placeholder in the command text that you want to execute

private void btnDelete_Click(object sender, EventArgs e)
{
     // No row selected no delete....
     if(dataGridView1.SelectedRows.Count == 0)
         return; // show a message here to inform

     // Prepare the command text with the parameter placeholder
     string sql = "DELETE FROM SpellingList WHERE ID = @rowID";

     // Create the connection and the command inside a using block
     using(SqlConnection myConnection = new SqlConnection("...."))
     using(SqlCommand deleteRecord = new SqlCommand(sql, myConnection))
     {
        myConnection.Open();

        int selectedIndex = dataGridView1.SelectedRows[0].Index;
        // gets the RowID from the first column in the grid
        int rowID = Convert.ToInt32(dataGridView1[0, selectedIndex].Value);

        // Add the parameter to the command collection
        deleteRecord.Parameters.Add("@rowID", SqlDbType.Int).Value = rowID;
        deleteRecord.ExecuteNonQuery();

        // Remove the row from the grid
        dataGridView1.Rows.RemoveAt(selectedIndex);
    }
}

As you can see, connections and commands are disposable objects and should be enclosed in a using statement to ensure proper closing and disposing also in case of exceptions.

Finally, removing the row from the grid should be done simply using the index of the row, removing the line in a single command. You don't need to loop over the cells to delete them one by one.