Solved

Updating gridview via checboxlist fails in some cases

Posted on 2013-01-16
5
407 Views
Last Modified: 2013-01-22
Hi!

I have a an asp.net gridview which is filtered by a checkboxlist. If I choose e.g. alternative number 5 of 10 and then click the button, I get the correct result. If I add also alternative 4 of 10, I also get correct  result.

However, if I add an alternative higher,e.g.7, then the first alternative, in this case 5, I do not see the result from alternative 7 together with 4 and 5 in the gridview when I click the button. If I for instance filter the gridview, the gridview also contains the result from alternative 7.

What is wrong in my code when only "lower" alternatives from the checkboxlist are reflected in the gridview?

This is code from the behind-page

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub CheckBoxList1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckBoxList1.Load
        Dim csvOfSelectedItems As String = ""
        Dim li As ListItem
        For Each li In CheckBoxList1.Items
            If li.Selected Then
                csvOfSelectedItems += "'" + li.Text + "',"
            End If
        Next

        If csvOfSelectedItems = "" Then
            Response.Write("<b>Statistik för 2012.Välj den eller de kommun du är intresserad av! Klicka sedan på rubrikerna för att ändra sorteringsordning</b>")
        Else
            ' Trim the last comma char
            csvOfSelectedItems = csvOfSelectedItems.Substring(0, csvOfSelectedItems.Length - 1)

            ' Prepare Query string
            Dim sqlQuery As String = "SELECT * FROM statistik WHERE [kommunnamn] IN (" + csvOfSelectedItems + ")"

            ' EXECUTE sqlQuery HERE and use the results.
            SqlDataSource1.SelectCommand = sqlQuery
        End If
    End Sub

    Protected Sub CheckBoxList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CheckBoxList1.SelectedIndexChanged
        Dim csvOfSelectedItems As String = ""
        Dim li As ListItem
        For Each li In CheckBoxList1.Items
            If li.Selected Then
                csvOfSelectedItems += "'" + li.Text + "',"
            End If
        Next

        If csvOfSelectedItems = "" Then
            Response.Write("Välj den eller de kommuner du är intresserad av!")
        Else
            ' Trim the last comma char
            csvOfSelectedItems = csvOfSelectedItems.Substring(0, csvOfSelectedItems.Length - 1)

            ' Prepare Query string
            Dim sqlQuery As String = "SELECT * FROM statistik WHERE [kommunnamn] IN (" + csvOfSelectedItems + ")"

            ' EXECUTE sqlQuery HERE and use the results.
            SqlDataSource1.SelectCommand = sqlQuery
        End If
    End Sub



    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

    End Sub

    Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting

    End Sub

    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
End Class

Open in new window

Example of how the page looks
This is the complete code of the page:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Statistik 2012</title>
<link rel="stylesheet" type="text/css" href="css/itis.css">
</head>

<body>

    <form id="form1" runat="server">
    <div>
 
        <asp:GridView ID="GridView1" runat="server" AllowSorting="True"
            AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1"
            ForeColor="#333333" GridLines="None">
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <Columns>
                <asp:BoundField DataField="PERIOD" HeaderText="PERIOD"
                    SortExpression="PERIOD" />
                <asp:BoundField DataField="KOMMUN" HeaderText="KOMMUN"
                    SortExpression="KOMMUN" />
                <asp:BoundField DataField="KOMMUNNAMN" HeaderText="KOMMUNNAMN"
                    SortExpression="KOMMUNNAMN" />
                <asp:BoundField DataField="SKOLNAMN"
                    HeaderText="SKOLNAMN"
                    SortExpression="SKOLNAMN" />
                <asp:BoundField DataField="STUDIEVAG" HeaderText="STUDIEVAGSKOD"
                    SortExpression="STUDIEVAG" />
                <asp:BoundField DataField="STV_NAMN" HeaderText="STV_NAMN"
                    SortExpression="STV_NAMN" />
                <asp:BoundField DataField="antalantagna" HeaderText="ANTALANTAGNA"
                    SortExpression="antalantagna" />
                <asp:BoundField DataField="minmeritv" HeaderText="MINMERITV"
                    SortExpression="minmeritv" />
                <asp:BoundField DataField="md" HeaderText="MEDELMERITV."
                    SortExpression="md" />
                <asp:BoundField DataField="antalsök" HeaderText="ANTALSÖK"
                    SortExpression="antalsök" />
                <asp:BoundField DataField="antalforstasök"
                    HeaderText="ANTALFÖRSTASÖK"
                    SortExpression="antalforstasök" />
                <asp:BoundField DataField="antalsökkvinnor" HeaderText="ANTALSÖKKV"
                    SortExpression="antalsökkvinnor" />
                <asp:BoundField DataField="antalsökmän"
                    HeaderText="ANTALSÖKMÄN" SortExpression="antalsökmän" />
            </Columns>
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#999999" />
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        </asp:GridView>
   
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
            ProviderName="<%$ ConnectionStrings:ConnectionString2.ProviderName %>"
            SelectCommand="SELECT * FROM [statistik] WHERE ([KOMMUNNAMN] = ?)">
            <SelectParameters>
                <asp:ControlParameter ControlID="CheckBoxList1" Name="KOMMUNNAMN"
                    PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:CheckBoxList ID="CheckBoxList1" runat="server"
            DataSourceID="SqlDataSource2" DataTextField="KOMMUNNAMN"
            DataValueField="KOMMUNNAMN" RepeatColumns="3">
        </asp:CheckBoxList>
        <asp:Button ID="Button1" runat="server" Text="SÖK" />
   
    </div>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
        ProviderName="<%$ ConnectionStrings:ConnectionString2.ProviderName %>"
        SelectCommand="SELECT DISTINCT [KOMMUNNAMN] FROM [statistik]">
    </asp:SqlDataSource>
    </form>
</body>
</html>
0
Comment
Question by:marcgu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 41

Expert Comment

by:guru_sami
ID: 38783833
Here are couple things I would try:
1: Set breakpoint and see if you are getting correct SelectedItems
2: Make sure those items are passed in the Query
3: If you have access to SQLProfiler via SQL Management Studio, start a trace to see the query that's being executed and the parameters passed
4: Try to run the query in directly in the sql server and verify correct data is returned
5: Last but not least check that your data is not hidden in markup for whatsoever reason.
0
 
LVL 20

Expert Comment

by:informaniac
ID: 38794587
Is [kommunnamn] a view or is it a table? As mentioned in the above comment. Did u try running the query directly?
0
 

Author Comment

by:marcgu
ID: 38798688
Hi!

[Kommunnamn] is a field. The query must be correct but for some reason it is not executed before e.g. the sortorder in the gridview has been changed if I choose the alternatives in the checkbox in the "wrong" order.

I hope I have made clear that the result is correct when I,

1. click "Sök" ="search the first time
2. Click "sök" the next time if I have added "lower" alternatives
3. click "sök" the next time if I have added higher alternatives and after clicking "Sök" also changed e.g. the sort order in the gridview.

The problem seems to be that my code make the loop stop when it reaches the previous highest alternative.

It can be viewed here: https://www.gymnasiestudera.se/stat2013.aspx
0
 
LVL 41

Accepted Solution

by:
guru_sami earned 400 total points
ID: 38807640
Can you try removing the CheckBoxList Load handler i.e.
Protected Sub CheckBoxList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CheckBoxList1.SelectedIndexChanged


I think the sqldatasource is getting confused.
0
 

Author Closing Comment

by:marcgu
ID: 38808850
Hi!

Thanks a lot. This was the problem. Now it works pefectly.
Best regards
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question