Deleting a record from multiple tables

Question

A table has been created for each subject (e.g. EnglishLanguage, Mathematics) and the students table is related to each of the subject table, I'd be thankful if someone could kindly edit the code below to enable me execute a delete command to delete a record from these multiple tables.

One important issue is that there should be a way to execute the delete command such that a related record can as well be deleted from any additional subject table that will subsequently be created in future when a new subject is introduced.

Dim cd As String

If txtName.Text = "" And cboDay.Text = "" And cboMonth.Text = "" And txtYear.Text = "" And lblAge.Text = "" And radioMale.Checked = False Or RadioFemale.Checked = False And txtGName.Text = "" And txtMPhone.Text = "" And txtEmail.Text = "" And txtAddress.Text = "" And txtCity.Text = "" And cboRegion.Text = "" And PictureBox1.ImageLocation = "" Then
    MessageBox.Show("There is no record selected to delete. Search for the record to delete.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
    cd = MessageBox.Show("You are about to delete this record. Are you sure you want to delete?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
    If cd = vbYes Then
        cmd = New SqlCommand("Delete from StudentDetails.Registration where StudentId='" & txtStudentId.Text & "'", cn)
        cmd.ExecuteNonQuery()

        cmd = New SqlCommand("Delete from StudentDetails.Students where StudentId='" & txtStudentId.Text & "'", cn)
        cmd.ExecuteNonQuery()

        cmd = New SqlCommand("Delete from ProgramDetails.EnglishLanguage where StudentId='" & txtStudentId.Text & "'", cn)
        cmd.ExecuteNonQuery()

        MessageBox.Show("Record deleted", "Deleted", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Showgrid()
        txtStudentId.Clear()
        txtName.Clear()
        cboDay.Text = ""
        cboMonth.Text = ""
        lblAge.Text = ""
        txtNationality.Clear()
        If radioMale.Checked = True Then
            Sex = ""
        End If
        cboStudentType.Text = ""
        cboHouse.Text = ""
        cboRoom.Text = ""
        txtGName.Clear()
        txtMPhone.Clear()
        txtHPhone.Clear()
        txtEmail.Clear()
        txtAddress.Clear()
        txtCity.Clear()
        cboRegion.Text = ""
        PictureBox1.Image = PictureBox1.ErrorImage
        txtStudentId.Focus()
    End If
End If

Abdusalam Ben Haj · Answer 1 · 2013-02-14T10:24:56.033

1

Why don't you try DELETE CASCADE. Its better than doing it manually in code.

By using cascading referential integrity constraints, you can define the actions that the SQL Server takes when a user tries to delete or update a key to which existing foreign keys point.

ON DELETE CASCADE Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.

As for the code you provided, the command should look like this :

cmd = New SqlCommand("Delete from StudentDetails.Registration where StudentId=" & Integer.Parse(txtStudentId.Text), cn)

Though you should be using parameterized queries to avoid Sql Injection :

cmd = New SqlCommand("Delete from StudentDetails.Registration where StudentId = @StudentId" , cn)
cmd.Parameters.AddWithValue("@StudentId", Integer.Parse(txtStudentId.Text))

edited Feb 14 '13 at 10:24

answered Feb 14 '13 at 10:15

Abdusalam Ben Haj

5,343
5
31
45

Thank you for your good suggestion on SQL injection, but there two main issues now. 1. I'd like you to show me how I can use the CASCADE methode in SQL Server and 2. How I do represent all the subjects by a variable or something as though all the subjects table are one table.Since it would not be appropriate to write something like ProgramDetails.EnglishLanguage, ProgramDetails.Mathematics, ProgramDetails.French, etc – Akaglo Feb 14 '13 at 10:44
@Akaglo the delete cascade is very simple, check [this tutorial](http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-%E2%80%93-how-to-cascade-updates-and-deletes-to-related-tables/). You just need to set the `ON DELETE CASCADE` for the StudentId reference in all the tables related. – Abdusalam Ben Haj Feb 14 '13 at 11:11
@Akaglo I didn't get the second point, if I understand you correctly, you don't need to specify the subject if they are all in the same table. – Abdusalam Ben Haj Feb 14 '13 at 11:13
@Akaglo also check [this answer](http://stackoverflow.com/a/6260736/799558) for cascade delete. – Abdusalam Ben Haj Feb 14 '13 at 11:16
The point is that every subject has a table, for example ProgramDetails.EnglishLanguage, ProgramDetails.Mathematics, etc. So, there should be a way to execute the delete command such that a related record can as well be deleted from any additional subject table that will subsequently be created in future when a new subject is introduced. – Akaglo Feb 14 '13 at 11:23
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/24509/discussion-between-akaglo-and-abzy) – Akaglo Feb 14 '13 at 11:24

Deleting a record from multiple tables

1 Answers1