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

 

[Godis63]

I have Got it. By trying the following code it worked.

 private void btnGrossCalculations_Click(object sender, EventArgs e)
        {
            decimal grossStockQty, grossSalesQty, availableQty;

            grossStockQtyTextBox.Text = (from DataGridViewRow row in availableQtyDataGridView.Rows
                                     where row.Cells[3].FormattedValue.ToString() != string.Empty
                                     select Convert.ToDecimal(row.Cells[3].FormattedValue)).Sum().ToString();

            grossSalesQtyTextBox.Text = (from DataGridViewRow row in availableQtyDataGridView.Rows
                                     where row.Cells[4].FormattedValue.ToString() != string.Empty
                                     select Convert.ToDecimal(row.Cells[4].FormattedValue)).Sum().ToString();

            grossStockQty = Convert.ToDecimal(grossStockQtyTextBox.Text);
            grossSalesQty = Convert.ToDecimal(grossSalesQtyTextBox.Text);

            availableQty = grossStockQty - grossSalesQty;
            availableQtyTextBox.Text = availableQty.ToString();
        }

However, I have encountered another problem in the same project which has to do with the code I used to build the stored procedure which is the source table for the Windows Form (AvailableQty).

The problem am encountering is that; when I run the project and pull data from the AvailableQty stored procedure into the form it displays wrong information. Initially I made two entries in the stock form and three entries in the sales form. But six rows were displayed in the AvailableQty form, making the calculations wrong. I am expecting three rows to be displayed with GrossStockQty as 1000, GrossSalesQty as 860 and AvailableQty as 40.

This is the SELECT query I used to create the stored procedure: -

CREATE PROCEDURE [dbo].[AvailableQty]
@code int = 0

AS
SELECT Stock.Category, Stock.ProductName, Stock.Model, Stock.StockQty, Sales.SaleQty FROM Stock, Sales
WHERE Stock.Code = Sales.Code AND Stock.Code = @code

RETURN 0

Attached is the output result detail.

I will appreciate if someone could help me fix the problem

Thanks

 

[Skydiver]

I suspect that you are inserting rows into your backend database instead of updating rows. Unfortunately, you are not showing us how you are updating the the database.

 

[Godis63]

I suspect that you are inserting rows into your backend database instead of updating rows. Unfortunately, you are not showing us how you are updating the the database.

I am neither inserting nor updating.
Am just filtering for information from the two related tables, and then calculate the difference into an unbound control, temporarily.

 

[Skydiver]

What two related tables? Your stored procedure is only querying a single table. Your LINQ queries are pulling data from a data grid view, and who knows what kind of data filtering/massaging has been done on that data in the data grid view.

And as you said, that data grid view is showing 6 rows, but you are expecting to only see 3 rows. That stored procedure that you shared above is querying everything out of the table from the database that satisfies the condition Stock.Code = Sales.Code AND Stock.Code = 0. Unless you are doing additional filtering/massaging, that means that the database contains 6 rows that satisfy that condition.

 

[cjard]

It would be easier to work with the strongly typed datatable the DataGridView is bound to, than work with pulling the values out of the DataGridView itself (that's vb6 style, about 30 years out of date)

 

[Godis63]

It would be easier to work with the strongly typed datatable the DataGridView is bound to, than work with pulling the values out of the DataGridView itself (that's vb6 style, about 30 years out of date)

Well, I built a stored procedure on two tables (i.e Stock and Sales). I know that has also become a table on its own. But has source data from some of the columns in the mentioned tables. No data is inserted or updated in the AvailableQty.

I think my problem had to do with SELECT language when creating the stored procedure. Fortunately, someone corrected me somewhere, so when I updated it , it gave me the expected result

CREATE PROCEDURE [dbo].[AvailableQty]
    @code int = 0
    
AS
    BEGIN
    -- Aggregate sales quantities
    SELECT
        s.Category,
        s.ProductName,
        s.Model,
        s.StockQty,
        ISNULL(SUM(sa.SaleQty), 0) AS SaleQty
        
    FROM
        Stock s
    LEFT JOIN
        Sales sa ON s.Code = sa.Code
    WHERE
        s.Code = @code
    GROUP BY
        s.Category, s.ProductName, s.Model, s.StockQty
END   
RETURN 0