Click here to Skip to main content
12,747,154 members (28,362 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB VB.NET VS2012
Hello,
I'm still working on project filter SqlDataSource with parameters.
1.Button1 is my first develope code which is working fine with Dropdownlist controls.

2.But, now I want to move to filter with Checkboxlist items. I have write code under Button2_Click event. I have combine codes of my current knowledge which I have it in my head. Button3_click is my second try filtering with Checkboxlists
I'm getting error on Button2
Error: Sys.WebForms.PageRequestManagerServerErrorException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

3.Second try would be Button3_click from which I also getting an error. If I select one item of checkboxlist it filter sucessful, but I check also another item then it return an error.
Error: Sys.WebForms.PageRequestManagerServerErrorException: The variable name '@FirstName' has already been declared. Variable names must be unique within a query batch or stored procedure.

4. Third try would be Button4_click from which I also get an error:
Error: Sys.WebForms.PageRequestManagerServerErrorException: Conversion from type 'ListItem' to type 'String' is not valid.

I hope someone could help me with codes to reach the goal :-)

Button1_Click:
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


Button2_click:
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


Button3_click:
Protected Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim parms As ArrayList = New ArrayList
    For Each li As ListItem In Me.CheckBoxListFirstName.Items
        If li.Selected Then
            parms.Add(li.Value)
            SqlDataSource1.CancelSelectOnNullParameter = False
            SqlDataSource1.SelectCommand = "SELECT * FROM test WHERE FirstName = @FirstName Or @FirstName IS NULL"
            SqlDataSource1.SelectParameters.Add("FirstName", li.Value)
            SqlDataSource1.DataBind()
        End If
    Next
End Sub


Button4_click:
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


Thanks in advance for help!
Posted 4-Jul-12 21:21pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

that why is no data in listbox;
can be listbox.selectedindex =-1; => its mean no data.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

You mean to have like this?

For i = 0 To CheckBoxListFirstName.SelectedIndex = -1
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170215.1 | Last Updated 5 Jul 2012
Copyright © CodeProject, 1999-2017
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