Updating "Quantity" value from the program to the database not successful

[fuhanspujisaputra]

i have a problem here.

Here is my case:

I want to update the Quantity value from program based on the value given from program and minus it to the database. For example: i have 100 in Quantity in the database, once i run the program and update the Quantity to 50, the Quantity in the database should be 50.

Here is my problem:

I already can update Quantity value from program to the database, but no matter what's the value that i gave to Quantity in the program, the Quantity value in the database always updating to 0.

Here is the code:

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

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

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

            string command = "UPDATE [Seranne] SET [Quantity]= " + newVal + " WHERE [Code] IN(" + string.Join(", ", integers) + ")";

            OleDbConnection conn = new OleDbConnection(connectionString);

            OleDbDataReader dReader;

            OleDbCommand cmd = new OleDbCommand(command, conn);

            conn.Open();

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

            dReader = cmd.ExecuteReader();

            while(dReader.Read())
            {
                if (textBoxQuantityContainer[index].Value != 0 && textBoxQuantityContainer[index].Value >= Convert.ToDecimal(dReader["Quantity"].ToString()))
                {
                    newVal = Convert.ToDecimal(dReader["Quantity"].ToString()) - textBoxQuantityContainer[index].Value;
                    cmd.ExecuteNonQuery();
                }

                index += 1;
            }

            if (newVal == 0)
            {
                System.Media.SoundPlayer sounds = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
                sounds.Play();
                MessageBox.Show("Cannot Update", "Error");
            }

            else
            {
                System.Media.SoundPlayer sound = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
                sound.Play();
                MessageBox.Show("Was Updated Successfully", "Success");
            }

            dReader.Close();
            conn.Close();
        }

The newVal value keep appear 0. Because the newVal keep 0, the program show this when i click "Update" button in the program:

if (newVal == 0)
                {
                    System.Media.SoundPlayer sounds = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
                    sounds.Play();
                    MessageBox.Show("Cannot Update", "Error");
                }

But when i check in the database, the Quantity value is changed to 0, no matter what is the value that i was given in the program and the newVal keep 0. So, i think the because of newVal is keep appear 0, then the database recognized it and update it based on the newVal, and this code seems not working:

if (textBoxQuantityContainer[index].Value != 0 && textBoxQuantityContainer[index].Value >= Convert.ToDecimal(dReader["Quantity"].ToString()))
                    {
                        newVal = Convert.ToDecimal(dReader["Quantity"].ToString()) - textBoxQuantityContainer[index].Value;
                        cmd.ExecuteNonQuery();
                    }

Could you guys help me out? Thanks in advance!

[Momerath]

Where is 'newval' set prior to line 16? And line 26 isn't doing anything as you don't have any parameters in your SQL statement.

[fuhanspujisaputra]

Hi Momerath, the 'newVal' is on line 34. And so, here is the update that i has been doing right now, the error is "Command text was not set for the command object." and it is pointed at line 25.

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

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

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

                OleDbConnection conn = new OleDbConnection(connectionString);

                OleDbDataReader dReader;

                OleDbCommand cmd = new OleDbCommand(command, conn); 

                conn.Open();

                dReader = cmd.ExecuteReader();

                while(dReader.Read())
                {
                    if (textBoxQuantityContainer[index].Value != 0 && textBoxQuantityContainer[index].Value >= Convert.ToInt32(dReader["Quantity"].ToString()))
                    {
                        int q1 = Convert.ToInt32(dReader["Quantity"].ToString());
                        int q2 = Convert.ToInt32(textBoxQuantityContainer[index].Value.ToString());

                        int totalQ = q1 - q2;

                        command = "UPDATE [Seranne] SET [Quantity]= " + totalQ + " WHERE [Code] IN(" + string.Join(", ", integers) + ")";
                        cmd.ExecuteNonQuery();
                    }

                    index += 1;
                }

                if (newVal == 0)
                {
                    System.Media.SoundPlayer sounds = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
                    sounds.Play();
                    MessageBox.Show("Cannot Update", "Error");
                }

                else
                {
                    System.Media.SoundPlayer sound = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
                    sound.Play();
                    MessageBox.Show("Was Updated Successfully", "Success");
                }

                dReader.Close();
                conn.Close();
            }

[Momerath]

Hi Momerath, the 'newVal' is on line 34

Setting it after you use it doesn't do anything. In your original code it would be the default value (zero) and thus was working perfectly.

As for your new code, you set command to an empty string in line 5, then tell it to execute that empty string as a SQL command. It's right, there isn't anything there to execute.

[fuhanspujisaputra]

Then sir, what should i do to get it right and also to get the value right? (not appear as 0)

[fuhanspujisaputra]

i already did like this sir, but the newVal keep appear 0 (because i declared newVal equal to 0), and 2 more problems: if i move the newVal = ... at the top, it is useless, because the one of the calculations in the newVal is reading data from the database (since i want database minus with the new value given when i run the program), but if i put the 'newVal' at the bottom after reading data, it is useless also, because i set the Quantity = ? and the value of ? is newVal.. Well, here is the code:

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

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

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

            string command = "UPDATE [Seranne] SET [Quantity]=? WHERE [Code] IN(" + string.Join(", ", integers) + ")";

            OleDbConnection conn = new OleDbConnection(connectionString);

            OleDbCommand cmd = new OleDbCommand(command, conn);

            cmd.Parameters.Add("Quantity", System.Data.OleDb.OleDbType.Integer);
            cmd.Parameters["Quantity"].Value = this.newVal.ToString();

            OleDbDataReader dReader;

            conn.Open();

            dReader = cmd.ExecuteReader();

            while (dReader.Read())
            {
                if (textBoxQuantityContainer[index].Value != 0 && textBoxQuantityContainer[index].Value >= Convert.ToInt32(dReader["Quantity"].ToString()))
                {
                    newVal = (Convert.ToInt32(dReader["Quantity"].ToString()) - textBoxQuantityContainer[index].Value);

                    int numberOfRows = cmd.ExecuteNonQuery();
                }

                index += 1;
            }

            if (newVal == 0)
            {
                System.Media.SoundPlayer sounds = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
                sounds.Play();
                MessageBox.Show("Cannot Update", "Error");
            }

            else
            {
                System.Media.SoundPlayer sound = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
                sound.Play();
                MessageBox.Show("Was Updated Successfully", "Success");
            }

            dReader.Close();
            conn.Close();
        }

Could you help me sir? i already thinking another way, but it is useless also, keep stuck at the newVal, since it is read the data from the database and required dReader = cmd.ExecuteReader();

Thanks a bunch

[fuhanspujisaputra]

Solved by myself! Thanks.