Video Rental c# problem

[RonKevinT.Manuela]

I have a problem with my code in deleting of a record in datagridview and database(see attatchment)...for some reason I cant delete the data in my database...heres the code:

private void btnDelete_Click(object sender, EventArgs e)
        {
            int selectedindex=dataGridView1.CurrentCell.RowIndex;
            string deleted;

            try
            {

                cnn.Open();
                if (MessageBox.Show("Are you sure you want to delete this feature?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {

                    dataGridView1.Rows.RemoveAt(selectedindex);
                    deleted = dataGridView1.Rows[selectedindex].Cells["MovieID"].Value.ToString();



                    cmd3=new SqlCommand("ALTER TABLE dbo.MovieCopy DROP CONSTRAINT FK_MovieCopy_Movies",cnn);



                    cmd1=new SqlCommand("Delete From Movies where movieid=@movieid",cnn);
                    cmd2 = new SqlCommand("Delete From MovieCopy where movieid=@movieid", cnn);
                    cmd1.Parameters.AddWithValue("@movieid", deleted);
                    cmd2.Parameters.AddWithValue("@movieid", deleted);
                    cmd1.ExecuteNonQuery();
                    cmd2.ExecuteNonQuery();
                    dataGridView1.Refresh();
                    cmd4 = new SqlCommand("ALTER TABLE dbo.MovieCopy ADD CONSTRAINT FK_MovieCopy_Movies FOREIGN KEY (MovieId) REFERENCES dbo.Movies(MovieID) ON DELETE CASCADE", cnn);

                }








            }
            catch (Exception ex)
            {
                MessageBox.Show("Input Error!" + ex.Message);
            }
            finally
            {
                cnn.Close();
            }
        }

[ChrisHunter]

Move dataGridView1.Rows.RemoveAt(selectedindex); and put it below cmd4 = new SqlCommand.

By the looks of it you're trying to get the MovieID from a row that doesn't exist at anymore.

Hope this helps.

[ChrisHunter]

Try deleting the record from MovieCopy table, before deleting from the Movie table, so that the reference to the Movie table record doesn't exist.

Also you might need to move dataGridView1.Rows.RemoveAt(selectedindex); and put it below cmd4 = new SqlCommand.

By the looks of it you're deleting the record at "Selectedindex" which removes that row and the row at "Selectedindex" is now the row below meaning MovieID won't be the ID of the row that has just been removed from the datagridview.

Hope this helps.

[RonKevinT.Manuela]

thanks..ima try it...but first, i need to cook for dinner...haha...

[RonKevinT.Manuela]

tried it..at least no error but sir, It doesnt delete the data from my database..

[RonKevinT.Manuela]

moviecopy table only updated..movie table didnt

[RonKevinT.Manuela]

ima fix it...thanks sir!

[ChrisHunter]

Comment out the contents of your if statement (DON'T DELETE IT) and paste this into it:

deleted = dataGridView1.Rows[selectedindex].Cells["MovieID"].Value.ToString();
cmd3 = new SqlCommand("ALTER TABLE dbo.MovieCopy DROP CONSTRAINT FK_MovieCopy_Movies",cnn);

cmd1 = new SqlCommand("Delete From MovieCopy where movieid=@movieid",cnn);
cmd1.Parameters.AddWithValue("@movieid", deleted);

cmd2 = new SqlCommand("Delete From Movies where movieid=@movieid", cnn);
cmd2.Parameters.AddWithValue("@movieid", deleted);

cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();

cmd4 = new SqlCommand("ALTER TABLE dbo.MovieCopy ADD CONSTRAINT FK_MovieCopy_Movies FOREIGN KEY (MovieId) REFERENCES dbo.Movies(MovieID) ON DELETE CASCADE", cnn);

dataGridView1.Rows.RemoveAt(selectedindex);
dataGridView1.Refresh();

Try that. I've put the delete for the MoveCopy first so that the primary key in the Movies table can be deleted without any problems (hopefully).

I've also put the dataGridView1.Rows.RemoveAt(); at the end so that the ID of the movie that is being deleted is the selected movie.

Let me know how you get on.

[ChrisHunter]

Sorry I didn't see your reply. Ignore my last comment if it's working.

Check the table names and field names are correct. It could be a case of a capital letter is needed instead of lower case.

[RonKevinT.Manuela]

Sir!! thanks! I fixed it already...after you gave me your first solution it was a matter of experimenting and understanding..Thank you sir ChrisHunter

[ChrisHunter]

No problem, glad I was of some kind of help.