Delet function not working on Gridview

I 'm having trouble with the delete function on Gridview - the update works great but I keep getting the error below when I try to delete

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.

I using the products table in Northwind to learn this stuff.

The code that I 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>

Any help would be appreciated - thanx

The table has a foreign key constraint.

If a product is part of an order, you may not delete the product.

Otherwise, when someone tried to look at the order, they would get an error about a missing product.

|||

Thanx for your help - The reason I'm using the Products table in Northwind is, "I tried building a table based on my clients original data (his music shows itinerary) but it would not allow me to click the Checkbox in the Wizard - "Generate additional INSERT, UPDATE, and DELETE statements" when configuring the db

Can you tell me how I can remove the foreign key constraints (or) how can I duplicate the table, rename it and then remove all foreign key constraints

thanx

|||

Open up the Keys folder under the table.

The foreign keys have a prefix FK_. Right click one and select Modify.

When you built your original table, did you give it a primary key? If a table does not have a primary key, you will not be able to generate Insert, Delete and Update statements.,

|||

thank you so much - The primary key issue was the problem and now I have had no problems adding, deleting and modyfing the table. I am having a problem with the formatting of a datetime field. It keeps returning the date as well as the time (12:00 AM). Could you point me in the right direction to take care of this. Do I need to change the field or can I deal with it by formatting the column in the GridView?

thanx for all of your help

|||

In the GridView date column set the DataFormatString to {0:d} or {0:D} or {0:yyyy-MM-dd} etc.

And set the column's HtmlEncode property to false.

|||

Thanx Steve - Everything seems to be working fine now. Except,...

When I try to put this little app on my server (www.discountasp.net) - I get errors

Server Error in '/dave' Application.

---

Configuration Error

Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message:Unrecognized attribute 'xmlns'.

Source Error:

Line 8: \Windows\Microsoft.Net\Framework\v2.x\Config Line 9: -->Line 10: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">Line 11: <appSettings/>Line 12: <connectionStrings/>

Source File:E:\web\nyguitarsne\htdocs\dave\web.config Line:10

I must be missing something crucial about deployment?

|||

PS - the site is sitting here:

www.caterdata.com/dave

|||

It's hard to say without seeing the whole file but it looks like you have a closing connectionStrings tag without an opening tag. The fix below may help (or you could delete both tags)

Line 8: \Windows\Microsoft.Net\Framework\v2.x\Config Line 9: -->Line 10: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">Line 11: <appSettings/>

new: <connectionStrings>Line 12: <connectionStrings/>

|||

The reason I'm confused is that it is working fine on localhost.

Do I need to call the site Administrator and set something up that I'm not aware of?

|||

It looks like the web.config file on you local machine and the server are different.

Sometimes they should be. For instance, the datasource on a local machine may not exist on the server.

|||

Try making sure the server is setup top use ASP.NET 2.0 and not ASP.NET 1.0

I have seen this cause weird problems as you are having.

David

|||

Try making sure the server is setup to use ASP.NET 2.0 and not ASP.NET 1.0

I have seen this cause weird problems as you are having.

David

sql