c# between in where condition not work

Question

   String start_cd;
   String end_cd;
   int time_start_int;
   int time_end_int;
    opencon();

     SqlCommand res = new SqlCommand("SELECT ID,Available,Type," + start_cd + "," + end_cd + " FROM " + going + " WHERE " + start_cd + "!=0 or " + end_cd + "!=0 and " + start_cd + " >= " + time_start_int + " and " + start_cd + " <= " + time_end_int + "", con);
    SqlDataAdapter sda_res = new SqlDataAdapter(res);
    DataTable dt_res = new DataTable();
    sda_res.Fill(dt_res);

    listBox1.DataSource=dt_res;
    listBox1.DisplayMember="ID";

    listBox2.DataSource = dt_res;
    listBox2.DisplayMember = start_cd;

i getting no errors but listbox show unfiltered values(i want geting values time_start_int between time_end_int )

People have already told you in your previous question, you concatenating strings to create queries is old fashioned and risky. — Itay, Feb 16 '14 at 08:07
Even if you insist on writing code with SQL injection problems please take time and edit your sample so there is no scrolling necessary (it is unnecessarily hard to scroll through your sample to try to guess what is wrong). — Alexei Levenkov, Feb 16 '14 at 08:26

score 0 · Answer 1 · edited May 23 '17 at 11:49

You'll need to compare time_start_int and time_end_int with start_cd in separate expressions like this

SqlCommand res = new SqlCommand("SELECT ID,Available,Type," + start_cd + "," + 
    end_cd + " FROM " + going + 
   " WHERE " + start_cd + "!=0 or " + end_cd + "!=0 and " + 
   time_start_int + " <= " + start_cd + " and " +
   start_cd + " <= " + time_end_int + "", 
   con);

Keep in mind that using strings to concatenate SQL statements make your code vulnerable to SQL injection attacks. You may refer to Algorithm to avoid SQL injection on MSSQL Server from C# code? to get some hints on how to avoid SQL injection attacks.

Hamidreza · Answer 2 · 2014-02-16T10:53:22.810

0

First of all i use parentheses for or, because and will calculate first and maybe it cause to remove all the filters and in second part i write time_start_int + " <= " + start_cd + " and " + start_cd + " <= " + time_end_int because we need start_cd being between time_start_int and time_end_int:

SqlCommand res = new SqlCommand("SELECT ID,Available,Type," + start_cd + "," + 
    end_cd + " FROM " + going + 
   " WHERE (" + start_cd + "!=0 or " + end_cd + "!=0 ) and " + 
   time_start_int + " <= " + start_cd + " and " + start_cd + " <= " + time_end_int + "", con);

edited Feb 16 '14 at 10:53

answered Feb 16 '14 at 08:11

Hamidreza

3,038
1
18
15

1

+0: while may be correct there is no explanation what you did and more importantly why. – Alexei Levenkov Feb 16 '14 at 08:27
i got error Incorrect syntax near 'FOT'.( FOT=start_cd) – sudhara Feb 16 '14 at 10:02
I hope the changes give you some clues to what i do. – Hamidreza Feb 16 '14 at 10:54

user3313598 · Answer 3 · 2014-02-16T08:14:05.630

-1

SqlCommand res = new SqlCommand("SELECT ID,Available,Type,"'+ start_cd +'","' +
        end_cd +'" FROM going  
       WHERE "'+ start_cd +'"!=0 or "'+ end_cd +'"!=0 and " + 
       time_start_int + " <= "'+ start_cd +'" <= " + time_end_int + "", con);

You missed ' (single quote) for string variable.

edited Feb 16 '14 at 08:14

answered Feb 16 '14 at 08:08

user3313598

51
5

c# between in where condition not work

3 Answers3