Checkboxlist项目传递给sql服务器

本文介绍了Checkboxlist项目传递给sql服务器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有批量电子邮件asp.net页面，用户可以从复选框列表中选择公司，我想要的结果是在所选公司待定公司工作的人员的所有记录的网格视图中显示。如果页面默认加载没有gridview显示哪个好，基本上我想要的是如果用户选择其中一个复选项，它必须显示与选择的复选框相关的记录，例如，如果用户选择公司Microsoft显示所有为微软工作的人 我的sql表（电子邮件）设计是：id，name，company，email **代码：** // 我的复选框列表的SelectedIndexChanged 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 = 没有为该公司捕获的雇员。; } } // 我的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; // 输入参数 SqlCommand.Parameters.AddWithValue（ @ SelectedDivisions，SelectedDivisions）; 尝试 { 使用（SqlConnection） { SqlConnection.Open（）; 使用（SqlCommand） { 使用 （SqlDataAdapter SqlDataAdapter = new SqlDataAdapter（）） { SqlDataAdapter.SelectCommand = SqlCommand; SqlDataAdapter.Fill（DataSet）; } // 阅读器关闭并在此处理 } // 此处处置的命令 } // 连接关闭并在此处理 } catch （例外） { } return DataSet; } **分割功能：** USE [样本] 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第一次在LOOP SELECT @INDEX = 1 WHILE @INDEX！= 0 BEGIN SELECT @INDEX = CHARINDEX（ @Delimiter，@ STRING） IF @INDEX！= 0 SELECT @SLICE = LEFT（@ STRING，@ INDEX - 1 ） ELSE SELECT @SLIC E = @STRING INSERT INTO @Results（Items）VALUES（@SLICE） SELECT @STRING = RIGHT（@ STRING，LEN（@STRING） - @INDEX） 如果LEN（@STRING）= 0 BREAK END RETURN END { grEmp.Visible = true ; LabelAllcompany.Text = 没有为此部门捕获客户。; } 这个 我的checkboxlist和gridview标记代码 < 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 = 无宽度= 100％ CellPadding = 4 ForeColor = ＃333333 > <％ - OnSelectedIndexChanged = Country_Selected - ％> < AlternatingRowStyle BackColor = White ForeColor = ＃284775 /> < Columns> < asp：BoundField DataField = id HeaderText = id可见= 错误 /> < asp：BoundField DataField = 名称 HeaderText = 名称 /> < asp：BoundField DataField = 公司 HeaderText = 公司 /> < asp：BoundField DataField = 电子邮件 HeaderText = 电子邮件ID /> < asp：TemplateField HeaderText = CheckAll > < HeaderTemplate> < asp：CheckBox ID = chkSelectAll runat = server AutoPostBack = true OnCheckedChanged = chkSelectAll_CheckedChanged / >向所有人发送邮件？ < / HeaderTemplate > < ItemTemplate> < asp：CheckBox ID = chkSelect runat = server /> < / ItemTemplate > < / asp：TemplateField > < / 列 > < EditRowStyle BackColor = ＃999999 / > < FooterStyle BackColor = ＃5D7B9D Font-Bold = True ForeColor = 白色 /> < HeaderStyle BackColor = ＃5D7B9D Font-Bold = True ForeColor = 白色 /> < PagerStyle BackColor = ＃284775 ForeColor = 白色 Horizo​​ntalAlign = 中心 /> < 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 > 即使在点击grEmp.DataSource = myDataView之前，我也会收到以下错误; 找不到表 0 。 描述：执行当前Web请求期间发生了未处理的异常。请查看堆栈跟踪 有关错误的更多信息，其中源自代码中的关键字>。 异常详细信息：System.IndexOutOfRangeException：找不到表 0 。 来源错误： 行 221 ：DataSetClients = JGSDataAccess.GetMultipleDivisionCompanies（SelectedDivisions）; 行 222 ：行 223 ： if （DataSetClients！= null && DataSetClients.Tables！= null && DataSetClients.Tables [ 0 ]。Rows.Count > 0 ）行 224 ：{行 225 ：grEmp.Visible = true ; 我已经厌倦了以下代码，但我仍然有点迷失，无法让它工作任何建议都会有所帮助解决方案 显而易见的原因似乎是您的数据集未填充，因此它不会在其中存在您尝试通过 DataSetClients.Tables [0] 调试并查看数据集是否已填满或尝试在访问前填写相同内容。 另外，你可以设置 SET NOCOUNT ON; $ SP $ b 在您的SProcs中返回行。 希望这会有所帮助。 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 MicrosoftMy 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 解决方案 The obvious reason seems to be your Dataset is not filled, so it wont have any Datatables present in it which you are trying to access thru DataSetClients.Tables[0]Debug and see whether the Dataset is filled or try to fill the same before accessing. Also , you can set theSET NOCOUNT ON; in your SProcs which is is returning the rows.Hope this helps. 这篇关于Checkboxlist项目传递给sql服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！