4 Replies - 1105 Views - Last Post: 13 October 2011 - 04:14 PM Rate Topic: -----

#1 JeremyC  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 80
  • Joined: 12-September 10

Error when trying to save database changes

Posted 13 October 2011 - 01:34 PM

Hello,
I am working on a school project but I am getting an error when I attempt to save an altered dataset.

This is my code

Public Class KarateMembers
      Private mstrFilterStatus As String
      Private mstrDate As String
      Private Sub MembersBindingNavigatorSaveItem_Click(sender As System.Object, e As System.EventArgs)
            Me.Validate()
            Me.MembersBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.KarateDataSet)

      End Sub

      Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'KarateDataSet.Members' table. You can move, or remove it, as needed.
            Me.MembersTableAdapter.Fill(Me.KarateDataSet.Members)
            radBefore.Checked = True
      End Sub

      Private Sub dtpFilterDate_TextChanged(sender As Object, e As System.EventArgs) Handles dtpFilterDate.TextChanged
            mstrDate = dtpFilterDate.Text
            filterGrid()
      End Sub

      Private Sub filterGrid()
            Select Case mstrFilterStatus
                  Case "before"
                        MembersTableAdapter.JoinedBefore(KarateDataSet.Members, mstrDate)
                  Case "after"
                        MembersTableAdapter.JoinedAfter(KarateDataSet.Members, mstrDate)
                  Case Else
                        MembersTableAdapter.Fill(KarateDataSet.Members)
            End Select
      End Sub

      Private Sub radAfter_CheckedChanged(sender As Object, e As System.EventArgs) Handles radAfter.CheckedChanged, radBefore.CheckedChanged
            Dim radRadioSelection = CType(sender, RadioButton)
            If radRadioSelection.Name = "radBefore" Then
                  mstrFilterStatus = "before"
            Else
                  mstrFilterStatus = "after"
            End If
            filterGrid()
      End Sub

      Private Sub btnSearchMembers_Click(sender As System.Object, e As System.EventArgs) Handles btnSearchMembers.Click
            Dim frmNewSearch As New frmNameFilter
            frmNewSearch.ShowDialog()
      End Sub

      Private Sub mnuSave_Click(sender As Object, e As System.EventArgs) Handles mnuSave.Click
            MembersTableAdapter.Update(KarateDataSet.Members)
      End Sub
End Class



And this is the error that I'm getting:
The DELETE statement conflicted with the REFERENCE constraint "FK_Payments_Members". The conflict occurred in database "C:\USERS\JEREMY\PROGRAMMING\VB\FALL_2011\CLOUTIER_CH4_KARATEMEMBERS\CLOUTIER_CH4_KARATEMEMBERS\BIN\DEBUG\KARATE.MDF", table "dbo.Payments", column 'Member_Id'.
The statement has been terminated.


I have done some research and I believe that this error has to do with the primary key having a relationship with the payments table. Something that I read said that you need to enable cascading deletes but I can't figure out how or even if that is the correct thing to do.

Any thoughts?

This post has been edited by JeremyC: 13 October 2011 - 01:35 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Error when trying to save database changes

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8890
  • View blog
  • Posts: 33,338
  • Joined: 12-June 08

Re: Error when trying to save database changes

Posted 13 October 2011 - 01:37 PM

Try specifically deleting any rows that use that key first, then delete the primary row afterwards.
Was This Post Helpful? 1
  • +
  • -

#3 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1817
  • View blog
  • Posts: 4,625
  • Joined: 14-March 10

Re: Error when trying to save database changes

Posted 13 October 2011 - 01:45 PM

Correct, it is because your primary key is used as a foreign key in another table. This means you cant edit/delete the row unless first you delete any row in another table that uses that data.

And it dont give any logic to delete a data in one table that its value has been used in another table. That is why we always do database design before coding......

My advice:
- Make sure your relations are designed correctly between tables
- When trying to edit/delete a primary key in the table that has been referenced, make sure the value has not been used in another table, and if used means you have to remove the row used it before removing the primary row.

That is why we disable other info instead of deleting them when coming to relation database programming...
Was This Post Helpful? 1
  • +
  • -

#4 JeremyC  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 80
  • Joined: 12-September 10

Re: Error when trying to save database changes

Posted 13 October 2011 - 02:59 PM

I understand what your saying about not deleting and just disabling. However, in this case I don't have a choice. It's the terms as dictated by the assignment and the database is pre-made as part of the books course materials. I will work on finding other rows that use that key and deleting them, thank you guys.
Was This Post Helpful? 0
  • +
  • -

#5 JeremyC  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 80
  • Joined: 12-September 10

Re: Error when trying to save database changes

Posted 13 October 2011 - 04:14 PM

I enabled cascade delete and update options on the relationship but it is still giving me the same error. :/
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1