CODEWITHSUNDEEP
c#mysqlsql
Massoud
Massoud

Reputation: 387

where statement doesn't work correctly in c#

I am using MYSQL in my c# app.

There are some string values in mysql table and there is a textbox that client can write in that and then I use this statement to show the results: 

"SELECT ID,METER FROM DB.TABLE1 WHERE METER >= '" +TEXT1.text.tostring+"'";

But for Example, if the client write 400 in that textbox, the results are like this: 

50,400,500,600,50,500

But we know that 50 is not bigger than 400!!!
And then I used this code: 

"SELECT ID,METER FROM DB.TABLE1 WHERE METER <= '" +TEXT1.text.tostring+"'";

If the client write 400 in that textbox, the results are like this: 

300,150,100,250;

50 is not shown!!!

Can you please help me what should I do???

Upvotes: 0

Views: 161

Answers (5)

FantasticJamieBurns
FantasticJamieBurns

Reputation: 3004

You can cast a string to a number in MySQL:

var sql = @" SELECT ID, METER FROM DB.TABLE1 WHERE CAST(METER AS UNSIGNED) >= " + Convert.ToInteger(TEXT1.text.ToString());

There are all kinds of oddities to consider when dealing with numbers as strings in MySQL.

I suggest reading: https://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

As Jon has suggested, a better solution would be to ALTER the table schema to use a numeric column datatype and use SQL parameters in your code, especially as you seem to be dealing with user input.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1499730

50 is "bigger" than 400 when you treat them as strings, so I suspect that's what you're doing.

First: never, never, never build SQL like this. It's susceptible to SQL injection attacks, it leads to error-prone conversions, and it makes your code harder to read.

Instead, use parameterized SQL. In this case you'll want SQL of something like:

SELECT ID,METER FROM DB.TABLE1 WHERE METER >= @Parameter

and then set the @Parameter parameter in the parameter collection - parsing it as an integer (or whatever type is suitable for the values you're trying to represent) first.

Next: check the type of METER in your schema. If it's varchar or some similar text type, you need to fix that. It should be a suitable numeric type if you want to treat the values as numbers.

Upvotes: 2

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Text comparison isn't numeric comparison. To avoid this misleading behavior and sql injection risk you must use parametrized queries.

Upvotes: 0

Elixir Techne
Elixir Techne

Reputation: 1856

Convert user input to Integer and then use the integer value in your query.

SELECT ID,METER FROM DB.TABLE1 WHERE METER >= Convert.ToInteger(TEXT1.text.ToString()) ;

Upvotes: 0

Vijay
Vijay

Reputation: 1034

< and > operator will work perfeclty on numeric values as you have added single quotes to the values you have passed, it may be causing the issue. try this

"SELECT ID,METER FROM DB.TABLE1 WHERE METER >= "+TEXT1.text.tostring;

Upvotes: 0

Related Questions