Question using resultant value of a select statement as a parameter

[mattatuni]

Hi im new to vb.net, I am currently working on a stock system, the below code is from a method in a web service i have created that allows someone to request items, the paramenters supplied to the webservice are itemID, qty and branch id.

I want to check the database to make sure that the item requested is in stock , if it is then the stock table will be decreased by the amount requested, I get an error though due to the output of the select statement not being in integer format.. the second part of the code works ....can anyone help with the first section (checking how many of the item requested are in stock and then putting that into the variable --'amountcheck')

Public Function RequestOrder(ByVal ItemID As Integer, ByVal qty As Integer, ByVal branchID As Integer) As Boolean

Dim strUpdate As String
Dim conn As SqlConnection

Dim cmd As SqlCommand
Dim connStr As String
Dim amountcheck As Integer

connStr = "Server=USER-214CE13F8A\SQLEXPRESS;Initial Catalog=BBB_DB;Integrated Security=True"
amountcheck = "SELECT stock from tblItem where itemID = '" & CStr(ItemID) & "';"
conn = New SqlConnection(connStr)
cmd = New SqlCommand(amountcheck, conn)
conn.Open()
cmd.ExecuteNonQuery()

If qty >= amountcheck Then

'decrease stock'
connStr = "Server=USER-214CE13F8A\SQLEXPRESS;Initial Catalog=BBB_DB;Integrated Security=True"
strUpdate = "UPDATE tblItem SET stock = stock - '" & CStr(qty) & "' WHERE ItemID = '" & CStr(ItemID) & "'"
conn = New SqlConnection(connStr)
cmd = New SqlCommand(strUpdate, conn)
conn.Open()
cmd.ExecuteNonQuery()

Return True

Else : Return False


End If
End Function


Help would be greatly appriciated

cheers

 

[cjard]

Using CODE tags would be greatly appreciated
Visual Basic .NET Forums - BB Code List

Then you should read the PQ link in my signature

Why do you bother with the SELECT at all? This would suffice:


UPDATE tblItem SET stock = stock - @qty WHERE ItemID = @itemID AND stock >= @qty


If youre wondering what the @qty and @itemID are, you didnt read the PQ link.

Now, when you ExecuteNonQuery() this, it will return a number greater than 0 if it succeeded, or 0 if it failed
It can fail for two reasons because there are two elements to the where clause:
ItemID was not found
stock was insufficient

Assuming you are certain the ItemID is correct, it can only be the case that stock level was insufficient if the query updates 0 rows