Hi
I'm totally stumped by this one. I have done nothing to the code of ch07/Delete_Datagrid.aspx except change the connection.
The page comes up ok but when I try to delete something I keep on getting the following error::
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.
Is this a database problem or a code problem.
The code is::
Code:
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.SqlClient" %>
<html>
<head>
<title>Updating Beverages</title>
</head>
<body>
<form method="post" runat="server">
<asp:DataGrid id="dgProducts" runat="server"
CellPadding="5" AutoGenerateColumns="False"
OnEditCommand="EditRecord"
OnCancelCommand="CancelEdit"
OnUpdateCommand="UpdateRecord"
OnDeleteCommand="DeleteRecord">
<Columns>
<asp:BoundColumn DataField="ProductID" ReadOnly="True"
Visible="False" />
<asp:BoundColumn DataField="ProductName" ReadOnly="True"
HeaderText="Name" />
<asp:BoundColumn DataField="UnitPrice" HeaderText="Price" />
<asp:EditCommandColumn ButtonType="LinkButton"
UpdateText="Save" CancelText="Cancel"
EditText="Edit" />
<asp:ButtonColumn Text="Delete" CommandName="Delete" />
</Columns>
</asp:DataGrid>
</form>
</body>
</html>
<script language="c#" runat="server">
private String strConnection = ConfigurationSettings.AppSettings["NWind"];
private String strSQLSelect = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE CategoryID = 1";
private String ProductTableName = "ProductTable";
private SqlConnection objConnection;
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
String strSQL = "INSERT INTO Products (" + "ProductName, CategoryID, UnitPrice) " + "VALUES ('RolaBolaCola', 1, 15)";
Connect();
SqlCommand dbComm = new SqlCommand(strSQL, objConnection);
dbComm.ExecuteNonQuery();
Disconnect();
LoadGrid();
}
}
private void LoadGrid()
{
Connect();
SqlDataAdapter adapter = new SqlDataAdapter(strSQLSelect, objConnection);
DataSet ds = new DataSet();
adapter.Fill(ds, ProductTableName);
Disconnect();
dgProducts.DataSource = ds.Tables[ProductTableName];
dgProducts.DataBind();
}
private void Connect()
{
if (objConnection == null)
objConnection = new SqlConnection(strConnection);
if (objConnection.State == ConnectionState.Closed)
objConnection.Open();
}
private void Disconnect()
{
objConnection.Close();
}
public void EditRecord(object sender, DataGridCommandEventArgs e)
{
dgProducts.EditItemIndex = e.Item.ItemIndex;
LoadGrid();
}
public void CancelEdit(object sender, DataGridCommandEventArgs e)
{
dgProducts.EditItemIndex = -1;
LoadGrid();
}
public void UpdateRecord(object sender, DataGridCommandEventArgs e)
{
// Retrieve the field values in the edited row
// make sure id is a int for security [int ProductID = Convert.ToInt32]
int ProductID = Convert.ToInt32(e.Item.Cells[0].Text);
TextBox PriceTextBox = (TextBox)e.Item.Cells[2].Controls[0];
decimal Price = Convert.ToDecimal(PriceTextBox.Text);
dgProducts.EditItemIndex = -1;
UpdateProduct(ProductID, Price);
DataSet ds = new DataSet();
dgProducts.DataSource = ds.Tables[ProductTableName];
dgProducts.DataBind();
}
private void UpdateProduct(int ProductID, decimal Price)
{
// Create and load a DataSet with records from Northwind.Products table
Connect();
SqlDataAdapter adapter = new SqlDataAdapter(strSQLSelect, objConnection);
DataSet ds = new DataSet();
adapter.Fill(ds, ProductTableName);
Disconnect();
// Modify the in-memory records in the DataSet
DataTable tbl = ds.Tables[ProductTableName];
tbl.PrimaryKey = new DataColumn[] {tbl.Columns["ProductID"]};
DataRow row = tbl.Rows.Find(ProductID);
row["UnitPrice"] = Price;
// Reconnect the DataSet and update the database
SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
Connect();
adapter.Update(ds, ProductTableName);
Disconnect();
}
public void DeleteRecord(object sender, DataGridCommandEventArgs e)
{
// Retrieve the ID of the product to be deleted
int ProductID = Convert.ToInt32(e.Item.Cells[0].Text);
dgProducts.EditItemIndex = -1;
DeleteProduct(ProductID);
// Display the remaining items in the DataGrid
DataSet ds = new DataSet();
dgProducts.DataSource = ds.Tables[ProductTableName];
dgProducts.DataBind();
}
private void DeleteProduct(int ProductID)
{
// Create and load a DataSet with records from Northwind's Products table
Connect();
SqlDataAdapter adapter = new SqlDataAdapter(strSQLSelect, objConnection);
DataSet ds = new DataSet();
adapter.Fill(ds, ProductTableName);
Disconnect();
// Mark the product as Deleted in the DataSet
DataTable tbl = ds.Tables[ProductTableName];
tbl.PrimaryKey = new DataColumn[] {tbl.Columns["ProductID"]};
DataRow row = tbl.Rows.Find(ProductID);
row.Delete();
// Reconnect the DataSet and delete the record from the database
SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
Connect();
adapter.Update(ds, ProductTableName);
Disconnect();
}
</script>
ch 7- Delete_Datagrid.aspx - The conflict occurred
