Brian
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!
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!
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.
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?
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;
}
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
http://www.devasp.net/net/articles/display/267.html
This article mostly talks about the RadioButtonList. Mine is much simpler and for the CheckBoxList
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?
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.
2. This code will work
3. You should specify that the SqlParameter has a data type of int.
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.Conne ctionStrin gs("HR").C onnectionS tring
' Initialize connection
conn = New SqlConnection(connectionSt ring)
' Create Stored Procedure
Insertcomm = New SqlCommand("HR_EmpInsert", conn)
Insertcomm.CommandType = CommandType.StoredProcedur e
Insertcomm.Parameters.Add( "@BLDGID", System.Data.SqlDbType.Int)
Insertcomm.Parameters("@BL DGID").Val ue = BuildingList.SelectedItem. Value
Insertcomm.Parameters.Add( "@EMPTITID ", System.Data.SqlDbType.Int)
Insertcomm.Parameters("@EM PTITID").V alue = TitleList.SelectedItem.Val ue
Insertcomm.Parameters.Add( "@FLID", System.Data.SqlDbType.Int)
Insertcomm.Parameters("@FL ID").Value = FloorsList.SelectedItem.Va lue
For Each item As ListItem In NeedsList.Items
If item.Selected Then
Insertcomm.Parameters.Add( "@NID", System.Data.SqlDbType.Int)
Insertcomm.Parameters("@NI D").Value = NeedsList.SelectedItem.Val ue
item.Selected = False
'Add the item.Value
'Clear the selection of the item
End If
Next
Insertcomm.Parameters.Add( "@HRNAME", System.Data.SqlDbType.VarC har, 50)
Insertcomm.Parameters("@HR NAME").Val ue = HRNAME.Text
Insertcomm.Parameters.Add( "@HRSTART" , System.Data.SqlDbType.Date Time)
Insertcomm.Parameters("@HR START").Va lue = HRSTARTDATE.Text
Insertcomm.Parameters.Add( "@HRDEPT", System.Data.SqlDbType.VarC har, 50)
Insertcomm.Parameters("@HR DEPT").Val ue = HRDEPT.Text
Insertcomm.Parameters.Add( "@HRSUPERV IS", System.Data.SqlDbType.VarC har, 50)
Insertcomm.Parameters("@HR SUPERVIS") .Value = HRSUPERVIS.Text
Insertcomm.Parameters.Add( "@HRPOSTIT LE", System.Data.SqlDbType.VarC har, 50)
Insertcomm.Parameters("@HR POSTITLE") .Value = HRPOSTITLE.Text
Insertcomm.Parameters.Add( "@HRNOTES" , System.Data.SqlDbType.VarC har, 50)
Insertcomm.Parameters("@HR NOTES").Va lue = 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("questio n_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
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.Conne
' Initialize connection
conn = New SqlConnection(connectionSt
' Create Stored Procedure
Insertcomm = New SqlCommand("HR_EmpInsert",
Insertcomm.CommandType = CommandType.StoredProcedur
Insertcomm.Parameters.Add(
Insertcomm.Parameters("@BL
Insertcomm.Parameters.Add(
Insertcomm.Parameters("@EM
Insertcomm.Parameters.Add(
Insertcomm.Parameters("@FL
For Each item As ListItem In NeedsList.Items
If item.Selected Then
Insertcomm.Parameters.Add(
Insertcomm.Parameters("@NI
item.Selected = False
'Add the item.Value
'Clear the selection of the item
End If
Next
Insertcomm.Parameters.Add(
Insertcomm.Parameters("@HR
Insertcomm.Parameters.Add(
Insertcomm.Parameters("@HR
Insertcomm.Parameters.Add(
Insertcomm.Parameters("@HR
Insertcomm.Parameters.Add(
Insertcomm.Parameters("@HR
Insertcomm.Parameters.Add(
Insertcomm.Parameters("@HR
Insertcomm.Parameters.Add(
Insertcomm.Parameters("@HR
' 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("questio
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.
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.
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
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
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
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
- BLDGID
- EMPTITID
- FLID
- HRNAME
- HRSTART
- HRDEPT
- HRSUPERVIS
- HRPOSTITLE
- HRNOTES
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
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.
- 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.
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.
-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
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.
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
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
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.
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
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
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?
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?
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()
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()
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
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()
SELECT @HRID
or replace the last 2 lines by:
SELECT @@IDENTITY
or replace the last 2 lines by:
SELECT SCOPE_IDENTITY()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I get the following error below when I add 'Dim HRID As Integer = Insertcomm.ExecuteReader() '
Value of Type 'System.Data.SqlClient.Sql DataReader ' cannot be converted to 'Integer'
Value of Type 'System.Data.SqlClient.Sql
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.
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?
ASKER
I'm attaching the code allowing with both SP's.
Code.txt
HR-EmpInsert-SP.txt
HR-EmpNeedsInsert-SP.txt
Code.txt
HR-EmpInsert-SP.txt
HR-EmpNeedsInsert-SP.txt
You have to remove:
' Execute the command
Insertcomm.ExecuteNonQuery ()
' Execute the command
Insertcomm.ExecuteNonQuery
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.
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.
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.
ASKER
Perfect!! I will name the title of the new question "Update multiple values from CheckBoxList Control".
Thank you!
Thank you!
You are welcome!! Send me the link of the new question
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.
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.