Click here to Skip to main content
13,140,231 members (44,999 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
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:
<br />
Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click<br />
        SqlDataSource1.SelectCommand = "Select * From test WHERE (FirstName = @FirstName Or @FirstName IS NULL) AND (SecondName = @SecondName Or @SecondName IS NULL)"<br />
        SqlDataSource1.SelectParameters.Add("FirstName", DropDownListFirstName.Text)<br />
        SqlDataSource1.SelectParameters.Add("SecondName", DropDownListSecondName.Text)<br />
        SqlDataSource1.CancelSelectOnNullParameter = False<br />
        SqlDataSource1.DataBind()<br />
    End Sub<br />


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


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


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


Thanks in advance for help!
Posted 4-Jul-12 20: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 |
Web01 | 2.8.170915.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