Incorrect syntax near Parameter

Question

I'm using Visual Studio 2013, C# and SQL Server database.

The T-SQL command works just fine if I replace the parameter with a concrete value.

I get this error on the last line of code:

Incorrect syntax near '@Collection1'.

My code:

string myCommandString = "select Name, Collection, Text from List_Card @Collection1";
SqlConnection myConnection = new SqlConnection(connectionstring);

SqlCommand myCommand = new SqlCommand(myCommandString, myConnection);
SqlDataAdapter myydata = new SqlDataAdapter();

if (comboBox1.Text != "")
{
    string1 = "where Collection IN (select Shortcut from Collections where Collection Like '" + comboBox1.Text + "')";
}
else 
{
    string1 = ""; 
}

myCommand.Parameters.Add(new SqlParameter("@Collection1", string1));
myydata.SelectCommand = myCommand;

myConnection.Open();
DataTable myytab = new DataTable();
myydata.Fill(myytab);

this is definitely invalid syntax `string myCommandString = "select Name, Collection, Text from List_Card @Collection1";` what are you intending to accomplish putting `@Collection1` after the table name? — Ousmane D., Nov 19 '17 at 21:19
i have 'where' clause in @Collection. If user selects something in comboBox, i need that 'where' clause. If user doesnt select anything in comboBox, i dont need that 'where' clause. — Matthew, Nov 19 '17 at 21:21
Parameters are used to represent **values** to the database engine. It seems that you think that a parameter _substitutes_ a string in your command text. This is a common misconception. — Steve, Nov 19 '17 at 21:27
The database engine in question is Microsoft **SQL Server**, while T-SQL (or Transact-SQL) is the "dialect" of SQL that is used by SQL Server for querying the database engine, and writing functions and procedures — marc_s, Nov 19 '17 at 21:58

score 4 · Accepted Answer · answered Nov 19 '17 at 21:24

There are several errors in your code.

First, your myCommandString:

"select Name, Collection, Text from List_Card @Collection1"

That is invalid SQL syntax (what the error is you're getting). You're not doing anything with the parameter. You need to put it as a part of a WHERE clause, but you're not using that value.

Next, you're using the SqlParameter completely incorrectly. Check out the documentation to see how to properly use it. The specific issue is that you don't assign a conditional SQL string as the second parameter. You need to conditionally append that to your query.

Finally, you should also wrap everything in using statements to properly dispose of the objects.

This should give you what you're looking for:

var myCommandString = "select Name, Collection, Text from List_Card ";

if (comboBox1.Text != "")
{
    myCommandString += " where Collection IN (select Shortcut from Collections where Collection Like '@Collection1')";
}

using (var myConnection = new SqlConnection(connectionstring))
using (var myCommand = new SqlCommand(myCommandString, myConnection))
{
    myCommand.Parameters.Add(new SqlParameter("@Collection1", string1));

    using (var myData = new SqlDataAdapter()) 
    {
        myData.SelectCommand = myCommand;
        myConnection.Open();

        var myytab = new DataTable();
        myydata.Fill(myytab);
    }
}

score 1 · Answer 2 · answered Nov 19 '17 at 21:23

Parameters don't work like that. I'm guessing you want to have same query and then dynamically add where clause if user selects something. Unfortunately, you can't do it in a way that whole where clause is parameter. You can try something like this:

string myCommandString = @"select Name, Collection, Text from 
List_Card where Collection IN 
    (select Shortcut from Collections where Collection Like '%' + @collection + '%')";
SqlConnection myConnection = new SqlConnection(connectionstring);
SqlCommand myCommand = new SqlCommand(myCommandString, myConnection);
SqlDataAdapter myydata = new SqlDataAdapter();
myCommand.Parameters.Add(new SqlParameter("@Collection1", comboBox1.Text));
myydata.SelectCommand = myCommand;
myConnection.Open();
DataTable myytab = new DataTable();
myydata.Fill(myytab);

Skipping disposal of IDisposable objects WILL lead to an unstable application, so, please, don't encourage it. — Bozhidar Stoyneff, Nov 19 '17 at 21:42

Bozhidar Stoyneff · Answer 3 · 2017-11-20T08:26:33.187

You don't specify the whole WHERE clause through a parameter. Parameters are allowed in place of ... well, parameters. If you want to add the WHERE clause based on a condition in your C# code, do this:

string myCommandString = "SELECT Name, Collection, Text FROM List_Card";
.
.
.

if (comboBox1.Text != "")
{
    myCommandString += " WHERE Collection IN (SELECT Shortcut FROM Collections WHERE Collection Like '" + comboBox1.Text + "')";
}

Also, it is super important to dispose of an object after its usage. The fastest way to do this is to eclose the code in a using statement. Finally, you should do your best to prevent SQL injection attacks. You're already on the right track with this - in case of ADO.NET, adding Parameters to your dynamic SQL queries is the right approach.

Since the SqlConnection, SqlCommand and SqlDataAdapter are all IDisposable objects your code should look like this:

string myCommandString = "SELECT Name, Collection, Text FROM List_Card";

using (var  myConnection = new SqlConnection(connectionstring))
using (var  myCommand = new SqlCommand(myCommandString, myConnection))
using (var  myydata = new SqlDataAdapter())
{
    if (comboBox1.Text != "")
    {
        myCommandString += " WHERE Collection IN (SELECT Shortcut FROM Collections WHERE Collection Like @Collection1)";
        myCommand.Parameters.Add(new SqlParameter("@Collection1", comboBox1.Text));
    }

    myydata.SelectCommand = myCommand;
    myConnection.Open();
    DataTable myytab = new DataTable();
    myydata.Fill(myytab);
}

this is string concatenation, prone to sql injection. Please don't encourage unsafe and bad way of doing queries. — Nino, Nov 19 '17 at 21:25
This is what the OP already has. The composition of the SQL statement is not a subject of that post, is it? — Bozhidar Stoyneff, Nov 19 '17 at 21:28

Incorrect syntax near Parameter

3 Answers3