Question The record cannot be deleted or changed because table includes related records

[ldles3]

I'm having this message when I try to edit or delete records in a database that have relational tables:
"The record cannot be deleted or changed because table 'documentos' includes related records. This is my code:

Validate()
Me.MiembrosBindingSource.EndEdit()
Me.DocumentosBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.RegmembersDataSet)
MsgBox("Participante actualizado")
cleartextboxes()

The database in in Access 2007 and I'm using VB 2008. The table "miembros" and the table "documentos" are related by the member_id. I'm new at this. I'm learning while I'm coding. I know the basics of VB, but this is the first time I'm working with databases with VB.

 

[ldles3]

I go to the walkthroughs: Saving Data from Related Data Tables (Hierarchical Update), and insert the same instructions. I verify the Hierarchical Update is in True. the parent table is 'miembros' and the child is 'documentos'. I don't know what I'm doing wrong. Do I need to insert more instructions?

 

[jmcilhinney]

I'm using Access 2013 but I believe that it should be the same. When you create or edit a relationship between two tables, you need to check the three boxes described below:

'Enforce Referential Integrity': adds a foreign key constraint to the relation, meaning that only values that exist in the parent table can exist in the child table
'Cascade Update Related Fields': means that the value in the child table will be updated whenever the value in the parent table is updated, which should never actually be used in the database but is important in your application
'Cascade Delete Related Records': means that a child record will be deleted automatically when a related parent record is deleted

There is a bit of interplay between these setting is the database and the corresponding settings in the DataSet in your application, most notably with respect to deletes. I think that the TableAdapterManager should be able to handle that but maybe not.

Consider the case where you have deletes set to cascade in both the DataSet and the database. If you delete a parent record in the DataSet then that will automatically delete the related child records in the DataSet too. When you save the changes, if you save the parent changes first then the database will also automatically delete the related child records so, when you come to save the child changes, the records you want to delete do not exist. If you save the child changes first then, when you save the parent changes, there are no related child records so cascading deletes in the database is pointless. It doesn't hurt though and it will cause the same properties to be used in the DataSet.

So, the first thing you should do is check whether those boxes are check in the database. If they're not, check them. If you do make any changes to the database then you should regenerate the DataSet. You can do that in the Data Sources window by clicking the button to re-run the configuration wizard but I'm not sure whether it will pick up those changes or not. I probably suggest deleting the DataSet altogether in the Solution Explorer and then generating a new one. As long as all the names are the same, any existing code that uses the DataSet will continue to work.

 

[ldles3]

Ok. I verify the checkboxes in the database, the cascade update related fields and the cascade delete related fields weren't mark. I changed it and save it. And I generate a new dataset. When I try the program, it saves the changes or delete the record, but now I have a new error:



Also I verify the database and every time I save or delete a record, it erase me the first record I have in the database. Do I have to eliminate the instruction of endedit of the child? or is something else?

 

[jmcilhinney]

Ok. I verify the checkboxes in the database, the cascade update related fields and the cascade delete related fields weren't mark. I changed it and save it. And I generate a new dataset. When I try the program, it saves the changes or delete the record, but now I have a new error:

I think that the error message is fairly clear. Did you read it? It says that the values in the 'nummember' column have to be unique and you are trying to save a value (an empty string) that already exists. First things first, why is that column constrained to be unique? Is it the primary key or did you add a unique index to it? You need to determine whether it makes sense that that column be unique to begin with and, if it does, then you need to make sure that you do indeed save unique values to it. It really doens't make sense to be saving an empty string to a unique column.

Also I verify the database and every time I save or delete a record, it erase me the first record I have in the database. Do I have to eliminate the instruction of endedit of the child? or is something else?

Follow the first link in my signature to learn how to manage local data files. You simply need to change one property value to solve that problem.

 

[ldles3]

I read the message, I understand what it means, but the 'nummember' column is the PK of the table. I have a combobox for the search of the record, I'm not using the PK for that, because that is a number, I'm searching through the name.



When I make a change, it saves me the changes, but when I press the combobox, it erases the record I edit. This is my code:

Private Sub participantes_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         'TODO: This line of code loads data into the  'RegmembersDataSet.documentos' table. You can move, or remove it, as  needed.
        Me.MiembrosTableAdapter.Fill(Me.RegmembersDataSet.miembros)
        Me.DocumentosTableAdapter.Fill(Me.RegmembersDataSet.documentos)

    End Sub


    Private Sub btnMP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMP.Click

        frmmenu.Show()
        Me.Close()

    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        Dim findpart = Me.MiembrosTableAdapter.MiembrosQuery(Me.NummemberTextBox.Text)

        If findpart Is Nothing Then

             Me.MiembrosTableAdapter.Insert(Me.NummemberTextBox.Text,  Me.NombreTextBox.Text, Me.Direccion1TextBox.Text,  Me.Direccion2TextBox.Text, Me.PuebloTextBox.Text,  Me.ZipcodeTextBox.Text, Me.TelefonoTextBox.Text, Me.CelularTextBox.Text,  Me.EmailTextBox.Text, Me.FechanacDateTimePicker.Value,  Me.FechaentDateTimePicker.Value, Me.NivelTextBox.Text,  Me.ActivoCheckBox.CheckState)
             Me.DocumentosTableAdapter.Insert(Me.NummemberTextBox.Text,  Me.HistorialmedicoCheckBox.CheckState, Me.FechahmDateTimePicker.Value,  Me.CertmedicoCheckBox.CheckState, Me.FechacmDateTimePicker.Value,  Me.CertvacunasCheckBox.CheckState, Me.FechacvDateTimePicker.Value,  Me.PermisopadresCheckBox.CheckState, Me.FechappDateTimePicker.Value,  Me.LeyhippaCheckBox.CheckState, Me.FechalhDateTimePicker.Value)
            Me.MiembrosTableAdapter.Fill(RegmembersDataSet.miembros)
            Me.DocumentosTableAdapter.Fill(RegmembersDataSet.documentos)
            MsgBox("Participante A?adido")

        Else

            MsgBox("Este participante ya existe")

        End If

    End Sub

    Sub cleartextboxes()

        NummemberTextBox.Text = ""
        NombreTextBox.Text = ""
        Direccion1TextBox.Text = ""
        Direccion2TextBox.Text = ""
        PuebloTextBox.Text = ""
        ZipcodeTextBox.Text = ""
        TelefonoTextBox.Text = ""
        CelularTextBox.Text = ""
        NivelTextBox.Text = ""
        HistorialmedicoCheckBox.Checked = False
        CertmedicoCheckBox.Checked = False
        CertvacunasCheckBox.Checked = False
        PermisopadresCheckBox.Checked = False
        LeyhippaCheckBox.Checked = False



    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click

        Me.Validate()
        Me.MiembrosBindingSource.EndEdit()
        Me.DocumentosBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.RegmembersDataSet)

        MsgBox("Participante actualizado")

    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

        MsgBox("Deseas eliminar este participante?", MsgBoxStyle.YesNo)

        If MsgBoxResult.Yes Then
            Me.MiembrosBindingSource.RemoveCurrent()
            Me.DocumentosBindingSource.RemoveCurrent()
            Me.MiembrosBindingSource.EndEdit()
            Me.DocumentosBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.RegmembersDataSet)
            MsgBox("Participante Eliminado")

        ElseIf MsgBoxResult.No Then
            MsgBox("Ok")

        End If

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        cleartextboxes()

    End Sub

End Class

I check your link. When I insert the database the first time I say yes to copy the database to the project, and the setting was in Copy Always. Then I change it to Copy if newer.
I read the link you told me, and I erase the database connection and I create a new one with the same name, but when the Dialog appear for saving the database in the project, I answer NO. So, the database stays out of the project. And I change the setting to Do not copy.


and the problem continues.







The problem is only when I edit. If I add or delete, I don't.

 

[jmcilhinney]

If 'nummember' the PK then why are you trying to save an empty string to that column and why does it already contain an empty string?

 

[ldles3]

I'm not trying to save an empty string. I'm clearing all the boxes to add a new record. I decided to change the way I add records. I create a new form to add records, and the problem was solved. Thank you very much for your help. I learn a lot with the links.

 

[Herman]

Personally I would highly recommend you NOT enable automatic cascade deletion. It is potentially a recipe for disaster, where relationships you had not anticipated or thought through will delete important data. Instead write your delete queries manually to delete only explicitly stated rows. Much safer... Deletion should really not happen all that often, the only deletions that happen on two production databases I designed and maintain are monthly clean-ups.

 

[ldles3]

I would check that option. Thanks a lot!