我試圖在此處獲取特定記錄，但它始終回傳空白
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
我不知道是查詢的問題還是我拉資料的方式
uj5u.com熱心網友回復：
不要寫空Catch塊。他們只會吞下錯誤。
最好將資料庫代碼和用戶界面代碼分開。我創建了一個Function來做到這一點。
命令和連接需要Dispose呼叫它們的方法，因為它們使用在Dispose方法中釋放的非托管資源。Using即使出現錯誤，塊也會為我們處理關閉和處理。
在使用它們的方法中宣告連接，以便可以處理它們。您可以將連接字串傳遞給連接的建構式。同樣，將CommandText字串和連接傳遞給命令的建構式。
由于我們只檢索單個值，因此我們可以使用ExecuteScalar. 這將回傳結果集第一行的第一列，始終是Object. 因此，CInt. 我假設 qty_sold 欄位是一個整數欄位。
在嘗試代碼之前，先在 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
uj5u.com熱心網友回復：
似乎有一些代碼是從其他東西中遺留下來的。由于您只需要一個值，因此可以使用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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/396895.html