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
