Error: Failed to unable constraints.. ;when binding records in RadGrid

Question

Below is the code I am using to bind the RadGrid using Stored Procedure. I am calling the Stored Procedure using .xsd file - TableAdapter approach

HTML code:

<telerik:RadGrid ID="rgData" runat="server" ShowFooter="true"
OnNeedDataSource="rgData_NeedDataSource"
AllowSorting="True" AllowFilteringByColumn="True" ShowGroupPanel="True" GroupLoadMode="Client"
EnableEmbeddedSkins="False" ImagesPath="../App_Themes/MetroRed/Grid" Skin="MetroRed">
   <ClientSettings AllowDragToGroup="True"></ClientSettings>
   <GroupingSettings ShowUnGroupButton="false" CaseSensitive="false"></GroupingSettings>
   <MasterTableView ShowGroupFooter="true" CommandItemDisplay="None" AutoGenerateColumns="false" AllowMultiColumnSorting="true">
   <CommandItemSettings ShowAddNewRecordButton="false" />
    <Columns>
           <telerik:GridBoundColumn HeaderText="Billing ID" DataField="BillingID" SortExpression="BillingID" FilterDelay="2000" ShowFilterIcon="false">
       </telerik:GridBoundColumn>
       <telerik:GridBoundColumn HeaderText="Direct Cost" DataField="DCIDescription" SortExpression="DCIDescription" FilterDelay="2000" ShowFilterIcon="false">
       </telerik:GridBoundColumn>
           <telerik:GridBoundColumn HeaderText="Business Unit" DataField="BUName" SortExpression="BUName" FilterDelay="2000" ShowFilterIcon="false">
           </telerik:GridBoundColumn>
    </Columns>
   </MasterTableView>
</telerik:RadGrid>

C# code:

private DataTable _dtData = null; 

protected void rgData_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        try
        {
            _dtData = SDM.Invoice.GetInvoiceBySPID(_SPID);
            rgData.DataSource = _dtData;
        }
        catch (Exception ex)
        {
        }
    }

Class file inside BLL folder:

#region Invoice
private static SDM_Invoice _Invoice = null;
public static SDM_Invoice Invoice
{
    get
    {
        if (_Invoice == null)
        _Invoice = new SDM_Invoice();
 
        return _Invoice;
    }
}
#endregion

private SDM_Tran_GenerateInvoiceTableAdapter _GenerateInvoiceTableAdapter = null;
protected SDM_Tran_GenerateInvoiceTableAdapter Adapter
{
    get
    {
        if (_GenerateInvoiceTableAdapter == null)
                _GenerateInvoiceTableAdapter = new SDM_Tran_GenerateInvoiceTableAdapter();

            return _GenerateInvoiceTableAdapter;
    }
} 
    
public SDMDAL.SDM_Tran_GenerateInvoiceDataTable GetInvoiceBySPID(string SPID)
{
    return Adapter.GetInvoiceBillingBySPID(SPID);
}

Stored Procedure to bind RadGrid:

ALTER PROCEDURE [dbo].[Select_InvoiceBillingBySPID]
    @SPID as nvarchar(50)
    AS
BEGIN

    SET NOCOUNT ON; 

SELECT 
--B.ID,
B.BillingID, 
DCIDescription,
(SELECT BUName FROM dbo.SDM_Master_BU WITH (NOLOCK) WHERE BUID=AfoBUID) as BUName

FROM SDM_Tran_Billing B WITH (NOLOCK),
dbo.SDM_Tran_DCI D WITH (NOLOCK), 
dbo.SDM_Tran_Allocation A WITH (NOLOCK)

WHERE B.BfoAllocationID=AllocationID 
and A.AfoDCIID=D.DCIID 
and D.DCIfoSPID=@SPID
and B.BfoStatusID=1

END

.xsd file - Table Adapter

Whenever I run my web page, I get below error:

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

I think the error is because I am binding the data in RadGrid using 3 different tables (which have their own primary key) in Stored Procedure and then calling this Stored Proc into some other Table Adapter which has its own Primary key. But not sure whether the reason that I understood is correct or not.

NOTE that when I run the Stored Proc in Sql query wizard its working fine but when I run my page I get above error.

But I have to bind the RadGrid using 3 tables (like above). How can I resolve this error?

Answer 1

Replaced below line of code:

_dtData = SDM.Invoice.GetInvoiceBySPID(_SPID); 

with:

_dtData = SDM.Billing.GetBillingBySPID(_SPID);

since, Invoice TableAdapter/Table already has primary key in its database table and
I was using other database tables which has its own primary key, with Invoice TableAdapter/Table therefore it was giving me error. Issue resolved.