在当前月份之前获取记录的问题

Question

我试图在此处获取特定记录，但它总是返回空白

Try
cmd = con.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT SUM(a.qty_sold) FROM sales AS a INNER JOIN transaction_history AS c ON c.Id = a.transaction_id WHERE MONTH(c.transaction.date) = MONTH(CURRENT_DATE()) AND YEAR(c.transaction.date) = YEAR(CURRENT_DATE())"
cmd.ExecuteNonQuery()
Dim dt As New DataTable()
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While dr.Read
current_month_products_sold = dr.GetInt32(3).ToString()
End While

Catch ex As Exception
End Try
MessageBox.Show(current_month_products_sold)
total_product_sold.Text = current_month_products_sold

我不知道查询是问题还是我提取数据的方式

Answer 1

不要写入空的Catch块。他们只会吞下错误。

最好将数据库代码和用户界面代码分开。为此，我创建了一个Function。

命令和连接需要调用其Dispose方法，因为它们使用在Dispose方法中释放的非托管资源。即使出现错误，Using块也为我们处理关闭和处理。

在使用连接的方法中声明连接，以便可以对其进行处理。您可以将连接字符串传递给连接的构造函数。同样，将CommandText字符串和连接传递给命令的构造函数。

由于我们只检索一个值，因此可以使用ExecuteScalar。这将返回结果集第一行的第一列，始终为Object。因此，CInt。我假设qty_seld字段是Integer字段。

在代码中尝试之前，请在SSMS中测试Select语句。(SQL Server管理系统(

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim current_month_products_sold = ""
Try
current_month_products_sold = GetSalesSum().ToString
Catch ex As Exception
MessageBox.Show(ex.Message)
Exit Sub
End Try
MessageBox.Show(current_month_products_sold)
total_product_sold.Text = current_month_products_sold
End Sub
Private Function GetSalesSum() As Integer
Dim RetVal As Integer
Dim sql = "SELECT SUM(a.qty_sold) FROM sales AS a INNER JOIN transaction_history AS c ON c.Id = a.transaction_id WHERE MONTH(c.transaction.date) = MONTH(CURRENT_DATE()) AND YEAR(c.transaction.date) = YEAR(CURRENT_DATE())"
Using cn As New SqlConnection("Your connection string"),
cmd As New SqlCommand(sql, cn)
RetVal = CDec(cmd.ExecuteScalar())
End Using
Return RetVal
End Function

Answer 2

似乎有一些代码是其他东西遗留下来的。由于您只需要一个值，因此可以使用ExecuteScalar，如下所示：

Dim sql = "
SELECT SUM(a.qty_sold)
FROM sales AS a
INNER JOIN transaction_history AS c
ON c.Id = a.transaction_id
WHERE MONTH(c.transaction.date) = MONTH(CURRENT_DATE())
AND YEAR(c.transaction.date) = YEAR(CURRENT_DATE())"
Dim currentMonthProductsSold = 0
Using conn As New SqlConnection("yourConnectionString"),
cmd As New SqlCommand(sql, conn)
conn.Open()
currentMonthProductsSold = CInt(cmd.ExecuteScalar())
End Using
MessageBox.Show(currentMonthProductsSold)