0

I am having a hard time figuring out how to delete a row from the datagridview using a datasource that will also delete the row in SQL Database.

public partial class Form1 : Form
    {
        const String sStr = "Server= ************;Database=***; Integrated Security=SSPI";
        SqlConnection sConn;
        SqlDataAdapter daCourse;
        DataSet ds;
        SqlCommandBuilder cb;
     

        public Form1()
        {
            InitializeComponent();

        }
        private void Form1_Load(object sender, EventArgs e)
        {
            sConn = new SqlConnection(sStr);
            daCourse = new SqlDataAdapter("SELECT * from Course ", sConn);
            ds = new DataSet();
            daCourse.Fill(ds, "Course");
            dgvCourse.DataSource = ds.Tables["Course"];
        }

  private void btnDelete_Click(object sender, EventArgs e)
        {   [B]THIS PART DOES DELETE FROM DATAGRIDVIEW BUT NOT IN MSSQL DB[/B]
            //remove the row from datagridview
            dgvCourse.Rows.Remove(dgvCourse.CurrentRow);
            ds.Tables["Course"].AcceptChanges();     
            daCourse.Update(ds, "Course");

           

        }

}

Thanks to all who can help :)

2
Contributors
1
Reply
11
Views
5 Years
Discussion Span
Last Post by Mitja Bonca
0

If your DGV is data bound (so the DGV`s property DataSource is used), then all the changes made in the DGV will automatically reflect in the dataTable (or dataSet) as well.
Then if you want to do updates in the database as well, you have to use Update SqlCommand.
Take a lookt at this example of mine I did a copule of months ago

public void DAL_UpdateStudentsTable(DataTable table)
        {
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = @"UPDATE Students SET " +
                                       "StudentID = @id, " +
                                       "FirstName = @first, " +
                                       "LastName = @last, " +
                                       "Birthday = @birthday, " +
                                       "PersonalNo = @personal " +
                                       "WHERE StudentID = @oldId";
                    cmd.Parameters.Add("@id", SqlDbType.Int, 5, "StudentID");
                    cmd.Parameters.Add("@first", SqlDbType.VarChar, 50, "FirstName");
                    cmd.Parameters.Add("@last", SqlDbType.VarChar, 50, "LastName");
                    cmd.Parameters.Add("@birthday", SqlDbType.DateTime, 1, "Birthday");
                    cmd.Parameters.Add("@personal", SqlDbType.VarChar, 50, "PersonalNo");
                    SqlParameter param = cmd.Parameters.Add("@oldId", SqlDbType.Int, 5, "StudentID");
                    param.SourceVersion = DataRowVersion.Original;
                    cmd.Connection = sqlConn;
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        da.UpdateCommand = cmd;
                        da.Update(table);
                    }
                }
            }
        }

As you can see, I pass a DataTable to the method which has a code to do an UPDATE. Using SqlDataAdaper will execute the changes.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.