Click here to Skip to main content
14,735,317 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi all i need to add multiple selected checkboxlist items to one row under the applications column, e.g. Business Central, Opera PMS, Micros. I am struggling to be cable to figure out the script to write.

Below is my aspx code:
<td class="auto-style15">

                        <asp:CheckBoxList ID="AppList" runat="server" Height="60px" RepeatColumns="3" RepeatDirection="Horizontal" Width="486px">
                           <asp:ListItem>Business Central</asp:ListItem>
                           <asp:ListItem>WineMS</asp:ListItem>
                           <asp:ListItem>CPAR</asp:ListItem>
                           <asp:ListItem>OperaPMS</asp:ListItem>
                           <asp:ListItem>My Micos</asp:ListItem>
                           <asp:ListItem>Micros EMC</asp:ListItem>
                           <asp:ListItem>Jet Professional</asp:ListItem>
                           </asp:CheckBoxList>

                   </td>


Here is my C# code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;




namespace EmployeeRequest 
{
    public partial class EmployeeRequestForm : System.Web.UI.Page
    {

        string ConnectionString = @"Data Source=sh-jasonkNew\ICTDev;Initial Catalog=NewUserReqForm;Integrated Security=True;";
     

        protected void Page_Load(object sender, EventArgs e)
        {
           
        }
      
        protected void btnSubmit_Click(object sender, EventArgs e)
        {

            if (txtName.Text == "" || txtSurname.Text == "" || txtDepartment.Text == "")
                lblErrorMessage.Text = "Please fill Mandatory Fields";
            using (SqlConnection sqlCon = new SqlConnection(ConnectionString))
            {

                sqlCon.Open();
                
                SqlCommand SqlCmd = new SqlCommand("EmployeeAdd", sqlCon);
                SqlCmd.CommandType = CommandType.StoredProcedure;
                SqlCmd.Parameters.AddWithValue("@Name", txtName.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@Surname", txtSurname.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@Department", txtDepartment.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@EmploymentType", ddlEmploymentType.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@CommenceDate", DateTime.Now);
                SqlCmd.Parameters.AddWithValue("@JobTitle", txtJobTitle.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@TelephoneExt", txtTelephoneExt.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@Gender", ddlGender.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@Workstations", ddlWorkstations.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@Applications", AppList.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@FolderAccessRights", txtFolderAccessRights.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@EmailGroupAccess", txtEmailGroupRights.Text.Trim());
                SqlCmd.Parameters.AddWithValue("@Authorisedby", txtAuth.Text.Trim());
                SqlCmd.ExecuteNonQuery();
                Clear();
                lblSuccessMessage.Text = "Information Submitted Successfully";



            }
        }

        void Clear()
        {
            txtName.Text = txtSurname.Text = txtDepartment.Text = Cal1.Text = ddlEmploymentType.Text = txtJobTitle.Text = txtTelephoneExt.Text = ddlGender.Text = txtFolderAccessRights.Text = txtEmailGroupRights.Text = txtAuth.Text ="";
            hfEmployeeID.Value = "";
            lblSuccessMessage.Text = lblErrorMessage.Text = "";

        }

        protected void Calendar1_SelectionChanged(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

            }
        }
        protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
        
        {
            string chkboxselect = "";
            for (int i=0;i<AppList.Items.Count;i++)
            {
                if(AppList.Items[i].Selected)
                {
                    if(chkboxselect == "")
                    {
                        chkboxselect = AppList.Items[i].Text;
                    }
                    else
                    {
                        chkboxselect += "," + AppList.Items[i].Text;
                    }
                }
            }
            string mainconn = ConfigurationManager.ConnectionStrings["NewUserReqFormConnectionString1"].ConnectionString;
            SqlCommand sqlconn = new SqlCommand(mainconn);
            string sqlquery = "insert into [dbo].[tbl_EmployeeAdd] ([Applications]) values ('"+ chkboxselect + "')";
           
            
        }
            

        protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
        {
       
           
        }


        
    }
}


What I have tried:

I have tried the following script but it still does not add the application names into the column. what it does it only add the abbreviation of the first selected checkbox e.g. Bus or Opr, Mic etc.

string chkboxselect = "";
           for (int i=0;i<AppList.Items.Count;i++)
           {
               if(AppList.Items[i].Selected)
               {
                   if(chkboxselect == "")
                   {
                       chkboxselect = AppList.Items[i].Text;
                   }
                   else
                   {
                       chkboxselect += "," + AppList.Items[i].Text;
                   }
               }
           }
           string mainconn = ConfigurationManager.ConnectionStrings["NewUserReqFormConnectionString1"].ConnectionString;
           SqlCommand sqlconn = new SqlCommand(mainconn);
           string sqlquery = "insert into [dbo].[tbl_EmployeeAdd] ([Applications]) values ('"+ chkboxselect + "')";


       }
Posted
Comments
Richard MacCutchan 6-May-20 5:35am
   
The code adds exactly what you tell it in the above listing. You need to check what is returned by AppList.Items[i].Text. Or maybe there is just not enough space on the display to show the complete words.
CHill60 6-May-20 9:40am
   
It is very, very bad practice to store multiple values like this - i.e. in a comma-separated list in a single column. Use a linked table that contains the checked values with a foreign key link into the EmployeeAdd table - one row in that linked table per check box per employee
SuperJWP 6-May-20 10:03am
   
The employee use multiple applications, therefore, i cannot create multiple rows for one user with all relevant applications the employee use.
Richard Deeming 7-May-20 10:15am
   
You don't need to. You create one row for the employee. Then you have a separate table which stores the applications for that employee.

Since you presumably have a standard list of applications, a many-to-many relationship would be best.

Applications: ApplicationID, Name
Contains the standard list of applications. One row per application.

Employee: EmployeeID, Name, ...
Contains the employee details. One row per employee.

EmployeeApplications: EmployeeID, ApplicationID
Links the employee record to the applications they use. One row for each employee + application combination.

Eg:
Applications
1 | Business Central
2 | WineMS
3 | CPAR

Employees
1 | E1 | ...
2 | E2 | ...
3 | E3 | ...

EmployeeApplications
-- E1 uses Business Central and CPAR:
1 | 1
1 | 3
-- E2 uses Business Central, WineMS, and CPAR:
2 | 1
2 | 2
2 | 3
-- E3 uses WineMS
3 | 2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900