Click here to Skip to main content
15,867,832 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi, I try to update my form in that dateedite (InvoiceDate) getting Syntax error. This is my code. Help me to solve this ?
C#
OleDbCommand top = new OleDbCommand("UPDATE NewInvoice_1 SET Terms = " + CBL_Terms.EditValue.ToString() + ", InvoiceDate= " + CBL_Date.DateTime + ",  OurQuote='" + TXE_OurQuote.Text + "', SalesPerson=" + CBL_Sales_Person.EditValue.ToString() + ", CustomerName=" + CBL_Customer_Name.EditValue.ToString() + ", OrderNumber='" + TXE_Order_Number.Text + "', InvoiceAddress='" + TXE_Invoice_Address.Text + "', DeliveryAddress='" + TXE_Delivery_Address.Text + "', WholeDiscountP=" + Convert.ToDecimal(TXE_FlatDiscountP.Text) + ", WholeDiscountA=" + Convert.ToDecimal(TXE_FlatDiscountA.Text) + ", ShippingP=" + Convert.ToDecimal(TXE_ShippingPercentage.Text) + ", ShippingA=" + Convert.ToDecimal(TXE_ShippingAmount.Text) + ", Price=" + Convert.ToDecimal(TXE_SubTotal.Text) + ", Discount=" + Convert.ToDecimal(TXE_Discount.Text) + ", Tax=" + Convert.ToDecimal(TXE_Tax.Text) + ", Shipping=" + Convert.ToDecimal(TXE_Shipping.Text) + ", GrandTotal=" + Convert.ToDecimal(TXE_GrandTotal.Text) + ", TaxforDisc=" + barCheckItem1.Checked + ",   DiscountType='" + selectedItem + "', ShippingBy='" + TXE_Shipping_By.Text + "',ShipReferenceNo='" + TXE_Reference_No.Text + "', IsInsured=" + CBX_Is_Insured.Checked + ", Notes='" + TXE_Notes.Text + "', DueDate=" + CBL_DueDate.DateTime + ", AmountinWords='" + TXE_AmountinWords.Text + "' WHERE InvoiceId=" + TXE_Unvisible.Text, conn);
top.ExecuteNonQuery();
Thank in advance.
Posted
Comments
[no name] 9-Apr-14 9:06am    
Change your query to a proper parameterized query, like you have already been told and your problem might just go away all by itself.
Sergey Alexandrovich Kryukov 9-Apr-14 9:23am    
What error?
—SA

This way of writing queries is wrong from the very beginning. You are composing a query concatenating strings taken from UI. Among other problems, this way, you invite a well-known exploit called SQL injection. This is how:
http://xkcd.com/327[^].

For further detail, please see my past answers:
EROR IN UPATE in com.ExecuteNonQuery();[^],
hi name is not displaying in name?[^].

This is what you need to do: http://msdn.microsoft.com/en-us/library/ff648339.aspx[^].

—SA
 
Share this answer
 
Comments
Maciej Los 9-Apr-14 10:34am    
Agree, but... (i hate this kind of argumentation) Sergey, you're talking about SQL Injection, but i don't see ASP.NET tag ;)
Well, it is good idea to warn OP about SQL Injection. 5ed!
See, my updated answer.
Volynsky Alex 9-Apr-14 13:59pm    
good answer Sergey Alexandrovich!
Sergey Alexandrovich Kryukov 9-Apr-14 14:06pm    
Thank you, Alex.
—SA
Volynsky Alex 9-Apr-14 14:08pm    
You're welcome)))
For any date field, use # arround the date.

Replace:
C#
DueDate=" + CBL_DueDate.DateTime + "

With:
C#
DueDate=#" + CBL_DueDate.DateTime + "# ..."


Date should be formatted in ISO standard: MM/dd/yyyy
where:
MM - month
dd - day
yyyy - year

Have a look here: Examples of query criteria[^]
Examples of using dates as criteria in Access queries[^]

[EDIT]
As a rule, you need to use parameterized queries. See my past answers[^].
[/EDIT]
 
Share this answer
 
v3
Comments
Sergey Alexandrovich Kryukov 9-Apr-14 9:30am    
Why encouraging concatenation? Besides, required format can be the part of the query. And, much more importantly, using string representation is wrong in principle. It should be a parameter of a parametrized query.

Please see my answer.

—SA
Bernhard Hiller 10-Apr-14 3:21am    
Oh be careful! Do not trust the date format. with different locales between client and server, you'll run into trouble. We had to experience that: MS Access prefers one format, but it does switch to a different format when the prefered one does fails to parse the DateTime - really, it used both dd.MM.yyyy (e.g. Germany) and MM/dd/yyyy, just depending on the string...
Maciej Los 10-Apr-14 5:59am    
Bernard, thank you for your comment. As per my experince, using MM/dd/yyyy date format direct in Access queries quaranties proper results no matter of regional settings. I think that the database engine (JET/ACE) prefers this format. Of course, GUI displays date in format which corrensponds to regional settings.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900