Exception Unhandled... System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement-CodePudding

Please any help this is my final project I've been working on with a deadline... I've tried many solution but couldn't still resolve this... Error appear on this line 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()

CodePudding user response：

Say you have field name of type string and field amount of type int. If you want to insert into table, your query would go like this: insert into table_name (name, amount) values ('mark', 100). So, as you can see, only string field is surrounded by single quotes. In your example query would look like this insert into table_name (name, amount) values ('mark, 100') which is not valid.

Let's look at next example. Say you have fields name:String, age:int, country:String and that you get values from txtName, txtAge, txtCountry. Query to insert in table would look like this:

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

Much easier way would be using $ before string which gives you option to pass variables directly to string with surrounding them with {}. So this example would look like this:

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

CodePudding user response：

Currently you are creating a SQL command looking like this:

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')

but the VALUES part should look like this

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

i.e., each string in SQL must be enclosed in single quotes. Unless of course, when the column types are dates or numbers.

And there is a comma missing between the column names WAmount and Drink.

But using string concatenation to create a SQL command leads to several problems:

SQL Injection
Single quotes in texts must be escaped
Dates and times must be formatted the right way to be instered correctly into the DB. This is especially tricky with dates.

A better approach is to use parametrized queries:

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()

I don't know the types of your table columns. Therefore you might have do some adjustments to the code above.