Click here to Skip to main content
11,703,438 members (72,687 online)
Rate this: bad
Please Sign up or sign in to vote.
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 Smile | :)

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
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"
MyFilter = MyFilter & " OR FirstName = @FirstName Or @FirstName IS NULL"
End If
End If
SqlDataSource1.CancelSelectOnNullParameter = False
SqlDataSource1.SelectCommand = "SELECT * FROM test" & MyFilter
SqlDataSource1.SelectParameters.Add("FirstName", CheckBoxListFirstName.Items(i).Text)
End Sub

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
SqlDataSource1.CancelSelectOnNullParameter = False
SqlDataSource1.SelectCommand = "SELECT * FROM test WHERE FirstName = @FirstName Or @FirstName IS NULL"
SqlDataSource1.SelectParameters.Add("FirstName", li.Value)
End If
End Sub

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)
End Sub

Thanks in advance for help!
Posted 4-Jul-12 20:21pm
Rate this: bad
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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

You mean to have like this?

For i = 0 To CheckBoxListFirstName.SelectedIndex = -1

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

  Print Answers RSS
0 Maciej Los 540
1 Sergey Alexandrovich Kryukov 400
2 OriginalGriff 340
3 Andy Lanng 124
4 Matt T Heffron 115
0 OriginalGriff 8,519
1 Sergey Alexandrovich Kryukov 7,667
2 CPallini 4,987
3 Maciej Los 4,686
4 Mika Wendelius 3,566

Advertise | Privacy | Mobile
Web03 | 2.8.150819.1 | Last Updated 5 Jul 2012
Copyright © CodeProject, 1999-2015
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