Keep stuck at the reading the data from database

Question

I would like to minus the data from the database with the value that I give when I run the program. Everything works, but I keep stuck at the newVal. I already did it like this, but the newVal keep appear 0 (because I declared decimal newVal = 0, but on this question, I just used decimal newVal;). Two more problems: if I move the newVal = ... to the top, it is useless, because 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, required dReader = cmd.ExecuteReader();), but if I put the newVal at the bottom after reading data, it is useless as well, because I set the Quantity = ? and the value of ? is newVal.. Well, here is the code:

private void AddObjects(object sender, EventArgs e, Form theForm)
{
    button1.Visible = true;

    textBoxQuantityContainer = new List<NumericUpDown>();
    textBoxCodeContainer = new List<NumericTextBox>();
    textBoxDescContainer = new List<TextBox>();
    textBoxSubTotalContainer = new List<TextBox>();
    textBoxTotalContainer = new List<TextBox>();
    textBoxAllTotalContainer = new TextBox();

    OleDbDataReader dReader;
    OleDbConnection conn = new OleDbConnection(connectionString);
    conn.Open();

    OleDbCommand cmd = new OleDbCommand("SELECT [Code] FROM [Seranne]", conn);

    dReader = cmd.ExecuteReader();

    AutoCompleteStringCollection codesCollection = new AutoCompleteStringCollection();

    while (dReader.Read())
    {
        string numString = dReader[0].ToString().PadLeft(4, '0');
        codesCollection.Add(numString);
    }

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

    if (firstForm.comboBox1.SelectedIndex == 0)
    {
        label1.Text = "Code:";
        label1.Location = new Point(60, 125);
        label2.Text = "Welcome to the Selling System.";
        label2.Location = new Point(600, 30);
        label3.Text = "Quantity:";
        label3.Location = new Point(155, 125);
        label4.Text = "Description:";
        label4.Location = new Point(580, 125);
        label5.Text = "Sub Total on Rp:";
        label5.Location = new Point(1020, 125);
        label6.Text = "Total on Rp:";
        label6.Location = new Point(1210, 125);
        label7.Text = "Total on Rp:";
        label7.Location = new Point(1080, 580);
    }

    else if (firstForm.comboBox1.SelectedIndex == 1)
    {
        label1.Text = "Kode:";
        label1.Location = new Point(60, 125);
        label2.Text = "Selamat datang di Selling System.";
        label2.Location = new Point(600, 30);
        label3.Text = "Banyaknya:";
        label3.Location = new Point(145, 125);
        label4.Text = "Keterangan:";
        label4.Location = new Point(580, 125);
        label5.Text = "Sub Total di Rp:";
        label5.Location = new Point(1020, 125);
        label6.Text = "Total di Rp:";
        label6.Location = new Point(1210, 125);
        label7.Text = "Total di Rp:";
        label7.Location = new Point(1080, 580);
    }

    //****TextBox for Code****
    for (int y = 0; y <= 16; y++)
    {
        textBoxCodeContainer.Add(new NumericTextBox());
        textBoxCodeContainer[y].Size = new Size(100, 50);
        textBoxCodeContainer[y].Location = new Point(25, 150 + (y * 25));
        textBoxCodeContainer[y].TextChanged += new System.EventHandler(this.textBox_TextChanged);

        textBoxCodeContainer[y].AutoCompleteMode = AutoCompleteMode.Suggest;
        textBoxCodeContainer[y].AutoCompleteSource = AutoCompleteSource.CustomSource;
        textBoxCodeContainer[y].AutoCompleteCustomSource = codesCollection;

        theForm.Controls.Add(textBoxCodeContainer[y]);
    }

    //****TextBox for Quantity****
    for (int y = 0; y <= 16; y++)
    {
        textBoxQuantityContainer.Add(new NumericUpDown());
        textBoxQuantityContainer[y].Size = new Size(100, 50);
        textBoxQuantityContainer[y].Location = new Point(125, 150 + (y * 25));
        textBoxQuantityContainer[y].TextChanged += new System.EventHandler(this.textBox_TextChanged);
        textBoxQuantityContainer[y].Maximum = 1000;

        theForm.Controls.Add(textBoxQuantityContainer[y]);
    }

    //****TextBox for Description****
    for (int y = 0; y <= 16; y++)
    {
        textBoxDescContainer.Add(new TextBox());
        textBoxDescContainer[y].Size = new Size(750, 50);
        textBoxDescContainer[y].Location = new Point(225, 150 + (y * 25));

        theForm.Controls.Add(textBoxDescContainer[y]);
    }

    //****TextBox for Sub Total****
    for (int y = 0; y <= 16; y++)
    {
        textBoxSubTotalContainer.Add(new TextBox());
        textBoxSubTotalContainer[y].Size = new Size(175, 50);
        textBoxSubTotalContainer[y].Location = new Point(975, 150 + (y * 25));

        theForm.Controls.Add(textBoxSubTotalContainer[y]);
    }

    //****TextBox for Total****
    for (int y = 0; y <= 16; y++)
    {
        textBoxTotalContainer.Add(new TextBox());
        textBoxTotalContainer[y].Size = new Size(175, 50);
        textBoxTotalContainer[y].Location = new Point(1150, 150 + (y * 25));
        textBoxTotalContainer[y].TextChanged += new System.EventHandler(this.textBox_TextChanged);

        theForm.Controls.Add(textBoxTotalContainer[y]);
    }

    //****TextBox for Total All****
    textBoxAllTotalContainer.Size = new Size(175, 50);
    textBoxAllTotalContainer.Location = new Point(1150, 575);
    textBoxAllTotalContainer.TextChanged += new System.EventHandler(this.textBox_TextChanged);

    theForm.Controls.Add(textBoxAllTotalContainer);
}

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

    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);

    OleDbDataReader dReader;

    dReader = cmd.ExecuteReader();

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

        index += 1;
    }

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

private void UpdatePrice()
{
    int totalPrice = 0;
    int quantity = 0;
    int price = 0;

    for (int i = 0; i < 17; i++)
    {
        if (textBoxQuantityContainer[i].Value > 0)
        {
            quantity = (int)textBoxQuantityContainer[i].Value;
            price = Convert.ToInt32(textBoxSubTotalContainer[i].Text);
            textBoxTotalContainer[i].Text = (quantity * price).ToString();
        }

        else
        {
            textBoxSubTotalContainer[i].Text = "";
            textBoxTotalContainer[i].Text = "";
        }
    }

    for (int i = 0; i < 17; i++)
    {
        if (textBoxTotalContainer[i].TextLength != 0)
        {
            totalPrice += Convert.ToInt32(textBoxTotalContainer[i].Text);
        }
    }

    textBoxAllTotalContainer.Text = totalPrice.ToString("n2");
}

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

    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)
        {
            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();
}

private void textBox_TextChanged(object sender, EventArgs e)
{
    UpdateDatas();
    UpdatePrice();
}

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

Thanks a bunch

Answer 1

Good day, i got several things here:

1) cmd.Parameters["Quantity"].Value = this.newVal.ToString(); conversion to string is not needed here, because the Value is an object. You already defined that it should be handled as integer.

2) Replace the ? within you query to @Quantity, so it will be filled by the query execution.

3) When updating you don't need to execute it as a reader. use the int numberOfRows = cmd.ExecuteNonQuery(); without the loop. It will update all items.

4) You should execute the if (textBoxQuantityContainer[index].Value != 0 && textBoxQuantityContainer[index].Value >= Convert.ToInt32(dReader["Quantity"].ToString())) { when building the integers list, this way you are only updating the right quantities.

If you only want to update certain rows, you'll have to expand your where clause:

cmd.Parameters.Add("MinimumQuantity", System.Data.OleDb.OleDbType.Integer).Value = minimumQuantity;

string command = "UPDATE [Seranne] 
                  SET [Quantity]=@Quantity
                  WHERE [Code] IN(" + string.Join(", ", integers) + ")
                  AND [Quantity] > @MinimumQuantity

The higest risk is: You assume that the order and count of the records are the same between your textBoxCodeContainer and the database.

What is the relation between a textbox and a row. How do you know what textbox links to which row?

I could give you a push in the right direction, if you show me some more code (like where/how is textBoxCodeContainer defined)

---

UPDATE:

---

I made some code the read and manipulate your database, this is not tested since i don't have any database here.

I would create these classes, 1 is a data class Product and one is a Handler class ProductHandler.

The data class only contains the data (not in presentation format) The data handler knows how to read and write them.

    public class Product
    {
        public int Code { get; set; }
        public string Description { get; set; }
        public int Quantity { get; set; }
    }

    public class ProductHandler
    {
        public ProductHandler(string connectionString)
        {
            ConnectionString = connectionString;
        }

        public bool AddProduct(Product product)
        {
            return AddProducts(new Product[] { product }) > 0;
        }
        public int AddProducts(IEnumerable<Product> products)
        {
            int rowsInserted = 0;

            using (OleDbConnection conn = new OleDbConnection(ConnectionString))
            {
                conn.Open();

                string query = "INSERT INTO [Seranne] (Code, Description, Quantity) VALUES(@Code, @Description, @Quantity)";

                using (OleDbCommand cmd = new OleDbCommand(query, conn))
                {
                    cmd.Parameters.Add("Code", OleDbType.Integer);
                    cmd.Parameters.Add("Description", OleDbType.VarChar);
                    cmd.Parameters.Add("Quantity", OleDbType.Integer);

                    foreach (var product in products)
                    {
                        cmd.Parameters["Code"].Value = product.Code;
                        cmd.Parameters["Description"].Value = product.Description;
                        cmd.Parameters["Quantity"].Value = product.Quantity;

                        rowsInserted += cmd.ExecuteNonQuery();
                    }
                }
            }
            return rowsInserted;
        }

        public bool UpdateProduct(Product product)
        {
            return UpdateProducts(new Product[] { product }) > 0;
        }
        public int UpdateProducts(IEnumerable<Product> products)
        {
            int rowsUpdated = 0;

            using (OleDbConnection conn = new OleDbConnection(ConnectionString))
            {
                conn.Open();

                string query = "UPDATE [Seranne] SET Description = @Description, Quantity = @Quantity WHERE [Code] == @Code)";

                using (OleDbCommand cmd = new OleDbCommand(query, conn))
                {
                    cmd.Parameters.Add("Code", OleDbType.Integer);
                    cmd.Parameters.Add("Description", OleDbType.VarChar);
                    cmd.Parameters.Add("Quantity", OleDbType.Integer);

                    foreach (var product in products)
                    {
                        cmd.Parameters["Code"].Value = product.Code;
                        cmd.Parameters["Description"].Value = product.Description;
                        cmd.Parameters["Quantity"].Value = product.Quantity;

                        rowsUpdated += cmd.ExecuteNonQuery();
                    }
                }
            }

            return rowsUpdated;
        }

        public bool DeleteProduct(Product product)
        {
            return DeleteProducts(new int[] { productCode }) > 0;
        }
        public int DeleteProducts(IEnumerable<Product> products)
        {
            using (OleDbConnection conn = new OleDbConnection(ConnectionString))
            {
                conn.Open();

                string productCodeStr = string.Join(", ", products.Select(item => item.Code));
                string query = string.Format("DELETE FROM [Seranne] WHERE [Code] in ({0})", productCodeStr);

                using (OleDbCommand cmd = new OleDbCommand(query, conn))
                {
                    int rowsDeleted = cmd.ExecuteNonQuery();
                    return rowsDeleted;
                }
            }
        }

        public IEnumerable<Product> ReadAllProducts()
        {
            List<Product> result = new List<Product>();

            using (OleDbConnection conn = new OleDbConnection(ConnectionString))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand("SELECT [Code], [Description], [Quantity] FROM [Seranne]", conn))
                using (OleDbDataReader dReader = cmd.ExecuteReader())
                    while (dReader.Read())
                    {
                        Product product = new Product();
                        product.Code = Convert.ToInt32(dReader["Code"]);
                        product.Description = Convert.ToString(dReader["Description"]);
                        product.Quantity = Convert.ToInt32(dReader["Quantity"]);
                        result.Add(product);
                    }
            }

            return result;
        }

        public string ConnectionString { get; private set; }
    }

---

Some example code:

    ProductHandler _productHandler = new ProductHandler("connectionstring here or from config");

    public void Example()
    {
        _productList.Clear();
        _productList.AddRange(_productHandler.ReadAllProducts());

        // displaying
        foreach (var product in _productList)
            Trace.WriteLine(string.Format("code: {0}, description: {1}, quantity: {2}", product.Code, product.Description, product.Quantity);

        // updating
        var selectedProduct = _productList.FirstOrDefault(item => item.Code == 15);
        if(selectedProduct!= null)
        {
            selectedProduct.Quantity = 50;
            _productHandler.UpdateProduct(selectedProduct);
        }

        // deleting
        _productHandler.DeleteProducts(_productList.Where(item => item.Quantity < 5));
    }

---

How to link the textboxes to the right product:

I would create a UserControl that contains a Product property and the TextBoxes and handles when textbox_changed events occurs. Those event handlers manipulate the Product instance.

You only generate 16 controls and bind a product to it. When you press a button, you only need to save the changed products.