Link to home
Start Free TrialLog in
Avatar of Brian
BrianFlag for United States of America

asked on

Add multiple values from CheckBoxList Control

Hello,

I'm trying to find a way to add multiple values into a single field in my DB if the user would select multiple choices in a CheckBoxList server control. My layout is below.

All values from CheckBoxList control are getting populated from a Single table in my DB from a Stored Procedure that I call on Page_Load. This populates the CheckBoxList Control with 3 checkboxes.

I have an Insert Event that will only insert one value even if the user selects all three values from the CheckBoxList control.

Thanks in advance!
Avatar of Salim Fayad
Salim Fayad
Flag of Lebanon image

You can loop through each item in the CheckBoxList and check if it is selected, then add it to the DB. Or you can loop through each item, get all the values in a string variable, and send them once to a stored procedure that will loop through each one of them and add them to the DB.
Avatar of Brian

ASKER

Could you provide me with a sample of the code. I'm very new to CheckBoxList's. I understand binding and with the dropdown but first time with CheckBoxList control. I would really appreciate if you code help me with the code for this as I'm unable to.

Also the field that holds this value is an Int field. Will this matter?
Here is a sample of the code where I am looping on each checkbox and adding the item if it exists:
foreach(ListItem item in CheckBoxList1.Items)
{
    if (item.Selected)
         //Add the item.Value
    
    //Clear the selection of the item
    item.Selected = false;
}

Open in new window

Avatar of Brian

ASKER

I found this article online is this what I need to implement and if so what are they missing that I might need to add?

http://www.devasp.net/net/articles/display/267.html
This article mostly talks about the RadioButtonList. Mine is much simpler and for the CheckBoxList
Avatar of Brian

ASKER

1. What Page Event should I add your code?
2. Will this code work for what I need?
3. How do I bind multiple values from the CheckBoxList to a single Int field in the DB once I add this code and perform the Insert Event?
1. You shoud put this code when the user submits his data (when he adds his data)
2. This code will work
3. You should specify that the SqlParameter has a data type of int.
Avatar of Brian

ASKER

Okay, I added your code to my Insert Event below but nothing happened. I did not recieve any errors which is great but I'm guessing that I misplaced your code in the wrong area. Please see below.

Protected Sub HRInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles HRInsert.Click

        ' Define data objects
        Dim conn As SqlConnection
        Dim Insertcomm As SqlCommand

        ' Read the connection string from Web.config
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("HR").ConnectionString

        ' Initialize connection
        conn = New SqlConnection(connectionString)

        ' Create Stored Procedure
        Insertcomm = New SqlCommand("HR_EmpInsert", conn)
        Insertcomm.CommandType = CommandType.StoredProcedure

        Insertcomm.Parameters.Add("@BLDGID", System.Data.SqlDbType.Int)
        Insertcomm.Parameters("@BLDGID").Value = BuildingList.SelectedItem.Value

        Insertcomm.Parameters.Add("@EMPTITID", System.Data.SqlDbType.Int)
        Insertcomm.Parameters("@EMPTITID").Value = TitleList.SelectedItem.Value

        Insertcomm.Parameters.Add("@FLID", System.Data.SqlDbType.Int)
        Insertcomm.Parameters("@FLID").Value = FloorsList.SelectedItem.Value

        For Each item As ListItem In NeedsList.Items
            If item.Selected Then

                Insertcomm.Parameters.Add("@NID", System.Data.SqlDbType.Int)
                Insertcomm.Parameters("@NID").Value = NeedsList.SelectedItem.Value

                item.Selected = False
                'Add the item.Value

                'Clear the selection of the item
            End If
        Next

        Insertcomm.Parameters.Add("@HRNAME", System.Data.SqlDbType.VarChar, 50)
        Insertcomm.Parameters("@HRNAME").Value = HRNAME.Text

        Insertcomm.Parameters.Add("@HRSTART", System.Data.SqlDbType.DateTime)
        Insertcomm.Parameters("@HRSTART").Value = HRSTARTDATE.Text

        Insertcomm.Parameters.Add("@HRDEPT", System.Data.SqlDbType.VarChar, 50)
        Insertcomm.Parameters("@HRDEPT").Value = HRDEPT.Text

        Insertcomm.Parameters.Add("@HRSUPERVIS", System.Data.SqlDbType.VarChar, 50)
        Insertcomm.Parameters("@HRSUPERVIS").Value = HRSUPERVIS.Text

        Insertcomm.Parameters.Add("@HRPOSTITLE", System.Data.SqlDbType.VarChar, 50)
        Insertcomm.Parameters("@HRPOSTITLE").Value = HRPOSTITLE.Text

        Insertcomm.Parameters.Add("@HRNOTES", System.Data.SqlDbType.VarChar, 50)
        Insertcomm.Parameters("@HRNOTES").Value = HRNOTES.Text

        ' Enclose database code in Try-Catch-Finally
        Try
            ' Open the connection
            conn.Open()

            ' Execute the command
            Insertcomm.ExecuteNonQuery()
            ' Reload page if the query executed successfully
            Response.Redirect("question_success.aspx")
        Catch
            ' Display error message
            Label1.Text = "We are experiencing technical difficulties. Please try again later"
        Finally
            ' Close the connection
            conn.Close()
        End Try
    End Sub
You should put all of this in a loop.

But what I guess, is that you have a problem in your database schema since normally it should be a parent table that has a child table. The child table is represented by the CheckBoxList.
Avatar of Brian

ASKER

DB Schema below:

HR_EMPINFO (Main Table)
HRID int (primary key for table)
NID is FK from CheckBox Table

HR_NEEDS (Table that holds CheckBoxList Data)
NID int
NDESC varchar
So, your DB Schema is correct. But you should have 2 insert SPs:
1. To insert into the parent table of HR_EMPINFO
2. To insert into the HR_EMPINFO table

You call the insert command of the HR_EMPINFO in the loop
Avatar of Brian

ASKER

Very confused. If i run the code above and if I only select 1 value from the 3 values then the insert succeeds and I see the value that I chose from the CheckBoxList in the HR_EMPINFO Table (Main Table) within the field NID. But when I choose 2 or more values from the CheckBoxList and then perform the Insert it does not succed nor do I recieve an error message.
Ok, where are the following columns, in which table:
- BLDGID
- EMPTITID
- FLID
- HRNAME
- HRSTART
- HRDEPT
- HRSUPERVIS
- HRPOSTITLE
- HRNOTES
Avatar of Brian

ASKER

BLDGID = HR_BUILDING
EMPTITID = HR_TITLE
FLID = HR_FLOOR

HRNAME = HR_EMPINFO
HRSTART = HR_EMPINFO
HRDEPT = HR_EMPINFO
HRSUPERVIS = HR_EMPINFO
HRPOSTITLE = HR_EMPINFO
HRNOTES = HR_EMPINFO
Correct me if I am wrong. Your Database schema is the following:
- HR_EMPINFO: holds the employee information
- HR_NEEDS: is a lookup table that the CheckBoxList is binded
- HR_EMPINFO is linked to the HR_NEEDS: by a single column NID which is a FK. This is a 1N relationship. This is where it is wrong. You should have a MN relationship. For each employee, he can have many needs. To do that, you should create another table that holds a FK from the HR_EMPINFO table and a FK from the HR_NEEDS. In the loop, you insert into this table.
Avatar of Brian

ASKER

-HR_EMPINFO should hold all data along with FK values from the other tables.
-HR_NEEDS has three values within it. All values are pulled from the DB and bound to the CheckBoxList Control.
-HR_NEEDS is linked to HR_EMPINFO. HR_EMPINFO has a field called NID which is the Primary Key of HR_NEEDS. I would like the value or values of what is chosen from HR_NEEDS to be added to HR_EMPINFO in the NID field.
How are you going to save 2 or 3 integer values in 1 field? You cannot that's why you should have another table to hold those values
Avatar of Brian

ASKER

Okay, That is what I was not sure about. Now I'm stumped as to what I need to do now :(

Everything is fine except adding multiple values from the CheckBoxList control.
Here is what you have to do:
1. Remove the NID field from the HR_EMPINFO table (Note that this step is not required, but it is preferable and if you did it don't forget to change everything related to this: all the SPs, the code, ...)
2. Create a table called HR_EMPINFO_NEEDS (for example), that holds the following fields:
          a. EID: which is the FK of the HR_EMPINFO table
          b. NID: which is the FK of the HR_NEEDS table
3. Create a SP to insert into HR_EMPINFO_NEEDS (for example: HR_EmpNeedsInsert)
4. Change your code to reflect the following:
         a. Insert into HR_EMPINFO table
         b. Loop into the CheckBoxList items and insert them into HR_EMPINFO_NEEDS table
Avatar of Brian

ASKER

Okay, I completed Steps 1 - 2. I know how to create a SP but not sure how to create one for Step 3. Am I only adding HRID and NID fields into the SP for Step 3?

Not sure what or how to handle Step 4.
Step 3 is just to insert one single row into HR_EMPINFO_Needs table which is as you said adding HRID and NID fields.

As for Step 4, Ithe SP that inserts into HR_EMPINFO table should return the HRID as one of the following:
1. As a SELECT statement (if it is an identity column, then use SELECT @@IDENTITY)
2. Or as an output parameter

Make sure of that before we continue in Step 4
Avatar of Brian

ASKER

The eagle:

I'm currently at home now and my work laptop is there. I will start on this at 8:00 am tomorrow EST. I can't thank you enough for your help. Please check back tomorrow morning and I will go ahead and get started again.

Just so I know for tomorrow morning. How should I declare HRID field since it's the Primary Key of the main table? Also, should I use SELECT @@IDENTITY or SCOPE @IDENTITY?
Avatar of Brian

ASKER

Okay, below is my HR_EmpInsert SP that I modified. Notice the bottom line where i have SCOPE_IDENTITY.

ALTER PROCEDURE [dbo].[HR_EmpInsert]

(
@BLDGID int,
@EMPTITID int,
@FLID int,
@HRNAME varchar(50),
@HRSTART DateTime,
@HRDEPT varchar(50),
@HRSUPERVIS varchar(50),
@HRPOSTITLE varchar(50),
@HRNOTES varchar(50)
)

AS

INSERT HR_EMPINFO (BLDGID, EMPTITID, FLID, HRNAME, HRSTART, HRDEPT, HRSUPERVIS, HRPOSTITLE, HRNOTES)
VALUES (@BLDGID, @EMPTITID, @FLID, @HRNAME, @HRSTART, @HRDEPT, @HRSUPERVIS, @HRPOSTITLE, @HRNOTES)

DECLARE @HRID INT
SELECT @HRID = SCOPE_IDENTITY()
Avatar of Brian

ASKER

The SP below is for the New Table:

CREATE PROCEDURE HR_EmpNeedsInsert
(
@HRID int,
@NID int
)

AS

INSERT HR_EMPINFO_NEEDS (HRID, NID)
VALUES (@HRID, @NID)

GO
For the SP HR_EmpInsert, add at the end:
SELECT @HRID

or replace the last 2 lines by:
SELECT @@IDENTITY

or replace the last 2 lines by:
SELECT SCOPE_IDENTITY()
ASKER CERTIFIED SOLUTION
Avatar of Salim Fayad
Salim Fayad
Flag of Lebanon image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brian

ASKER

I get the following error below when I add 'Dim HRID As Integer = Insertcomm.ExecuteReader()'

Value of Type 'System.Data.SqlClient.SqlDataReader' cannot be converted to 'Integer'
Avatar of Brian

ASKER

Okay, I changed 'Dim HRID As Integer = Insertcomm.ExecuteReader() to 'Dim HRID As Integer = Insertcomm.ExecuteScalar()

However once I add this and perform my Insert Event it duplicates the user within the HR_EMPINFO table but inserts the correct information in the HR_EMPINFO_NEEDS table.
Can you send me your code?
Avatar of Brian

ASKER

I'm attaching the code allowing with both SP's.
Code.txt
HR-EmpInsert-SP.txt
HR-EmpNeedsInsert-SP.txt
You have to remove:
            ' Execute the command
            Insertcomm.ExecuteNonQuery()
Avatar of Brian

ASKER

I can't thank you enough. It works GREAT!!! You have been the onlly person to fully help me from start to finish since I signed up with EE. You should be extremly proud. Most GURU's or Master's don't do that. That itself makes you a "GURU".

However, I have one more question for you. You can either help more or I will open a new issue and let you know what the Title is and award you another 500 points. During this process that you helped me with my BOSS also wants me to be able to look an employee up and populate that same information that was Inserted along with doing an Update once populated. If you would like me to open a new ticket I will.

Thank you.
I guess what you want to do is bind the data again after doing the insert.
Avatar of Brian

ASKER

Yes. For example if I perform an Insert today and then tomorrow I would like to lookup the Employee by there HRID and have the same form populate the information that I entered along with populating the dropdowns which I can do and also the CheckBoxList.
Those are new features, new pages. It is better to close this question and open new one. I will help you in them.
Avatar of Brian

ASKER

Perfect!! I will name the title of the new question "Update multiple values from CheckBoxList Control".

Thank you!
You are welcome!! Send me the link of the new question
Avatar of Brian

ASKER

The eagle,

Ignore what I have already in the new post. I can sum it up better here.

1.) Enter HRID in textbox and bind data to my existing form which will need to populate the data inside the textbox, dropdown, and checkboxList Server Controls. I can bind the data to the textbox and dropdown but I don't know how to bind the values to the checkboxlist controls.

2.) Once the page pulls the data into the form then I would like to perform and update if need be. This would basically be the same as the Insert Event but I don't know if there is something different that needs to be done since I'm updating and not inserting.

Thanks again for your help with this.