例外未處理...System.Data.OleDb.OleDbException：'INSERTINTO陳述句中的語法錯誤。'...請我已經安靜很久了，

請任何幫助這是我在截止日期前一直在做的最后一個專案......我嘗試了很多解決方案，但仍然無法解決這個問題......錯誤出現在這一行cmd.ExecuteNonQuery()

conn.Open()
    cmd = conn.CreateCommand
    cmd.CommandType = CommandType.Text
    cmd.CommandText = "insert into 
    Salestracker ( SDate, Waakye, Salad, 
    Gari, Spaghetti, Fish, Chicken, 
    Sausage, Egg, Beef, Water, WAmount
    Drink, DAmount, DrinksSold, 
    TotalCostOfMeal, TotalCostOfDelivery, 
    SubTotal, Tax, TotalAmount)Values('"   
    lbldt.Text   ","   txtwaakye.Text   
    ","  
    txtsalad.Text   ","   txtgari.Text   
    ","   txtspag.Text   ","   
    txtfish.Text   ","   txtchicken.Text   
    ","   txtsausage.Text   ","  
    txtegg.Text   ","   txtbeef.Text   "," 
      CmbWater.Text   ","   
    txtWAmount.Text   ","   CmbDrink.Text 
      ","   txtDAmount.Text   ","  
    txtDrinksSold.Text   ","   
    txtTotalCostOfMeal.Text   ","   
    txtTotalCostOfDelivery.Text   ","  
    txtSubTotal.Text   ","   txtTax.Text   
     ","   txtTotalAmount.Text   "')"
cmd.ExecuteNonQuery()
conn.Close()

uj5u.com熱心網友回復：

目前，您正在創建一個如下所示的 SQL 命令：

insert into
    Salestracker ( SDate, Waakye, Salad,
    Gari, Spaghetti, Fish, Chicken,
    Sausage, Egg, Beef, Water, WAmount
    Drink, DAmount, DrinksSold,
    TotalCostOfMeal, TotalCostOfDelivery,
    SubTotal, Tax, TotalAmount)Values('A,B,C,D,E,F,G,H,I,J,T,K,S,Q,L,M,N,O,P,R')

但 VALUES 部分應該是這樣的

Values('A','B','C','D','E','F','G','H','I','J','T','K','S','Q','L','M','N','O','P','R')

即，SQL 中的每個字串都必須用單引號引起來。當然，除非列型別是日期或數字。

WAmount并且列名和.之間缺少逗號Drink。

但是使用字串連接創建 SQL 命令會導致幾個問題：

1. SQL 注入
2. 文本中的單引號必須轉義
3. 日期和時間必須以正確的方式格式化才能正確插入到資料庫中。這對于日期尤其棘手。

更好的方法是使用引數化查詢：

conn.Open()
cmd = conn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "INSERT INTO Salestracker ( SDate, Waakye, Salad, Gari, Spaghetti, Fish, Chicken, 
Sausage, Egg, Beef, Water, WAmount, Drink, DAmount, DrinksSold, TotalCostOfMeal, TotalCostOfDelivery, 
SubTotal, Tax, TotalAmount) VALUES (@dt, @waakye, @salad, @gari, @spag, @fish, @chicken,
@sausage, @egg, @beef, @water, @wAmount, @drink, @dAmount, @drinksSold, @totalCostOfMeal, @totalCostOfDelivery,
@subTotal, @tax, @totalAmount)"

cmd.Parameters.Add(New OleDb.OleDbParameter("@dt", OleDbType.VarChar)).Value = lbldt.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@waakye", OleDbType.VarChar)).Value = txtwaakye.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@salad", OleDbType.VarChar)).Value = txtsalad.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@gari", OleDbType.VarChar)).Value = txtgari.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@spag", OleDbType.VarChar)).Value = txtspag.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@fish", OleDbType.VarChar)).Value = txtfish.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@chicken", OleDbType.VarChar)).Value = txtchicken.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@sausage", OleDbType.VarChar)).Value = txtsausage.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@egg", OleDbType.VarChar)).Value = txtegg.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@beef", OleDbType.VarChar)).Value = txtbeef.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@water", OleDbType.VarChar)).Value = CmbWater.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@wAmount", OleDbType.Double)).Value = CDbl(txtWAmount.Text)
cmd.Parameters.Add(New OleDb.OleDbParameter("@drink", OleDbType.VarChar)).Value = CmbDrink.Text
cmd.Parameters.Add(New OleDb.OleDbParameter("@dAmount", OleDbType.Double)).Value = CDbl(txtDAmount.Text)
cmd.Parameters.Add(New OleDb.OleDbParameter("@drinksSold", OleDbType.Integer)).Value = CInt(txtDrinksSold.Text)
cmd.Parameters.Add(New OleDb.OleDbParameter("@totalCostOfMeal", OleDbType.Double)).Value = CDbl(txtTotalCostOfMeal.Text)
cmd.Parameters.Add(New OleDb.OleDbParameter("@totalCostOfDelivery", OleDbType.Double)).Value = CDbl(txtTotalCostOfDelivery.Text)
cmd.Parameters.Add(New OleDb.OleDbParameter("@subTotal", OleDbType.Double)).Value = CDbl(txtSubTotal.Text)
cmd.Parameters.Add(New OleDb.OleDbParameter("@tax", OleDbType.Double)).Value = CDbl(txtTax.Text)
cmd.Parameters.Add(New OleDb.OleDbParameter("@totalAmount", OleDbType.Double)).Value = CDbl(txtTotalAmount.Text)

cmd.ExecuteNonQuery()
conn.Close()

我不知道您的表格列的型別。因此，您可能對上面的代碼進行了一些調整。

uj5u.com熱心網友回復：

假設您有name字串型別的欄位amount和 int 型別的欄位。如果你想插入到表中，你的查詢會是這樣的：insert into table_name (name, amount) values ('mark', 100). 因此，如您所見，只有字串欄位用單引號括起來。在您的示例中，查詢看起來像這樣insert into table_name (name, amount) values ('mark, 100')，這是無效的。

讓我們看下一個例子。假設您有欄位name:String, age:int，country:String并且您從txtName, txtAge,獲取值txtCountry。在表中插入的查詢如下所示：

cmd.CommandText = "insert into table_name (name, age, country) values ('"   txtName.Text   "', "   txtAge.Text   ", '"   txtCountry.Text   "')";

更簡單的方法是$在字串之前使用，它使您可以選擇將變數直接傳遞給字串，并用{}. 所以這個例子看起來像這樣：

cmd.CommandText = $"insert into table_name (name, age, country) values ('{txtName.Text}', {txtAge.Text}, '{txtCountry.Text}')";

標籤：视觉工作室 毫秒访问

上一篇：如何設定脊體速度限制

下一篇：使用HTACCESS從ServerVariables中洗掉SERVER_SOFTWARE