ลองเขียนดูแล้วครับ แต่ Error ว่า Object reference not set to an instance of an object.
โค้ดครับ
'////////////////////////////UpdateProductCostAvg
CProductID = LvOrder.Items(i).SubItems(1).Text
Dim sqlsave As String = ""
sb.Append("SELECT SUM(AmountOrder*Price)/SUM(NumberToReceived) ")
sb.Append(" FROM OrdersDetail")
sb.Append(" Where (ProductID=@ProductID)")
sb.Append(" GROUP BY ProductID")
sqlSave = sb.ToString()
With cm
.CommandText = sqlsave
.Parameters.Clear()
.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = CProductID
dr = .ExecuteReader()
If dr.HasRows Then
dr.Read()
CCostAvg = dr.GetDouble(1)
End If
End With
dr.Close()
sb.Remove(0, sb.Length())
sb.Append("UPDATE Product")
sb.Append(" SET LastCost=@LastCost,")
sb.Append(" CostAvg=@CostAvg")
sb.Append(" WHERE (ProductID=@ProductID)")
sqlSave = sb.ToString()
With cm
.CommandText = sqlsave
.Parameters.Clear()
.Parameters.Add("@LastCost", SqlDbType.Float).Value = CCost
.Parameters.Add("@CostAvg", SqlDbType.Float).Value = CCostAvg
.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = CProductID
.ExecuteNonQuery()
End With
Load balance : Server 02
