ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Filter ASP.Net GridView based on DropDownList and CheckBoxList using C# and VB.Net<p>Hey <a class="username" rel="democloud">democloud</a>,</p> <p>Please refer below sample.</p> <p>In this sample i have used split function to search multiple columns, Refer the below article to know more about split function.</p> <p><a href="Split function in SQL Server Example: Function to Split Comma separated (Delimited) string in SQL Server 2005, 2008 and 2012" target="_blank">Split function in SQL Server Example: Function to Split Comma separated (Delimited) string in SQL Server 2005, 2008 and 2012</a></p> <p><span style="text-decoration: underline;"><strong>SQL</strong></span></p> <pre class="brush: sql">CREATE TABLE ServiceDay(Service VARCHAR(100), Day VARCHAR(50)) INSERT INTO ServiceDay VALUES(&#39;A&#39;,&#39;Monday&#39;) INSERT INTO ServiceDay VALUES(&#39;A&#39;,&#39;Tuesday&#39;) INSERT INTO ServiceDay VALUES(&#39;B&#39;,&#39;Wednesday&#39;) INSERT INTO ServiceDay VALUES(&#39;B&#39;,&#39;Friday&#39;) INSERT INTO ServiceDay VALUES(&#39;C&#39;,&#39;Monday&#39;) INSERT INTO ServiceDay VALUES(&#39;C&#39;,&#39;Wednesday&#39;) INSERT INTO ServiceDay VALUES(&#39;C&#39;,&#39;Friday&#39;) GO CREATE PROCEDURE ServiceDay_getServiceDays @Service VARCHAR(100) = NULL, @Day VARCHAR(100) = NULL AS BEGIN SELECT * FROM ServiceDay WHERE (Service LIKE @Service + &#39;%&#39; OR @Service IS NULL) AND (Day IN(SELECT ITEM FROM dbo.SplitString(@Day, &#39;,&#39;)) OR ISNULL(@Day,&#39;&#39;) = &#39;&#39;) END GO EXEC ServiceDay_getServiceDays</pre> <p><span style="text-decoration: underline;"><strong>HTML</strong></span></p> <pre class="brush: html">Services : &lt;asp:DropDownList ID=&#34;chkServices&#34; runat=&#34;server&#34;&gt; &lt;asp:ListItem Text=&#34;Select Services&#34; Value=&#34;0&#34; /&gt; &lt;asp:ListItem Text=&#34;A&#34; Value=&#34;A&#34; /&gt; &lt;asp:ListItem Text=&#34;B&#34; Value=&#34;B&#34; /&gt; &lt;/asp:DropDownList&gt; &lt;br /&gt; Days : &lt;asp:CheckBoxList ID=&#34;chkDays&#34; runat=&#34;server&#34;&gt; &lt;asp:ListItem Text=&#34;Monday&#34; Value=&#34;Monday&#34; /&gt; &lt;asp:ListItem Text=&#34;Tuesday&#34; Value=&#34;Tuesday&#34; /&gt; &lt;asp:ListItem Text=&#34;Wednesday&#34; Value=&#34;Wednesday&#34; /&gt; &lt;asp:ListItem Text=&#34;Thursday&#34; Value=&#34;Thursday&#34; /&gt; &lt;asp:ListItem Text=&#34;Friday&#34; Value=&#34;Friday&#34; /&gt; &lt;asp:ListItem Text=&#34;Saturday&#34; Value=&#34;Saturday&#34; /&gt; &lt;asp:ListItem Text=&#34;Sunday&#34; Value=&#34;Sunday&#34; /&gt; &lt;/asp:CheckBoxList&gt; &lt;br /&gt; &lt;asp:Button Text=&#34;Search&#34; runat=&#34;server&#34; OnClick=&#34;Search&#34; /&gt; &lt;br /&gt; &lt;asp:GridView runat=&#34;server&#34; ID=&#34;gvServices&#34; AutoGenerateColumns=&#34;false&#34;&gt; &lt;Columns&gt; &lt;asp:BoundField DataField=&#34;Service&#34; HeaderText=&#34;Service&#34; /&gt; &lt;asp:BoundField DataField=&#34;Day&#34; HeaderText=&#34;Day&#34; /&gt; &lt;/Columns&gt; &lt;/asp:GridView&gt;</pre> <p><span style="text-decoration: underline;"><strong>Namespaces</strong></span></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.Data; using System.Data.SqlClient; using System.Configuration;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.Data.SqlClient Imports System.Data</pre> <p><span style="text-decoration: underline;"><strong>Code</strong></span></p> <p><strong>C#</strong></p> <pre class="brush: csharp">protected void Search(object sender, EventArgs e) { using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[&#34;constr&#34;].ConnectionString)) { using (SqlCommand cmd = new SqlCommand(&#34;ServiceDay_getServiceDays&#34;, con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(&#34;@Service&#34;, chkServices.SelectedItem.Value); cmd.Parameters.AddWithValue(&#34;@Day&#34;, GetCheckedItems(chkDays)); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); da.Fill(dt); gvServices.DataSource = dt; gvServices.DataBind(); } } } } private string GetCheckedItems(CheckBoxList checkBoxList) { string checkedItems = string.Empty; foreach (ListItem item in checkBoxList.Items) { if (item.Selected) { checkedItems += item.Text + &#34;,&#34;; } } checkedItems = checkedItems.Remove(checkedItems.Length - 1); return checkedItems; }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs) Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings(&#34;constr&#34;).ConnectionString) Using cmd As SqlCommand = New SqlCommand(&#34;ServiceDay_getServiceDays&#34;, con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue(&#34;@Service&#34;, chkServices.SelectedItem.Value) cmd.Parameters.AddWithValue(&#34;@Day&#34;, GetCheckedItems(chkDays)) Using da As SqlDataAdapter = New SqlDataAdapter(cmd) Dim dt As DataTable = New DataTable() da.Fill(dt) gvServices.DataSource = dt gvServices.DataBind() End Using End Using End Using End Sub Private Function GetCheckedItems(ByVal checkBoxList As CheckBoxList) As String Dim checkedItems As String = String.Empty For Each item As ListItem In checkBoxList.Items If item.Selected Then checkedItems += item.Text &amp; &#34;,&#34; End If Next checkedItems = checkedItems.Remove(checkedItems.Length - 1) Return checkedItems End Function</pre> <p><span style="text-decoration: underline;"><strong>Screenshot</strong></span></p> <p><img src="https://imgur.com/RU2daSL.gif" alt="" width="234" height="489" /></p>https://www.aspforums.net:443/Threads/320369/Filter-ASPNet-GridView-based-on-DropDownList-and-CheckBoxList-using-C-and-VBNet/https://www.aspforums.net:443/Threads/320369/Filter-ASPNet-GridView-based-on-DropDownList-and-CheckBoxList-using-C-and-VBNet/Fri, 04 Jan 2019 06:04:47 GMT