-2

AM trying to update the date in that query am getting this error Syntax error in UPDATE statement., this is my code

Updated Code

OleDbCommand top = new OleDbCommand(
         "UPDATE NewInvoice_1 SET (" +
         "Terms=?, [InvoiceDate]=?, OurQuote=?," +
         "SalesPerson=?, CustomerName=?, OrderNumber=?," +
         "InvoiceAddress=?, DeliveryAddress=?," +
         "WholeDiscountP=?, WholeDiscountA=?, ShippingP=?, ShippingA=?," +
         "Price=?, Discount=?, Tax=?," +
         "Shipping=?, GrandTotal=?, TaxforDisc=?, DiscountType=?," +
         "ShippingBy=?, ShipReferenceNo=?, IsInsured=?, Notes=?," +
         "[DueDate]=?, AmountinWords=? ) WHERE InvoiceId=?", conn);


        top.Parameters.AddWithValue("?", CBL_Terms.EditValue.ToString());
        top.Parameters.AddWithValue("?", CBL_Date.DateTime);
        top.Parameters.AddWithValue("?", TXE_OurQuote.Text);
        top.Parameters.AddWithValue("?", CBL_Sales_Person.EditValue.ToString());
        top.Parameters.AddWithValue("?", CBL_Customer_Name.EditValue.ToString());
        top.Parameters.AddWithValue("?", TXE_Order_Number.Text);
        top.Parameters.AddWithValue("?", TXE_Invoice_Address.Text);
        top.Parameters.AddWithValue("?", TXE_Delivery_Address.Text);

        top.Parameters.AddWithValue("?", Convert.ToDecimal(TXE_FlatDiscountP.Text));
        top.Parameters.AddWithValue("?", Convert.ToDecimal(TXE_FlatDiscountA.Text));
        top.Parameters.AddWithValue("?", Convert.ToDecimal(TXE_ShippingPercentage.Text));
        top.Parameters.AddWithValue("?", Convert.ToDecimal(TXE_ShippingAmount.Text));
        top.Parameters.AddWithValue("?", Convert.ToDecimal(TXE_SubTotal.Text));
        top.Parameters.AddWithValue("?", Convert.ToDecimal(TXE_Discount.Text));
        top.Parameters.AddWithValue("?", Convert.ToDecimal(TXE_Tax.Text));
        top.Parameters.AddWithValue("?", Convert.ToDecimal(TXE_Shipping.Text));
        top.Parameters.AddWithValue("?", Convert.ToDecimal(TXE_GrandTotal.Text));

        top.Parameters.AddWithValue("?", barCheckItem1.Checked);
        top.Parameters.AddWithValue("?", selectedItem);

        top.Parameters.AddWithValue("?", TXE_Shipping_By.Text);
        top.Parameters.AddWithValue("?", TXE_Reference_No.Text);
        top.Parameters.AddWithValue("?", CBX_Is_Insured.Checked);
        top.Parameters.AddWithValue("?", TXE_Notes.Text);

        top.Parameters.AddWithValue("?", CBL_DueDate.DateTime);
        top.Parameters.AddWithValue("?", TXE_AmountinWords.Text);
        top.Parameters.AddWithValue("?", TXE_Unvisible.Text);
top.ExecuteNonQuery();

Now I update my code to parameters but getting Syntax error on updating code

Sri
  • 159
  • 1
  • 6
  • 14
  • Why you don't use parameterized query? – Mihai Hantea Apr 09 '14 at 05:47
  • 4
    seriously, are you doing this? – Amit Joki Apr 09 '14 at 05:48
  • Text and datetime values has to be string literals enclosed in quotation marks. But seriously, don't do this. Use a parameterized query. [This leaves you wide open to SQL injection](http://xkcd.com/327/) – Mikael Eriksson Apr 09 '14 at 05:51
  • possible duplicate of [How do parameterized queries help against SQL injection?](http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection) – Alexei Levenkov Apr 09 '14 at 05:55
  • String concatenation is not an acceptable method of constructing SQL queries from user provided data. So far your question is duplicate of many "why my query have all sorts of issues when I don't use parameterized queries" questions. If after rewriting your code to use parameter you still have a problem - please update the sample code and question will no longer be considered for duplicate. – Alexei Levenkov Apr 09 '14 at 05:59
  • I think your `SalesPerson` and `CustomerName` columns are character type. You might need to use `' '` their values. But please.. use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). **ALWAYS!** – Soner Gönül Apr 09 '14 at 06:07
  • Hi, Thanks for commenting guys I updated my code but still am getting syntax error. Help me to update this. – Sri Apr 09 '14 at 08:08

1 Answers1

0

Use parameters. Put ? for every parameter value. Use same order when adding parameter values.

string sql = "UPDATE NewInvoice_1 SET Terms =?, [InvoiceDate]=?, OurQuote=? ... WHERE InvoiceId=?";

            using (var command = conn.CreateCommand())
            {
                command.CommandText = sql;
                command.Parameters.Add(CBL_Terms.EditValue.ToString());
                command.Parameters.Add(CBL_Date.DateTime);
                command.Parameters.Add(TXE_OurQuote.Text);

                //add all other fields keeping order

                command.ExecuteNonQuery();
            }

Side note - you shouldn't really execute or keep queries in a form. Also, if you use adapter, you could use below syntax to add parameters:

command.Parameters.Add(adapter.Parameter(value));
Anton Gorlin
  • 57
  • 3