CODEWITHSUNDEEP
c#asp.netsql-server-2008
ALOK
ALOK

Reputation: 553

Deleting columns from multiple table having relationship

I am using Gridview to Display some Data from database and have giving option to delete data But now i just referenced primary key column to another table by foreign key. Anyway to delete and now delete is not working because of conflict.

Error:-The DELETE statement conflicted with the REFERENCE constraint "FK_temp1_ProductDetails". The conflict occurred in database "E:\VISUAL STUDIO 2010\SHOPMOBILE\APP_DATA\SHOPPING.MDF", table "dbo.temp1", column 'product_id'. The statement has been terminated.

Anyway to delete data from both the table on same time.Delete query is

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConflictDetection="CompareAllValues" 
            ConnectionString="<%$ ConnectionStrings:shoppingConnectionString2 %>" 
            DeleteCommand="DELETE FROM [ProductDetails] WHERE [Product_id] = @original_Product_id" 
            OldValuesParameterFormatString="original_{0}" 
            SelectCommand="SELECT * FROM [ProductDetails]" 
            UpdateCommand="UPDATE [ProductDetails] SET [Product_name] = @Product_name, [Brand] = @Brand, [Category] = @Category, [Sub_category] = @Sub_category, [Product_info] = @Product_info, [Price] = @Price, [Big_img] = @Big_img, [Quantity] = @Quantity WHERE [Product_id] = @original_Product_id">
            <DeleteParameters>
                <asp:Parameter Name="original_Product_id" Type="Int32" />
                <asp:Parameter Name="original_Product_name" Type="String" />
                <asp:Parameter Name="original_Brand" Type="String" />
                <asp:Parameter Name="original_Category" Type="String" />
                <asp:Parameter Name="original_Sub_category" Type="String" />
                <asp:Parameter Name="original_Product_info" Type="String" />
                <asp:Parameter Name="original_Price" Type="Int64" />
                <asp:Parameter Name="original_Big_img" Type="String" />
                <asp:Parameter Name="original_Quantity" Type="Int64" />
            </DeleteParameters>
        </asp:SqlDataSource>

Upvotes: 1

Views: 813

Answers (1)

Hadi Sharifi
Hadi Sharifi

Reputation: 1527

On sql server management studio, select ProductDetails table design view and open relationships window, then set delete action to CASCADE DELETE, It's works fine. enter image description here

UPDATE: set it for all table relationships.

Upvotes: 1

Related Questions