none
Update inventory from data grid RRS feed

  • Question

  • I have a ms sql server database table "Item" and i want to update the item table "QtyInHand" column when the user presses a button. It should update the table for all the records in the data grid view for the relevant ItemNo.


    • Edited by Thushan Withanage Saturday, November 30, 2019 7:13 AM
    • Moved by CoolDadTx Monday, December 2, 2019 3:09 PM Winforms related
    Saturday, November 30, 2019 7:11 AM

All replies

  • If you have a DataTable as the DataSource of the DataGridView use a class to do actual database operations which would have a update method such as shown below. Note how parameters are done, declared once used many times. Note the method returns nothing, you could return an int as cmd.ExecuteNonQuery returns an int of rows affected and this could be added to a local var and passed back to the caller or simple return a bool for success or failure based on if an exception was raised.

    using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace WindowsFormsApp1
    {
        public class DataOperations
        {
            public void Update(DataTable table)
            {
    
                var updateStatement = "UPDATE Item " + 
                                      "SET QtyInHand = @QtyInHand " + 
                                      "WHERE ItemNo = @ItemNo";
    
                using (var cn = new SqlConnection() {ConnectionString = "TODO"})
                {
                    using (var cmd = new SqlCommand() {Connection = cn, CommandText = updateStatement})
                    {
                        cmd.Parameters.Add("@QtyInHand", SqlDbType.Float);
                        cmd.Parameters.Add("@ItemNo", SqlDbType.NChar);
    
                        try
                        {
                            cn.Open();
    
                            for (int rowIndex = 0; rowIndex < table.Rows.Count - 1; rowIndex++)
                            {
                                cmd.Parameters["@QtyInHand"].Value = 
                                    table.Rows[rowIndex].Field<float>("QtyInHand");
    
                                cmd.Parameters["@ItemNo"].Value = 
                                    table.Rows[rowIndex].Field<string>("ItemNo");
    
                                cmd.ExecuteNonQuery();
                            }
                        }
                        catch (Exception ex)
                        {
                            /*
                             * This is for debugging only,
                             * for a production app you need to
                             * decide what to do as there are many
                             * paths that are possible.
                             */
                            Console.WriteLine(ex.Message);
                        }
                    }
                }
            }
    
            public DataTable Read()
            {
                //TODO
            }
        }
    }

    In a form calling the update, in this case the DataGridView name is dataGridView1.

    Button click event.

    private void UpdateCurrentDataButton_Click(object sender, EventArgs e)
    {
        var ops = new DataOperations();
        ops.Update((DataTable)dataGridView1.DataSource);
    }
    Ending comment, I would highly recommend adding a auto incrementing primary key to this table and any table in your database. Using a string field as the key can cause issues down the road but that is your choice.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Saturday, November 30, 2019 10:52 AM
    Moderator
  • The code should add the value  in the datagrid view to the "QtyInHand" column previous value for the relevant Item No.

    Eg. If the previous value of the "ItemNo" ITEM1001 is 10, so the new value should be 15.

    Saturday, November 30, 2019 4:42 PM
  • The code should add the value  in the datagrid view to the "QtyInHand" column previous value for the relevant Item No.

    Eg. If the previous value of the "ItemNo" ITEM1001 is 10, so the new value should be 15.

    If that is the case you should consider what happens if subtracting and go below zero. I don't have time to write a proper code sample to your table but this should get you going. It uses a auto inc primary key which 99.999 percent of tables use.

    You need to pass in what the new value is and adjust to the current value. As I said this is simply a pattern to consider to expand upon which is what developers do when something is close to what they want.

    public class DataOperations
    {
    
    
        public bool CanOrder(int id, bool adding)
        {
            var result = false;
            using (var cn = new SqlConnection { ConnectionString = "TODO" })
            {
                using (var cmd = new SqlCommand { Connection = cn })
                {
                    cmd.CommandText = $"SELECT Quantity FROM  Products WHERE id = {id}";
    
                    cn.Open();
                    var quantity = Convert.ToInt32((int)(cmd.ExecuteScalar()));
                    result = quantity > 0;
    
    
                    if (adding)
                    {
                        quantity -= 1;
                    }
                    else
                    {
                        quantity += 1;
                    }
    
                    // 
                    // * We could forgo this check which would give a negative Quantity which then
                    // * a query could run that has Quantity less than zero which would be how many
                    // * are needed for back-orders
                    //                     
                    if (quantity > 0)
                    {
                        cmd.CommandText = "UPDATE [dbo].[Products] SET [Quantity] = @Quantity WHERE id = @Id";
                        cmd.Parameters.AddWithValue("@Quantity", quantity);
                        cmd.Parameters.AddWithValue("@id", id);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
    
            return result;
    
        }
    
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Sunday, December 1, 2019 12:18 AM
    Moderator