how to concatenate string for query

Question

0.00/5 (No votes)

See more:

Hello
i have this code in web page
my problem:
there is 2 records in the database one of it have ip=99 and the other one have ip=000099 as varchar
but when i requset ip=99 i get record for ip=000099
i tried to concatenate the command text using

VB

string.Concat();
string.Format();
string.Append();
stringBuilder

but no change
my code:

C#

try
                {
                    OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
                    myOleDbConnection.Open();
                    OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
                  string str="99";
                    myOleDbCommand.CommandText = "select sale_price,postion from item where ip=" + str;
                    OleDbDataReader dr = myOleDbCommand.ExecuteReader();
                    dr.Read();
///////////get results
 dr.Close();
                    myOleDbConnection.Close();

                }
                catch 
                {
                    
                }

Posted 26-Mar-14 3:21am

M. Daban

Add a Solution

3 solutions

Solution 3

You have forgotten to enclose your str in the where clause with single quote. But you should not inject variable directly into the sql query. Instead, you should use parameterized query[^] to prevent sql injection[^].

Posted 26-Mar-14 3:50am

Peter Leow

Updated 26-Mar-14 3:54am

v2

Solution 2

"i tried to concatenate the command text"

DON"T!!!!

Use a parameterized query. Every time. It always works.

Posted 26-Mar-14 3:42am

PIEBALDconsult

Comments

M. Daban 26-Mar-14 9:47am

can you give an example please

Raul Iloc 27-Mar-14 7:53am

The parameterized string should be used only in the case when the SQL is made by using user input, but here is not the case!

PIEBALDconsult 27-Mar-14 9:10am

No. Every time. It's not just about SQL injection; there are actions that can be performed only with parameters, and parameters can improve performance as well.
Concatenation should be avoided except for when parameters won't work -- for example specifying the table name, or a TOP value.
Parameters should always be your first choice, not your last.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raul Iloc · Accepted Answer · 2014-03-26T03:26:00

Solution 1

Because your IP type is varchar, you should correct the SQL in this way:

C#

myOleDbCommand.CommandText = string.Format("select sale_price,postion from item where ip='{0}'", str);

Posted 26-Mar-14 3:26am

Raul Iloc

Updated 26-Mar-14 3:29am

v2