none
Data Set error when using tableadapter.fill "One or more rows contain values violating non-null, unique, or foreign-key constraints" RRS feed

  • Question

  • I am getting the above error when I run the code:

    proj_tableAdapter.fillby3(DatasetAB._projectInit_)

    This is then being used to populate a datagrid.

    Yet when I run the code from in the "preview Data" that is provided for the dataset from the VB.NET windows form, the Proj_tableAdapter it reports NO errors!

    I would think I would either get errors in both places or in none.   Can some one explain why the results are different?

    There are no duplicate numbers.  

    One of the tables has a group of rows that are totally null, with no values in any field.   I am not sure how they got there.  When I go into SQL and try to manually remove those rows it gives an error message, "no row was updated"   "The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(123).

    Does anyone have an easy way to remove those blank rows?   Maybe the blank rows are causing the error?

    The call going to the database is:

    Select I.JobNo, I.ClientCompany, I.ClientStreet, I.ClientCity, I.ClientState, I.ClientZip, I.ClientOfficePhone, I.ClientExtension, I.ClientFax, I.ClientSalutation, I.ClientFirstName, I.ClientLastName,
                             I.ClientCellPhone, I.ClientEmail,
                             C.POnum, C.ProjectTotal,
                             N.EPAstartDate, N.EPAendDate, N.DOLNotif#, N.DOLvar#, N.NYCdepSTARTd, N.NYCdepENDd, N.DEPVar#,  N.NYCtoProcessorD
    FROM ProjInit$ I
    JOIN Contractinit$ C
    ON C.JobNo=I.JobNo
    JOIN Notifica$ N
    ON N.JobNo=I.JobNo
    ORDER BY I.JobNo

    As mentioned above it works great from the DataSet designer but when called from code the error message arrives!

    There is only one key that is being used.   That one key has a one to one relationship in all tables of the database.   There are not duplicate uses of the foreign key.  

    Wednesday, April 18, 2018 8:15 PM

Answers

  • After playing around with this some more, I found that in the dataset designer it apparently just ignores the problem records.

    For populating the dataset, I found the problem went away if I do not stop execution when the error message shows up.

    So when my try catch    end try   has a catch, I just let the program continue.    I am not happy with that fix, but it works.

    I put a counter in to see how often the issue is happening and it came up with a count of 1 record.  

    Thank you for the suggestions that were offered.

                      

    • Marked as answer by AWIcurrent Wednesday, April 18, 2018 9:11 PM
    Wednesday, April 18, 2018 9:11 PM

All replies

  • See if the below link helps troubleshoot the exception:

    https://stackoverflow.com/questions/7026566/failed-to-enable-constraints-one-or-more-rows-contain-values-violating-non-null


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 18, 2018 8:26 PM
  • I would wrap the highlighted in [] which may or may not solve the issue but worth a try as # and $ may be an issue.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, April 18, 2018 8:56 PM
    Moderator
  • After playing around with this some more, I found that in the dataset designer it apparently just ignores the problem records.

    For populating the dataset, I found the problem went away if I do not stop execution when the error message shows up.

    So when my try catch    end try   has a catch, I just let the program continue.    I am not happy with that fix, but it works.

    I put a counter in to see how often the issue is happening and it came up with a count of 1 record.  

    Thank you for the suggestions that were offered.

                      

    • Marked as answer by AWIcurrent Wednesday, April 18, 2018 9:11 PM
    Wednesday, April 18, 2018 9:11 PM
  • After playing around with this some more, I found that in the dataset designer it apparently just ignores the problem records.

    For populating the dataset, I found the problem went away if I do not stop execution when the error message shows up.

    So when my try catch    end try   has a catch, I just let the program continue.    I am not happy with that fix, but it works.

    I put a counter in to see how often the issue is happening and it came up with a count of 1 record.  

    Thank you for the suggestions that were offered.

                      

    You should get rid of the try-catch and try DataSetAB.EnforceConstraints = False

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, April 18, 2018 9:37 PM
    Moderator