0

hey I'm trying to run this query:

command.UseSqlCommand("INSERT INTO DisplayOrders Values ('" + OrderId.Text + "','" + "(SELECT ProductId FROM Products WHERE ProductName =N'" + listBox1.Text + "')','" + listBox1.Text + "','" + OrderQuantity.Text + "','" + TotalCost.Text + "')");

now the command.UseSqlCommand is just running the query, but I keep getting this error: incorrect syntax near 'intel'

(intel is the 'ProductName' (that I'm getting from here:

SELECT ProductId FROM Products WHERE ProductName =N'" + listBox1.Text + "'

Edit : this is the value of the command , (getting the 'incorrect syntax near 'intel')

INSERT INTO DisplayOrders 
Values ('2', '(SELECT ProductId FROM Products WHERE ProductName =N'Intel Quad Core i5 3470 3.2Ghz 6MB Tray')','Intel Quad Core i5 3470 3.2Ghz 6MB Tray','1','900')"
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ShmuelCohen
  • 472
  • 3
  • 9
  • 17
  • 2
    1) This is open to SQL injection and 2) split it into a `string`, take it's value, run that on the database yourself, see the result. – Arran Apr 10 '13 at 09:59
  • 3
    Don't do this. Please. Use parameterized SQL to avoid [SQL injection attacks](http://bobby-tables.com). That may well fix your immediate problem too, but it will *definitely* improve the security of your app. – Jon Skeet Apr 10 '13 at 10:00
  • What's that N in WHERE ProductName =N'" + listBox1.Text + "')` all about? Possibly move the single quote one space left so it encompasses the N as well. – Ash Apr 10 '13 at 10:00
  • N is for Unicode on SQL Server Mr @Ash :) – Obama Apr 10 '13 at 10:01
  • Hey thanks for concern , but this is a school project and no one test me for SQLI . – ShmuelCohen Apr 10 '13 at 10:02

4 Answers4

3

As others have stated you should use Parameterised queries which will overcome this issue. But to answer your question "as-is"...

You need to double-close your single quote. Best way to see this is store into a string and debug / write to trace. Example of how to double-close query here: How to insert text with single quotation sql server 2005

As-is your query string will contain something like this:

INSERT INTO DisplayOrders Values ('1234','(SELECT ProductId FROM Products WHERE ProductName =N'Fred')'...

But it should really contain something like this (notice the '''):

INSERT INTO DisplayOrders Values ('1234','(SELECT ProductId FROM Products WHERE ProductName =N'''Fred''')'...

Otherwise you are closing the INSERT Values not the SELECT statement.

Community
  • 1
  • 1
Belogix
  • 8,129
  • 1
  • 27
  • 32
2

Just remove the single quotes around the SELECT statement

command.UseSqlCommand("INSERT INTO DisplayOrders Values ('" + OrderId.Text + "'," + 
         "(SELECT ProductId FROM Products WHERE ProductName =N'" + listbox.Text +"'), " +
         "'" + listBox1.Text + "','" + OrderQuantity.Text + "','" + TotalCost.Text + "')");

But this code will fail if any of your listbox items contains a single quote.
I have read your comment about SQL Injection not been an issue here, but it is a good habit to use even for schoolworks. At least change to

string itemName = listBox1.Text.Replace("'", "''");
command.UseSqlCommand("INSERT INTO DisplayOrders Values ('" + OrderId.Text + "'," + 
         "(SELECT ProductId FROM Products WHERE ProductName =N'" + itemName +"'), " +
         "'" + itemName + "','" + OrderQuantity.Text + "','" + TotalCost.Text + "')");
Steve
  • 213,761
  • 22
  • 232
  • 286
0

Need replace to this 

  command.UseSqlCommand("INSERT INTO DisplayOrders Values ('" + OrderId.Text + "'," + "(SELECT IdProduct FROM Products WHERE ProductName =N'" + listBox1.Text + "'),'" + listBox1.Text + "','" + OrderQuantity.Text + "','" + TotalCost.Text + "')");
ShmuelCohen
  • 472
  • 3
  • 9
  • 17
-2
SELECT ProductId FROM Products WHERE ProductName =N'" + listBox1.Text + "'

will return a table so it is wrong to use it inside insert statement.

instead of using this , execute this command alone then get the returned value and then execute the insert statement alone

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jo9876
  • 13