Results 1 to 11 of 11

Thread: Typed Dataset Failed to enable constraints...

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    437

    Typed Dataset Failed to enable constraints...

    Hello:

    I am getting this error:
    Code:
    System.Data.ConstraintException: 'Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.'
    On the following (red line is the error):

    Code:
       
        Public DS As New PurchasingRequestDataSet 
    
        Private Sub Updatedb_Request()
            Dim Status As String = CheckStatus()
    
            Dim RequestID As Integer = CInt(txtID.Text)
    
            Dim ta_MinRequestID As PurchasingRequestDataSetTableAdapters.PartsTableAdapter = New PurchasingRequestDataSetTableAdapters.PartsTableAdapter
            ta_MinRequestID.FillBy_MinRequestID(DS.Parts, RequestID)
    
            Dim MinDate As Date = ds.Parts.Rows(0)(1)
    
            Dim ta_Request As PurchasingRequestDataSetTableAdapters.RequestTableAdapter = New PurchasingRequestDataSetTableAdapters.RequestTableAdapter
            ta_Request.UpdateQuery(cboRequestor.Text.Trim, datDate1.Value, cboPriority.Text.Trim, MinDate, datExpectedDate.Value, "", Status, CInt(txtID.Text))
    
        End Sub

    I feel like I have done this exact same logic several times. The only difference is I am looking at the minimum date ans passing that information through the variable MinDate.

    The actual query works great!

    Name:  2021-01-08 10_19_13-Window.jpg
Views: 80
Size:  33.6 KB

    What am I missing here?

    Thank you!
    Last edited by ssabc; Jan 8th, 2021 at 11:28 AM.
    -SA

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,310

    Re: Typed Dataset Failed to enable constraints...

    The error message is fairly clear:
    One or more rows contain values violating non-null, unique, or foreign-key constraints.
    Which one of the three is it? Unless you have changed the table schema since you created the DataSet, it seems unlikely to be the first. Unless you are populating the same DataTable twice without clearing it in between then it's unlikely to be the second. That leaves the third. Are there any foreign keys in your data? Is RequestID perhaps a foreign key? Have you populated the parent DataTable in that relation within your DataSet before populating the child DataTable? If not, it seems pretty clear which type of constraint your violating. You can't have a value in a foreign key column of a child table without having that same value in the related column of the parent table.

    If you want to be able to work with child data without the corresponding parent data then don't use the full DataSet. Instead of using a Fill method of a table adapter to populate an existing DataTable, use a GetData method to return a new DataTable that is not part of a DataSet. No DataSet means no constraints. If you really need the DataSet for some reason then you can set its EnforceConstraints property to False, but be aware that that will ignore all constraints, not just the one you want to ignore.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    437

    Re: Typed Dataset Failed to enable constraints...

    Thanks for the explanation.

    I populate the parent data table with todays date.

    Then parts are added with various required dates. I am then looking at the earliest date to repopulate the date field in the parent data table.

    Parent table is Request
    Child Table is Parts

    The confusion is that this is not occurring when the data is being populated, but when filling the table adapter.
    -SA

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,217

    Re: Typed Dataset Failed to enable constraints...

    The confusion is that this is not occurring when the data is being populated, but when filling the table adapter.
    What do you think filling the adaptor is doing? Populating the data... so... yeah... that is when the error is happening. When it's populating the data into your DataTable... There is a constraint in your datatable... and when you fill it from your data adaptor, you're violating that constraint somehow... whether you realize it or not... and that's what the error is trying to tell you.
    Personally, that's not how I'd build the query but, then again, I wouldn't be using a typed dataset like that either, I'd be rolling my own built SQL and calling the query directly, but that's just my own personal choice.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,310

    Re: Typed Dataset Failed to enable constraints...

    Quote Originally Posted by ssabc View Post
    The confusion is that this is not occurring when the data is being populated, but when filling the table adapter.
    That statement could not be more wrong. There's no such thing as "filling a table adapter". A table adapter doesn't contain any data. When you call a Fill the table adapter opens a connection to the database, executes a query, reads the result set of the query and populates a DataTable with that data. It IS occurring when the data is being populated because that's EXACTLY what a Fill method does. The question you need to ask yourself is very simple: does DS.Request contain a record with a PK value equal to that RequestID variable? I have to hope that the answer is "no", otherwise the fact that you are using a DB Direct method to update a Request record in the database makes no sense at all. If your DataSet does contain that record then why aren't you updating that record in the DataSet? If your DataSet doesn't contain that record, why are you telling me that it does?

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    437

    Re: Typed Dataset Failed to enable constraints...

    I have changed the logic a little. I am only trying to read the data, and then update that data back.

    Code:
        Private Sub Updatedb_Request()
            Dim Status As String = CheckStatus()
    
            Dim ta_Request As PurchasingRequestDataSetTableAdapters.RequestTableAdapter = New PurchasingRequestDataSetTableAdapters.RequestTableAdapter
            ta_Request.UpdateQuery(cboRequestor.Text.Trim, datDate1.Value, cboPriority.Text.Trim, datRequiredDate.Value, datExpectedDate.Value, "", Status, CInt(txtID.Text))
    
            Dim RequestID As Integer = CInt(txtID.Text)
            Dim ta_MinRequestID As PurchasingRequestDataSetTableAdapters.PartsTableAdapter = New PurchasingRequestDataSetTableAdapters.PartsTableAdapter
            ta_MinRequestID.GetDataBy10_MinRequestID(RequestID)
    
            Dim MinDate As Date = DS.Parts.Rows(0)(1)
    
            Dim ta_Request_UpdateQuery_RequiredDateOnly As PurchasingRequestDataSetTableAdapters.RequestTableAdapter = New PurchasingRequestDataSetTableAdapters.RequestTableAdapter
            ta_Request_UpdateQuery_RequiredDateOnly.UpdateQuery1_RequiredDateOnly(MinDate, CInt(txtID.Text))
    
        End Sub
    I need to be able to update the data based on the correct record number, which is RequestID...
    -SA

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,310

    Re: Typed Dataset Failed to enable constraints...

    I said earlier:
    use a GetData method to return a new DataTable that is not part of a DataSet
    This is your code:
    vb.net Code:
    1. ta_MinRequestID.GetDataBy10_MinRequestID(RequestID)
    Do you see anything missing there? What do you usually do when a method returns a value?

    Apart from that, why would you update the same record twice? No one suggested doing that. The one and only issue was that you were using a DataSet that didn't contain the parent data for the child data you were retrieving. That was the one and only thing you had to change.

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    437

    Re: Typed Dataset Failed to enable constraints...

    I'm sorry, I find all of this quite confusing.

    This works great!!!

    Code:
        Private Sub Updatedb_Request()
            Dim Status As String = CheckStatus()
    
            Dim ta_Request As PurchasingRequestDataSetTableAdapters.RequestTableAdapter = New PurchasingRequestDataSetTableAdapters.RequestTableAdapter
            ta_Request.UpdateQuery(cboRequestor.Text.Trim, datDate1.Value, cboPriority.Text.Trim, datRequiredDate.Value, datExpectedDate.Value, "", Status, CInt(txtID.Text))
    
        End Sub
    I could just do an ADODB Recordset to read the parts table to get the minimum date.
    -SA

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,310

    Re: Typed Dataset Failed to enable constraints...

    It's not confusing if you actually think about what I'm saying rather than trying to create some other problem. I said to call a GetData method instead of a Fill method to return a new DataTable instead of populating an existing DataTable. You made a bunch of changes I didn't suggest and when you called the GetData method you just threw away the DataTable it returned. You obviously know how to assign a return value to a variable because you're doing it in the code you just posted:
    vb.net Code:
    1. Dim Status As String = CheckStatus()
    If you can call that method and use the return value, why can't you use the return value of the GetData method you're calling?

    Here's your original code:
    Code:
        Public DS As New PurchasingRequestDataSet 
    
        Private Sub Updatedb_Request()
            Dim Status As String = CheckStatus()
    
            Dim RequestID As Integer = CInt(txtID.Text)
    
            Dim ta_MinRequestID As PurchasingRequestDataSetTableAdapters.PartsTableAdapter = New PurchasingRequestDataSetTableAdapters.PartsTableAdapter
            ta_MinRequestID.FillBy_MinRequestID(DS.Parts, RequestID)
    
            Dim MinDate As Date = ds.Parts.Rows(0)(1)
    
            Dim ta_Request As PurchasingRequestDataSetTableAdapters.RequestTableAdapter = New PurchasingRequestDataSetTableAdapters.RequestTableAdapter
            ta_Request.UpdateQuery(cboRequestor.Text.Trim, datDate1.Value, cboPriority.Text.Trim, MinDate, datExpectedDate.Value, "", Status, CInt(txtID.Text))
    
        End Sub
    All you have to do is use the DataTable returned by the GetData method instead of calling the Fill method to populate an existing DataTable:
    Code:
        Public DS As New PurchasingRequestDataSet 
    
        Private Sub Updatedb_Request()
            Dim Status As String = CheckStatus()
    
            Dim RequestID As Integer = CInt(txtID.Text)
    
            Dim ta_MinRequestID As PurchasingRequestDataSetTableAdapters.PartsTableAdapter = New PurchasingRequestDataSetTableAdapters.PartsTableAdapter
            Dim table = ta_MinRequestID.GetDataBy_MinRequestID(RequestID)
    
            Dim MinDate As Date = table.Rows(0)(1)
    
            Dim ta_Request As PurchasingRequestDataSetTableAdapters.RequestTableAdapter = New PurchasingRequestDataSetTableAdapters.RequestTableAdapter
            ta_Request.UpdateQuery(cboRequestor.Text.Trim, datDate1.Value, cboPriority.Text.Trim, MinDate, datExpectedDate.Value, "", Status, CInt(txtID.Text))
    
        End Sub

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    437

    Re: Typed Dataset Failed to enable constraints...

    Thank you!
    -SA

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,643

    Re: Typed Dataset Failed to enable constraints...

    Back to your original post,

    Code:
            Dim ta_MinRequestID As PurchasingRequestDataSetTableAdapters.PartsTableAdapter = New PurchasingRequestDataSetTableAdapters.PartsTableAdapter
            ta_MinRequestID.FillBy_MinRequestID(DS.Parts, RequestID)
    If you have a Typed DataSet, you do, and a relation setup between two datatables that has constraints setup, then you most fill the parent first and then the child. This is usually done in the form load event and all add/update/deletes are done using those two TableAdapters. Then send the updates back to the database using their Update method or the TableAdapterManager.UpdateAll method.
    Last edited by wes4dbt; Jan 9th, 2021 at 12:11 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width