CODEWITHSUNDEEP
c#asp.netsql-serverdatabase
seeker
seeker

Reputation: 3333

SqlCommand parameters aren't working properly

Consider the following code:

SqlConnection conn = new SqlConnection(@"connection string");

SqlCommand ourCommand = new SqlCommand(String.Format(
    @"SELECT Field1,Field2,Field3 FROM Tbl WHERE Field1 LIKE '@FL1'"), conn);
ourCommand.CommandTimeout = 6000;

ourCommand.Parameters.AddWithValue("@FL1", TextBox1.Text);

SqlDataAdapter adapter = new SqlDataAdapter(ourCommand);
DataTable dt = new DataTable();

conn.Open();
adapter.Fill(dt);
GridView1.DataSource = dt;

GridView1.DataBind();

The problem is that datatable is empty - which means the command either was not executed or incorrect query was generated. What am I missing? Connection and query are valid. The command without parameter also works. Database engine is SQL Server 2008 R2

Upvotes: 0

Views: 2467

Answers (6)

Renato Magalhães
Renato Magalhães

Reputation: 1

You need to force concatenation with percent % -> 

SELECT Field1,Field2,Field3 FROM Tbl WHERE Field1 LIKE **'%'** + @FL1 + **'%'**

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500525

You've put the parameter name in quotes, so it's being treated as a value, not as a parameter. Try this instead:

"SELECT Field1,Field2,Field3 FROM Tbl WHERE Field1 LIKE @FL1"

(I'd actually expect your existing code to throw an exception given that you're supplying more parameters than are in the SQL...)

As noted in comments, you don't need the string.Format either.

Upvotes: 6

Oded
Oded

Reputation: 499002

Your query is not a well formatted query.

Instead of:

String.Format(
    @"SELECT Field1,Field2,Field3 FROM Tbl WHERE Field1 LIKE '@FL1'")

Use: 

"SELECT Field1,Field2,Field3 FROM Tbl WHERE Field1 LIKE @FL1"

Note that there is no need for string.Format, nor to enclose the parameter name in '' and since there is nothing to escape in the string, no need for it to be a verbatim string literal (using the @), as Jon commented.

Upvotes: 5

Phillip Schmidt
Phillip Schmidt

Reputation: 8818

Well, first, you don't need the string.format or the @'s (you have no escaped chars, your string are on one line, and you aren't using parameterized strings, so there's no reason for either one) Then, you don't need the quotes around @FL1. SqlCommand parses entire string for @'s, not for substrings delimited by quotes. The final code, I believe, should look like this:

SqlCommand ourCommand=new SqlCommand("SELECT Field1,Field2,Field3 FROM Tbl WHERE Field1 LIKE @FL1",conn);

Everything else I think you can keep the same.

Upvotes: 1

Johnny_D
Johnny_D

Reputation: 4652

If you are using parameters, you don't need to use single quotes. Take a look on lesson here.

Upvotes: 1

Jason
Jason

Reputation: 3960

Try changing the line to this (remove the tick marks sorrounding @FL1)

SqlCommand ourCommand=new SqlCommand(
             "SELECT Field1,Field2,Field3 FROM Tbl WHERE Field1 LIKE @FL1",conn);

Upvotes: 3

Related Questions