IN SQL Query Error ""Incorrect syntax near '0)'." in c#

Question

SqlConnection con = new SqlConnection(@"Data Source=HAMMAD2-PC\SQLEXPRESS;Initial Catalog=StockManagement;Integrated Security=True");
con.Open();

SqlCommand cmd = new SqlCommand(@"INSERT INTO [StockManagement].[dbo].[Product] ([ProductID], [ProductName], [SalePrice], [PurchasePrice], [Status])
 VALUES ('" + pcodetxt.Text + "','" + pnametxt.Text + "','" + rtlpricetxt + "','" + purpricetxt.Text + "','" + statuscbox.SelectedIndex+")'",con);

cmd.ExecuteNonQuery();
con.Close();

This code causes an error

Incorrect syntax near '0)'

What is the solution?

I'm using Visual Studio 2012 and SQL Server

You have missed closing quotes try this ('"+pcodetxt.Text+"','"+pnametxt.Text+"','"+rtlpricetxt+"','"+purpricetxt.Text+"','"+statuscbox.SelectedIndex+"')",con); — Abhishek, Jul 21 '18 at 12:19
Use parameterized queries by placing the code in a `stored proc` — hiFI, Jul 21 '18 at 12:21
Simple debug 101: Copy string into variable, look at generated string. Paste into SSMS (SQL Server Managemen Studio). THis is not C# related at all, except "you make mistake putting a string together". — TomTom, Jul 21 '18 at 12:45

score 3 · Answer 1 · edited Jul 22 '18 at 04:32

3

There wouldn't be such an error if you have used parameters, plus you would be protected from "SQL injection attack". ie:

using (SqlConnection con = new SqlConnection(@"server=.\SQLEXPRESS;Initial Catalog=StockManagement;Integrated Security=True"))
using (SqlCommand cmd = new SqlCommand(@"INSERT INTO [StockManagement].[dbo].[Product]
   ([ProductID]
   ,[ProductName]
   ,[SalePrice]
   ,[PurchasePrice]
   ,[Status])
VALUES
   (@pid, @pname, @salePrice, @purPrice, @status)", con))
{
    cmd.Parameters.Add("@pid", SqlDbType.Int).Value = int.Parse(pcodetxt.Text);
    cmd.Parameters.Add("@pname", SqlDbType.VarChar).Value = pnametxt.Text;
    cmd.Parameters.Add("@salePrice", SqlDbType.Money).Value = decimal.Parse(rtlpricetxt);
    cmd.Parameters.Add("@purPrice", SqlDbType.Money).Value = decimal.Parse(purpricetxt.Text);
    cmd.Parameters.Add("@status", SqlDbType.Int).Value = statuscbox.SelectedIndex;

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close(); // This is not needed: it is done by the implicit Dispose when exiting the using block
}

edited Jul 22 '18 at 04:32

Richardissimo

5,596
2
18
36

answered Jul 21 '18 at 12:33

Cetin Basoz

22,495
3
31
39

You should be using a `using (....) { .... }` block for the `SqlCommand` as well! – marc_s Jul 21 '18 at 12:43
And there's no need to close the connection, which is done by the implicit Dispose when exiting the using block. And you could probably help the OP by sticking with their connection string, to avoid them thinking there was something subtlely wrong with it. – Richardissimo Jul 21 '18 at 20:18
@Richardissimo, there was something subtlety wrong with it. It doesn't always work if you write it with machine name. Dot works. – Cetin Basoz Jul 21 '18 at 20:25
@CetinBasoz Thanks for explaining that... I haven't heard of that before. Maybe consider explaining that in your answer; but it's not a problem this user is suffering from. – Richardissimo Jul 21 '18 at 20:33

score 2 · Answer 2 · answered Jul 21 '18 at 12:18

The error is because you're missing a closing quote in your sql statement, but you shouldnt be creating your statement manually with string manipulation in any case - this is very error prone, and extremely unsafe!

Use declared parameters instead. See What's the best method to pass parameters to SQLCommand?

Vahid Farahmandian · Answer 3 · 2019-05-22T05:55:29.200

0

Incorrect Syntax near X, tries to show you that there is some thing wrong just before or after the X.

In your query you have placed ' in wrong place

So just rewrite it as below:

SqlCommand cmd = new SqlCommand(@"INSERT INTO [StockManagement].[dbo].[Product] ([ProductID], [ProductName], [SalePrice], [PurchasePrice], [Status])
 VALUES ('" + pcodetxt.Text + "','" + pnametxt.Text + "','" + rtlpricetxt + "','" + purpricetxt.Text + "','" + statuscbox.SelectedIndex+"')",con);

Note: Using following code you put your self in the scope of the SQL Injection vulnerability, so you should always try to write the code as @CetinBasoz posted or other similar methods that makes you secure against the similar vulnerabilities.

edited May 22 '19 at 05:55

answered Jul 21 '18 at 12:49

Vahid Farahmandian

6,081
7
42
62

@CetinBasoz problem is not with the coding style. If he/she asks about the right or secure code, we can pay attention to your comment, other wise your comment is out of the scope of the question – Vahid Farahmandian Jul 21 '18 at 12:51
@CetinBasoz you are 100% right and I DO agree with you. But I am trying to tell you that there is different options to solve the abovementioned problem. one is yours and the other is mine etc. Your code is secure and etc...And my code is in the form of the question and I've just tried not to change the code. I think there is no need to down vote! – Vahid Farahmandian Jul 21 '18 at 12:56
@CetinBasoz OK, you can mention it as a comment to the given code. Good Luck ;-) – Vahid Farahmandian Jul 21 '18 at 12:58
@CetinBasoz I have updated my answer and put your note inside it – Vahid Farahmandian Jul 21 '18 at 13:00

IN SQL Query Error ""Incorrect syntax near '0)'." in c#

3 Answers3