ExecuteNonQuery always returns -1 even when the SQL statement is correct

user_12419718 0
Viewed 93

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))

Data table

executenonquery vb.net sql-server sql
1 Answers

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
user_29679650
Related