stock calculation vb.net and sql

Question

my code is working but i think there's another way to do it using sql only. i am thinking reducing this code to much simpler. please help the code is getting the sum of stock table then subtract from transac table where same ricename then display the result. but if the there is null from the second query it is then display the result from the first query without having subtracting from the second query.

 Dim commnd2 As New SqlCommand("SELECT RICETYPE.RICENAME, Sum(STOCK.NUMBEROFSTOCKS) AS SumOfNUMBEROFSTOCKS FROM SUPPLIER INNER JOIN (RICETYPE INNER JOIN STOCK ON RICETYPE.ID = STOCK.RICETYPEID) ON SUPPLIER.ID = STOCK.SUPPLIER GROUP BY RICETYPE.RICENAME", cn)
    dr = commnd2.ExecuteReader()
    If dr.HasRows Then
        While dr.Read
            Dim commnd3 As New SqlCommand("SELECT RICETYPE.RICENAME, Sum(TRANSAC.NUMBEROFSALES) AS SumOfNUMBEROFSALES
            FROM RICETYPE INNER JOIN TRANSAC ON RICETYPE.ID = TRANSAC.RICETYPEID WHERE RICETYPE.RICENAME = '" & dr.Item(0) & "'
            GROUP BY RICETYPE.RICENAME
            ", cn)
            dr2 = commnd3.ExecuteReader()
            If dr2.HasRows Then
                While dr2.Read
                    dt.Rows.Add(dr2.GetValue(0), dr.GetValue(1) - dr2.GetValue(1), dr2.GetValue(1))
                End While
            Else
                dt.Rows.Add(dr.GetValue(0), dr.GetValue(1), 0)
            End If
        End While
    End If

Start using `Option Strict`, there's no excuse for using `Off`. Normally that wouldn't compile because you are subtracting objects from each other at `dr.GetValue(1) - dr2.GetValue(1)`. Use `dr.GetInt32(1) - dr2.GetInt32(1)` — Tim Schmelter, Jul 06 '18 at 13:49
Side note - Always use parameterized sql and avoid string concatenation to add values to sql statements. See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204) — Igor, Jul 06 '18 at 13:52
You tagged [tag:ssms-2017], if this is Sql Server why are you using the OleDb types (like `OleDbCommand`) over the Sql types (like `SqlCommand`)? Also a more appropriate tag would be [tag:sql-server] as you are not doing anything with SSMS directly in your posted question. — Igor, Jul 06 '18 at 14:03
forgot to change to SqlCommand. i already change my code. i forgot to edit the my code for this post — Kikiloaw, Jul 06 '18 at 14:08

score 1 · Accepted Answer · answered Jul 06 '18 at 14:32

1

This is one way to do it as a single query with the nested sum.

SELECT RTO.RICENAME, Sum(ST.NUMBEROFSTOCKS) AS SumOfNUMBEROFSTOCKS, (SELECT Sum(TRANSAC.NUMBEROFSALES) AS SumOfNUMBEROFSALES
            FROM RICETYPE RTI INNER JOIN TRANSAC T ON RTI.ID = T.RICETYPEID WHERE RTI.RICENAME = RTO.RICENAME) 
FROM SUPPLIER S INNER JOIN (RICETYPE RTO INNER JOIN STOCK ST ON RTO.ID = ST.RICETYPEID) ON S.ID = ST.SUPPLIER GROUP BY RTO.RICENAME

RTI is the inner reference to RICETYPE and RTO is the outer one.

answered Jul 06 '18 at 14:32

Ctznkane525

7,297
3
16
40

thanks a lot, i made it work in my new database with different names of columns and table. but my problem is understanding about how it works. – Kikiloaw Jul 07 '18 at 18:51
It's a nested query...there's a calculation inside to get the sum for the matching name for the given row...rti is the innner alias to record....if u like....please accept...thanks – Ctznkane525 Jul 07 '18 at 19:46

stock calculation vb.net and sql

1 Answers1