Solved

Updating gridview via checboxlist fails in some cases

Posted on 2013-01-16
5
394 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
  • 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now