jkhaung jkhaung - 3 months ago 27
SQL Question

how to save multiple selected checkboxlist to database asp .net c#

I have two checkboxlists CandidateName and Payment, and a dropdownlist. User can do multiple selections in checkboxlists and one selection at dropdownlist. I am populating those data of chechboxlists and dropdownlist to Panel nested inside UpdatePanel where gridview exist.. When I click on Add Button to open up Panel, all data is properly populating. When I tried to save multiple selected checkedboxlists through "for loop" at debugging, it is not going inside the loop to accumulate checked data with comma to save at database. Instead, after chekcing "if (cblPayment1.Items[j].Selected == true)", it skips over to another query statement. Please help me advise.

protected void btnAddNew_Click(object sender, EventArgs e)
{
try
{

string strcbl1 = string.Empty;
string strcbl2 = string.Empty;
compCon = new SqlConnection(strConnectionString);
compCon.Open();


for (int i = 0; i < cblCandidateName1.Items.Count-1; i++)
{
if (cblCandidateName1.Items[i].Selected ==true)
{
strcbl1 = strcbl1 + cblCandidateName1.Items[i].Value.ToString() + ",";
}
}

for (int j = 0; j < cblPayment1.Items.Count - 1; j++)
{
if (cblPayment1.Items[j].Selected == true)
{
strcbl2 += cblPayment1.Items[j].Value.ToString() + ",";
}
}


string InsertSchedule = "INSERT INTO ScheduleEmail(CANDIDATE_ID, PAYMENT_ID, TEMPLATE_ID)VALUES(@strCID, @strPID, @strCourseID)";
SqlCommand InsertScheduleCommand = new SqlCommand(InsertSchedule, compCon);
InsertScheduleCommand.Connection = compCon;


InsertScheduleCommand.Parameters.AddWithValue(@"strCID", strcbl1);
InsertScheduleCommand.Parameters.AddWithValue(@"strPID", strcbl2);
InsertScheduleCommand.Parameters.AddWithValue(@"strCourseID", strCourseID);

InsertScheduleCommand.ExecuteNonQuery();
compCon.Close();
}
catch (Exception ex)
{
ShowPopUpMsg("Data is failed to save in table. Please contact your system administrator \n" + ex.ToString());
}
finally
{
DispalyGridViewScheduleEmail();

compCon.Close();
}

}


THis is my HTML Code.

<!--Panel to add new record--><asp:Panel ID="panelAddNew" runat="server"
style="display:none; background-color:gray;" ForeColor="Black" Width="500" Height="550">
<asp:Panel ID="panelAddNewTitle" runat="server"
style="cursor:move;font-family:Tahoma;
padding:2px;" HorizontalAlign="Center" BackColor="Blue" ForeColor="White" Height="25">
<b>Add New</b></asp:Panel>
<table width="100%" style="padding:5px">
<tr>
<td colspan="3">
<asp:Label ID="lblStatus1" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td><b>Select Candidate Name(s)</b></td>
<td><b>:</b></td>
<td><asp:CheckBoxList ID="cblCandidateName1" runat="server" RepeatColumns="3" RepeatDirection="Horizontal" RepeatLayout="table">
</asp:CheckBoxList>
</td>
</tr>
<tr>
<td><b>Select Payments</b></td>
<td><b>:</b></td>
<td>
<asp:CheckBoxList ID="cblPayment1" runat="server" RepeatDirection="Horizontal" RepeatLayout="Table"></asp:CheckBoxList>
</td>
</tr>


<tr>
<td><b>Course Name</b></td>
<td><b>:</b></td>
<td> <asp:DropDownList ID="ddlCourseName" runat="server"> </asp:DropDownList>


</td>
</tr>
</table>
<br />
<div align="center">
<asp:Button ID="btnAddNew2" runat="server" Width="70" Text="Add" OnClick="btnAddNew_Click" ValidationGroup="add"/>
&nbsp;

<asp:Button ID="btnCancel1" runat="server" Width="70" Text="Cancel" CausesValidation="false" OnClick="Cancel_Click" ValidationGroup="add"/>
</div>
</asp:Panel>
<!--End of Panel to add new record-->

Answer

Both of your for blocks will not be executed at the last item of cblCandidateName1.Items and cblPayment1.Items because of this condition:

for (int i = 0; i < cblCandidateName1.Items.Count-1; i++)

and

for (int j = 0; j < cblPayment1.Items.Count - 1; j++)

You need to change both of your for blocks to the following

for (int i = 0; i < cblCandidateName1.Items.Count; i++)
{
    if (cblCandidateName1.Items[i].Selected ==true)
    {
        strcbl1 = strcbl1 + cblCandidateName1.Items[i].Value.ToString() + ",";
    }
}

for (int j = 0; j < cblPayment1.Items.Count; j++)
{
    if (cblPayment1.Items[j].Selected == true)
    {
        strcbl2 += cblPayment1.Items[j].Value.ToString() + ",";
    }
}

UPDATE

It seems that cblPayment1.Items[j].Selected is always false even though you check some of the payment checkboxlist. The possible cause could be that you populate cblPayment1 items in Page_Load method but not inside if (!this.IsPostBack) block

protected void Page_Load(object sender, EventArgs e)
{
    cblPayment1.DataSource = .... ;
    cblPayment1.DataBind();
} 

When you click the save button, Page_Load method will be executed first before btnAddNew_Click, so cblPayment1 will be repopulated and all of its items won't be selected when you reach btnAddNew_Click method. You need to change Page_Load code as the following

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        cblPayment1.DataSource = .... ;
        cblPayment1.DataBind();
    }
} 
Comments