0

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.

  1. 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!

Edited by dejanc

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by hericles
0

The error on button 3 is because you are using the same SqlDataSource1 for each of the functions. You already added firstName as a parameter in the 2nd button click method so it doesn't need to be added again.
The error on button 4 is because you try to access the strFirstName in the checkbox items control. You need to cycle through the list items instead (exactly as you did in the 3rd button click) and then access their value:

For Each li As ListItem In Me.CheckBoxListFirstName.Items

Not

For Each strFirstName As String In CheckBoxListFirstName.Items

Edited by hericles

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.