Click here to Skip to main content
15,885,366 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have stored procedure for filtration.

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


AS
BEGIN
    select distinct e.EMPLOYEE_ID, e.DEPARTMENT_ID,e.FIRST_ENAME, e.LAST_ENAME, e.MOBILE_NO, e.LANDLINE, e.EMAIL_ID, e.ADDRESS from EMPLOYEE_INFO e, BRANCH_MASETR b, DEPARTMENT_MASTER d, EMPLOYEE_PICTURE EP

    where (e.BRANCH_ID=b.BRANCH_ID)and
   

    (e.EMPLOYEE_ID=EP.EMPLOYEE_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)

END


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;
      GridView1.DataBind();


SQL
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();

       cn.Open();
       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();
       da.Fill(dssearch);
       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
Updated 14-Nov-12 22:12pm
v2
Comments
[no name] 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..

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

SQL
[b.COUNTRY_ID] = COALESCE(@countryid, [b.COUNTRY_ID])
 
Share this answer
 
Comments
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.
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:
C#
if(countryid == 0)
{
 cmd.Parameters.AddWithValue("@countryid", DBnull.Value);
}
else
{
 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)
 
Share this answer
 
v2
Comments
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 ddl.in 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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900