我有以下 SQLite 表。
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")
)
我有一個 C# 應用程式，我嘗試使用以下方法將記錄插入到該表中：
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;
}
}
盡管指示了 9 個欄位并提供了 9 個引數，SQLite 還是拋出了一個例外，提示“未知錯誤：提供給命令的引數不足”。
我復制并粘貼了引數的名稱以確保它們中沒有拼寫錯誤，但它仍然會引發錯誤。
我在方法執行期間除錯了應用程式，并且提供的應用程式pDetailedIngredient已分配了所有必要的屬性值，我可以看到命令中的每個引數都被正確分配。
我有其他幾種將資料插入其他表的方法，都遵循相同的結構，所以我懷疑這與我的方法的撰寫方式有關。
Am I missing something here? It doesn't feel like this is the right error.
uj5u.com熱心網友回復：
老實說，我希望我對“為什么”發生這種情況有一個很好的回答。我只能假設“AddWithValue”正在做一些我不知道的事情。但是，經過一番反復之后，我會請您嘗試一下，看看它是否像對我一樣適合您。
我改變的是通過添加如下所示的引數。這在一些測驗中始終有效，并且僅在 id 重復時才按預期失敗。請讓我知道這是否對我有幫助，因為從表面上看，您的代碼對我來說看起來不錯。
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;
如果這對您不起作用，請告訴我，我將洗掉它。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/337756.html
標籤：c# sql sqlite prepared-statement