asked on

Updating gridview via checboxlist fails in some cases

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

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>

guru_sami

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.

Obadiah Christopher

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

marcgu

ASKER

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

ASKER CERTIFIED SOLUTION

guru_sami

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marcgu

ASKER

Hi!

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