Databases
StackOverflow2013
Postsdbo
POHow to pass multi value of checkbox list to stored procedure in code behind?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to pass multi value of checkbox list to stored procedure in code behind?
    text
    copied!<p>I have developed a page like as below:</p> <pre><code>&lt;table&gt; &lt;tr&gt; &lt;td align="right"&gt; Collection Area&lt;/td&gt; &lt;td&gt; :&lt;/td&gt; &lt;td align="left"&gt; &lt;asp:CheckBoxList ID="ChkCollAreaNewFile" runat="server"&gt; &lt;asp:ListItem Value="Retail Banking Division"&gt;Retail Banking Division&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="Chittagong Zone"&gt;Chittagong Zone&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="Khulna"&gt;Khulna&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="Sylhet"&gt;Sylhet&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="Rajshahi"&gt;Rajshahi&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="Barisal"&gt;Barisal&lt;/asp:ListItem&gt; &lt;/asp:CheckBoxList&gt; &lt;/td&gt; &lt;td align="right"&gt; Disbursed Area&lt;/td&gt; &lt;td&gt; &amp;nbsp;&lt;/td&gt; &lt;td align="left" style="margin-left: 40px"&gt; &lt;asp:CheckBoxList ID="ChkDisbAreaNewFile" runat="server" DataSourceID="DSDisbAreaNewFile" DataTextField="RegionName" DataValueField="RegionName"&gt; &lt;/asp:CheckBoxList&gt; &lt;asp:SqlDataSource ID="DSDisbAreaNewFile" runat="server" ConnectionString="&lt;%$ ConnectionStrings:OptimaWebCustomerQueryCon %&gt;" SelectCommand="SELECT [RegionName] FROM [DDRegion]"&gt;&lt;/asp:SqlDataSource&gt; &lt;/td&gt; &lt;td&gt; &amp;nbsp;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td align="right"&gt; &amp;nbsp; Loan Type&lt;/td&gt; &lt;td&gt; &amp;nbsp;:&lt;/td&gt; &lt;td align="left"&gt; &lt;asp:DropDownList ID="DDLLoanTypeNewFile" runat="server" AppendDataBoundItems="true" DataSourceID="DSAssignCELoanType" DataTextField="LType" DataValueField="LType"&gt; &lt;asp:ListItem Value="%"&gt;-Select-&lt;/asp:ListItem&gt; &lt;/asp:DropDownList&gt; &lt;asp:SqlDataSource ID="DSAssignCELoanType" runat="server" ConnectionString="&lt;%$ ConnectionStrings:OptimaWebCustomerQueryCon %&gt;" SelectCommand="SELECT [LType] FROM [DDLoanType] ORDER BY [LType]"&gt; &lt;/asp:SqlDataSource&gt; &lt;/td&gt; &lt;td align="right"&gt; Cycle&lt;/td&gt; &lt;td&gt; :&lt;/td&gt; &lt;td align="left"&gt; &lt;asp:DropDownList ID="DDLCycleNewFile" runat="server"&gt; &lt;asp:ListItem Value=""&gt;-Select-&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="X"&gt;X&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="1"&gt;1&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="2"&gt;2&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="3"&gt;3&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="4"&gt;4&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="5"&gt;5&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="6"&gt;6&lt;/asp:ListItem&gt; &lt;asp:ListItem Value="7"&gt;7&lt;/asp:ListItem&gt; &lt;/asp:DropDownList&gt; &lt;/td&gt; &lt;td&gt; &amp;nbsp;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td align="right"&gt; &amp;nbsp;&lt;/td&gt; &lt;td&gt; &amp;nbsp;&lt;/td&gt; &lt;td align="left"&gt; &lt;asp:Label ID="lblMsBx" runat="server" Text="Label"&gt;&lt;/asp:Label&gt; &lt;/td&gt; &lt;td align="right"&gt; &lt;asp:TextBox ID="TextBox1" runat="server"&gt;&lt;/asp:TextBox&gt; &lt;/td&gt; &lt;td&gt; &amp;nbsp;&lt;/td&gt; &lt;td align="right" style="margin-left: 40px"&gt; &lt;asp:TextBox ID="TextBox2" runat="server"&gt;&lt;/asp:TextBox&gt; &lt;asp:Button ID="btnRankUpdate" runat="server" Text="Update" onclick="btnRankUpdate_Click"/&gt; &lt;/td&gt; &lt;td&gt; &amp;nbsp;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td align="right"&gt; &amp;nbsp;&lt;/td&gt; &lt;td&gt; &amp;nbsp;&lt;/td&gt; &lt;td align="left"&gt; &amp;nbsp;&lt;/td&gt; &lt;td align="right"&gt; &amp;nbsp;&lt;/td&gt; &lt;td&gt; &amp;nbsp;&lt;/td&gt; &lt;td align="right" style="margin-left: 40px"&gt; &amp;nbsp;&lt;/td&gt; &lt;td&gt; &amp;nbsp;&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; </code></pre> <p>When I press button my stored procedure will be run and update the record. my stored procedure as below:</p> <pre><code>SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[WebRecNewFileAssign] @ParmAppLoanType varchar(100), @ParmBRegion varchar(1000), --Disbursed Area @ParmUserBrDiv varchar(1000), -- Collection Area @AssingCycle varchar(100) as begin DECLARE db_cursor CURSOR FOR SELECT dbo.TblDisburseInfo.DisbID FROM dbo.TblDisburseInfo INNER JOIN dbo.TblMasterInfo ON dbo.TblDisburseInfo.DisbAppID = dbo.TblMasterInfo.AppID WHERE (dbo.TblDisburseInfo.DisbRecAssignTo IS NULL) AND (dbo.TblMasterInfo.AppLoanType = @ParmAppLoanType) AND (dbo.TblDisburseInfo.DisbBr in(SELECT BrName FROM DDBranchName Where BRegion in(@ParmBRegion) )) ORDER BY dbo.TblDisburseInfo.DisbAmt DESC declare @tdisbid as int declare @cnt as int set @cnt = 1 declare @maxcnt as int select @maxcnt = MAX(reid) from [dbo].[TblRecAssignName] WHERE ReAssignName in (SELECT LogId FROM TblUserTest where UserBrDiv in(@ParmUserBrDiv) AND ReCycle=@AssingCycle) OPEN db_cursor FETCH NEXT FROM db_cursor INTO @tdisbid WHILE @@FETCH_STATUS = 0 BEGIN update [OptimaUCBL].[dbo].[TblDisburseInfo] set DisbRecAssignTo = (select ReAssignName from [OptimaUCBL].[dbo].[TblRecAssignName] where [ReID] = @cnt AND ReAssignName in (SELECT LogId FROM TblUserTest where UserBrDiv in (@ParmUserBrDiv) AND ReCycle=@AssingCycle)) where [DisbID] = @tdisbid if @cnt = @maxcnt set @cnt = 1 else set @cnt = @cnt + 1 FETCH NEXT FROM db_cursor INTO @tdisbid END CLOSE db_cursor DEALLOCATE db_cursor end </code></pre> <p>Finally my code (C#)::</p> <pre><code> protected void btnRankUpdate_Click(object sender, EventArgs e) { string myDisbArea = ""; for (int i = 0; i &lt; ChkDisbAreaNewFile.Items.Count; i++) { if (ChkDisbAreaNewFile.Items[i].Selected) { myDisbArea = myDisbArea + "'" + ChkDisbAreaNewFile.Items[i].Value + "' ,"; } } string NewDisbAreaNewFile = myDisbArea.Substring(myDisbArea.Length - (myDisbArea.Length), (myDisbArea.Length - 1)); string AreaSelected = ""; for (int i = 0; i &lt; ChkCollAreaNewFile.Items.Count; i++) { if (ChkCollAreaNewFile.Items[i].Selected) { AreaSelected = AreaSelected + "'" + ChkCollAreaNewFile.Items[i].Value + "' ,"; } } string NewAreaSelected = AreaSelected.Substring(AreaSelected.Length - (AreaSelected.Length), (AreaSelected.Length - 1)); String strConnString = ConfigurationManager.ConnectionStrings["OptimaWebCustomerQueryCon"].ConnectionString; SqlConnection con = new SqlConnection(strConnString); SqlCommand com = new SqlCommand(); com.CommandType = CommandType.StoredProcedure; com.CommandText = "WebRecNewFileAssign"; com.Parameters.AddWithValue("@ParmAppLoanType", SqlDbType.VarChar).Value = DDLLoanTypeNewFile.SelectedValue; com.Parameters.AddWithValue("@ParmBRegion", SqlDbType.VarChar).Value = Convert.ToString(NewDisbAreaNewFile); com.Parameters.AddWithValue("@ParmUserBrDiv", SqlDbType.VarChar).Value = Convert.ToString(NewAreaSelected); com.Parameters.AddWithValue("@AssingCycle", SqlDbType.VarChar).Value = DDLCycleNewFile.SelectedValue; com.Connection = con; try { con.Open(); com.ExecuteNonQuery(); lblMsBx.Text = "Record updated successfully"; } catch (Exception ex) { throw ex; } finally { con.Close(); con.Dispose(); } } </code></pre> <p>When I pressed update button <code>Record updated successfully</code> message show but record is not updated. Where is my problem, I can't identify. Pls support me any one.</p> <p>Thanks</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload