Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET
I have stored procedure for filtration.
 
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();
 
 
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 14-Nov-12 22:57pm
Edited 14-Nov-12 23:12pm
v2
Comments
Krunal Rohit at 15-Nov-12 4:19am
   
What output you are getting here.??
pinky1810 at 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
good
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])
  Permalink  
Comments
pinky1810 at 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 at 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 at 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 at 15-Nov-12 5:39am
   
posted another solution on how that can be done. See if that is helpful.
Rate this: bad
good
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);
}
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)
  Permalink  
v2
Comments
pinky1810 at 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 at 15-Nov-12 5:54am
   
made a minor modification in the solution. see if this one works.
pinky1810 at 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 at 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 at 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
Rahul Rajat Singh at 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: http://www.codeproject.com/Articles/402611/AJAX-for-beginners-Part-2-Using-XMLHttpRequest-and
 
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
0 OriginalGriff 270
1 Sergey Alexandrovich Kryukov 185
2 BillWoodruff 173
3 CPallini 155
4 Kamal Rocks 144
0 OriginalGriff 5,655
1 DamithSL 4,506
2 Maciej Los 3,997
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 15 Nov 2012
Copyright © CodeProject, 1999-2014
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