Add checkboxes items to SQL using C#

Question

0.00/5 (No votes)

See more:

I need to add my checkbox items to SQL database but i am having difficulty finding the correct line of code to be able to add the items. The user will select multiple items as they are using multiple business applications.

Here is the SQL Table Code

SQL

CREATE TABLE [dbo].[tbl_EmployeeAdd](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Surname] [nvarchar](50) NULL,
	[Department] [nvarchar](50) NULL,
	[EmploymentType] [nvarchar](50) NULL,
	[CommenceDate] [date] NULL,
	[JobTitle] [nvarchar](50) NULL,
	[TelephoneExt] [nchar](10) NULL,
	[Gender] [nvarchar](10) NULL,
	[Workstations] [nvarchar](10) NULL,
	[BusinessApplication] [nvarchar](20) NULL,
	[FolderAccessRights] [nvarchar](250) NULL,
	[EmailGroupAccess] [nvarchar](250) NULL,
	[Authorisedby] [nvarchar](50) NULL
) ON [PRIMARY]
GO

Here is the C# code

C#

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", txtdept.Text.Trim());
        SqlCmd.Parameters.AddWithValue("@EmploymentType", ComboEmpType.Text.Trim());
        SqlCmd.Parameters.AddWithValue("@CommenceDate", dateTimePicker1.Text.Trim());
        SqlCmd.Parameters.AddWithValue("@Workstations", comboWkst.Text.Trim());
        SqlCmd.Parameters.AddWithValue("@JobTitle", txtJobTit.Text.Trim());
        SqlCmd.Parameters.AddWithValue("@TelephoneExt", txtTel.Text.Trim());
        SqlCmd.Parameters.AddWithValue("@Gender", comboGen.Text.Trim());
        SqlCmd.Parameters.AddWithValue("@BusinessApplication", CPAR.Text.Trim());
        SqlCmd.Parameters.AddWithValue("@FolderAccessRights", txtAxxessRights.Text.Trim());
        SqlCmd.Parameters.AddWithValue("@EmailGroupAccess", EmailGroup.Text.Trim());
        SqlCmd.Parameters.AddWithValue("@Authorisedby", AuthBy.Text.Trim());


        SqlCmd.ExecuteNonQuery();
        MessageBox.Show("Employee Request Form Successfully Completed");
        Clear();

Here is my stored procedure

SQL

CREATE PROC [dbo].[EmployeeAdd]

@Name nvarchar(50),
@Surname nvarchar(50),
@Department nvarchar(50),
@EmploymentType nvarchar(50),
@CommenceDate date,
@JobTitle nvarchar(50),
@TelephoneExt nchar(10),
@Gender nvarchar(10),
@Workstations nvarchar(10),
@BusinessApplication nvarchar(20),
@FolderAccessRights nvarchar(250),
@EmailGroupAccess nvarchar(250),
@Authorisedby nvarchar(50)


AS 
	INSERT INTO tbl_EmployeeAdd (
								Name,Surname,Department,EmploymentType,CommenceDate,JobTitle,TelephoneExt,Gender,
								Workstations,BusinessApplication,FolderAccessRights,EmailGroupAccess,Authorisedby							
	) 
	VALUES (@Name,@Surname,@Department,@EmploymentType,@CommenceDate,@JobTitle,@TelephoneExt,@Gender,@Workstations,
			@BusinessApplication,@FolderAccessRights,@EmailGroupAccess,@Authorisedby) 
GO

What I have tried:

i haven't tried anything as yet as i am new to this type of coding

Posted 30-Mar-20 5:14am

SuperJWP

Updated 1-Apr-20 8:53am

CHill60

v2

Add a Solution

Comments

CHill60 30-Mar-20 11:42am

Where do you want to store your checkbox details - there don't appear to be any columns for them

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2020-04-01T08:54:00

Quote:
The user will select multiple items as they are using multiple business applications.

Then that information should not be stored in the employee table.

You will need three tables:

The table which stores the list of employees;
The table which stores the list of business applications;
The table which stores the business applications which each employee uses;

SQL

CREATE TABLE [dbo].[tbl_EmployeeAdd](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Surname] [nvarchar](50) NULL,
    [Department] [nvarchar](50) NULL,
    [EmploymentType] [nvarchar](50) NULL,
    [CommenceDate] [date] NULL,
    [JobTitle] [nvarchar](50) NULL,
    [TelephoneExt] [nchar](10) NULL,
    [Gender] [nvarchar](10) NULL,
    [Authorisedby] [nvarchar](50) NULL,
    
    CONSTRAINT [PK_tbl_EmployeeAdd] PRIMARY KEY ([EmployeeID])
);

CREATE TABLE [dbo].[tbl_BusinessApplications](
    [BusinessApplicationID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](20) NOT NULL,
    
    CONSTRAINT [PK_tbl_BusinessApplications] PRIMARY KEY ([BusinessApplicationID])
);

CREATE TABLE [dbo].[tbl_Employee_BusinessApplications](
    [EmployeeID] [int] NOT NULL,
    [BusinessApplicationID] [int] NOT NULL,
    
    CONSTRAINT [PK_tbl_Employee_BusinessApplications] PRIMARY KEY ([EmployeeID], [BusinessApplicationID]),
    CONSTRAINT [FK_tbl_Employee_BusinessApplications_Employee) FOREIGN KEY ([EmployeeID]) REFERENCES [dbo].[tbl_EmployeeAdd] ([EmployeeID]) ON DELETE CASCADE,
    CONSTRAINT [FK_tbl_Employee_BusinessApplications_Application) FOREIGN KEY ([BusinessApplicationID]) REFERENCES [dbo].[tbl_BusinessApplications] ([BusinessApplicationID]) ON DELETE CASCADE
);

You will need to pass the IDs of the selected business applications to your stored procedure. There are several ways to do that - for example, using Table Valued Parameters:
Use Table-Valued Parameters (Database Engine) - SQL Server | Microsoft Docs[^]
Table-Valued Parameters - ADO.NET | Microsoft Docs[^]
Using Table-Valued Parameters in SQL Server and .NET[^]

SQL

CREATE TYPE [dbo].[IntegerList] As TABLE ([Value] int NOT NULL);

SQL

CREATE PROC [dbo].[EmployeeAdd]
(
    @Name nvarchar(50),
    @Surname nvarchar(50),
    @Department nvarchar(50),
    @EmploymentType nvarchar(50),
    @CommenceDate date,
    @JobTitle nvarchar(50),
    @TelephoneExt nchar(10),
    @Gender nvarchar(10),
    @Authorisedby nvarchar(50),
    @BusinessApplications [dbo].[IntegerList] READONLY
)
As
BEGIN
DECLARE @EmployeeID int;
    
    SET NOCOUNT ON;
    
    INSERT INTO dbo.tbl_EmployeeAdd
    (
        Name,
        Surname,
        Department,
        EmploymentType,
        CommenceDate,
        JobTitle,
        TelephoneExt,
        Gender,
        Authorisedby							
    ) 
    VALUES 
    (
        @Name,
        @Surname,
        @Department,
        @EmploymentType,
        @CommenceDate,
        @JobTitle,
        @TelephoneExt,
        @Gender,
        @Authorisedby
    );
    
    SET @EmployeeID = Scope_Identity();
    
    INSERT INTO dbo.tbl_Employee_BusinessApplications
    (
        EmployeeID,
        BusinessApplicationID
    )
    SELECT
        @EmployeeID,
        A.BusinessApplicationID
    FROM
        @BusinessApplications As L
        INNER JOIN dbo.tbl_BusinessApplications As A
        ON A.BusinessApplicationID = L.Value
    ;
END
GO

NB: Based on the names, I suspect you'll need to do something similar for Workstations, FolderAccessRights, and EmailGroupAccess as well.

MadMyche · Answer 2 · 2020-03-30T06:09:00

To add additional columns to a table can be easily done with ALTER TABLE

SQL

ALTER TABLE dbo.tbl_EmployeeAdd 
ADD  Checkbox1  BIT  NULL
,    Checkbox2  BIT  NULL
GO

After that is done you will need to do an ALTER PROCEDURE to utilize those new fields as well as editing your C# code to populate them

Reference:
Add Columns to a Table (Database Engine) - SQL Server | Microsoft Docs[^]

phil.o · Answer 3 · 2020-03-30T06:10:00

Assuming you have the data in a grid and you want to save only checked items: you just have to enumerate only those items which are checked, and pass each of them to your SP.

C#

using (SqlConnection SqlCon = new SqlConnection(connectionstring))
{
   SqlCon.Open();
                
   using (SqlCommand SqlCmd = new SqlCommand("EmployeeAdd", SqlCon)
   {
      SqlCmd.CommandType = CommandType.StoredProcedure;

      SqlCmd.Parameters.Add("@Name", DbType.String);
      SqlCmd.Parameters.Add("@Surname", DbType.String);
      SqlCmd.Parameters.Add("@Department", DbType.String);
      SqlCmd.Parameters.Add("@EmploymentType", DbType.String);
      SqlCmd.Parameters.Add("@CommenceDate", DbType.String);
      SqlCmd.Parameters.Add("@Workstations", DbType.String);
      SqlCmd.Parameters.Add("@JobTitle", DbType.String);
      SqlCmd.Parameters.Add("@TelephoneExt", DbType.String);
      SqlCmd.Parameters.Add("@Gender", DbType.String);
      SqlCmd.Parameters.Add("@BusinessApplication", DbType.String);
      SqlCmd.Parameters.Add("@FolderAccessRights", DbType.String);
      SqlCmd.Parameters.Add("@EmailGroupAccess", DbType.String);
      SqlCmd.Parameters.Add("@Authorisedby", DbType.String);

      foreach (var item in theGrid.CheckedItems)
      {
         SqlCmd.Parameters["@Name"].Value = item["Name"].Trim();
         SqmCmd.Parameters["@Surname"].Value = item["Surname"].Trim();
         // ...
         SqlCmd.ExecuteNonQuery();
      }
   }
   MessageBox.Show("Employee Request Form Successfully Completed");
   Clear();
}

Add checkboxes items to SQL using C#

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0