Checkboxlist items passing to sql sever

See more:

ASP.NET

I have the Bulk email asp.net page where the user can select a company from a checkboxlist and the result I want is to display in a gridview of all the records of people who works under the company pending on the company selected. If the page loads up by default no gridview displays which is fine, Basically what I want is if the user selects one of the checkboxs items it must display the record related to the checkbox that was select so for example if the user selects the company Microsoft display all the people working for Microsoft


My sql table(Email) design is: id ,name, company,email

**CODE:**

    // SelectedIndexChanged of my checkboxlist
    protected void cblCompany_SelectedIndexChanged(object sender, EventArgs e)
    {
        LabelAllcompany.Text = string.Empty;
        grEmp.Visible = true;
        grEmp.PageIndex = 0;

        StringBuilder StringBuilderSelectedDivisions = new StringBuilder();
        foreach (ListItem item in cblCompany.Items)
        {
            if (item.Selected)
            {
                StringBuilderSelectedDivisions.Append(item.Value + ",");
            }
        }

        SelectedDivisions = StringBuilderSelectedDivisions.ToString().TrimEnd(',');
        BindClientGridViewDataMultipleCompanpies(SelectedDivisions);
    }


    //SelectedDivisions

    public void BindClientGridViewDataMultipleCompanpies(string SelectedDivisions)
    {
        LabelAllcompany.Text = SelectedDivisions;
        DAL DataAccess = new DAL();
        DataSet DataSetCompanies = new DataSet();
        DataSetCompanies = DataAccess.GetMultipleDivisionCompanies(SelectedDivisions);

         if (DataSetClients != null && DataSetClients.Tables != null && DataSetClients.Tables[0].Rows.Count > 0)
        {
            grEmp.Visible = true;

            DataView myDataView = new DataView();
            myDataView = DataSetCompanies.Tables[0].DefaultView;
            grEmp.DataSource = myDataView;
            grEmp.DataBind();
            LabelAllcompany.Text = string.Empty;
        }
        else
        {
            grEmp.Visible = true;
            LabelAllcompany.Text = "No employes captured for this company.";
        }

    }


    //my DAL
    public DataSet GetMultipleDivisionCompanies(string SelectedDivisions)
    {
        DataSet DataSet = new DataSet();

        SqlConnection SqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sampleConnectionString2"].ConnectionString);
        SqlCommand SqlCommand = new SqlCommand();
        SqlCommand.CommandType = CommandType.Text;
        SqlCommand.CommandText = @"SELECT DISTINCT id, Name, Company, Email
                                 FROM Email
                                 WHERE id IN (SELECT Items FROM Split(@SelectedDivisions, ','))
                                 ORDER BY Name, Company";
        //Input parameters
        SqlCommand.Parameters.AddWithValue("@SelectedDivisions", SelectedDivisions);

        try
        {
            using (SqlConnection)
            {
               SqlConnection.Open();

                using (SqlCommand)
                {
                    using (SqlDataAdapter SqlDataAdapter = new SqlDataAdapter())
                    {
                        SqlDataAdapter.SelectCommand = SqlCommand;
                        SqlDataAdapter.Fill(DataSet);
                    } // reader closed and disposed up here
                } // command disposed here
            } //connection closed and disposed here

        }
        catch (Exception )
        {

        }
        return DataSet;
    }




**Split function:**

    USE [sample]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
    RETURNS @Results TABLE (Items nvarchar(4000))
    AS
    BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    --     ERO FIRST TIME IN LOOP
    SELECT @INDEX = 1
    WHILE @INDEX !=0
    BEGIN

    SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)

    IF @INDEX !=0
    SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
    ELSE
    SELECT @SLICE = @STRING

    INSERT INTO @Results(Items) VALUES(@SLICE)

    SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)

    IF LEN(@STRING) = 0 BREAK
    END
    RETURN
    END



        {
            grEmp.Visible = true;
            LabelAllcompany.Text = "No clients captured for this division.";
        }

This is my checkboxlist and gridview markup code

      <asp:CheckBoxList
              ID="cblCompany" runat="server"
            OnSelectedIndexChanged="cblCompany_SelectedIndexChanged"
                AutoPostBack="True">
          </asp:CheckBoxList>
          <br />
                     <br />
                      <asp:GridView ID="grEmp" runat="server" AllowPaging="True" AutoGenerateColumns="False"
                       DataKeyNames="id" GridLines="None" Width="100%" CellPadding="4" ForeColor="#333333"> <%--OnSelectedIndexChanged="Country_Selected"--%>

                        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />

                        <Columns>
                            <asp:BoundField DataField="id" HeaderText="id" Visible="False" />
                            <asp:BoundField DataField="Name" HeaderText="Name"  />
                            <asp:BoundField DataField="Company" HeaderText="Company"  />
                           <asp:BoundField DataField="Email" HeaderText="Email Id" />

                         <asp:TemplateField HeaderText="CheckAll">
                        <HeaderTemplate>
                            <asp:CheckBox ID="chkSelectAll" runat="server"
                                          AutoPostBack="true"
                                          OnCheckedChanged="chkSelectAll_CheckedChanged"/>Send Mail To All ?
                        </HeaderTemplate>
                            <ItemTemplate>
                            <asp:CheckBox ID="chkSelect" runat="server"/>
                            </ItemTemplate>
                         </asp:TemplateField>
                        </Columns>
                        <EditRowStyle BackColor="#999999" />
                        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                        <SortedAscendingCellStyle BackColor="#E9E7E2" />
                        <SortedAscendingHeaderStyle BackColor="#506C8C" />
                        <SortedDescendingCellStyle BackColor="#FFFDF8" />
                        <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                    </asp:GridView>

Im getting the following error even before hits the grEmp.DataSource = myDataView;


    Cannot find table 0.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.IndexOutOfRangeException: Cannot find table 0.

    Source Error:


    Line 221:            DataSetClients = JGSDataAccess.GetMultipleDivisionCompanies(SelectedDivisions);
    Line 222:
    Line 223:            if (DataSetClients != null && DataSetClients.Tables != null && DataSetClients.Tables[0].Rows.Count > 0)
    Line 224:            {
    Line 225:                grEmp.Visible = true;

I have tired the following code but Im still a little bit lost and cant get it to work any advise will be helpful