sql - ExecuteNonQuery 总是返回 -1，即使 SQL 语句是正确的

Question

我正在使用以下函数来计算给定项目代码(VB.NET、SQL Server、SQL)的总数量值。我没有收到任何错误，但函数的返回值始终是 -1。表中提供了数据(附有屏幕截图)。我想，我的代码中缺少某些东西，但无法弄清楚。任何建议将不胜感激。

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

我按如下方式执行测试功能，但稍后计划从日期时间选择器中获取值，

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

Answer 1

顾名思义ExecuteNonQuery不是 SELECT 的正确方法，因为它返回受影响的行数，而不是 SELECT-Query 的结果。

您要找的是ExecuteScalar

由于 ExecuteScalar 返回一个 Object，您必须将结果转换为 Double。

Dim purchaseTotal As Double = DirectCast(command.ExecuteScalar(), Double)

---

如评论中所述，检查 ExecuteScalar() 是否返回数字而不是 DBNull.Value 是个好主意，以防止 DirectCast 失败。

Dim result As Object = command.ExecuteScalar()
Dim purchaseTotal As Double
If Not DBNull.Value.Equals(result) Then
    purchaseTotal = DirectCast(result, Double)
End If