Update to the database was only read the first value in program

[fuhanspujisaputra]

Hai everyone, i have a problem, the program just read the first entered Quantity value in the program and apply it to all rows in database and the value of first and second row are same, even though in the beginning, first and second row value are different. First of all, my database is like this (Screenshot 1). The screenshot 1 until screenshot 5 are working properly, i just want to show you to not confuse you later. My problem is on screenshot 6 and 7.

Screenshot 1:
https://www.dropbox.com/s/5q8pyztqy7ejupy/Capture.PNG

And when i run the program and entered the first "Product Code" in first row and want to change the first Quantity value to 25, so i just enter 75 in Quantity in the program like the screenshot below (Screenshot 2):

Screenshot 2:
https://www.dropbox.com/s/wd9oz7wdi7s4l0k/Capture_1.PNG

And when i click update, the database changed to this (Screenshot 3):

Screenshot 3:
https://www.dropbox.com/s/9i0zc285netfrg3/Capture2.PNG

When i change the first "Product Code" in first row to second "Product Code" in first row and want to change the second Quantity value to 100, so i just enter 50 in Quantity in the program like the screenshot below (Screenshot 4):

Screenshot 4:
https://www.dropbox.com/s/khsvh5bf1dc4c8u/Capture_2.PNG

And when i click update, the database changed to this (Screenshot 5):

Screenshot 5:
https://www.dropbox.com/s/kdsyd2csccim63w/Capture3.PNG

But, when i enter the first "Product Code" in first row and second "Product code" in second row in my program like the screenshot below (Screenshot 6):

Screenshot 6:
https://www.dropbox.com/s/7hmsoe4ba5n18re/Capture_3.PNG

And when i click update, the database changed to this (Screenshot 7):

Screenshot 7:
https://www.dropbox.com/s/1mmtkz71f8s4u2h/Capture4.PNG .

I have been wondering, why it become like this?

Here is the code:

private void UpdateQuantity()
        {
            int codeValue = 0;
            int index = 0;

            List<int> integers = new List<int>();

            foreach (var tb in textBoxCodeContainer)
            {
                if (int.TryParse(tb.Text, out codeValue))
                {
                    integers.Add(codeValue);
                }
            }

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                string commandSelect = "SELECT [Quantity], [Description], [Price] FROM [Seranne] WHERE [Code] = @Code";
                string commandUpdate = "UPDATE [Seranne] SET [Quantity] = @Quantity WHERE [Code] IN(" + string.Join(", ", integers) + ")";

                using (OleDbCommand cmdSelect = new OleDbCommand(commandSelect, conn))
                using (OleDbCommand cmdUpdate = new OleDbCommand(commandUpdate, conn))
                {
                    cmdSelect.Parameters.Add("Code", System.Data.OleDb.OleDbType.Integer);
                    cmdSelect.Parameters["Code"].Value = this.textBoxCodeContainer[index].Text;

                    cmdUpdate.Parameters.Add("Quantity", System.Data.OleDb.OleDbType.Integer);

                    using (OleDbDataReader dReader = cmdSelect.ExecuteReader())
                    {
                        while (dReader.Read())
                        {
                            if (textBoxQuantityContainer[index].Value != 0)
                            {
                                newVal = Convert.ToInt32(dReader["Quantity"].ToString()) - textBoxQuantityContainer[index].Value;
                                cmdUpdate.Parameters["Quantity"].Value = newVal;
                                int numberOfRows = cmdUpdate.ExecuteNonQuery();
                            }
                        }

                        index += 1;

                        dReader.Close();
                    }
                }

                conn.Close();
            }
      }

Could you guys help me out? Thanks a bunch!

[lolafuertes]

Just a few hints.

In order to update a database you do not need to issue a select clause first.

So, I will remove all the code related to the commandSelect string in the UpdateQuantity procedure.

Also note that the UPDATE command, will update all the records that match the condition IN (...). In this case, you are updating always both code 1 and code 1 to the same value.

I would suggest to replace the procedure to something like:

private void UpdateQuantity()
{
    // this index will be used to loop over the container
    int index;
    // Here we will store the code once verified to be integer
    int codeValue = 0;
    // Create a new connection to the DataBase
    using (OleDbConnection conn = new OleDbConnection(connectionString))
        {
            // Open the connection, usually will not fail
            conn.Open();
            // For each row entered ...
            for (index = 0, index < textBoxCodeContainer.Length, index++)
            {
                var tb = textBoxCodeContainer[index]
                // and if the code is integer...
                if (int.TryParse(tb.Text, out codeValue))
                {              
                    // Issue an update command to the DataBase
                    // Note here that the subtract operation is in the SET clause
                    // updating the old value with the result of substract the paramenter quantity
                    // from the old quantity value
                    string commandUpdate = "UPDATE [Seranne] SET [Quantity] = [Quantity] - @Quantity WHERE [Code] = @Code )";
                    using (OleDbCommand cmdUpdate = new OleDbCommand(commandUpdate, conn))
                    {
                        //Add the parameters for the code ..
                        cmdUpdate.Parameters.Add("Code", System.Data.OleDb.OleDbType.Integer);
                        cmdUpdate.Parameters["Code"].Value = codeValue;
                        // and the parameter for the cuantity to subtract
                        cmdUpdate.Parameters.Add("Quantity", System.Data.OleDb.OleDbType.Integer);
                        cmdUpdate.Parameters["Quantity"].value = Convert.ToInt32( textBoxQuantityContainer[index].Value);
                        // As this is a kind of operation that can throw  errors, 
                        // we use the try - catch structure
                        try
                        { 
                            int numberOfRows = cmdUpdate.ExecuteNonQuery();
                        }
                        catch (Exception exception)
                        {
                            // If any error, it is shown
                            ShowMessage "Update failed: " + exception.Message
                        }
                    }
                }                   
            }
            // Do not forget to close the connection.
            conn.Close();
        }
    }

This code has not been tested and can contain some typo errors, but ...

Hope this helps