I'm using the following function to calculate sum qty value of given item code(VB.NET, SQL Server, SQL). I do not get any errors but the returned value by the function is always -1
. The data is available in the table(Screenshot attached). I think, something is missing in my code but cant figure it out. Any advice will be highly appreciated.
Public Function findPurchaseQty(ByVal itCode As String, ByVal fromDate As DateTime, toDate As DateTime) As Double
Dim sql = "SELECT sum(purchase_qty) FROM TB_STOCK WHERE it_code = @it_code AND added_date >= @added_date_start AND added_date < @added_date_end"
Dim command As New SqlCommand(sql, conn)
With command.Parameters
.Add("@it_code", SqlDbType.VarChar, 50).Value = itCode
.Add("@added_date_start", SqlDbType.DateTime).Value = fromDate
.Add("@added_date_end", SqlDbType.DateTime).Value = toDate
End With
Dim purchaseTotal As Double = command.ExecuteNonQuery()
Return purchaseTotal
End Function
I execute the function for testing as follows but later plan to take values from date time picker,
Dim fromDate As DateTime = "2020-07-20 00:00:00"
Dim toDate As DateTime = "2020-07-22 23:59:59"
Dim itCode As String = "0001"
MsgBox(findPurchaseQty(itCode, fromDate, toDate))
As the name already says ExecuteNonQuery
is not the right method for a SELECT
as it returns the number of rows affected, not the result of the SELECT
-Query.
What you're looking for is ExecuteScalar
As ExecuteScalar
returns an Object
you have to cast the result to a Double
.
Dim purchaseTotal As Double = DirectCast(command.ExecuteScalar(), Double)
As mentioned in comments, it is a good idea to check if ExecuteScalar()
returns a number instead of DBNull.Value
, to prevent DirectCast
from failing.
Dim result As Object = command.ExecuteScalar()
Dim purchaseTotal As Double
If Not DBNull.Value.Equals(result) Then
purchaseTotal = DirectCast(result, Double)
End If