Click here to Skip to main content
13,248,927 members (44,450 online)
Rate this:
Please Sign up or sign in to vote.
See more:
I have stored procedure for filtration.

ALTER PROCEDURE [dbo].[SearchEmployee](@countryid int=null, @cityid int=null,@branchid int=null,@deptid int=null)

    where (e.BRANCH_ID=b.BRANCH_ID)and
    (b.COUNTRY_ID=@countryid or ISNULL(@countryid, -1) = -1 ) and
    (b.CITY_ID=@cityid or ISNULL(@cityid, -1) = -1 ) and
    (e.BRANCH_ID=@branchid or ISNULL(@branchid, -1) = -1 ) AND
    (e.DEPARTMENT_ID=@deptid or ISNULL(@deptid, -1) = -1)

this stored procedure is giving correct info when i am executing it.

but i used it in my code like this in button click event.
  DataSet dsserachemp = new DataSet();
        string countryid = Ddlcountry.SelectedValue.ToString();
        string cityid = DdlCity.SelectedValue.ToString();
        string branchid= DdlBranch.SelectedValue.ToString();
        string deptid = DdlDepartment.SelectedValue.ToString();
        dsserachemp = bl.BsearchEmp(countryid, cityid, branchid, deptid);
        GridView1.DataSource = dsserachemp;

public DataSet BsearchEmp(string countryid, string cityid, string branchid, string deptid)
       return Dl.searchemp(countryid, cityid, branchid, deptid);

  public DataSet searchemp(string countryid, string cityid,string branchid,string deptid)
         if (cn.State.Equals(ConnectionState.Open)) cn.Close();
         cmd = new SqlCommand("SearchEmployee", cn);
         cmd.Parameters.AddWithValue("@countryid", countryid);
         cmd.Parameters.AddWithValue("@cityid", cityid);
         cmd.Parameters.AddWithValue("@branchid", branchid);
         cmd.Parameters.AddWithValue("@deptid", deptid);
         da = new SqlDataAdapter(cmd);
         DataSet dssearch = new DataSet();
         return dssearch;  

than, while executing

This code it is not giving proper result, it is showing all the records even if i change the value for department or branch( stored procedure is working fine) .

Please Help....

[PO'H - edited post because it was breaking the layout of the site homepage]
Posted 14-Nov-12 22:57pm
Updated 14-Nov-12 23:12pm
Krunal Rohit 15-Nov-12 4:19am
What output you are getting here.??
pinky1810 15-Nov-12 4:21am
It is displaying all the records not performing filtration even if i change department value or branch value.
please help..
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Try using the COALESCE in all your where clause. here is one example:

[b.COUNTRY_ID] = COALESCE(@countryid, [b.COUNTRY_ID])
pinky1810 15-Nov-12 4:51am
Sorry, But not working It is same again. stored procedure is working fine but trough code again it is displaying all records.Is there any changes i need to make in code?.
please advice
Rahul Rajat Singh 15-Nov-12 4:58am
from your application layer what values for the parameters are being passed to the DB layer?

I mean if you want some parameter not to be considered in the where clause you will explicitly need to pass their value as DBnull.Value. Are you doing that? Do not pass null or empty string from there.
pinky1810 15-Nov-12 5:27am
in my application i have four variables of type Int64
countryid, cityid,departmentid and branchid and i am assigning them the dropdoenlist selected value ,
if any of the dropdown is not selected i want to assign a null to that integer variable, how to assign DBnull.Value to integer type. please help
Rahul Rajat Singh 15-Nov-12 5:39am
posted another solution on how that can be done. See if that is helpful.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Note: Posting separate solution to avoid cluter.

OK lets see how this can be done.

Lets say you have the dropdown for country with items as All, India, Denmark .... with values 0,1,2.......

Modify the sqlparameter population as:
if(countryid == 0)
 cmd.Parameters.AddWithValue("@countryid", DBnull.Value);
 cmd.Parameters.AddWithValue("@countryid", countryid);

The value for all will come under the check of ternary operator. Also, this solution will work along with my previous solution i.e. you will have to use COALESCE operator.(it could work with your SP too but i have not tested it)
pinky1810 15-Nov-12 5:50am
Getting error :
Type of conditional expression cannot be determined because there is no implicit conversion between DBnull and long
Rahul Rajat Singh 15-Nov-12 5:54am
made a minor modification in the solution. see if this one works.
pinky1810 15-Nov-12 6:01am
I did some modification like this

cmd.Parameters.AddWithValue("@countryid", (countryid == 0) ? (object) DBNull.Value : countryid);

But sad...
still the same thing is happening retrieving all the records not department wise
I am not getting what might be the reason that Sp is working but not my code.

please help...
Rahul Rajat Singh 15-Nov-12 6:05am
ok, let me ask a very basic question first. the dropdowns must be getting populated by values coming from database, right? are you sure that is happening only when the ispostback property is false and not always?
pinky1810 15-Nov-12 6:18am
yes they are getting values from Database itself.
The country Drop Down is populating when the ispostback property is false,
but the city ddl will populate based on the selection of country on selectedIndexChanged event Of Country ddl.

and Branches will get loaded based on the values of country and city on cityddl selectedIndexChanged Event and
department ddl get loads after the selection of branch branchddl
selectedindex changed Event.

I thought may be i am loosing the values during postback and i traced the valus and the values passed to function are correct.department id passed correctly to the function.

please Advice
Rahul Rajat Singh 15-Nov-12 6:50am
perhaps that is causing the problem. why not you move this cascading behavior to the client side. refer this on how to do that:

I think multiple postbacks must be causing the values to get to reset to the original ones.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.171114.1 | Last Updated 15 Nov 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100