Wednesday, March 21, 2012

Errors when I use the "Delete" function

I've been trying to build a data table that allows a client to add, edit and delete his itinerary. I've been using the products table in Northwind to get up to speed on this functions and everything works fine - (ie-connects to the database, shows the data I need, allows me to edit the data) but when I try to use the delete function, I keep getting the error below.

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.
The statement has been terminated.

Can anybody help me with this problem?

thanx

Code used is below:

<%@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Gigs.aspx.cs"Inherits="Gigs" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @.ProductID"InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@.ProductName, @.SupplierID, @.CategoryID, @.QuantityPerUnit, @.UnitPrice, @.UnitsInStock, @.UnitsOnOrder, @.ReorderLevel, @.Discontinued)"SelectCommand="SELECT * FROM [Products]"UpdateCommand="UPDATE [Products] SET [ProductName] = @.ProductName, [SupplierID] = @.SupplierID, [CategoryID] = @.CategoryID, [QuantityPerUnit] = @.QuantityPerUnit, [UnitPrice] = @.UnitPrice, [UnitsInStock] = @.UnitsInStock, [UnitsOnOrder] = @.UnitsOnOrder, [ReorderLevel] = @.ReorderLevel, [Discontinued] = @.Discontinued WHERE [ProductID] = @.ProductID"><DeleteParameters><asp:ParameterName="ProductID"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/><asp:ParameterName="ProductID"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/></InsertParameters></asp:SqlDataSource>

</div><asp:GridViewID="GridView1"runat="server"AllowPaging="True"AutoGenerateColumns="False"CellPadding="4"DataKeyNames="ProductID"DataSourceID="SqlDataSource1"ForeColor="#333333"GridLines="None"><FooterStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><Columns><asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="ProductName"HeaderText="ProductName"SortExpression="ProductName"/><asp:BoundFieldDataField="SupplierID"HeaderText="SupplierID"SortExpression="SupplierID"/><asp:BoundFieldDataField="CategoryID"HeaderText="CategoryID"SortExpression="CategoryID"/><asp:BoundFieldDataField="QuantityPerUnit"HeaderText="QuantityPerUnit"SortExpression="QuantityPerUnit"/><asp:BoundFieldDataField="UnitPrice"HeaderText="UnitPrice"SortExpression="UnitPrice"/><asp:BoundFieldDataField="UnitsInStock"HeaderText="UnitsInStock"SortExpression="UnitsInStock"/><asp:BoundFieldDataField="UnitsOnOrder"HeaderText="UnitsOnOrder"SortExpression="UnitsOnOrder"/><asp:BoundFieldDataField="ReorderLevel"HeaderText="ReorderLevel"SortExpression="ReorderLevel"/><asp:CheckBoxFieldDataField="Discontinued"HeaderText="Discontinued"SortExpression="Discontinued"/></Columns><RowStyleBackColor="#FFFBD6"ForeColor="#333333"/><SelectedRowStyleBackColor="#FFCC66"Font-Bold="True"ForeColor="Navy"/><PagerStyleBackColor="#FFCC66"ForeColor="#333333"HorizontalAlign="Center"/><HeaderStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><AlternatingRowStyleBackColor="White"/></asp:GridView></form>

</

body>

</

html>

aspx.cs code used below:

using

System;

using

System.Data;

using

System.Configuration;

using

System.Collections;

using

System.Web;

using

System.Web.Security;

using

System.Web.UI;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Web.UI.HtmlControls;

public

partialclassGigs : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

}

}

Hello my friend,

Your code is fine. It is not allowing you to delete the product from the product table because a related record also exists in the Order Details table. If you want to delete the product and its related records in the other table, go to the relationship in the database and choose the "Cascade Delete Related Records" option.

However, I would not do it this way. If I buy something from your website I will have a record in the Order Details table that links back to a product. If you delete this product and it cascades to the Order Details table, the record of my order is also lost. Instead of deleting products, create a field called "Active" in the product table that will contain either 1 or 0. Then configure your website to only show products with Active=1. A benefit of this approach is that you can always set something back to Active if you make a mistake.

Kind regards

Scotty

|||

Looks like product ID is used as foreign Key in some orders and you can not delete product as long as this reference exists, to delete record from product table you have to delete all orders in which it is included or remove connection between Order details and products table.

But I think that in case like this you should display message to user : You can not delete this product because it was used in some orders!!!

No comments:

Post a Comment