CODEWITHSUNDEEP
c#sql-server
Matthew
Matthew

Reputation: 37

Incorrect syntax near Parameter

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);

Upvotes: 2

Views: 2203

Answers (3)

Bozhidar Stoyneff
Bozhidar Stoyneff

Reputation: 3634

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);
}

Upvotes: 0

krillgar
krillgar

Reputation: 12815

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);
    }
}

Upvotes: 4

Nino
Nino

Reputation: 7115

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);

Upvotes: 1

Related Questions