Error while try to Filter gridview/sqldatasource with sql parameters, and checkboxlis

[dejanc]

Hello,
I'm still working on project filter SqlDataSource with parameters. Code under Button1_Click event works fine, controls are dropdownlists.
Now, I want to move forward, to filter with checkboxlists items. I have write code under Button2_Click event. I have combine codes of my current knowledge which I have it in my head.
I'm getting error:
Error: Sys.WebForms.PageRequestManagerServerErrorException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

I hope someone could help me with code from Button2_Click to reach the goal :-)

Imports System.Data
Imports System.Data.SqlClient
Partial Class zest
    Inherits System.Web.UI.Page
    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        SqlDataSource1.SelectCommand = "Select * From test WHERE (FirstName = @FirstName Or @FirstName IS NULL)"
        SqlDataSource1.SelectParameters.Add("FirstName", CheckBoxListFirstName.Text)
        SqlDataSource1.CancelSelectOnNullParameter = False
        SqlDataSource1.DataBind()
    End Sub
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        SqlDataSource1.SelectParameters.Clear()
    End Sub

    Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim MyFilter As String = ""
        Dim Count As Integer = 0
        Dim i As Integer
        For i = 0 To CheckBoxListFirstName.Items.Count - 1
            If CheckBoxListFirstName.Items(i).Selected Then
                Count += 1
                If MyFilter = "" Then
                    MyFilter = " WHERE FirstName = @FirstName Or @FirstName IS NULL"
                Else
                    MyFilter = MyFilter & " OR FirstName = @FirstName Or @FirstName IS NULL"
                End If
            End If
        Next
        SqlDataSource1.CancelSelectOnNullParameter = False
        SqlDataSource1.SelectCommand = "SELECT * FROM test" & MyFilter
        SqlDataSource1.SelectParameters.Add("FirstName", CheckBoxListFirstName.Items(i).Text)
        SqlDataSource1.DataBind()

    End Sub
End Class

Working code 'Filter gridview with checkboxlist items' which I was using in the past.

Dim MyFilter As String = ""
        Dim Count As Integer = 0
        Dim i As Integer
        For i = 0 To CheckBoxListFirstName.Items.Count - 1
            If CheckBoxListFirstName.Items(i).Selected Then
                Count += 1
                If MyFilter = "" Then
                    MyFilter = " WHERE FirstName like '%" & CheckBoxListFirstName.Items(i).Text & "%'"
                Else
                    MyFilter = MyFilter & " OR FirstName like '%" & CheckBoxListFirstName.Items(i).Text & "%'"
                End If
            End If
        Next
SqlDataSource1.SelectCommand = "SELECT * FROM test" & MyFilter

Aspx page:

<form id="form1" runat="server"> 
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager> 
<br /> 
<asp:DropDownList ID="DropDownListFirstName" runat="server"> 
    <asp:ListItem></asp:ListItem> 
    <asp:ListItem>John</asp:ListItem> 
    <asp:ListItem>Paul</asp:ListItem> 
    <asp:ListItem>James</asp:ListItem> 
</asp:DropDownList> 
FirstName<br /> 
<asp:DropDownList ID="DropDownListSecondName" runat="server"> 
    <asp:ListItem></asp:ListItem> 
    <asp:ListItem>Brown</asp:ListItem> 
    <asp:ListItem>White</asp:ListItem> 
</asp:DropDownList> 
SecondName<br /> 
<asp:DropDownList ID="DropDownListTown" runat="server"> 
    <asp:ListItem></asp:ListItem> 
    <asp:ListItem>New York</asp:ListItem> 
    <asp:ListItem>London</asp:ListItem> 
    <asp:ListItem>Paris</asp:ListItem> 
</asp:DropDownList> 
Town<br /> 
<asp:Button ID="Button1" runat="server" Text="Button" /> 
<br /> 
<asp:UpdatePanel ID="UpdatePanel1" runat="server"> 
<ContentTemplate> 
 
<div> 
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" 
 EmptyDataText="There are no data records to display."> 
 <Columns> 
 <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
 SortExpression="FirstName" /> 
 <asp:BoundField DataField="SecondName" HeaderText="SecondName" 
 SortExpression="SecondName" /> 
     <asp:BoundField DataField="Town" HeaderText="Town" SortExpression="Town" /> 
 </Columns> 
 </asp:GridView> 
    <asp:CheckBoxList ID="CheckBoxListFirstName" runat="server" 
        DataSourceID="SqlDataSourceFirstName" DataTextField="FirstName" 
        DataValueField="FirstName" BorderStyle="Solid"> 
    </asp:CheckBoxList> 
    <asp:SqlDataSource ID="SqlDataSourceFirstName" runat="server" 
        ConnectionString="<%$ ConnectionStrings:database21ConnectionString %>" 
        SelectCommand="SELECT DISTINCT [FirstName] FROM [test]"></asp:SqlDataSource> 
    <asp:CheckBoxList ID="CheckBoxListSecondName" runat="server" 
        DataSourceID="SqlDataSourceSecondName" DataTextField="SecondName" 
        DataValueField="SecondName" BorderStyle="Solid"> 
    </asp:CheckBoxList> 
    <asp:SqlDataSource ID="SqlDataSourceSecondName" runat="server" 
        ConnectionString="<%$ ConnectionStrings:database21ConnectionString %>" 
        SelectCommand="SELECT DISTINCT [SecondName] FROM [test]"></asp:SqlDataSource> 
    <asp:CheckBoxList ID="CheckBoxListTown" runat="server" 
        DataSourceID="SqlDataSourceTown" DataTextField="Town" 
        DataValueField="Town" BorderStyle="Solid"> 
    </asp:CheckBoxList> 
    <asp:SqlDataSource ID="SqlDataSourceTown" runat="server" 
        ConnectionString="<%$ ConnectionStrings:database21ConnectionString %>" 
        SelectCommand="SELECT DISTINCT [Town] FROM [test]"> 
    </asp:SqlDataSource> 
    <asp:Button ID="Button2" runat="server" Text="Button" /> 
    <br /> 
 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:database21ConnectionString %>" 
 SelectCommand="SELECT [FirstName], [SecondName], [Town] FROM [test]"> 
  </asp:SqlDataSource> 
 </div> 
</ContentTemplate> 
</asp:UpdatePanel> 
</form>

Thanks in advance for help!

[kaliman79912]

OK, much more analisys you need but I can tell you this: You are constructing a Parametered statement which contains " WHERE FirstName = @FirstName Or @FirstName IS NULL"

If you look at the second part of this, you will filter your statement for all the rows in which your parameter is null???? what does that mean?. keep in mind that the name of the parameter has no effect and the SQL will not see it as FirstName.

In other words, its like saying:

SELECT FirstName FROM MyTable Where John IS NULL

Do you see what I mean? John is not a field of MyTable.

[kaliman79912]

Another thing is that you are creating a long list of parameters (all named the same by the way). But only adding one actual parameter. Do something like

For Each strFirstName As String In CheckedListBox1.Items
            SqlDataSource1.SelectParameters.Add("FirstName", strFirstName)
        Next

Still, there is the issue of the parameter name being the same for all in this example.

[kaliman79912]

But the actual error I think it is being generated by this line:

MyFilter = MyFilter & " OR FirstName like '%" & CheckBoxListFirstName.Items(i).Text

Because you are calling it after the loop ends so i has the value of CheckBoxListFirstName.Items.Count, which is one above the highest index in the collection.

[dejanc]

Another thing is that you are creating a long list of parameters (all named the same by the way). But only adding one actual parameter. Do something like

For Each strFirstName As String In CheckedListBox1.Items
            SqlDataSource1.SelectParameters.Add("FirstName", strFirstName)
        Next

Still, there is the issue of the parameter name being the same for all in this example.

Getting an error: Error: Sys.WebForms.PageRequestManagerServerErrorException: Conversion from type 'ListItem' to type 'String' is not valid.

  Protected Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        For Each strFirstName As String In CheckBoxListFirstName.Items
            SqlDataSource1.SelectCommand = "SELECT * FROM test WHERE FirstName = @FirstName Or @FirstName IS NULL"
            SqlDataSource1.SelectParameters.Add("FirstName", strFirstName)
        Next
    End Sub

[dejanc]

But the actual error I think it is being generated by this line:

MyFilter = MyFilter & " OR FirstName like '%" & CheckBoxListFirstName.Items(i).Text

Because you are calling it after the loop ends so i has the value of CheckBoxListFirstName.Items.Count, which is one above the highest index in the collection.

Sorry, but this code is an issue:

Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim MyFilter As String = ""
        Dim Count As Integer = 0
        Dim i As Integer
        For i = 0 To CheckBoxListFirstName.Items.Count - 1
            If CheckBoxListFirstName.Items(i).Selected Then
                Count += 1
                If MyFilter = "" Then
                    MyFilter = " WHERE FirstName = @FirstName Or @FirstName IS NULL"
                Else
                    MyFilter = MyFilter & " OR FirstName = @FirstName Or @FirstName IS NULL"
                End If
            End If
        Next
        SqlDataSource1.CancelSelectOnNullParameter = False
        SqlDataSource1.SelectCommand = "SELECT * FROM test" & MyFilter
        SqlDataSource1.SelectParameters.Add("FirstName", CheckBoxListFirstName.Items(i).Text)
        SqlDataSource1.DataBind()

    End Sub

[dejanc]

I have add an example how does works with Dropdownlist controls. It return results for every dropdownlist item. Even if dropdownlist selected item string is " ", gridview is then return all records. Same, I would like to write for checkboxlists. But, like a life it seems everything has to be complicated.

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        SqlDataSource1.SelectCommand = "Select * From test WHERE (FirstName = @FirstName Or @FirstName IS NULL) AND (SecondName = @SecondName Or @SecondName IS NULL)"
        SqlDataSource1.SelectParameters.Add("FirstName", DropDownListFirstName.Text)
        SqlDataSource1.SelectParameters.Add("SecondName", DropDownListSecondName.Text)
        SqlDataSource1.CancelSelectOnNullParameter = False
        SqlDataSource1.DataBind()
    End Sub