Click here to Skip to main content
11,921,353 members (64,349 online)
Rate this:
Please Sign up or sign in to vote.
See more: VB VB.NET VS2012
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 21: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 OriginalGriff 553
1 Dave Kreskowiak 245
2 phil.o 160
3 KrunalRohit 134
4 Sergey Alexandrovich Kryukov 132
0 OriginalGriff 6,951
1 KrunalRohit 4,530
2 Sergey Alexandrovich Kryukov 3,171
3 George Jonsson 2,840
4 Suvendu Shekhar Giri 2,181

Advertise | Privacy | Mobile
Web02 | 2.8.151120.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