SqlCommand Parameter not working

Question

When I execute the following command in SSMS I get the results I expect -

SELECT *
FROM [Event]
WHERE Active = 'True'
AND [Name] LIKE '%P%'

i.e. All the Events whose Name contains a P are displayed

However, when I build this command in my WinForms app written in C# I always get an empty datatable.

I am using the following code -

string sqlText = "SELECT * " +
                 "FROM Event " +
                 "WHERE Active = @Active";
SqlCommand sqlCom = new SqlCommand();
sqlCom.Parameters.Add("@Active", SqlDbType.Bit).Value = active;
if (txtSearchName.Text != null && txtSearchName.Text.Length > 0)
{
    sqlText += " AND [Name] LIKE '@Name'";
    string value = "%" + txtSearchName.Text + "%";
    sqlCom.Parameters.Add("@Name", SqlDbType.VarChar).Value = value;
}
sqlText += " ORDER BY [Date] DESC;";
sqlCom.CommandText = sqlText;
DataTable table = Express.GetTable(sqlCom);
DataView view = new DataView(table);
DataTable show = view.ToTable(true, "ID", "Date", "Name");
dataEventsFound.DataSource = show;

Please note Express.GetTable is a method that simply adds a SQLConnection and uses a DataReader to fill and return a DataTable. I do not believe the fault lies in that Method as it is used hundreds of times throughout this and other applications I have written.

I think the error is something to do with the Command Text and the @Name Parameter, but I can't determine exactly what the problem is, and why my DataTable is always empty.

Consider C# - the difference between Console.WriteLine(id) and Console.WriteLine("id") - this is exactly the same in TSQL for the difference between LIKE @Name and LIKE '@Name' — Marc Gravell
– Marc Gravell, Commented Dec 3, 2013 at 11:16
looks like you should remove ' and write sqlText += " AND [Name] LIKE @Name"; — Guru Stron
– Guru Stron, Commented Dec 3, 2013 at 11:16
Thanks Marc - that will definitely help me remember in future. — PJW
– PJW, Commented Dec 3, 2013 at 11:32

gzaxx · Accepted Answer · 2013-12-03 11:14:51Z

10

Remove single quote from between @Name parameter.

sqlText += " AND [Name] LIKE @Name";

answered Dec 3, 2013 at 11:14

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PJW Over a year ago

Ah nice and easy thank you - will mark as answer soon as the site lets me

vahnevileyes · Accepted Answer · 2013-12-03 11:23:22Z

0

After this line : sqlCom.CommandText = sqlText;

You should add this line: sqlCom.ExecuteNonQuery();

answered Dec 3, 2013 at 11:23

vahnevileyes

4153 silver badges6 bronze badges

Collectives™ on Stack Overflow

SqlCommand Parameter not working

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related