Must declare the scalar variable - SQL parameter for filling DataGridView

Question

I found similar questions, but none of the answers have helped me or apply to me.

I'm using Visual Studio 2013, C# and database engine called T-SQL.

The SQL command works just fine if I replace the parameter with a concrete value. But I need the parameter there.

I get this error:

Must declare the scalar variable "@Collection1".

On this line:

myydata.Fill(myytab);

No matter what I do, that @Collection1 just doesn't want to be declared.

string stringcommand = "select Name, Collection, Text from Card_List where Collection IN" +
                       "(select Shortcut from Collections where Collection Like @Collection1)";

SqlConnection myConnection = new SqlConnection(stringconnection);
SqlCommand myCommand = new SqlCommand(stringcommand, myConnection);

// Tried to declare parameter 5 times here, unsuccessfully
myCommand.Parameters.Add(new SqlParameter("Collection1", string1));
myCommand.Parameters.Add("@Collection1", SqlDbType.NVarChar).Value = string1;
myCommand.Parameters["@Collection1"].Value = string1;
myCommand.Parameters.AddWithValue("@Collection1", string1);
SqlParameter Param1 = myCommand.Parameters.Add("@Collection1", SqlDbType.NVarChar);
Param1.Value = string1;

SqlDataAdapter myydata = new SqlDataAdapter();
myydata.SelectCommand = new SqlCommand(stringcommand, myConnection);
myConnection.Open();
DataTable myytab = new DataTable();
myydata.Fill(myytab);
BindingSource bsour = new BindingSource();
bsour.DataSource = myytab;
dataGridView1.DataSource = bsour;
myydata.Update(myytab);
myConnection.Close();

Answer 1

You want the SqlCommand myCommand you set up to be used as the myydata.SelectCommand, like:

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

The way you have it with myydata.SelectCommand = new SqlCommand(stringcommand, myConnection);, that command has the @Collection1 in the command, but without having any parameters attached you get the Must declare the scalar variable "@Collection1". error.