How do I add new columns to a .NET DataTable?

This code shows how to add new columns to a datatable. It uses 2 datatables, one contains Sales Person data while the second contains Sales figures. Each sales person can have many sales. It is desired to add details for the top sale for each sales person to the Sales Person datatable in the form of new columns.

The VB.NET code below shows how to add new DataColumns to a DataTable.

Retrieve data from the database:

    Imports System.Data.OleDb
    ' Retrieve the data.
    Dim Connection As New OleDbConnection(strConnection)
    Dim DS As New DataSet

    Dim strSQL As String = _
          "Select * From SalesAssociates Order By SalesPersonId"
    Dim DA As New OleDbDataAdapter(strSQL, Connection)
    DA.Fill(DS, "SalesPerson")

    strSQL = "Select SalesPersonID,SaleAmount,OrderNum," & _
             "ItemName From Sales Order By SaleAmount Desc"
    DA = New OleDbDataAdapter(strSQL, Connection)
    DA.Fill(DS, "SalesData")

Add the new columns to the SalesPerson datatable:

    Dim adc1 As DataColumn
    Dim adc2 As DataColumn
    Dim adc3 As DataColumn

    adc1 = New DataColumn("SaleAmount", System.Type.GetType("System.Decimal"))
    adc2 = New DataColumn("OrderNum", System.Type.GetType("System.Int32"))
    adc3 = New DataColumn("ItemName", System.Type.GetType("System.String"))


Fill the new SalesPerson columns with the SalesData data:

    Dim aDR As DataRow
    Dim intResult As Integer
    ' Process each sales person row.
    For Each aDR In DS.Tables("SalesPerson").Rows
        ' Filter the SalesData datatable by the current sales person.
        DS.Tables("SalesData").DefaultView.RowFilter = _
              "SalesPersonID=" & aDR(SalesPersonID).ToString
        ' If there is a sales row for this sales person populate the data.
        intResult = DS.Tables("SalesData").DefaultView.Count - 1

        If intResult > 0 Then
            aDR("SaleAmount") = _
            aDR("OrderNum") = _
            aDR("ItemName") = _
        End If
        ' Remove the filter.
       DS.Tables("SalesPerson").DefaultView.RowFilter = ""


About TheScarms
About TheScarms

Sample code
version info

If you use this code, please mention ""

Email this page

© Copyright 2016 TheScarms
Goto top of page