Update to the database didn't work

[fuhanspujisaputra]

I got the problem. I want to update the data to the database, but the database won't update.

Here is the code:

else if (firstForm.textBox1.Text == "Seranne")
            {
                string query = "SELECT [Quantity], [Description], [Price] FROM [Seranne] WHERE [Code] IN (";

                OleDbConnection conn = new OleDbConnection(connectionString);

                conn.Open();

                if (int.TryParse(this.textBoxCodeContainer[0].Text, out codeValue))
                {
                    query = query + codeValue.ToString();
                }

                for (int i = 1; i < 17; i++)
                {
                    if (int.TryParse(this.textBoxCodeContainer[i].Text, out codeValue))
                    {
                        query = query + "," + codeValue.ToString();
                    }
                }

                query = query + ")";

                OleDbCommand cmd = new OleDbCommand(query, conn);

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

                OleDbDataReader dReader;

                dReader = cmd.ExecuteReader();

                while (dReader.Read())
                {
                    if (textBoxCodeContainer[index].TextLength != 0)
                    {
                        this.textBoxQuantityContainer[index].Maximum = Convert.ToDecimal(dReader["Quantity"].ToString());
                        this.textBoxDescContainer[index].Text = dReader["Description"].ToString();
                        this.textBoxSubTotalContainer[index].Text = dReader["Price"].ToString();
                    }

                    if (textBoxQuantityContainer[index].Value != 0 && textBoxQuantityContainer[index].Value >= Convert.ToDecimal(dReader["Quantity"].ToString()))
                    {
                        newVal = textBoxQuantityContainer[index].Value - Convert.ToDecimal(dReader["Quantity"].ToString());
                        cmd = new OleDbCommand("UPDATE [Seranne] SET [Quantity] ='" + newVal + "' WHERE [Code] IN ('");
                    }

                    index += 1;
                }

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

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

        private void button1_Click(object sender, EventArgs e)
        {
            UpdateQuantity();
        }

Above code all worked, excepts for updating the Quantity to the database. What I mean is, I set the Quantity in database to 100, when I set Quantity to 10 in my program and update it, the database should be update the Quantity to 90 (because 100 - 10), but it is still at 100.

Could I wrong somewhere?

Here is the link of the screenshots: (ScreenShot 1) https://www.dropbox.com/s/rph5iuh371rc9ny/Untitled.png

(ScreenShot 2) https://www.dropbox.com/s/5q8pyztqy7ejupy/Capture.PNG

In the Screenshot 1, I already set the quantity to 10 and the messagebox show that the data has been updated successfully and the data in the database supposed to be 90 (because 100-10). But, in the Screenshot 2 where the database is, the Quantity still at 100.

Thanks in advance!

[Ketsuekiame]

textBoxQuantityContainer[index].Value - Convert.ToDecimal(dReader["Quantity"].ToString())

Have you debugged and checked what the result of this operation is?

[fuhanspujisaputra]

Yeah, i already debug this code:

newVal = textBoxQuantityContainer[index].Value - Convert.ToDecimal(dReader["Quantity"].ToString());

and the result for it is 0, newVal is 0

[fuhanspujisaputra]

Here is the link of the screenshot of the result:

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

[ddanbe]

In debugmode your program works with a copy of the database, as to not affect your original DB during testing.

[fuhanspujisaputra]

So, what should i do sir ddanbe?

[Ketsuekiame]

In debugmode your program works with a copy of the database, as to not affect your original DB during testing.

It won't do this by default. You'd need to set it up specfically to do this, which I presume has not been done.

The error is stemming from the fact that his newVal is 0 and so his assumption that it is subtracting 10 from his DB value is incorrect.

Your textBoxQuantityContainer[index].Value contains 100. 100 - 100 is 0.

Why this would be 100 instead of 10 as you think, I don't know. There is nothing in the code you've provided to suggest why this would be anything other than what you input in your application.

[ss125]

It may be wrong but I think the problem is with your updater statement.

cmd = new OleDbCommand("UPDATE [Seranne] SET [Quantity] ='" + newVal + "' WHERE [Code] IN ('");

There is a unended round bracket in your query.

Correct me if I am wrong Ketsuekiame and ddanbe...

[ss125]

And I also forget to mention that to perform a math operation the value must be converted to integer(since it is a quntity).

Hope this helps you...

Have a happy coding...:D

[Ketsuekiame]

Hi ss125, Yes the query is unfinished, but the numbers are being converted to decimal so that works.

Additionally, there is no actual call to update the DB. It's like you haven't finished that part...

You will need to complete the query and call ExecuteNonQuery() to run the update.

[ss125]

@Ketsuekiame,

yup...I forget to notice that...:D