Click here to Skip to main content
15,895,142 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
An explicit value for the identity column in table 'Inventory' can only be specified when a column list is used and IDENTITY_INSERT is ON.

This is my code
C#
public List<inventory> getInventory()
        {
            List<inventory> inventory = new List<inventory>();

            int fID, iID;
            
            string queryStr = "INSERT INTO Inventory SELECT FoodID= @fID, ItemID=@itID From Food,Item";

            SqlConnection conn = new SqlConnection(_connStr);
            SqlCommand cmd = new SqlCommand(queryStr, conn);
            cmd.Parameters.AddWithValue("@fID", _fID);
            cmd.Parameters.AddWithValue("@itID", _ITD);
            
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                string foodtemp = dr["FoodID"].ToString();
                fID = int.Parse(foodtemp);
                string itemtemp = dr["ItemID"].ToString();
                iID = int.Parse(itemtemp);

                inventory.Add(new Inventory(foodID,itemID));
            }


            conn.Close();
            dr.Close();
            dr.Dispose();

            return inventory;

        }
Posted
Updated 3-Feb-14 4:33am
v4

As the Inventory table has an identity column, you need to specify which fields you are inserting into. If you don't specify the fields, the database engine assumes you mean all the fields and will try to put something in them all if it can. So, modify your INSERT statement to include the fields. Note that your query doesn't actually make sense as it stands - if you are trying to insert values that you pass in, then you don't need to do a SELECT in your INSERT. Try this instead:
SQL
INSERT INTO Inventory(FoodID, ItemID) VALUES (@fID, @itID)
 
Share this answer
 
Comments
Member 10548723 3-Feb-14 10:02am    
No need From? I getting from two different tables
Pete O'Hanlon 3-Feb-14 10:04am    
It doesn't matter - you are still just supplying literal values - you have no WHERE clause in there.
Member 10548723 3-Feb-14 10:05am    
still have the same error
Pete O'Hanlon 3-Feb-14 10:07am    
Then either FoodID or ItemID is an identity column. You shouldn't be attempting to insert that - you let the database handle that itself.
Member 10548723 3-Feb-14 10:07am    
Cannot insert explicit value for identity column in table 'Inventory' when IDENTITY_INSERT is set to OFF.
Another point - I'm looking at your code and it's mixing purposes. You are attempting to do an insert, and then you are attempting to read from a SqlDataReader. You use an ExecuteNonQuery to perform an insert, and an ExecuteReader to read into a data reader.
 
Share this answer
 
Comments
Member 10548723 3-Feb-14 10:18am    
Yeah, so I should do a executenonquery for inserting the fields to my Inventory table and do a executereader to read my data to my gridview

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900