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]