CODEWITHSUNDEEP
sql-servervb.netwinformst-sqlado.net
Akaglo
Akaglo

Reputation: 127

Deleting a record from multiple tables

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

Upvotes: 0

Views: 185

Answers (1)

Abdusalam Ben Haj
Abdusalam Ben Haj

Reputation: 5423

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))

Upvotes: 1

Related Questions