SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict foolios's Avatar
    Join Date
    Dec 2006
    Location
    The least technologically advanced state in the US
    Posts
    202
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL INSERT error - C#/Asp.Net/SQL2005

    I have been learning how to retrieve data from my SQL database using C#/Asp.Net. I am able to read the data and I am able to update my data.

    I am now wanting to insert into my database. I think I've gotten everything under control except for one error. It's something I don't understand and would like some assistance with.

    I assumed that as soon as I inserted something into a table that the auto-increment field would auto increment. From the error I take it that I am wrong.

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_inventoryTbl_categoryTbl". The conflict occurred in database "Chaos", table "dbo.categoryTbl", column 'categoryID'.
    The statement has been terminated.

    That foreign key is categoryID but it's a number I am trying to insert that does exist in the other table, categoryID 11. The auto increment field is ID, in the inventoryTbl. The id of a new item that gets entered is auto incremented for the new item.

    I am not sure whether it's an error related to that I am not inserting a number into the id field of a newly added item into the table or whether it's come kind of constraint rule that I do not understand concerning Insertion and relationships between two tables.



    Here is the code:





    protected void bttnApplyChanges_Click(object sender, EventArgs e)
    {
    string connectionString = ConfigurationManager.ConnectionStrings["ChaosConnectionString1"].ConnectionString;
    SqlConnection conn = new SqlConnection(connectionString);
    conn.Open();
    SqlCommand comm = new SqlCommand("INSERT INTO inventoryTbl (categoryID, item, description, price, image_url, qty, page_url) VALUES (@category, @item, @desc, @price, @imageurl, @quantity, @page)", conn);
    // Add command parameters
    comm.Parameters.Add("@category", System.Data.SqlDbType.SmallInt);
    comm.Parameters["@category"].Value = txtbx_itemCategory.Text;
    comm.Parameters.Add("@item", System.Data.SqlDbType.NVarChar);
    comm.Parameters["@item"].Value = txtbx_itemName.Text;
    comm.Parameters.Add("@desc", System.Data.SqlDbType.NVarChar);
    comm.Parameters["@desc"].Value = txtbx_ItemDesc.Text;
    comm.Parameters.Add("@price", System.Data.SqlDbType.Money);
    comm.Parameters["@price"].Value = txtbx_ItemPrice.Text;
    comm.Parameters.Add("@imageurl", System.Data.SqlDbType.NVarChar);
    comm.Parameters["@imageurl"].Value = txtbx_itemImgUrl.Text;
    comm.Parameters.Add("@quantity", System.Data.SqlDbType.SmallInt);
    comm.Parameters["@quantity"].Value = txtbx_qty.Text;
    comm.Parameters.Add("@page", System.Data.SqlDbType.NVarChar);
    comm.Parameters["@page"].Value = txtbxPageUrl.Text;

    int rowsAffected = comm.ExecuteNonQuery();
    bttnAddItem.Text = rowsAffected.ToString() + " row affected: Success";

    comm.ExecuteNonQuery();
    conn.Close();
    }




  2. #2
    SitePoint Addict foolios's Avatar
    Join Date
    Dec 2006
    Location
    The least technologically advanced state in the US
    Posts
    202
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I understand my error now. I was actually using an ID that did not exist and didn't realize it.
    Now the data does insert. The major problem is that I am now inputting exactly two of the same record when I press that add buttonbttn(ApplyChanges_Click).



    EDIT:

    Got it, I was running it during assignment as well as executing the nonquery again.

    int rowsAffected = comm.ExecuteNonQuery();
    bttnAddItem.Text = rowsAffected.ToString() + " row affected: Success";

    comm.ExecuteNonQuery();

    All working. =)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •