SqlCommand parameters aren't working properly

Question

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

Try removing the single quotes from around the parameter '@FL1' --> @FL1 — Phil Murray
– Phil Murray, Commented Aug 2, 2012 at 14:43
Do you get results when running the same query (manually adding the value for the parameter) from the SQL Management Studio? — Gerald Versluis
– Gerald Versluis, Commented Aug 2, 2012 at 14:44
What data are you passing from TextBox1.Text? Is it something like "%sometext%"? I.e. if you execute query by itself with the same parameter (in SSMS for example) - does it return results? — Comfortably Numb
– Comfortably Numb, Commented Aug 2, 2012 at 14:44

Jon Skeet · Accepted Answer · 2012-08-02 14:50:00Z

6

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.

edited Aug 2, 2012 at 14:50

answered Aug 2, 2012 at 14:43

Jon Skeet

1.5m892 gold badges9.3k silver badges9.3k bronze badges

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

5 Comments

Oded Over a year ago

And that string.Format doesn't have an overload that only takes a single parameter...

seeker Over a year ago

Thanks to all, that's correct. One more question: will such usage of parameters protect from SQL injection?

Jon Skeet Over a year ago

@Oded: Yes it does - because of the params array. String.Format("foo") is equivalent to String.Format("Foo", new object[])

Jon Skeet Over a year ago

@seeker: Yes, it's fine - the value isn't part of the SQL, hence there's no injection risk.

Oded Over a year ago

@JonSkeet - I see. Didn't consider that the compiler will send an empty array if nothing was supplied...

Oded · Accepted Answer · 2012-08-02 14:49:47Z

5

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.

edited Aug 2, 2012 at 14:49

answered Aug 2, 2012 at 14:44

Oded

501k102 gold badges899 silver badges1k bronze badges

4 Comments

seeker Over a year ago

If parameter will be equal to "'" (apostrophe) then the exception will be thrown and that also mean that it's possible to perform sql injection. How to protect that?

Oded Over a year ago

@seeker - No, it will not be vulnerable. Try it. The ADO libraries will escape the single quotes and you will not have a problem.

seeker Over a year ago

Yes, that's true. And one more question: what is the best way to store connection strings? In application as string or in web.config?

Oded Over a year ago

@seeker - web.config is the standard. It has a connectionStrings section just for this. And it is then configurable, which most application do need.

Jason · Accepted Answer · 2012-08-02 14:44:11Z

3

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

answered Aug 2, 2012 at 14:44

Jason

3,9801 gold badge24 silver badges48 bronze badges

2 Comments

Oded Over a year ago

Drop the string.Format and you are there.

Jason Over a year ago

@Oded ha! was trying to see if anyone was paying attention ;) didn't even notice that myself :D fixed

Johnny_D · Accepted Answer · 2012-08-02 14:45:02Z

1

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

answered Aug 2, 2012 at 14:45

Johnny_D

4,6524 gold badges36 silver badges65 bronze badges

Comments

Phillip Schmidt · Accepted Answer · 2012-08-02 14:46:43Z

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.

ProGM · Accepted Answer · 2014-01-15 19:03:25Z

0

You need to force concatenation with percent % ->

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

edited Jan 15, 2014 at 19:03

ProGM

7,1284 gold badges36 silver badges54 bronze badges

answered Jan 15, 2014 at 18:43

Renato Magalhães

1

Collectives™ on Stack Overflow

SqlCommand parameters aren't working properly

6 Answers 6

5 Comments

4 Comments

2 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

4 Comments

2 Comments

Comments

Comments

Comments

Linked

Related