Problem in updating database with relationship one to many

Question

I'm trying to update my database but I'm getting this error:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.OleDb.OleDbException: You cannot add or change a record because a related record is required in table 'ItemTypes'.

I'm not sure how to fix it.

I don't know what i need to show so I will just show most of everything.

I've tried to look for a solution and the solutions that I've found said that it's because I'm trying to update the field with a value that is different but I don't understand why because the field is suppose to be the same.

The databases I have the problem with

This is the html part:

<asp:DropDownList ID="ItemType" runat="server">
    <asp:ListItem></asp:ListItem>
</asp:DropDownList></td>

This is my code at the PageLoad:

if (!Page.IsPostBack)
{
    DataTable dtItem = s.GetItemByIdForUser(Session["ItemCode"].ToString(),Session["Name"].ToString());
    ItemCode.Text = dtItem.Rows[0][0].ToString();
    ItemName.Text = dtItem.Rows[0][1].ToString();
    ItemDes.Text = dtItem.Rows[0][2].ToString();
    DataTable Types = s.GetAllItemsTypes();
    for (int i = 0; i < Types.Rows.Count; i++)
        ItemType.Items.Add(Types.Rows[i][0].ToString());
    ItemType.SelectedItem.Text = dtItem.Rows[0][3].ToString();
    ItemPrice.Text = dtItem.Rows[0][4].ToString();
    Stock.Text = dtItem.Rows[0][5].ToString();
    SellerName.Text = dtItem.Rows[0][6].ToString();
    Image1.ImageUrl = "~/ProjectPictures/" + dtItem.Rows[0][8].ToString();
}

This is my code that happens when i click at the update:

DataTable dtItem = s.GetItemByIdForUser(Session["ItemCode"].ToString(), Session["Name"].ToString());
i.ItemCode = ItemCode.Text;
i.Name = ItemName.Text;
i.Des = ItemDes.Text;
i.Type = ItemType.SelectedItem.ToString();
i.Price = ItemPrice.Text;
i.Stock = Stock.Text;
i.UserName = SellerName.Text;
if (Pic.HasFile)
{
    Pic.SaveAs(Server.MapPath("ProjectPictures/") + Pic.FileName);
    i.Pic = Pic.FileName;
}
else
{
    i.Pic = dtItem.Rows[0][8].ToString();
}
s.UpdateItem(i, ItemCode.Text);
Session["ItemCode"] = null;
Response.Redirect("Main.aspx");

and this is the sql sentence

string sql = "UPDATE [Items] SET [ItemName]= '@p1' , [ItemDes] = '@p2' , [ItemType] = '@p3' , [Price] = '@p4' , [Stock] = '@p5', [ItemPic] = '@p6' where [ItemCode] = '" +itemCode+ "'";
OleDbCommand cmd = new OleDbCommand(sql);
cmd.Parameters.Add(new OleDbParameter("@p1", OleDbType.VarChar));
cmd.Parameters["@p1"].Value = i.Name;
cmd.Parameters.Add(new OleDbParameter("@p2", OleDbType.VarChar));
cmd.Parameters["@p2"].Value = i.Des;
cmd.Parameters.Add(new OleDbParameter("@p3", OleDbType.VarChar));
cmd.Parameters["@p3"].Value = i.Type;
cmd.Parameters.Add(new OleDbParameter("@p4", OleDbType.VarChar));
cmd.Parameters["@p4"].Value = i.Price;
cmd.Parameters.Add(new OleDbParameter("@p5", OleDbType.VarChar));
cmd.Parameters["@p5"].Value = i.Stock;
cmd.Parameters.Add(new OleDbParameter("@p6", OleDbType.VarChar));
cmd.Parameters["@p6"].Value = i.Pic;

Answer 1

The problem is in your update statement. You have single quotation marks surrounding the parameter names. This means that they are interpreted as literal strings and not as the names of the parameter. So your statement is telling the database to update the ItemType with the value '@p3', (which is not a value in your ItemTypes table hence the foreign key violation) and not with the value in the parameter. In fact your parameters are being totally ignored.

Please also remember when using OleDB that the parameter names are ignored. All that matters is that you provide the parameters in the correct order.

Change your sql to this:

string sql = "UPDATE [Items] SET [ItemName]= @p1 , [ItemDes] = @p2 , [ItemType] = @p3 , [Price] = @p4 , [Stock] = @p5, [ItemPic] = @p6 where [ItemCode] = '" +itemCode+ "'";