Solved

Updating gridview via checboxlist fails in some cases

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 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