How to Compute a Database Column in .NET

Question

I have a Database named CarsType.accdb there are four fields in the data base Item_Name, Item_Num, Item_Qty, Item_Cost.

I am able to get the database to display my data in VisualBasic but I am not sure how to get the total cost to appear in my label (lblTotalCost). I prefer doing it in VB versus writing in my access program. All I am wanting to do it multiply item_qty * Item_Cost How would I go about doing that?

Public Class frmCarInventory

    Private Sub CarInventoryBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CarInventoryBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.CarInventoryBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.CarDataSet)

    End Sub

    Private Sub frmCarInventory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CarDataSet.CarInventory' table. You can move, or remove it, as needed.
        Me.CarInventoryTableAdapter.Fill(Me.CarDataSet.CarInventory)


        Try
            Me.CarInventoryTableAdapter.Fill(Me.CarDataSet.CarInventory)
        Catch ex As Exception
            MsgBox("The Database Files is Unavailable", , "Error")
        End Try



    End Sub

    Private Sub btnComputeTheTotalValueOfInventory_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnComputeTheTotalValueOfInventory.Click


        Dim strSql As String = "SELECT * FROM CarType "

        'strPath provides the database type and path of the CarType database.
        Dim strPath As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;" & "Data Source=..\CarType.accdb"
        Dim odaInventory As New OleDb.OleDbDataAdapter(strSql, strPath)
        Dim DatCost As New DataTable
        Dim intCount As Integer
        Dim decTotalCost As Decimal = 0D

        'The DataTable name datCost is filled with the data
    odaInventory.Fill(DatCost)

        'The connection to the databsise is disconnected
        odaInventory.Dispose()

        For intCount = 0 To DatCost.Rows.Count - 1
            decTotalCost += Convert.ToDecimal(DatCost.Rows(intCount)("Total Inventory  Cost"))
        Next

        Me.lblTotalCost.Visible = True
        Me.lblTotalCost.Text = "El Value " & decTotalCost.ToString("C")

    End Sub
End Class

Would this be handled like an sql satement?

Answer 1

Perhaps you could just use something like this?

Dim TotalCost As String

TotalCost = Me![item_qty] * Me![item_cost]

Me![lblTotalCost].Caption = TotalCost

Answer 2

When you add a new System.Data.DataColumn to a System.Data.DataTable, you can specify an expression, thus creating a computed column. In C# the syntax to do this is:

dataTable.Columns.Add("total_cost", typeof(double), "item_qty * item_cost");  

The syntax to do this in VB.NET will be quite similar, but I've left it as an exercise for the reader.