C# SQL command variable vs literal

Question

I'm writing a database CRUD application, and I'm stuck when trying to pass in a variable to the Database. When I pass in a literal value to the database, the application works fine, such as the code below:

myCommand.CommandText = "Select * From Customers Where CustomerID = 'ALFKI'";

However, when I try to make a variable from the text within a textbox, the application will not do anythying. It does not throw an error, it just sits idle. Here is the code i used for my variable:

string searcher = Convert.ToString(txtSearch);

...

myCommand.CommandText = "Select * From Customers Where CustomerID = " + "'" + searcher + "'";

I've tried different CustomerIDs in the textbox. I also tried re-arranging the way the single and double quotation marks are used, but to no avail. Could anyone help me with this?

Thanks

**

Thanks Jon Skeet! Your method worked perfectly. Darren, thank you for your input as well. I will be brushing up on parameterized sql statements very soon, thanks for giving me another frontier to explore.

Be aware that this pattern is ripe for SQL injection attacks. — crthompson
– crthompson, Commented Dec 4, 2013 at 21:56
Don't try to quote the values yourself at all - use parameterized SQL. I'm somewhat concerned about the diagnostics of "just sits idle" though... and it's not clear why you're using Convert.ToString instead of what I'd expect to be txtSearch.Text. — Jon Skeet
– Jon Skeet, Commented Dec 4, 2013 at 21:57
Oh good.. no need to code properly then... for practicing good coding techniques. ;) — crthompson
– crthompson, Commented Dec 4, 2013 at 21:57

Darren Kopp · Accepted Answer · 2013-12-04 21:56:45Z

4

What you should be doing is using parameterized sql statement.

Try out the following

myCommand.CommandText = "Select * From Customers Where CustomerID = @CustomerId";
myCommand.Parameters.AddWithValue("@CustomerId", searcher);

answered Dec 4, 2013 at 21:56

Darren Kopp

77.8k9 gold badges81 silver badges93 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 10:25:40Z

1

This line

string searcher = Convert.ToString(txtSearch);

Is likely setting the variable searcher to System.Windows.Forms.TextBox, Text: Foo

You should use the text property instead

string searcher = txtSearch.Text;

Still you should do as Darren Kopp Suggested and use a parameterized query to address a potential SQL injection attack. It also makes quoting easier

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Dec 4, 2013 at 22:00

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Collectives™ on Stack Overflow

C# SQL command variable vs literal

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related