Question How to subtract the sum of sale qty from the sum of stock qty to have available qty

[Godis63]

Greetings to all Forum members.

I am designing a sales database in C# Windows Form Application where I need to create a stored procedure to relate the stock and sales tables to each other.


In the project I have textboxes named “grossStockQty” from the stock table, “grossSalesQty” from the sales table and “AvailableQty” on the stored procedure form, respectfully.


I used the following code to sum up record from the “stockQty” in the stock table into the “grossStockQty” textbox:-

grossStockQtyTextBox.Text = (from DataGridViewRow row in stockDataGridView.Rows

where row.Cells[7].FormattedValue.ToString() != string.Empty

select Convert.ToDecimal(row.Cells[7].FormattedValue)).Sum().ToString();



I also used the following code to sum up record from the “salesQty” in the sales table into the “grossSalesQty” textbox:-

grossSalesQtyTextBox.Text = (from DataGridViewRow row in salesDataGridView.Rows

where row.Cells[17].FormattedValue.ToString() != string.Empty

select Convert.ToDecimal(row.Cells[17].FormattedValue)).Sum().ToString();



Now, on the Stored Procedure Form, I have created a textbox I want to subtract gross sales qty from the gross stock qty into the textbox named “AvailableQty”, but I am stuck.


Please, I will appreciate if someone can help me fix my problem.

Thanks.

 

[Skydiver]

A stored procedure normally runs on the database server and written in SQL, not on the client side UI using a client side programming language/framework. Are you just calling it a "Stored Procedure" but you are really just running a C# LINQ query on the Windows Form UI?

 

[Godis63]

Yes, as usual, I build the AvailableQty stored procedure on the database server side but included it in the dataset from the project DataSource. I drew the stored procedure as controls on the Windows Form UI to create the interface to operate on the database server.

I closely observed that the only way (I don't know if there are different other ways) I can draw data from the two table on one interface is by using the stored procedure. I have successfully achieved that, through which I can pull data from the source.

Now, what I want to do is after pulling the data onto the form UI I can use a button to manipulate the data in an unbound control. All I need is a code in the button to subtract the gross sales qty from the gross stock qty to display the difference in the unbound control.
Thanks

 

[Skydiver]

I drew the stored procedure as controls on the Windows Form UI to create the interface to operate on the database server.

I closely observed that the only way (I don't know if there are different other ways) I can draw data from the two table on one interface is by using the stored procedure. I have successfully achieved that, through which I can pull data from the source.

Let's step back a bit. This is not a stored procedure:

from DataGridViewRow row in stockDataGridView.Rows

where row.Cells[7].FormattedValue.ToString() != string.Empty

select Convert.ToDecimal(row.Cells[7].FormattedValue

It is a LINQ query. This LINQ query is running on the client side. It is not running on the database server. The database server would have no access to the UI elements of the stockDataGridView.

 

[Skydiver]

Anyway, if you are running client side, and you have access to both of the grids in that form, then you could do something:

decimal ColumnSum(DataGridView dgv, int column)
    => dgv.Rows.Sum(row => decimal.TryParse(row.Cells[column].FormattedValue.ToString(), out decimal v) ? v : 0m);

var availableQuantity = ColumnSum(stockDataGridView, 7) - ColumnSum(salesDataGridView, 17);

 

[Godis63]

Anyway, if you are running client side, and you have access to both of the grids in that form, then you could do something:

decimal ColumnSum(DataGridView dgv, int column)
    => dgv.Rows.Sum(row => decimal.TryParse(row.Cells[column].FormattedValue.ToString(), out decimal v) ? v : 0m);

var availableQuantity = ColumnSum(stockDataGridView, 7) - ColumnSum(salesDataGridView, 17);

Thanks for your assistance.
However, I have an error display that: -

"Error CS1061 'DataGridViewRowCollection' does not contain a definition for 'Sum' and no accessible extension method 'Sum' accepting a first argument of type 'DataGridViewRowCollection' could be found (are you missing a using directive or an assembly reference?)"

So, the error could not allow me to build the form application.

This the code I tailored using the format you sent me: -

 private void btnAvailableQty_Click(object sender, EventArgs e)
        {
            decimal ColumnSum(DataGridView availableStockDataGridView, int column) => availableStockDataGridView.Rows.Sum(row => decimal.TryParse(row.Cells[column].FormattedValue.ToString(), out availableQtyTextBox.Text v) ? v : 0m);

            var availableQty = ColumnSum(availableStockDataGridView, 3) - ColumnSum(availableStockDataGridView, 6);
        }