Concatenated String Not Working in SQL

Question

C# ... this works

string sql = "SELECT * FROM STATEMENTS WHERE [idTrip] = '2015Q15'";
command.CommandText = sql;

But when I try to replace the '2015Q15' with a variable as follows, it does not work

string sql = "SELECT * FROM STATEMENTS WHERE [idTrip] = '" + myVariable + "'";
command.CommandText = sql;

When I run through line by line, I can see that the str sql looks fine but it does not select any records

Do you have any rows that meet the criteria of your query? BTW, you should be using parameterized queries instead of building up a string like this and executing it. That is a classic example of sql injection. — Sean Lange
– Sean Lange, Commented Feb 19, 2015 at 14:41
Maybe there are no records where idTrip is that value you provide? — Roy Dictus
– Roy Dictus, Commented Feb 19, 2015 at 14:42
Use parameterized queries! Not the solution to your problem, but a must! — Thorsten Dittmar
– Thorsten Dittmar, Commented Feb 19, 2015 at 14:42

jkl · Accepted Answer · 2015-02-19 15:06:24Z

4

Try this:

command.CommandText = "SELECT * FROM STATEMENTS WHERE [idTrip] = @idTrip";
command.Parameters.AddWithValue("@idTrip", myVariable);

answered Feb 19, 2015 at 15:06

jkl

6752 gold badges10 silver badges24 bronze badges

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

2 Comments

TKO Over a year ago

is this what is meant by a parameterized query?

jkl Over a year ago

@TKO yes. it is a parameterized query.

JThome · Accepted Answer · 2015-02-19 15:06:12Z

1

Aside from the danger for SQL injection...

Do you have checked for leading or trailing white spaces in myVariable? Use .Trim() on myVariable to rule this out. I assume you have checked the content of myVariable to be correct otherwise?

If still no results are returned: Trace the SQL that is actually arriving at the server with the SQL Server profiler. Capture the command, execute it in SQL Server Management Studio to make sure it executes & yields the expected results.

Is your database configured to be case-sensitive? Could this be the reason? If the letter casing in your myVariable content is not exactly the same as in your table it could have this effect also.

answered Feb 19, 2015 at 15:06

JThome

663 bronze badges

Collectives™ on Stack Overflow

Concatenated String Not Working in SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related