locked
Delete validation when child table doesn't contain rows RRS feed

  • Question

  • User-1352156089 posted

    Hi All,

    I have implemented a listview which implements the ItemCommand method.

    My delete statement looks like the below:

     Private Sub deleteChild(Id As Integer)
            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString)
            Dim cmdText As String = "DELETE FROM Countries WHERE CountryId=@CountryId"
            Dim cmd As New SqlCommand(cmdText, con)
            cmd.Parameters.AddWithValue("@CountryId", Id)
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            cmd.ExecuteNonQuery()
            con.Close()
            PopulateChildListView()
            plhsuccesschild.Visible = True
            ltlsuccesschild.Text = "A Picture has been succesfully deleted"
            btnUpdate.Visible = False
            btnAdd.Visible = True
            btnCancel.Visible = False
        End Sub

    I would like to add a validation which tells the user that the Country column cannot be deleted because it's references as a foreign key in the City table.

    At present, the error is throwned in an unfriendly way but I would like to make it friendly.

    How would you suggest to accomplish this?

    Thanks

     

    Thursday, May 28, 2015 12:53 PM

Answers

  • User61956409 posted

    Hi claudio7810,

    Thanks for your post.

    You could try to use the Try/Catch block to catch exceptions, and you could alert some message in a catch block.

            Try
                'your code
            Catch ex As Exception
                ClientScript.RegisterStartupScript(Me.[GetType](), "alert", "alert('the Country column cannot be deleted because its references as a foreign key in the City table!')", True)
            End Try
    

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 29, 2015 1:09 AM
  • User-1352156089 posted

    Hi Fei Han,

    I was thinking to something like:

      Private Sub deleteChild(Id As Integer)
            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString)
            Dim cmdText As New SqlCommand("SELECT CountryId FROM Cities WHERE CountryId=@CountryId", con)
            cmdText.Parameters.AddWithValue("@CityId", Id)
            Dim ad As New SqlDataAdapter(cmdText)
            Dim dt As New DataTable()
            ad.Fill(dt)
            If dt.Rows.Count > 0 Then
                plhwarningchild.Visible = True
                ltlwarningchild.Text = "This country cannot be deleted because it has been populated with cities"
            Else
    
                Dim cmd As New SqlCommand("DELETE FROM Countries WHERE CountryId=@CountryId", con)
                cmd.Parameters.AddWithValue("@CountryId", Id)
                If con.State = ConnectionState.Closed Then
                    con.Open()
                End If
                cmd.ExecuteNonQuery()
                PopulateChildListView()
                plhsuccesschild.Visible = True
                ltlsuccesschild.Text = "Country succesfully deleted"
                btnUpdate.Visible = False
                btnAdd.Visible = True
                btnCancel.Visible = False
                clearmyInputControls()
            End If
            con.Close()
        End Sub

    Thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 29, 2015 8:38 AM

All replies

  • User61956409 posted

    Hi claudio7810,

    Thanks for your post.

    You could try to use the Try/Catch block to catch exceptions, and you could alert some message in a catch block.

            Try
                'your code
            Catch ex As Exception
                ClientScript.RegisterStartupScript(Me.[GetType](), "alert", "alert('the Country column cannot be deleted because its references as a foreign key in the City table!')", True)
            End Try
    

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 29, 2015 1:09 AM
  • User-1352156089 posted

    Hi Fei Han,

    I was thinking to something like:

      Private Sub deleteChild(Id As Integer)
            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString)
            Dim cmdText As New SqlCommand("SELECT CountryId FROM Cities WHERE CountryId=@CountryId", con)
            cmdText.Parameters.AddWithValue("@CityId", Id)
            Dim ad As New SqlDataAdapter(cmdText)
            Dim dt As New DataTable()
            ad.Fill(dt)
            If dt.Rows.Count > 0 Then
                plhwarningchild.Visible = True
                ltlwarningchild.Text = "This country cannot be deleted because it has been populated with cities"
            Else
    
                Dim cmd As New SqlCommand("DELETE FROM Countries WHERE CountryId=@CountryId", con)
                cmd.Parameters.AddWithValue("@CountryId", Id)
                If con.State = ConnectionState.Closed Then
                    con.Open()
                End If
                cmd.ExecuteNonQuery()
                PopulateChildListView()
                plhsuccesschild.Visible = True
                ltlsuccesschild.Text = "Country succesfully deleted"
                btnUpdate.Visible = False
                btnAdd.Visible = True
                btnCancel.Visible = False
                clearmyInputControls()
            End If
            con.Close()
        End Sub

    Thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 29, 2015 8:38 AM