We help IT Professionals succeed at work.

MS SQL Command to determine if sufficient quantities exist

I have a table with two fields to hold qty values.  Qty In Stock and Qty Out.  I want to subtract the qty out from the qty in to determine qty available.  The SQL db is accessed through a Win Form.  The issue is I want the SQL command to recognize the qty in & qty out of currently selected item.
Comment
Watch Question

select [Qty In Stock]-[qty out ] from TableName
Aaron TomoskyDirector, SD-WAN Solutions

Commented:
To add:
Select * from (
select [Qty In Stock]-[qty out ] as stock from TableName
) where stock > 0

Commented:
select  qtyin, qtyout, stock = (qtyin - qtyout)
from  yourtable
where item_id = your_selected_item

and if you want to show just those that have actual stock add the and condition:

and (qtyin-qtyout)  > 0

Author

Commented:
Thanks gents.  I am trying to call this into the form in Visual Studio.  These appear to be the commads themselves.  I think I was good on the sum fuction.  (Baretree appears to be the closest with the selected value).  I tried creating a new sqlclient.sqlcommand in the form but the label I created the result for just kept saying false.

Commented:
could you post a snippet of the code you're trying cause it is confusing the last part you're writing "the label i created the result for just kept saying false"....
it'd help a lot if you could post that so that we can help you better

Author

Commented:
Ok.  here is where I am at.  The following query was added to the dataset.
SELECT     SUM(UnitsInStock - UnitsOut) AS availableQty
FROM         Items
WHERE     (Description = @Description + '%')

Open in new window


I want to utilize the above query in my winform by passing the description value from a combo box

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        txtItemQty.Focus()
    End Sub

Open in new window


So my issues are:
1. Does the query look correct?
2. How do I call it into the code?
3. How do I pass in the string text of desription?
Does this make sense?
Most Valuable Expert 2012
Top Expert 2014
Commented:
Try the below code



Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim dbcon As New SqlConnection("your connection string")
dbcon.Open()
Dim dbcmd As New SqlCommand
dbcmd.Connection = dbcon
dbcmd.CommandText = "SELECT UnitsInStock - UnitsOut AS availableQty FROM Items WHERE Description = '" & combobox1.Text & "'"
Dim qty As Integer = dbcmd.ExecuteScalar()
dbcmd.Dispose()
dbcon.Dispose()
End Sub

Open in new window

Author

Commented:
The label says "Sold" after trying the above.

Author

Commented:
Nevermind.  that is the default text property.  I forgot to pass the qty result.  Thanks Code!!!