locked
Checkboxlist to retrieve data from database to display results in Gridview RRS feed

  • Question

  • User1578982284 posted

    hi,

    I have a page where I will insert all the data into the database. The page where I'm having problems is when I select an item from the checkboxlist it only displays one item. The data which is displayed is only the one that was inserted with one selected item from the checkboxlist. The following code is the page I'm using to try to retrieve the data from the database. I am trying to do is when a user selects the first, second, or third, or fourth item. The corresponding data appears in the gridview. In my current code, the record which has one item selected appears. If the user has more than one selected it does not appear. It comes up as empty. Is there a way I can do it? Thanks for helping me.

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="ExportContacts.aspx.vb" Inherits="ExportContacts" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <script runat="server">
        Protected Sub exportinfo_OnSelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim strtxtevents As String
            Dim txteventsresults2 As New StringBuilder("")
            
            Dim txteventsresults1 As Integer = 0
            Dim r As Integer = 0
            
            For r = 0 To exportinfo.Items.Count - 1
                If (exportinfo.Items(r).Selected = True) Then
                    txteventsresults1 += 1
                    If (txteventsresults1 > 1) Then txteventsresults2.Append(", ")
                    txteventsresults2.Append(exportinfo.Items(r).Text)
                End If
                
    
            Next r
           
            strtxtevents = txteventsresults2.ToString()
    
        End Sub
    
    </script>
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
         <asp:Panel ID="Export" runat="server">
           <fieldset style="height: 103px">
            <legend><strong> Export Contacts:</strong></legend>
              <table><tr><td>
                  <asp:CheckBoxList ID="exportinfo" runat="server" AppendDataBoundItems="True" 
                   DataSourceID="Sqlsrcexportcontacts" DataTextField="Events_name" 
                   DataValueField="Events_name"  RepeatColumns="4" RepeatDirection="Horizontal"  
                   Height="22px" Width="915px"  OnSelectedIndexChanged="exportinfo_OnSelectedIndexChanged">
                   
               </asp:CheckBoxList>
            
               <asp:SqlDataSource ID="Sqlsrcexportcontacts" runat="server" 
                   ConnectionString="<%$ ConnectionStrings:GAContactsConnectionString %>" 
                   SelectCommand="SELECT [Events_name] FROM [Events]"></asp:SqlDataSource></td></tr> 
                   <tr><td>
                       <asp:Button ID="Export1" runat="server"  Text="Submit" /></td></tr>
            </table>
             </fieldset>
            </asp:Panel>
            
            
            <asp:Panel ID="Panel1" runat="server">
           <fieldset style="height: 103px">
            <legend><strong> Export Results:</strong></legend>
               <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataContacts" 
                   AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" 
                   CellPadding="4" ForeColor="#333333" GridLines="None" Width="957px" 
                   PageSize="25">
                   <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                   <Columns>
                       <asp:BoundField DataField="I_company" HeaderText="I_company" 
                           SortExpression="I_company" />
                       <asp:BoundField DataField="title" HeaderText="title" SortExpression="title" />
                       <asp:BoundField DataField="Pre_name" HeaderText="Pre_name" 
                           SortExpression="Pre_name" />
                       <asp:BoundField DataField="I_fname" HeaderText="I_fname" 
                           SortExpression="I_fname" />
                       <asp:BoundField DataField="I_lname" HeaderText="I_lname" 
                           SortExpression="I_lname" />
                       <asp:BoundField DataField="I_Address1" HeaderText="I_Address1" 
                           SortExpression="I_Address1" />
                       <asp:BoundField DataField="I_Address2" HeaderText="I_Address2" 
                           SortExpression="I_Address2" />
                       <asp:BoundField DataField="I_City" HeaderText="I_City" 
                           SortExpression="I_City" />
                       <asp:BoundField DataField="I_State" HeaderText="I_State" 
                           SortExpression="I_State" />
                       <asp:BoundField DataField="I_Zipcode" HeaderText="I_Zipcode" 
                           SortExpression="I_Zipcode" />
                       <asp:BoundField DataField="I_Country" HeaderText="I_Country" 
                           SortExpression="I_Country" />
                       <asp:BoundField DataField="I_email" HeaderText="I_email" 
                           SortExpression="I_email" />
                   </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="SqlDataContacts" runat="server" 
                   ConnectionString="<%$ ConnectionStrings:GAContactsConnectionString %>" 
                   
                   SelectCommand="SELECT DISTINCT [Pre_name], [I_fname], [I_lname], [I_Address1], [I_Address2], [I_City], [I_State], [I_Zipcode], [I_Country], [I_email], [title], [I_company], [Events] FROM [Individual1] Where Individual1.Events IN ( @strtxtevents ) ">
                   <SelectParameters>
                   <asp:ControlParameter Name="strtxtevents" ControlID="exportinfo" Type="String" />
                   
                   
                   
                   
                   </SelectParameters>
                   
                   
                   
                   </asp:SqlDataSource>
            </fieldset>
            </asp:Panel>
        </div>
        </form>
    </body>
    </html>
    


     

    Tuesday, June 22, 2010 9:44 PM

Answers

  • User-925286913 posted

    You can create comma separated string from selected check boxes. Like:

    ''' <summary>
    ''' Gets comma separates string from selected items of CheckBoxList
    ''' </summary>
    ''' <param name="chk"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetStringFromCheckBoxList(ByVal chk As CheckBoxList) As String
    
    	Dim strTemp As String = String.Empty
    
    	For Each lst As ListItem In chk.Items
    		If lst.Selected Then
    			strTemp = strTemp & lst.Value & ","
    		End If
    	Next
    
    	Return strTemp.Trim(",")
    
    End Function


    Then Split this comma separated values in SQL query to table using below SQL user defined function:

    CREATE FUNCTION Split
    (
      @delimited nvarchar(max),
      @delimiter nvarchar(100)
    ) RETURNS @t TABLE
    (
      val nvarchar(max)
    )
    AS
    BEGIN
      declare @xml xml
      set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
    
      insert into @t(val)
      select
        r.value('.','varchar(5)') as item
      from @xml.nodes('//root/r') as records(r)
    
      RETURN
    END
    GO


    For example:

    If you are checking for categories, you can create comma separed categoryIds. e.g. 1,2,3,4,7,10

    And in SQL use it as:

    Select * From Products Where CategoryId IN (Select val From dbo.Split('1,2,3,4,7,10', ','));

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 23, 2010 10:56 AM

All replies

  • User-925286913 posted

    Refer:

    http://www.mikesdotnetting.com/Article/53/Saving-a-user%27s-CheckBoxList-selection-and-re-populating-the-CheckBoxList-from-saved-data

    http://www.codersbarn.com/post/2008/10/12/Bind-CheckBoxList-to-DataSet.aspx


    Wednesday, June 23, 2010 3:14 AM
  • User1578982284 posted

    Thanks for the links. The links you provided me are returning the checkboxes if I want to update them.

    What I have on this page is the checkboxes are being used as a search. When the user selected a box and click on the submit button, it will look inside the data and return the corresponding results.

    I have the checkboxes sent to a string. I'm trying to compare it to the data. Thats is the location where I'm having problems.

    Wednesday, June 23, 2010 9:25 AM
  • User-925286913 posted

    You can create comma separated string from selected check boxes. Like:

    ''' <summary>
    ''' Gets comma separates string from selected items of CheckBoxList
    ''' </summary>
    ''' <param name="chk"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetStringFromCheckBoxList(ByVal chk As CheckBoxList) As String
    
    	Dim strTemp As String = String.Empty
    
    	For Each lst As ListItem In chk.Items
    		If lst.Selected Then
    			strTemp = strTemp & lst.Value & ","
    		End If
    	Next
    
    	Return strTemp.Trim(",")
    
    End Function


    Then Split this comma separated values in SQL query to table using below SQL user defined function:

    CREATE FUNCTION Split
    (
      @delimited nvarchar(max),
      @delimiter nvarchar(100)
    ) RETURNS @t TABLE
    (
      val nvarchar(max)
    )
    AS
    BEGIN
      declare @xml xml
      set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
    
      insert into @t(val)
      select
        r.value('.','varchar(5)') as item
      from @xml.nodes('//root/r') as records(r)
    
      RETURN
    END
    GO


    For example:

    If you are checking for categories, you can create comma separed categoryIds. e.g. 1,2,3,4,7,10

    And in SQL use it as:

    Select * From Products Where CategoryId IN (Select val From dbo.Split('1,2,3,4,7,10', ','));

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 23, 2010 10:56 AM
  • User2108892867 posted

    Thank you very much for the post,chintanpshah. It is very useful. I just would like to know where you call the Function GetStringFromCheckBoxList. I don't see any relation between this function and the SQL split function. If you don't mind, can you elaborate on this one?

    Thanks.

    Regards;



    Tuesday, January 11, 2011 6:30 AM