Click here to Skip to main content
12,954,601 members (55,916 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
OriginalGriff 6,539
CHill60 3,490
Maciej Los 3,143
ppolymorphe 2,030
Jochen Arndt 1,975


Advertise | Privacy | Mobile
Web01 | 2.8.170525.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