SQLite showing "Insufficient Parameters supplied to the command" despite having enough par-CodePudding

I have the following SQLite table.

CREATE TABLE "Ingredient_Detailed" (
    "DETAILED_INGREDIENT_ID"    TEXT,
    "INGREDIENT_CODE"   INTEGER NOT NULL,
    "BRAND" TEXT NOT NULL,
    "INGREDIENT_SOURCE" TEXT NOT NULL,
    "UNIT_PRICE"    REAL NOT NULL DEFAULT 0,
    "AMOUNT_IN_UNIT"    REAL NOT NULL DEFAULT 0,
    "MINIMUM_PRICE_PER_UNIT"    REAL NOT NULL DEFAULT 0,
    "QUALITY"   INTEGER NOT NULL DEFAULT 1,
    "UNITS_AVAILABLE"   INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY("INGREDIENT_CODE") REFERENCES "Ingredient"("CODE"),
    PRIMARY KEY("DETAILED_INGREDIENT_ID")
)

I have a C# application where I am trying to insert records into this table with the following method:

public int SaveDetailedIngredient(DetailedIngredient pDetailedIngredient)
    {
        try
        {
            using (var conn = new SQLiteConnection(GetConnectionString()))
            {
                var saveDetailedIngredientCommand = new SQLiteCommand("INSERT INTO INGREDIENT_DETAILED (DETAILED_INGREDIENT_ID, "  
                    "INGREDIENT_CODE, BRAND, INGREDIENT_SOURCE, UNIT_PRICE, AMOUNT_IN_UNIT, "  
                    "MINIMUM_PRICE_PER_UNIT, QUALITY, UNITS_AVAILABLE) "  
                    "VALUES ($pDetailedIngredientId, $pCode, $pBrand, $pSource, $pUnitPrice, $pAmountInUnit, $pPricePerUnit, $pQuality, $pUnitsAvailable)", conn);
                pDetailedIngredient.DetailedIngredientCode = pDetailedIngredient.Code   "-"   pDetailedIngredient.Brand   "-"   pDetailedIngredient.IngredientSource;
                saveDetailedIngredientCommand.Parameters.AddWithValue("pDetailedIngredientId", pDetailedIngredient.DetailedIngredientCode);
                saveDetailedIngredientCommand.Parameters.AddWithValue("pCode", pDetailedIngredient.Code);
                saveDetailedIngredientCommand.Parameters.AddWithValue("pBrand", pDetailedIngredient.Brand.Trim().ToUpper());
                saveDetailedIngredientCommand.Parameters.AddWithValue("pSource", pDetailedIngredient.IngredientSource.Trim().ToUpper());
                saveDetailedIngredientCommand.Parameters.AddWithValue("pUnitPrice,", pDetailedIngredient.UnitPrice);
                saveDetailedIngredientCommand.Parameters.AddWithValue("pAmountInUnit", pDetailedIngredient.AmountInUnit);
                saveDetailedIngredientCommand.Parameters.AddWithValue("pPricePerUnit", pDetailedIngredient.MinimumUnitPrice);
                saveDetailedIngredientCommand.Parameters.AddWithValue("pQuality", pDetailedIngredient.Quality);
                saveDetailedIngredientCommand.Parameters.AddWithValue("pUnitsAvailable", pDetailedIngredient.UnitsAvailable);

                conn.Open();
                return saveDetailedIngredientCommand.ExecuteNonQuery();

            }
        }
        catch (SQLiteException sqlEx)
        {

            Console.WriteLine(sqlEx.Message);
            Console.WriteLine(sqlEx.ErrorCode);
            throw sqlEx;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            throw ex;
        }
    }

Despite indicating 9 fields and supplying 9 parameters, SQLite throws an exception saying "Unknown Error: Insufficient Parameters Supplied to Command".

I copy and pasted the name of the parameters to make sure there's no typo in them, yet it still throws the error.

I debugged the application during the method executing and the supplied pDetailedIngredient has all the necessary attribute values assigned, and I can see that each of the parameters in the command is being assigned correctly.

I have several other methods for inserting data into other tables, all follow the same structure, so I doubt that this is related to the way my method is written.

Am I missing something here? It doesn't feel like this is the right error.

CodePudding user response：

I honestly wish I had a good answer as to “WHY” this is happening. I can only assume that the “AddWithValue” is doing something I am not aware of. However, after a some back and forth I would ask you to try this and see if it works for you as it did for me.

Is what I changed is by adding the parameters as shown below. This consistently worked with some testing and only failed as expected when the id was duplicated. Please let me know if this helps as I am with you in a sense that on the surface, your code looks good to me.

saveDetailedIngredientCommand.Parameters.Add("pDetailedIngredientId", DbType.String).Value = pDetailedIngredient.DetailedIngredientCode;
saveDetailedIngredientCommand.Parameters.Add("pCode", DbType.Int32).Value = pDetailedIngredient.Code;
saveDetailedIngredientCommand.Parameters.Add("pBrand", DbType.String).Value = pDetailedIngredient.Brand.Trim().ToUpper();
saveDetailedIngredientCommand.Parameters.Add("pSource", DbType.String).Value = pDetailedIngredient.IngredientSource.Trim().ToUpper();
saveDetailedIngredientCommand.Parameters.Add("pUnitPrice", DbType.Decimal).Value = pDetailedIngredient.UnitPrice;
saveDetailedIngredientCommand.Parameters.Add("pAmountInUnit", DbType.Decimal).Value = pDetailedIngredient.AmountInUnit;
saveDetailedIngredientCommand.Parameters.Add("pPricePerUnit", DbType.Decimal).Value = pDetailedIngredient.MinimumUnitPrice;
saveDetailedIngredientCommand.Parameters.Add("pQuality", DbType.Int32).Value = pDetailedIngredient.Quality;
saveDetailedIngredientCommand.Parameters.Add("pUnitsAvailable", DbType.Int32).Value = pDetailedIngredient.UnitsAvailable;

Let me know if this does not work for you and I will remove it.