Update multiple checkboxlist values to SQL DB asp.net C#

Question

I am updating a row in SQL DB using the code below. The loop works and it updates the row but the problem is that each time it goes through the loop, it only updates one value and the other values are overwritten. So at the end, it has updated but instead of multiple values being inputted to the table for the respective Project ID, it only puts one value for the respective Project ID. I am not receiving any errors for this. Any help is much appreciated.

for (int i = 0; i < cbAvailableEntities.Items.Count - 1; i++)
{
    SqlConnection connection = new SqlConnection(connString);
    SqlCommand cmd = new SqlCommand("UpdateProjectEntity", connection);

    using (connection)
    {
        connection.Open();
        using (cmd)
        {
           if (cbAvailableEntities.Items[i].Selected)
            {
               cmd.CommandType = CommandType.StoredProcedure;
                //the following is the Project ID for the row being updated.
               SqlParameter paramPID = new SqlParameter("@ProjectID", nr.ProjectID);
               cmd.Parameters.Add(paramPID);
               nr.Entities = cbAvailableEntities.Items[i].Value;
               cmd.Parameters.AddWithValue("@CorpID", nr.Entities);
               cmd.ExecuteNonQuery();
           }
        }
    }
}

Here is the SQL query for the Stored Procedure "UpdateProjectEntity"

ALTER PROCEDURE [dbo].[UpdateProjectEntity]
    @ProjectID int,
    @CorpID int
AS
BEGIN
    UPDATE [dbo].[ProjectEntity]
    SET
           [CorpID] = @CorpID
     WHERE
           ProjectID = @ProjectID
END

Here are screenshots of inputs and results when I run the program. These are the checkboxes I am saving to the DB

This is the result after I have saved to the DB

I changed the date to show that everything else works in this program.

I dont understand the problem. Can you show some data of what is happening? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 7, 2015 at 19:34
btw Why you open the connection inside the loop? would work better if you open connection once and execute all the commands and then close it? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 7, 2015 at 19:35
The problem is that every time the query is executed, it updates the row. So each time it updates the row, it is only updating it with one value and then when it is done with the loop, only the last value is in the SQL table. So instead of multiple values updated for a row, only one value has been updated. — bds89
– bds89, Commented Oct 7, 2015 at 19:46
what is "UpdateProjectEntity"? If that is a storeproc you should include the code. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 7, 2015 at 19:58
@JuanCarlosOropeza, I have added the query for the Stored Procedure. — bds89
– bds89, Commented Oct 7, 2015 at 20:08

Juan Carlos Oropeza · Accepted Answer · 2015-10-07 21:22:13Z

0

I can see you save your enity INT, maybe you should save it as a Comma Separated String.

So instead of save 1, you can save 1,2,3

Of course you will have to add some logic before the save building and concat the string. And also need to do some parsing when you read from db doing the split by ,

The other aproach is creating a relation table to indicate with are the options selected.

But this is also have problem when you remove a selection and add new ones.

ProjectID   CorpID
   1          1
   1          2
   1          3

answered Oct 7, 2015 at 21:22

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bds89 Over a year ago

It will give an error if it is passed as a string since the stored procedure and the table is expecting an INT. I have tried with string in the past and forgot that it needs to be INT. There are a lot of tables in the DB that I am working with and my manager would not want more tables in it. I can do it but it is extra work.

Juan Carlos Oropeza Over a year ago

As I say, You wont be able to do solve it unless you change you structure. And I give you two options to do that. I will give you another ONE and THIS

Juan Carlos Oropeza Over a year ago

I remember an answer i give last month. If you have very few check box you can save all options as an integer using bitwise operator.

bds89 · Accepted Answer · 2015-10-12 18:57:02Z

The way I resolved this without making any changes to the DB is I used a DELETE Statement to delete the rows with the ProjectID and then I used an insert stored procedure that I have used before. It was a lot faster than creating another table among all that is already in place. So the code looks like this

for (int i = 0; i < cbAvailableEntities.Items.Count - 1; i++) {
SqlConnection connection = new SqlConnection(connString);
SqlCommand com = new SqlCommand("InsertProjectEntity", connection);
SqlCommand dcm = new SqlCommand();

using(connection) {
    //First time going through the loop, i = 0 is true.
    if (i == 0) {
        connection.Open();
        using(com) {
            //This will remove anything in the DB related to the ProjectID being edited.
            dcm.Connection = connection;
            dcm.CommandText = "DELETE FROM [dbo].[ProjectEntity] WHERE ProjectID = " + _pID;
            dcm.ExecuteNonQuery();
            //This will insert all items checked in the checkboxlist but will not insert the unchecked.
            if (cbAvailableEntities.Items[i].Selected) {
                com.CommandType = CommandType.StoredProcedure;
                SqlParameter paramPID = new SqlParameter("@ProjectID", nr.ProjectID);
                com.Parameters.Add(paramPID);
                nr.Entities = cbAvailableEntities.Items[i].Value;
                com.Parameters.AddWithValue("@CorpID", nr.Entities);
                com.ExecuteNonQuery();
            }

        }
    } else {
        connection.Open();
        using(com) {
            //This will insert all items checked in the checkboxlist but will not insert the unchecked.
            if (cbAvailableEntities.Items[i].Selected) {
                com.CommandType = CommandType.StoredProcedure;
                SqlParameter paramPID = new SqlParameter("@ProjectID", nr.ProjectID);
                com.Parameters.Add(paramPID);
                nr.Entities = cbAvailableEntities.Items[i].Value;
                com.Parameters.AddWithValue("@CorpID", nr.Entities);
                com.ExecuteNonQuery();
            }
        }
    }
}

Collectives™ on Stack Overflow

Update multiple checkboxlist values to SQL DB asp.net C#

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related