Hi mvadukul,
I have created sample upto this. You need to just maintain the selected checkbox state while checking the checkbox.
SQL
CREATE TABLE mvadukul
(
JobID INT,JobRefNo VARCHAR(20),JobName VARCHAR(50),SalaryFrom INT,SalaryTo INT,
ShortDesc VARCHAR(200),MainDesc VARCHAR(20),ClosingDate VARCHAR(20),Contract VARCHAR(20),
WorkingHRS VARCHAR(20),JobCategoryName INT,RegisterID VARCHAR(20),OrgName VARCHAR(20),
OrgType VARCHAR(20),Name VARCHAR(100),ContentType VARCHAR(20),JobType INT,JobLevel INT,
Scale INT,ContractID INT,WorkingHrsID INT,KeyStage INT,Subject INT,OrgTypeID INT
)
GO
-----------------------------------------------------
INSERT INTO mvadukul VALUES
(25,'VF101','Teaching Assistant (Inclusion Team)',15000,20000,'We are seeking to recruit an enthusiastic, committed SENCo to lead the provision of SEN throughout our school.','2016-09-02','Permanent','Full Time','Teaching',13,'School','ABCD','Sepcial','Bayliss Court Nursery.JPG','image/jpeg',0,0,1,1,1,2,1,4)
INSERT INTO mvadukul VALUES
(26,'VF102','School Meals Supervisory Assistant',31000,35000,'The Governors are seeking to appoint two caring and committed EYFS Teaching Assistants.','2016-09-02','Temporary','Full Time','Teaching',13,'School','ABCD','Sepcial','Bayliss Court Nursery.JPG','image/jpeg',0 ,0 ,1 ,2 ,1 ,2 ,1 ,4)
INSERT INTO mvadukul VALUES
(28,'VF103','Learning Support Assistants',10000,15000,'We are looking for a TA, to start in October 2016.','2016-09-02','Fixed Term','Part Time','Teaching',15,'SChool','ABD','Secondary','Lady Bankes Inf logo.png','image/png' ,0 ,0 ,1 ,3 ,2 ,2 ,1 ,2)
INSERT INTO mvadukul VALUES
(29,'VF104','Class Teacher',17000,19000,'School Meals Supervisory Assistants (SMSA) are required as soon as possible.','2016-09-02','Permanent','Part Time','Non-Teaching',13,'School','ABCD','Sepcial','Bayliss Court Nursery.JPG','image/jpeg',1 ,0 ,1, 1, 2, 2 ,1, 4)
INSERT INTO mvadukul VALUES
(30,'VF105','Reception Class Teacher',20000,31000,'Due to expansion and promotion, The Park Federation is looking for teachers to work at Wood End Park Academy','2016-09-02','Temporary','Full Time','Non-Teaching',13,'School','ABCD','Sepcial','Bayliss Court Nursery.JPG','image/jpeg',1 ,0 ,1 ,2 ,1 ,2 ,1 ,4)
INSERT INTO mvadukul VALUES
(31,'VF106','School Cleaner',15000,27000,'The Governors of this happy, welcoming and highly successful school.','2016-09-02','Temporary','Full Time','Non-Teaching',15,'SChool','ABD','Secondary','Lady Bankes Inf logo.png','image/png' ,1, 0 ,1 ,2, 1, 2, 1, 2)
INSERT INTO mvadukul VALUES
(32,'VF107','EYFS Teaching Assistant x2',34000,42000,'St Matthew’s CE Primary School is looking to recruit a cleaner to join our friendly team, starting as soon as possible.','2016-09-02','Permanent','Term Time Only','Non-Teaching',15,'SChool','ABD','Secondary','Lady Bankes Inf logo.png','image/png' ,1 ,0 ,1 ,1 ,3 ,2 ,1 ,2)
GO
-----------------------------------------------------
CREATE FUNCTION [dbo].[SplitString]
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
GO
-----------------------------------------------------
CREATE PROCEDURE Getmvadukul
@WorkingHRS VARCHAR(MAX) = NULL
,@Contract VARCHAR(MAX) = NULL
,@OrgType VARCHAR(MAX) = NULL
AS
BEGIN
SELECT JobName,OrgName,ShortDesc,SalaryFrom,SalaryTo,Contract,WorkingHRS,JobCategoryName,JobID
FROM mvadukul
WHERE (WorkingHRS IN(SELECT ITEM FROM dbo.SplitString(@WorkingHRS, ',')) OR ISNULL(@WorkingHRS,'') = '')
AND (Contract IN(SELECT ITEM FROM dbo.SplitString(@Contract, ',')) OR ISNULL(@Contract,'') = '')
AND (OrgType IN(SELECT ITEM FROM dbo.SplitString(@OrgType, ',')) OR ISNULL(@OrgType,'') = '')
END
GO
-----------------------------------------------------
SELECT Contract,SUM(COUNT) COUNT FROM
(
SELECT DISTINCT Contract,COUNT(Contract) COUNT FROM mvadukul WHERE WorkingHRS IN ('Non-Teaching','Teaching') GROUP BY Contract
UNION
SELECT DISTINCT Contract,0 COUNT FROM mvadukul WHERE WorkingHRS NOT IN ('Non-Teaching','Teaching') GROUP BY Contract
)c
GROUP BY Contract
GO
-----------------------------------------------------
CREATE PROCEDURE GetWorkingHRSCheckBox
@WorkingHRS VARCHAR(MAX) = NULL
,@Contract VARCHAR(MAX) = NULL
,@OrgType VARCHAR(MAX) = NULL
AS
BEGIN
SELECT WorkingHRS,SUM(COUNT) COUNT FROM
(
SELECT DISTINCT WorkingHRS,COUNT(WorkingHRS) COUNT FROM mvadukul
WHERE (WorkingHRS IN(SELECT ITEM FROM dbo.SplitString(@WorkingHRS, ',')) OR ISNULL(@WorkingHRS,'') = '')
AND (Contract IN(SELECT ITEM FROM dbo.SplitString(@Contract, ',')) OR ISNULL(@Contract,'') = '')
AND (OrgType IN(SELECT ITEM FROM dbo.SplitString(@OrgType, ',')) OR ISNULL(@OrgType,'') = '')
GROUP BY WorkingHRS
UNION
SELECT DISTINCT WorkingHRS,0 COUNT FROM mvadukul
WHERE (WorkingHRS NOT IN(SELECT ITEM FROM dbo.SplitString(@WorkingHRS, ',')) OR ISNULL(@WorkingHRS,'') = '')
AND (Contract NOT IN(SELECT ITEM FROM dbo.SplitString(@Contract, ',')) OR ISNULL(@Contract,'') = '')
AND (OrgType NOT IN(SELECT ITEM FROM dbo.SplitString(@OrgType, ',')) OR ISNULL(@OrgType,'') = '')
GROUP BY WorkingHRS
)c
GROUP BY WorkingHRS
END
GO
-----------------------------------------------------
CREATE PROCEDURE GetContractCheckBox
@WorkingHRS VARCHAR(MAX) = NULL
,@Contract VARCHAR(MAX) = NULL
,@OrgType VARCHAR(MAX) = NULL
AS
BEGIN
SELECT Contract,SUM(COUNT) COUNT FROM
(
SELECT DISTINCT Contract,COUNT(Contract) COUNT FROM mvadukul
WHERE (WorkingHRS IN(SELECT ITEM FROM dbo.SplitString(@WorkingHRS, ',')) OR ISNULL(@WorkingHRS,'') = '')
AND (Contract IN(SELECT ITEM FROM dbo.SplitString(@Contract, ',')) OR ISNULL(@Contract,'') = '')
AND (OrgType IN(SELECT ITEM FROM dbo.SplitString(@OrgType, ',')) OR ISNULL(@OrgType,'') = '')
GROUP BY Contract
UNION
SELECT DISTINCT Contract,0 COUNT FROM mvadukul
WHERE (WorkingHRS NOT IN(SELECT ITEM FROM dbo.SplitString(@WorkingHRS, ',')) OR ISNULL(@WorkingHRS,'') = '')
AND (Contract NOT IN(SELECT ITEM FROM dbo.SplitString(@Contract, ',')) OR ISNULL(@Contract,'') = '')
AND (OrgType NOT IN(SELECT ITEM FROM dbo.SplitString(@OrgType, ',')) OR ISNULL(@OrgType,'') = '')
GROUP BY Contract
)c
GROUP BY Contract
END
GO
-----------------------------------------------------
CREATE PROCEDURE GetOrgTypeCheckBox
@WorkingHRS VARCHAR(MAX) = NULL
,@Contract VARCHAR(MAX) = NULL
,@OrgType VARCHAR(MAX) = NULL
AS
BEGIN
SELECT OrgType,SUM(COUNT) COUNT FROM
(
SELECT DISTINCT OrgType,COUNT(OrgType) COUNT FROM mvadukul
WHERE (WorkingHRS IN(SELECT ITEM FROM dbo.SplitString(@WorkingHRS, ',')) OR ISNULL(@WorkingHRS,'') = '')
AND (Contract IN(SELECT ITEM FROM dbo.SplitString(@Contract, ',')) OR ISNULL(@Contract,'') = '')
AND (OrgType IN(SELECT ITEM FROM dbo.SplitString(@OrgType, ',')) OR ISNULL(@OrgType,'') = '')
GROUP BY OrgType
UNION
SELECT DISTINCT OrgType,0 COUNT FROM mvadukul
WHERE (WorkingHRS NOT IN(SELECT ITEM FROM dbo.SplitString(@WorkingHRS, ',')) OR ISNULL(@WorkingHRS,'') = '')
AND (Contract NOT IN(SELECT ITEM FROM dbo.SplitString(@Contract, ',')) OR ISNULL(@Contract,'') = '')
AND (OrgType NOT IN(SELECT ITEM FROM dbo.SplitString(@OrgType, ',')) OR ISNULL(@OrgType,'') = '')
GROUP BY OrgType
)c
GROUP BY OrgType
END
GO
----------------------------------------------------
HTML
<div>
<div id="Services">
<div class="col-md-12" style="background-color: #f2f2f2; border-top-left-radius: 6px;
border-top-right-radius: 6px;">
<div class="row">
<div class="col-md-3" style="background-color: #ececec; border-top-left-radius: 6px;">
<h3>
Filter Result</h3>
</div>
<div class="col-md-9" style="background-color: #ececec; border-left: 1px solid #e0e0e0;
border-top-right-radius: 6px;">
<h3>
Search Result</h3>
</div>
<div class="col-md-3">
<h3>
Working Hours</h3>
<asp:HiddenField ID="hfWorkingHRS" runat="server" />
<asp:HiddenField ID="hfContractType" runat="server" />
<asp:HiddenField ID="hfTypeOfSchool" runat="server" />
<asp:DataList ID="dlWorkingHRS" runat="server" RepeatDirection="Vertical" OnItemDataBound="WorkingHRSDataBound">
<ItemTemplate>
<table>
<tr>
<td>
<asp:CheckBox ID="chkWorkingHRS" Text='<%#Eval("WorkingHRS") %>' runat="server" OnCheckedChanged="OnChange"
AutoPostBack="true" />
</td>
<td>
<asp:Label ID="lblWorkingHours" Text='<%#Eval("Count")== "" ? "[ "+"0"+" ]" : "[ "+Eval("Count")+" ]" %>'
runat="server" />
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
<%-- <asp:CheckBoxList ID="chkWorkingHours" runat="server" AutoPostBack="True" OnSelectedIndexChanged="Country_Selected">
</asp:CheckBoxList>
<asp:Label ID="lblWorkingHours" runat="server" />--%>
<hr />
<h3>
Contract Type</h3>
<asp:DataList ID="dlContractType" runat="server" RepeatDirection="Vertical" OnItemDataBound="ContractTypeDataBound">
<ItemTemplate>
<table>
<tr>
<td>
<asp:CheckBox ID="chkContractType" Text='<%#Eval("Contract") %>' runat="server" OnCheckedChanged="OnChange"
AutoPostBack="true" />
</td>
<td>
<asp:Label ID="lblContractType" Text='<%#Eval("Count")== "" ? "[ "+"0"+" ]" : "[ "+Eval("Count")+" ]" %>'
runat="server" />
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
<%-- <asp:CheckBoxList ID="chkContractType" runat="server" AutoPostBack="True" OnSelectedIndexChanged="Country1_Selected">
</asp:CheckBoxList>
<asp:Label ID="lblContractType" runat="server" />--%>
<hr />
<h3>
Type of School</h3>
<asp:DataList ID="dlTypeOfSchool" runat="server" RepeatDirection="Vertical" OnItemDataBound="OrgTypeDataBound">
<ItemTemplate>
<table>
<tr>
<td>
<asp:CheckBox ID="chkTypeOfSchool" Text='<%#Eval("OrgType") %>' runat="server" OnCheckedChanged="OnChange"
AutoPostBack="true" />
</td>
<td>
<asp:Label ID="lblTypeOfSchool" Text='<%#Eval("Count")== "" ? "[ "+"0"+" ]" : "[ "+Eval("Count")+" ]" %>'
runat="server" />
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
<%--<asp:CheckBoxList ID="chkTypeOfSchool" runat="server" AutoPostBack="True" OnSelectedIndexChanged="Country2_Selected">
</asp:CheckBoxList>
<asp:Label ID="lblTypeOfSchool" runat="server" />--%>
<hr />
</div>
<div class="col-md-9" style="border-left: 1px solid #e0e0e0; padding: 5px;">
<%-- <%# Eval("SalaryFrom")%>
<%# Eval("SalaryTo")%>--%>
<asp:DataList ID="dlJobListing" runat="server" DataKeyField="JobID" CssClass="Mytable Mytable-hover "
BackColor="White">
<ItemTemplate>
<div class="col-md-3 hidden-xs ">
<div class="row">
<p>
<asp:Image ID="LogoLabel" CssClass="ImageLogo" Width="133px" Height="112px" runat="server" />
</p>
<asp:Button ID="btnJobDetails" runat="server" Width="132px" Text="Job Details" CssClass="btn btn-mine btn-block" />
<asp:Button ID="btnSaveJobs" runat="server" Width="132px" Text="Save Job" CssClass="btn btn-mine btn-block" />
<asp:Button ID="btnEmialJobs" runat="server" Width="132px" Text="Email Job" CssClass="btn btn-mine btn-block" />
</div>
</div>
<div class="col-md-9" style="margin-top: -15px;">
<div class="row">
<h3 style="color: #008001">
<asp:Label ID="JobNameLabel" runat="server" Text='<%# Eval("JobName") %>' />
</h3>
<h5>
<b>
<asp:Label ID="OrgNameLabel" runat="server" Text='<%# Eval("OrgName") %>' />
</b>
</h5>
<asp:Label ID="ShortDescLabel" runat="server" Text='<%# Eval("ShortDesc") %>' />
<br />
<br />
<asp:Label ID="SalaryFromLabel" runat="server" Text='<%# "£" + Eval("SalaryFrom") %>' />-
<asp:Label ID="SalaryToLabel" runat="server" Text='<%# "£" + Eval("SalaryTo") %>' />,
<asp:Label ID="ContractLabel" runat="server" Text='<%# " " + Eval("Contract") %>' />,
<asp:Label ID="WorkingHRSLabel" runat="server" Text='<%# " " + Eval("WorkingHRS") %>' />,
<asp:Label ID="JobCategoryNameLabel" runat="server" Text='<%# " " + Eval("JobCategoryName") %>' />
<asp:Label ID="JobIDLabel" runat="server" Text='<%# Eval("JobID") %>' Visible="false" />
<div class="text-right">
<asp:Button ID="btnApplyNow" runat="server" Text="Apply Now" CssClass="btn btn-success" />
</div>
</div>
</div>
</ItemTemplate>
</asp:DataList>
</div>
</div>
</div>
</div>
</div>
Code
static string checkedWorkingHRS, checkedContract, checkedOrgType;
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
BindDatalist("", "", "");
dlWorkingHRS.DataSource = DataTable("GetWorkingHRSCheckBox", "", "", "");
dlWorkingHRS.DataBind();
dlContractType.DataSource = DataTable("GetContractCheckBox", "", "", "");
dlContractType.DataBind();
dlTypeOfSchool.DataSource = DataTable("GetOrgTypeCheckBox", "", "", "");
dlTypeOfSchool.DataBind();
}
}
private DataTable DataTable(string query, string workingHRS, string contract, string orgType)
{
DataTable dt = new DataTable();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@WorkingHRS", workingHRS);
cmd.Parameters.AddWithValue("@Contract", contract);
cmd.Parameters.AddWithValue("@OrgType", orgType);
sda.SelectCommand = cmd;
sda.Fill(dt);
}
}
con.Close();
}
return dt;
}
private void BindDatalist(string WorkingHRS, string Contract, string OrgType)
{
DataTable dt = new DataTable();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("Getmvadukul"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@WorkingHRS", WorkingHRS);
cmd.Parameters.AddWithValue("@Contract", Contract);
cmd.Parameters.AddWithValue("@OrgType", OrgType);
sda.SelectCommand = cmd;
sda.Fill(dt);
}
}
con.Close();
}
dlJobListing.DataSource = dt;
dlJobListing.DataBind();
}
protected void OnChange(object sender, EventArgs e)
{
string workingHRS = "", contractType = "", typeOfSchool = "";
foreach (DataListItem item in dlWorkingHRS.Items)
{
CheckBox cb = item.FindControl("chkWorkingHRS") as CheckBox;
if (cb.Checked)
{
workingHRS += cb.Text + ",";
}
}
if (workingHRS != "")
{
workingHRS = workingHRS.Remove(workingHRS.Length - 1);
checkedWorkingHRS = workingHRS;
}
foreach (DataListItem item in dlContractType.Items)
{
CheckBox cb = item.FindControl("chkContractType") as CheckBox;
if (cb.Checked)
{
contractType += cb.Text + ",";
}
}
if (contractType != "")
{
contractType = contractType.Remove(contractType.Length - 1);
checkedContract = contractType;
}
foreach (DataListItem item in dlTypeOfSchool.Items)
{
CheckBox cb = item.FindControl("chkTypeOfSchool") as CheckBox;
if (cb.Checked)
{
typeOfSchool += cb.Text + ",";
}
}
if (typeOfSchool != "")
{
typeOfSchool = typeOfSchool.Remove(typeOfSchool.Length - 1);
checkedOrgType = typeOfSchool;
}
dlWorkingHRS.DataSource = DataTable("GetWorkingHRSCheckBox", workingHRS, contractType, typeOfSchool);
dlWorkingHRS.DataBind();
dlContractType.DataSource = DataTable("GetContractCheckBox", workingHRS, contractType, typeOfSchool);
dlContractType.DataBind();
dlTypeOfSchool.DataSource = DataTable("GetOrgTypeCheckBox", workingHRS, contractType, typeOfSchool);
dlTypeOfSchool.DataBind();
BindDatalist(workingHRS, contractType, typeOfSchool);
}
protected void WorkingHRSDataBound(object sender, DataListItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
if (!string.IsNullOrEmpty(checkedWorkingHRS))
{
string[] cheked = checkedWorkingHRS.Split(',');
for (int i = 0; i < cheked.Length; i++)
{
CheckBox cb = e.Item.FindControl("chkWorkingHRS") as CheckBox;
if (!string.IsNullOrEmpty(cb.Text))
{
if (cb.Text == cheked[i])
{
cb.Checked = true;
}
else
{
cb.Checked = false;
}
}
}
}
}
}
protected void ContractTypeDataBound(object sender, DataListItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
if (!string.IsNullOrEmpty(checkedContract))
{
string[] cheked = checkedContract.Split(',');
for (int i = 0; i < cheked.Length; i++)
{
CheckBox cb = e.Item.FindControl("chkContractType") as CheckBox;
if (!string.IsNullOrEmpty(cb.Text))
{
if (cb.Text == cheked[i])
{
cb.Checked = true;
}
else
{
cb.Checked = false;
}
}
}
}
}
}
protected void OrgTypeDataBound(object sender, DataListItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
if (!string.IsNullOrEmpty(checkedOrgType))
{
string[] cheked = checkedOrgType.Split(',');
for (int i = 0; i < cheked.Length; i++)
{
CheckBox cb = e.Item.FindControl("chkTypeOfSchool") as CheckBox;
if (!string.IsNullOrEmpty(cb.Text))
{
if (cb.Text == cheked[i])
{
cb.Checked = true;
}
else
{
cb.Checked = false;
}
}
}
}
}
}