Link to home
Start Free TrialLog in
Avatar of newbie27
newbie27Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

Hello Experts,

I am doing a query to the DB to return last week web orders and produce a CSV file for the reporting.

All I have done now, is created a View using the query below and defined a dataset (XSD) to pull these records...

When I have records with more than one orderID its giving me an error in the ASP.net script

error:

System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.


please can someone advise how to bypass this exception in ASP.NET?

thanks
s
Avatar of newbie27
newbie27
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

dt = WeeklyWebReporsBLL.GetWeeklyGRDropshipOrders(Feedback) ==> error on this line
 
WeeklyWebReporsBLL.vb
==========================
 
 Public Shared Function GetWeeklyGRDropshipOrders(Optional ByRef Feedback As String = "") As DataTable
        Dim dt As New DataTable
        Try
            dt = WeeklyWebReportsDAL.GetWeeklyGRDropshipOrders
        Catch ex As Exception
            Feedback = ex.ToString
        End Try
 
        Return dt
    End Function  
 
WeeklyWebReportsDAL.vb
======================
 
 Friend Shared Function GetWeeklyGRDropshipOrders() As dsWeekWebReports.WeeklyGRDropshipOrdersDataTable
        Dim Adapter As New dsWeekWebReportsTableAdapters.WeeklyGRDropshipOrdersTableAdapter
        Return Adapter.GetWeeklyGRDropshipOrders()
    End Function


viewWeeklyReport
================

SELECT     TOP (100) PERCENT dbo.DropShip.DropShipOrderID, dbo.DropShip.DropShipRetailerOrderID, dbo.Retailer.RetailerName,
                      dbo.DropShipDetail.DropShipGiftName, dbo.DropShipDetail.DropShipQty, DATEPART(day, dbo.DropShipHistory.DropShipHistDateAdded) AS day,
                      DATEPART(month, dbo.DropShipHistory.DropShipHistDateAdded) AS month, DATEPART(year, dbo.DropShipHistory.DropShipHistDateAdded) AS year,
                      dbo.[User].UserName, dbo.Category.CatDesc
FROM         dbo.DropShip INNER JOIN
                      dbo.DropShipDetail ON dbo.DropShip.DropShipOrderID = dbo.DropShipDetail.DropShipOrderID INNER JOIN
                      dbo.Retailer ON dbo.DropShip.RetailerID = dbo.Retailer.RetailerID INNER JOIN
                      dbo.DropShipHistory ON dbo.DropShipDetail.DropShipOrderID = dbo.DropShipHistory.DropShipOrderID INNER JOIN
                      dbo.[User] ON dbo.DropShipHistory.UserID = dbo.[User].UserID INNER JOIN
                      dbo.ProductCategory ON dbo.DropShipDetail.GiftProductCodeID = dbo.ProductCategory.ProdID INNER JOIN
                      dbo.Category ON dbo.ProductCategory.CatID = dbo.Category.CatID
WHERE     (dbo.DropShipHistory.StatusID = 'ORDER_CREATED') AND (dbo.DropShipHistory.DropShipHistDateAdded >= DATEADD(dd, - 7,
                      CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME))) AND (dbo.DropShipHistory.DropShipHistDateAdded <= GETDATE() - 1)
ORDER BY day, month, year
check the WeeklyGRDropshipOrdersTableAdapter for any contraints defined on the columns. the adapter is receiving duplicate values or null valuse for a required field
SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks both for your input ...

i thought earlier the same that i might have duplicate values for some reason but comment on this has explained it why they are not duplicate rows....

please follow this

https://www.experts-exchange.com/questions/24243860/sql-syntax.html

thanks
Youuuuu rannngggg?
hello TheLearnedOne,

thanks for coming .... please can you advise on this problem?

the problem is the duplicate orderids i am getting as a result of the SQL statement which i am using to return weekly web orders .... but dataset is not liking it ....

please advise..

thanks
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Arthur_Wood,

Adding just the distinct did not help me either ....


the result i am getting is
dropshiporderid      retailerid      retailername      giftname            quantiy      day      month      year      .........
43640            6179      TestName            Tea & Coffee      1      11      3      2009      
43640            6179      TestName            Wild Flowers      1      11      3      2009      

even if i use DISTINCT its still giving me duplicate records....


 
Dropship
=========
 
dropshiporderid       retailerid                customername    
4               17216571          Julieanne      
43640           6179              david  
 
 
DropShipDetail
===========
dropshiporderid       rowid        quantity    productname
43640           1       1        Tea & Coffee    
43640           2       1        Wild Flowers   
 
 
dropshiphistory
=============
dropshiporderid       rowid         dropshiphistdateadded       statusid
43640           1       11/03/2009 13:31:05     ORDER_CREATED                    
43640           2       11/03/2009 14:10:06     ORDER_SENT                       
43640           3       11/03/2009 14:10:07     NOTES_ADDED                      
43640           4       13/03/2009 02:10:03     ORDER_INVOICED                   
43640           5       13/03/2009 02:10:03     ORDER_INVOICED    

Open in new window

Are you getting an unexpected duplicate from not including a join condition?
the result i am getting is
dropshiporderid      retailerid      retailername      giftname            quantiy      day      month      year      .........
43640            6179      TestName            Tea & Coffee      1      11      3      2009      
43640            6179      TestName            Wild Flowers      1      11      3      2009      


are NOT duplicate recods.  They differ in the giftname field, thus they are two separate and distinct records.  The fact theat they have the same values in the first three fields is irrelevant.  They are different records (the difference is determined based on ALL of the fileds, not just the first field, or the first three fields, butt all of the fields, considered together.)

AW
thanks again for your comment...

how can i make the dataset to understand that this is not a duplicate record please?

i am unable to return the same result from the dataset(XSD)

please advise
thanks
You can disable the constraint checking with DataSet.EnforceConstraints = False.
hello again,

 Public Shared Function GetWeeklyGRDropshipOrders(Optional ByRef Feedback As String = "") As DataTable
        Dim dt As New DataTable
        Try
            dt = WeeklyWebReportsDAL.GetWeeklyGRDropshipOrders
        Catch ex As Exception
            Feedback = ex.ToString
        End Try

        Return dt
    End Function


 Friend Shared Function GetWeeklyGRDropshipOrders() As dsWeekWebReports.WeeklyGRDropshipOrdersDataTable
        Dim Adapter As New dsWeekWebReportsTableAdapters.WeeklyGRDropshipOrdersTableAdapter
        Return Adapter.GetWeeklyGRDropshipOrders()
    End Function


I have created a view for the above SQL statement and please see the auto generated code for it

 <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _
         Global.System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter"),  _
         Global.System.ComponentModel.DataObjectMethodAttribute(Global.System.ComponentModel.DataObjectMethodType.[Select], true)>  _
        Public Overloads Overridable Function GetWeeklyGRDropshipOrders() As dsWeekWebReports.WeeklyGRDropshipOrdersDataTable
            Me.Adapter.SelectCommand = Me.CommandCollection(0)
            Dim dataTable As dsWeekWebReports.WeeklyGRDropshipOrdersDataTable = New dsWeekWebReports.WeeklyGRDropshipOrdersDataTable
            Me.Adapter.Fill(dataTable)
            Return dataTable
        End Function


this is how i am calling it

 Dim dt As DataTable      
 dt = WeeklyWebReporsBLL.GetWeeklyGRDropshipOrders(Feedback)
 For Each Row As DataRow In dt.Rows
  ..
 Next

PLEASE CAN YOU ADVISE WHERE SHOULD I ADD YOUR CHANGE?

THANKS
It looks like you have typed DataSet, but I can't pick it out in all that code.
TheLearnedOne:

how would i know if i have a typed Dataset ? please can you advise .....

 
please see attached all i can extract from the auto generated code for the adapter...
'''<summary>
    '''Represents the connection and commands used to retrieve and save data.
    '''</summary>
    <Global.System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "2.0.0.0"),  _
     Global.System.ComponentModel.DesignerCategoryAttribute("code"),  _
     Global.System.ComponentModel.ToolboxItem(true),  _
     Global.System.ComponentModel.DataObjectAttribute(true),  _
     Global.System.ComponentModel.DesignerAttribute("Microsoft.VSDesigner.DataSource.Design.TableAdapterDesigner, Microsoft.VSDesigner"& _ 
        ", Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"),  _
     Global.System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")>  _
    Partial Public Class WeeklyGRDropshipOrdersTableAdapter
        Inherits Global.System.ComponentModel.Component
        
        Private WithEvents _adapter As Global.System.Data.SqlClient.SqlDataAdapter
        
        Private _connection As Global.System.Data.SqlClient.SqlConnection
        
        Private _commandCollection() As Global.System.Data.SqlClient.SqlCommand
        
        Private _clearBeforeFill As Boolean
        
        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Public Sub New()
            MyBase.New
            Me.ClearBeforeFill = true
        End Sub
        
        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Private ReadOnly Property Adapter() As Global.System.Data.SqlClient.SqlDataAdapter
            Get
                If (Me._adapter Is Nothing) Then
                    Me.InitAdapter
                End If
                Return Me._adapter
            End Get
        End Property
        
        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Friend Property Connection() As Global.System.Data.SqlClient.SqlConnection
            Get
                If (Me._connection Is Nothing) Then
                    Me.InitConnection
                End If
                Return Me._connection
            End Get
            Set
                Me._connection = value
                If (Not (Me.Adapter.InsertCommand) Is Nothing) Then
                    Me.Adapter.InsertCommand.Connection = value
                End If
                If (Not (Me.Adapter.DeleteCommand) Is Nothing) Then
                    Me.Adapter.DeleteCommand.Connection = value
                End If
                If (Not (Me.Adapter.UpdateCommand) Is Nothing) Then
                    Me.Adapter.UpdateCommand.Connection = value
                End If
                Dim i As Integer = 0
                Do While (i < Me.CommandCollection.Length)
                    If (Not (Me.CommandCollection(i)) Is Nothing) Then
                        CType(Me.CommandCollection(i),Global.System.Data.SqlClient.SqlCommand).Connection = value
                    End If
                    i = (i + 1)
                Loop
            End Set
        End Property
        
        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Protected ReadOnly Property CommandCollection() As Global.System.Data.SqlClient.SqlCommand()
            Get
                If (Me._commandCollection Is Nothing) Then
                    Me.InitCommandCollection
                End If
                Return Me._commandCollection
            End Get
        End Property
        
        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Public Property ClearBeforeFill() As Boolean
            Get
                Return Me._clearBeforeFill
            End Get
            Set
                Me._clearBeforeFill = value
            End Set
        End Property
        
        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Private Sub InitAdapter()
            Me._adapter = New Global.System.Data.SqlClient.SqlDataAdapter
            Dim tableMapping As Global.System.Data.Common.DataTableMapping = New Global.System.Data.Common.DataTableMapping
            tableMapping.SourceTable = "Table"
            tableMapping.DataSetTable = "WeeklyGRDropshipOrders"
            tableMapping.ColumnMappings.Add("DropShipOrderID", "DropShipOrderID")
            tableMapping.ColumnMappings.Add("DropShipRetailerOrderID", "DropShipRetailerOrderID")
            tableMapping.ColumnMappings.Add("RetailerName", "RetailerName")
            tableMapping.ColumnMappings.Add("DropShipGiftName", "DropShipGiftName")
            tableMapping.ColumnMappings.Add("DropShipQty", "DropShipQty")
            tableMapping.ColumnMappings.Add("day", "day")
            tableMapping.ColumnMappings.Add("month", "month")
            tableMapping.ColumnMappings.Add("year", "year")
            tableMapping.ColumnMappings.Add("UserName", "UserName")
            tableMapping.ColumnMappings.Add("CatDesc", "CatDesc")
            Me._adapter.TableMappings.Add(tableMapping)
        End Sub
        
        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Private Sub InitConnection()
            Me._connection = New Global.System.Data.SqlClient.SqlConnection
            Me._connection.ConnectionString = Global.GiftRepublic.My.MySettings.Default.GiftRepublicWebConnectionString
        End Sub
        
        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Private Sub InitCommandCollection()
            Me._commandCollection = New Global.System.Data.SqlClient.SqlCommand(0) {}
            Me._commandCollection(0) = New Global.System.Data.SqlClient.SqlCommand
            Me._commandCollection(0).Connection = Me.Connection
            Me._commandCollection(0).CommandText = "SELECT * FROM viewWeeklyWebDropshipOrders"
            Me._commandCollection(0).CommandType = Global.System.Data.CommandType.Text
        End Sub
        
        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _
         Global.System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter"),  _
         Global.System.ComponentModel.DataObjectMethodAttribute(Global.System.ComponentModel.DataObjectMethodType.[Select], true)>  _
        Public Overloads Overridable Function GetWeeklyGRDropshipOrders() As dsWeekWebReports.WeeklyGRDropshipOrdersDataTable
            Me.Adapter.SelectCommand = Me.CommandCollection(0)
            Dim dataTable As dsWeekWebReports.WeeklyGRDropshipOrdersDataTable = New dsWeekWebReports.WeeklyGRDropshipOrdersDataTable
            Me.Adapter.Fill(dataTable)
            Return dataTable
        End Function
    End Class

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I thought this is the first instance where I am using the table adapter defined in the XSD


Dim Adapter As New dsWeekWebReportsTableAdapters.WeeklyGRDropshipOrdersTableAdapter

No, that is the declaration for the table adapter, not the DataSet.  
hmmm..I am unable to trace this out,  TheLearnedOne....
Well, my friend, I am sitting here, you are sitting there, and there is probably a huge distance between us.  

What I would suggest is to attach a .png snapshot of your solution, the main form, where the DataSet is defined.