ASP.NET - Multiple where clause in dynamic select comand

Asked By mahesh on 23-Mar-11 12:54 AM
hello,
 I have one column name CITY which have four cities with checkboxes now suppose user check on 2 cities than how can i pass this 2 city names in select command. Please help its urgent.

Sahil Kumar replied to mahesh on 23-Mar-11 01:00 AM
Hi,
    You dont need here multiple where clause you just need multiple conditions seperated with OR.

select * from table where ( some condition ) and ( city=1st or city=2nd )

you can pass checkbox value by chkcity.selectedValue

I hope this will help you......
Deepak Amemane replied to mahesh on 23-Mar-11 01:02 AM
are you using a SqlDataSource???
\

If not the sql query would be

select Cit1,city2 from [CityTable]

Please specify the details if this is not what you needed
mahesh replied to Sahil Kumar on 23-Mar-11 01:05 AM
Heloo Shyam,

 But than what if user checks 3 cities or say sometime checks only one city... moreover 4 is just for example actualy I have a drop down list populated from database which have many cities.
Anoop S replied to mahesh on 23-Mar-11 01:09 AM
You can't use multiple where clause in select statement, but we can define a WHERE clause with multiple conditions

You can use SQL query like this way

SELECT * FROM tablename WHERE city ='X1' OR city ='X2'

or

SELECT * FROM tablename WHERE city ='X1' AND city ='X2'
mahesh replied to Deepak Amemane on 23-Mar-11 01:11 AM
Hello Deepak,

Yes I am using SQLDatasource and details are as follows

>I have dropDown list  with city names which is populated from database.
>user can select multiple cites say there are A,B,C,D cities user can select City A as well as CITY B so want I want is that the gridview should get filtered accordingly suppose if I select A,B,C than my gridview must show records with city names A,B,C than suppose next time I Select A,B than my gridview must show records with sity name A and B...

 hope this gives you a complete picture please help me out...
Vivek Jagga replied to mahesh on 23-Mar-11 01:12 AM
Hi,

You can do this by
Select * from Tbltable where city='city1' or city='city2'
Sahil Kumar replied to mahesh on 23-Mar-11 01:15 AM
Hi Mahesh,

   That's all fine...but finally user going to click on some action to search result is it or not.

Say if user clicked on search button at that moment you need to check what are all different checkbox's are checked and form your query.

If you want result just on click of checkbox and it should update then you need to use dynamic query.

string query="select * from table where (condition1) and (
check1.checked()
{
  here you need to add this condition.
  query+="fieldname="+check1.value.tostring();
}

like this but its better to have just one search button which will help you work in efficient manner.

I hope this will help you. Please let me know if you have any more queries.....
mahesh replied to Anoop S on 23-Mar-11 01:18 AM
Hello Anoop,
But I have to pass the city name dynamicaly... means the city name can be Single or it can be multiple like I can check only one city while some other time I can check both the cities...
Mihir Soni replied to mahesh on 23-Mar-11 01:20 AM
Hello,

You can do one thing you can make your query dynamically and pass the parameters.

Get number of selected Checkbox and loop it and make your SQL query dynamically and then add Parameters as per your need.

You can write something like this.

string sql;
sql="select * from city where city=? or city=?"//This should generate dynamically
SqlCommand cmd = new SqlCommand(sql,connection);
cmd.Parameters.add(value,Type)//Add this in loop as per selection

Jem Savery replied to mahesh on 23-Mar-11 01:21 AM
Hi Mahesh,

Here is the code for you.

string strQuery = "SELECT * FROM Your_Table WHERE Your_First_Condition";

string strWhere = string.Empty;

// Now you have your checkboxes for cities.

if( chkCity1.Checked)
{
strWhere = "(City = '" + chkCity1.Text + "'";
}

if( chkCity2.Checked)
{
if (strWhere == string.Empty)
         strWhere = "(City = '" + chkCity1.Text + "'";
else
strWhere = strWhere + " or City = '" + chkCity2.Text + "'";
}

if( chkCity3.Checked)
{
if (strWhere == string.Empty)
         strWhere = "(City = '" + chkCity3.Text + "'";
else
strWhere = strWhere + " or City = '" + chkCity3.Text + "'";
}

if( chkCity4.Checked)
{
if (strWhere == string.Empty)
         strWhere = "(City = '" + chkCity4.Text + "'";
else
strWhere = strWhere + " or City = '" + chkCity4.Text + "'";
}

//  At last complete the bracket

if(strWhere != string.Empty)
{
strWhere = strWhere + ")";

//Now add this to main query..

strQuery = strQuery + " and " + strWhere;
}

Now your query is ready. You can run it.

Hope this helps.

mahesh replied to Sahil Kumar on 23-Mar-11 01:22 AM

 hey shyam can you please suggest me the query that i should have on search button...
Anoop S replied to mahesh on 23-Mar-11 01:28 AM
1st store selected value to one array, then check array length and write code according to array length, using If statment
ie if array length is 1 then use query with one parameter, if length is 2 then use 2 parameter query, like that
Deepak Amemane replied to mahesh on 23-Mar-11 01:29 AM
hi,

construct a statement in this way

string stcommand = "select * from city where city in (";
foreach(selected item in dropdownlist)
{
strcommand = strcommand + "dropdown1.selectedItem.text" +",";
}

strCommand.Remove(strCommand.LastIndexOf(','));
strCommand = strCommand = ")";


Hope this helps You
Sahil Kumar replied to mahesh on 23-Mar-11 01:34 AM
Hi mahesh,

Jem Savery has provide a example which will work fine for you. I was going to provide you same

string query="select * from tablename where (your other conditions) and (";

string where = "";

if (chkIsAvailable.Checked)

where += "city1=" + chkIsAvailable.Text;


You can do this from backend also.

send all parameter to you stored procedure and there you can configure your query which can be more easy.
if(@city1 not null)
 configure query.

I hope this will help you.....
mahesh replied to Deepak Amemane on 23-Mar-11 05:47 AM
Thanks a lot guysssss its working now :)
mahesh replied to Deepak Amemane on 23-Mar-11 05:47 AM
Thanks a lot guysssss its working now :)