Nyx Assasin Nyx Assasin - 4 months ago 17
Vb.net Question

Don`t Update if the Possible Output is Zero

I have a two tables as shown below:

Tables

The left table is populated with data based on the column

SRNumber
and after that you can see that the Table
stockrequisition
and Table
generalinventory
have the same data in the column
ItemCode
and
Qty
(
RequestedQty
on the left table)

mySQL Command

UPDATE GeneralInventory AS tb1
INNER JOIN StockRequisition AS tb2
ON (tb1.ItemCode= tb2.ItemCode)
SET tb1.Qty = Format(tb1.Qty - tb2.RequestedQty,2)
WHERE tb2.SRNumber = 'SR487SHL'


and that command is located at the button Update where the code works

Code Output

GeneralInventory.Qty
=
GeneralInventory.Qty
-
StockRequisition.RequestedQty
where
StockRequisition.SRNumber
=
SR487SHL
and
GeneralInventory.ItemCode
=
StockRequisition.ItemCode


Issue

How can I determine if the output of
GeneralInventory.Qty
becomes negative so that I can stop the update command. something like this

if the future output of GeneralInventory.Qty < 0 or Negative then
Msg("Unable to update your request because some of Items Qty will result to Negative,Please review it first")
else
'Do the Update Command
End If


Here is my whole code in VB.Net

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
one()
two()
End Sub
Private Sub one()
Dim con1 As MySqlConnection = New MySqlConnection("server=localhost;userid=root;password=admin1950;database=inventory")
Dim sql1 As MySqlCommand = New MySqlCommand("select SRNumber,ItemCode,RequestedQty from stockrequisition where SRNumber = 'SR487SHL'", con1)
Dim ds1 As DataSet = New DataSet
Dim adapter1 As MySqlDataAdapter = New MySqlDataAdapter
con1.Open()
adapter1.SelectCommand = sql1
adapter1.Fill(ds1, "MyTable")
DataGridView1.DataSource = ds1.Tables(0)
con1.Close()
End Sub
Private Sub two()
Dim con1 As MySqlConnection = New MySqlConnection("server=localhost;userid=root;password=admin1950;database=inventory")
Dim sql1 As MySqlCommand = New MySqlCommand("select ItemCode,Qty from generalinventory", con1)
Dim ds1 As DataSet = New DataSet
Dim adapter1 As MySqlDataAdapter = New MySqlDataAdapter
con1.Open()
adapter1.SelectCommand = sql1
adapter1.Fill(ds1, "MyTable")
DataGridView2.DataSource = ds1.Tables(0)
con1.Close()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con1 As MySqlConnection = New MySqlConnection("datasource=localhost;database=inventory;userid=root;password=admin1950")
Dim cmdinsert As MySqlCommand = New MySqlCommand("UPDATE GeneralInventory AS tb1 INNER JOIN StockRequisition AS tb2 ON (tb1.ItemCode= tb2.ItemCode) SET tb1.Qty = Format(tb1.Qty - tb2.RequestedQty,2)WHERE tb2.SRNumber = 'SR487SHL' AND tb1.Qty >= tb2.RequestedQty", con1)
con1.Open()
cmdinsert.ExecuteNonQuery()
con1.Close()
one()
two()
End Sub


I hope you get me, TYSM for future help.

Answer

Simply use CASE WHEN:

UPDATE GeneralInventory AS tb1 
INNER JOIN StockRequisition AS tb2  
ON (tb1.ItemCode= tb2.ItemCode) 
SET tb1.Qty = CASE WHEN tb1.Qty - tb2.RequestedQty < 0 THEN tb1.Qty THEN Format(tb1.Qty - tb2.RequestedQty,2) END
WHERE tb2.SRNumber = 'SR487SHL'

Or you can use IF:

UPDATE GeneralInventory AS tb1 
INNER JOIN StockRequisition AS tb2  
ON (tb1.ItemCode= tb2.ItemCode) 
SET tb1.Qty = IF(tb1.Qty - tb2.RequestedQty < 0, tb1.Qty, Format(tb1.Qty - tb2.RequestedQty,2))
WHERE tb2.SRNumber = 'SR487SHL'

Or you can do it like this:

UPDATE GeneralInventory AS tb1 
INNER JOIN StockRequisition AS tb2  
ON (tb1.ItemCode= tb2.ItemCode) 
SET tb1.Qty = Format(tb1.Qty - tb2.RequestedQty,2)
WHERE tb2.SRNumber = 'SR487SHL'
AND tb1.Qty >= tb2.RequestedQty

If you want to return messsage, then you have to create a procedure.

Comments