我需要一个解决方案。我想将一个表中的字段值与另一个表中的字段值相减/相加。我在说细节。
我有两张桌子(同样的结构)。
表1-purchase
字段:id, purchase_from, product_name, quantity, rate
表2-sales
字段:id, sale_to, product_name, quantity, rate
现在我要将数量从purchase
表减到sales
表,并将数据显示到datagridview中。
我试过了,但失败了。我给你一段代码。Dim PurchasePrdName, SalesPrdName As String Dim PurchaseQty, SalesQty As Double
Private Sub LoadPurchase()
Try
OpenConnection()
Dim dcommand As New MySqlCommand
Dim qry As String = "SELECT ProductName,SUM(Quantity) FROM stockitems GROUP BY ProductId"
dcommand.Connection = conn
dcommand.CommandText = qry
Dim dbread As MySqlDataReader = dcommand.ExecuteReader
DataGridView1.Rows.Clear()
While dbread.Read
If dbread.HasRows Then
PurchasePrdName = dbread("ProductName").ToString
PurchaseQty = dbread("SUM(Quantity)").ToString
End If
End While
dbread.Close()
Catch ex As Exception
MsgBox(ex.Message)
Finally
CloseConnection()
End Try
End Sub
Private Sub LoadSales()
Try
OpenConnection()
Dim dcommand As New MySqlCommand
Dim qry As String = "SELECT SUM(Quantity) FROM salesitems WHERE ProductName='" & PurchasePrdName & "' GROUP BY ProductId"
dcommand.Connection = conn
dcommand.CommandText = qry
Dim dbread As MySqlDataReader = dcommand.ExecuteReader
DataGridView1.Rows.Clear()
While dbread.Read
If dbread.HasRows Then
SalesQty = dbread("SUM(Quantity)").ToString
End If
End While
dbread.Close()
Catch ex As Exception
MsgBox(ex.Message)
Finally
CloseConnection()
End Try
End Sub
然后将此数据加载到DataGridView。。。
Private Sub LoadData() Try LoadPurchase() LoadSales() Dim PrdName As String = PurchasePrdName Dim Qty As Double = PurchaseQty - SalesQty DataGridView1.Rows.Add(PrdName, Qty) DbGridCustomize() Catch ex As Exception MsgBox(ex.Message) End Try End Sub
但它只显示了一个产品从表。我搞不懂为什么!!!
你能帮我一下吗???
我将非常感谢你
最佳答案
您只向网格中添加了一行,这是分配给PurchasePrdName的产品。您要么需要将值集合绑定到网格,要么需要遍历值集合。似乎您在这里所做的一切都是将一个值赋给一个变量,然后使用以下命令向网格添加一行:
DataGridView1.Rows.Add(PrdName, Qty)
编辑:
我将把您的变量PurchasePrdName和SalePrdName更改为一个集合,因为您希望这里存储多个值。
Private PurchasePrdName As Dictionary(Of String, Double)
Private SalesPrdName As Dictionary(Of String, Double)
然后,当您加载您的购买和销售时,您将执行以下操作:
While dbread.Read
If dbread.HasRows Then
PurchasePrdName.Add(dbread("ProductName").ToString, dbread("SUM(Quantity)"))
End If
End While
然后,当你做你的销售,你需要循环通过你的产品字典,找出每个产品名称。您可以存储销售数量,但也可以同时计算差额并存储。最好创建一个包含所有项的类,并将它们存储在一个字典中。
在大量的销售中,你会反复使用你的字典
For Each product In PurchasePrdName.Keys
Dim qry As String = "SELECT SUM(Quantity) FROM salesitems WHERE ProductName='" & product & "' GROUP BY ProductId"
dcommand.CommandText = qry
Dim dbread As MySqlDataReader = dcommand.ExecuteReader
DataGridView1.Rows.Clear()
While dbread.Read
If dbread.HasRows Then
SalesPrdName.Add(product, PurchasePrdName(product) - Double.Parse(dbread("SUM(Quantity)")))
End If
End While
dbread.Close()
Next
最后,您还需要在循环中添加行。。。
For Each product In SalesPrdName.Keys
DataGridView1.Rows.Add(product, SalesPrdName(product))
Next
您可能需要更改变量名以反映它们的新含义等。如果要存储销售数量,我建议您使用一个字典,其中产品名为键,类为值。这个类将由产品名称、产品数量、销售数量以及两者之间的差异组成。
关于mysql - 计算来自两个不同表的字段并显示在DatagridView中,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/26658498/