multi value search

HI,

I have a three drop-down menus for City, Area, Zip.

I am using 'like' in my search query which is:

select col_nam from table where city like %txtcity% AND area like %txtarea% AND zip like %txtzip%

But, since AND is being used, if any of the parameter is NOT given, the result is Zero and If I use OR instead, it still not work if only two values are selected. For example,

If a user has selected city: A, area: X , then query using OR will return city matching A, area matching X BUT it will also return the area which should NOT be included.


So, what should be done here. Do you think that 'like' should be used here ?
Or any other suggestion?


Thanks
Yasir

You have to construct the SQL dynamically.

i.e.

sql = "select col_nam from table where 1=1 "; // so we can just use AND
if ( txtcity != null ) {
sql += "and city like '%" + txtcity + "%' ";
}
if ( txtArea != null ) {
sql += "and area like '%" + txtarea + "%' ";
}
if ( txtZip != null ) {
sql += "and zip like '%" + txtzip + "%' ";
}

Use dynamic SQL in combination with dynamic Command Parameters for better application security.