CODEWITHSUNDEEP
c#sqlwinforms
PJW
PJW

Reputation: 5397

SqlCommand Parameter not working

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.

Upvotes: 3

Views: 2607

Answers (2)

vahnevileyes
vahnevileyes

Reputation: 415

After this line : sqlCom.CommandText = sqlText;

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

Upvotes: 0

gzaxx
gzaxx

Reputation: 17590

Remove single quote from between @Name parameter.

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

Upvotes: 10

Related Questions