locked
Fill data table column based on another column from another table (reference table)? RRS feed

  • Question

  • Hallo,

    I have 2 data tables, one is standard rate table which has a predefined hours and rates for a specific object. Another table which comes from a user with selected objects and quantities, in this table i have to apply standard rates and hours. What is the best way to achieve this ? should i make a data table relations or any other method or any example? 

    Thanks

    Thursday, September 12, 2019 8:47 AM

Answers

  • Hi,
    try this demo:

    Public Class Form1
    
      Private ds As New DataSet
      Private dgv1 As New DataGridView With {.Dock = DockStyle.Left}
      Private dgv2 As New DataGridView With {.Dock = DockStyle.Fill}
      Private WithEvents btn As New Button With {.Dock = DockStyle.Top, .Text = "Populate"}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        Me.Controls.AddRange(New Control() {dgv2, dgv1, btn})
        LoadData()
        dgv1.DataSource = ds.Tables("StandardRates")
        dgv2.DataSource = ds.Tables("Offers")
      End Sub
    
      Private Sub LoadData()
        With ds
          With .Tables.Add("StandardRates")
            With .Columns
              .Add("Object", GetType(String))
              .Add("Size", GetType(Decimal))
              .Add("StandardHours", GetType(Decimal))
              .Add("StandardRate", GetType(Decimal))
            End With
            With .Rows
              .Add("Object 1", 20, 2, 30)
              .Add("Object 2", 20, 2, 30)
              .Add("Object 1", 30, 4, 50)
              .Add("Object 2", 30, 4, 50)
            End With
          End With
          With .Tables.Add("Offers")
            With .Columns
              .Add("Object", GetType(String))
              .Add("Size", GetType(Decimal))
              .Add("StandardHours", GetType(Decimal))
              .Add("StandardRate", GetType(Decimal))
              .Add("TotalHr", GetType(Decimal), "Size * StandardHours")
              .Add("TotalRate", GetType(Decimal), "Size * StandardRate")
            End With
            With .Rows
              .Add("Object 1", 20)
              .Add("Object 2", 20)
              .Add("Object 3", 20)
              .Add("Object 4", 20)
              .Add("Object 1", 30)
              .Add("Object 2", 30)
              .Add("Object 3", 30)
              .Add("Object 4", 30)
            End With
          End With
        End With
      End Sub
    
      Private Sub btn_Click(sender As Object, e As EventArgs) Handles btn.Click
        PopulateData()
      End Sub
    
      Private Sub PopulateData()
        For Each offerRow As DataRow In ds.Tables("Offers").Rows
          Dim offerObject = offerRow.Field(Of String)("Object")
          Dim offerSize = offerRow.Field(Of Decimal)("Size")
          Dim standard = (From rate In ds.Tables("StandardRates")
                          Where rate.Field(Of String)("Object") = offerObject _
                         AndAlso rate.Field(Of Decimal)("Size") = offerSize).FirstOrDefault
          If standard IsNot Nothing Then
            offerRow("StandardHours") = standard.Field(Of Decimal)("StandardHours")
            offerRow("StandardRate") = standard.Field(Of Decimal)("StandardRate")
          Else
            offerRow("StandardHours") = DBNull.Value
            offerRow("StandardRate") = DBNull.Value
          End If
        Next
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by Shan1986 Saturday, September 14, 2019 4:21 PM
    Thursday, September 12, 2019 11:35 AM

All replies

  • Hello,

    I see the standard rates table as a reference table and offer table as the main table. The proper method to join them and produce a DataTable would be to join the two together using keys. In the example below the tables are joined on keys (note the key symbol). This is SQL-Server but will work for MS-Access, Oracle and other databases too.

    In the code below I placed the SQL SELECT statement in a function which is used by the function below. The for-next is completely optional, if marks key fields as hidden for when I show the data in a DataGridView and I saw the DataTable in a DataGridView so you can see the results. Note those key fields provide the ability to change values if needed in a SQL UPDATE statement.

    In the form the for-each is optional, it's for beautifying column headers

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New Operations
            DataGridView1.DataSource = ops.ReadData
    
            For Each col As DataGridViewColumn In DataGridView1.Columns
                col.HeaderText = col.HeaderText.SplitCamelCase()
            Next
        End Sub
    End Class
    


    Imports System.Data.SqlClient
    
    Public Class Operations
        Private ConnectionString As String =
                    "Data Source=KARENS-PC;" &
                    "Initial Catalog=PatientTrackerDatabase;" &
                    "Integrated Security=True"
    
        Public LastException As Exception
        Public Function ReadDataStatement() As String
            Return <SQL>
    SELECT P.id ,
           P.FirstName ,
           P.LastName ,
           P.GenderIdentifier ,
           P.DepartmentIdentifier ,
           D.DepartmentName ,
           GT.Gender
    FROM   Patient AS P
           INNER JOIN Department AS D ON P.DepartmentIdentifier = D.DepartmentIdentifier
           INNER JOIN GenderTypes AS GT ON P.GenderIdentifier = GT.GenderIdentifier;
                   </SQL>.Value
        End Function
        Public Function ReadData() As DataTable
            Dim dt = New DataTable
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    Try
                        cmd.CommandText = ReadDataStatement()
                        cn.Open()
                        dt.Load(cmd.ExecuteReader())
                        For index As Integer = 0 To dt.Columns.Count
                            If dt.Columns(index).ColumnName.ToLower().Contains("id") Then
                                dt.Columns(index).ColumnMapping = MappingType.Hidden
                            End If
                        Next
                    Catch ex As Exception
                        LastException = ex
                    End Try
                End Using
            End Using
    
            Return dt
    
        End Function
    End Class

    Language extension for splitting column headers in a DataGridView.

    Imports System.Text.RegularExpressions
    ''' <summary>
    ''' Language extension methods for type String.
    ''' </summary>
    Public Module StringExtensions
        ''' <summary>
        ''' Given a string with upper and lower cased letters 
        ''' separate them before each upper cased characters
        ''' </summary>
        ''' <param name="sender">String with Camel case to split.</param>
        ''' <returns>String with spaces between upper-case letters</returns>
        <Runtime.CompilerServices.Extension>
        Public Function SplitCamelCase(sender As String) As String
            Return Regex.Replace(Regex.
                Replace(sender, "(\P{Ll})(\P{Ll}\p{Ll})", "$1 $2"),
                                 "(\p{Ll})(\P{Ll})", "$1 $2")
        End Function
    End Module

    Results

    So the only thing I have not shown is getting the Object column as I have never seen the need to do a object field in a DataTable but instead tend to use classes to store other data.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, September 12, 2019 11:13 AM
  • Hi,
    try this demo:

    Public Class Form1
    
      Private ds As New DataSet
      Private dgv1 As New DataGridView With {.Dock = DockStyle.Left}
      Private dgv2 As New DataGridView With {.Dock = DockStyle.Fill}
      Private WithEvents btn As New Button With {.Dock = DockStyle.Top, .Text = "Populate"}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        Me.Controls.AddRange(New Control() {dgv2, dgv1, btn})
        LoadData()
        dgv1.DataSource = ds.Tables("StandardRates")
        dgv2.DataSource = ds.Tables("Offers")
      End Sub
    
      Private Sub LoadData()
        With ds
          With .Tables.Add("StandardRates")
            With .Columns
              .Add("Object", GetType(String))
              .Add("Size", GetType(Decimal))
              .Add("StandardHours", GetType(Decimal))
              .Add("StandardRate", GetType(Decimal))
            End With
            With .Rows
              .Add("Object 1", 20, 2, 30)
              .Add("Object 2", 20, 2, 30)
              .Add("Object 1", 30, 4, 50)
              .Add("Object 2", 30, 4, 50)
            End With
          End With
          With .Tables.Add("Offers")
            With .Columns
              .Add("Object", GetType(String))
              .Add("Size", GetType(Decimal))
              .Add("StandardHours", GetType(Decimal))
              .Add("StandardRate", GetType(Decimal))
              .Add("TotalHr", GetType(Decimal), "Size * StandardHours")
              .Add("TotalRate", GetType(Decimal), "Size * StandardRate")
            End With
            With .Rows
              .Add("Object 1", 20)
              .Add("Object 2", 20)
              .Add("Object 3", 20)
              .Add("Object 4", 20)
              .Add("Object 1", 30)
              .Add("Object 2", 30)
              .Add("Object 3", 30)
              .Add("Object 4", 30)
            End With
          End With
        End With
      End Sub
    
      Private Sub btn_Click(sender As Object, e As EventArgs) Handles btn.Click
        PopulateData()
      End Sub
    
      Private Sub PopulateData()
        For Each offerRow As DataRow In ds.Tables("Offers").Rows
          Dim offerObject = offerRow.Field(Of String)("Object")
          Dim offerSize = offerRow.Field(Of Decimal)("Size")
          Dim standard = (From rate In ds.Tables("StandardRates")
                          Where rate.Field(Of String)("Object") = offerObject _
                         AndAlso rate.Field(Of Decimal)("Size") = offerSize).FirstOrDefault
          If standard IsNot Nothing Then
            offerRow("StandardHours") = standard.Field(Of Decimal)("StandardHours")
            offerRow("StandardRate") = standard.Field(Of Decimal)("StandardRate")
          Else
            offerRow("StandardHours") = DBNull.Value
            offerRow("StandardRate") = DBNull.Value
          End If
        Next
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by Shan1986 Saturday, September 14, 2019 4:21 PM
    Thursday, September 12, 2019 11:35 AM
  • Thanks Peter and Karen for the answers.
    Saturday, September 14, 2019 4:21 PM