Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on Calling a function with different variables


I am using a code example where the following procedure is called in two different ways where the variable
ColumnFields is first given "Year" and then "Company", "Year".
How does this work? I need to use a variable in the function call that can contain
"Year" and "Company", "Year".
How do I do this?

 grdCompanyYear.DataSource = pvt.PivotData("Company", "CTC", AggregateFunction.Count, "Year")

 grdDesignationCompanyYear.DataSource = pvt.PivotData("Designation", "CTC", AggregateFunction.Max, "Company", "Year")

    Public Function PivotData(ByVal RowField As String, ByVal DataField As String, ByVal Aggregate As AggregateFunction, ByVal ParamArray ColumnFields As String()) As DataTable
        Dim dt As New DataTable()
        Dim Separator As String = "."
        Dim RawRowList = (From x In _SourceTable.AsEnumerable() Select New With {.Name = x.Field(Of Object)(RowField).ToString()}).Distinct()
        Dim RowListParam As String() = (From s In RawRowList Select s.Name).ToArray()
        Dim RowList = GetDistinct(RowListParam)

        ' Gets the list of columns .(dot) separated.
        Dim RawColList = (From x In _SourceTable.AsEnumerable() _
                       Select New With {.Name = ColumnFields.Select(Function(n) x.Field(Of Object)(n).ToString()) _
                                                 .Aggregate(Function(a, b) (a & Separator & b.ToString()))}).Distinct() _
                                                 .OrderBy(Function(x) x.Name)

        Dim ColListParam As String() = (From s In RawColList Select s.Name).ToArray()
        Dim ColList = GetDistinct(ColListParam)

        For Each col In ColList
            ' Cretes the result columns.//
            If Not dt.Columns.Contains(col.ToString()) Then
            End If

        For Each RowName In RowList
            Dim row As DataRow = dt.NewRow()
            row(RowField) = RowName.ToString()
            For Each col In ColList
                Dim strFilter As String = (RowField & " = '") + RowName & "'"
                Dim strColValues As String() = col.ToString().Split(Separator.ToCharArray(), StringSplitOptions.None)
                For i As Integer = 0 To (ColumnFields.Length - 1)
                    strFilter = strFilter & " and " & ColumnFields(i) & " = '" & strColValues(i) & "'"
                row(col.ToString()) = GetData(strFilter, DataField, Aggregate)
        Return dt
    End Function 

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

that is a "paramarray", which accepts any number of arguments:
example here:
in short:
Dim pivotFields1() As String = {"YEAR"}
Dim pivotFields2() As String = {"Company", "YEAR"}
 grdCompanyYear.DataSource = pvt.PivotData("Company", "CTC", AggregateFunction.Count, pivotFields1)
 grdDesignationCompanyYear.DataSource = pvt.PivotData("Designation", "CTC", AggregateFunction.Max, pivotFields2)

Open in new window

Avatar of Murray Brown



For Dim pivotFields2() As String = {"Company", "YEAR"}
The list can be any number of items out of a ListBox
How would I gather them into this variable
yourlistbox.SelectedItems gives you the items selected (and hence the count), so you define the string array with that size, and put the values of those items accordingly to that array. that is basic .net Programming, see here:
Dim myValues(yourlistbox.SelectedItems.Count - 1) As String
Dim myItem2 As ListItem
Dim loopIndex as Integer
For Each myItem2 In yourlistbox.SelectedItems
    myValues[loopIndex] = yourlistbox.SelectedItems[loopIndex].Value
Next myItem2

Open in new window

The reason I used a list box was to understand how to gather multiple items into a variable that would be accepted into a parameter array variable. I still don't understand
the next line from above would be:
grdCompanyYear.DataSource = pvt.PivotData("Company", "CTC", AggregateFunction.Count, myValues)
I understand that but you are still not telling me how to build variableI tried the following to no avail

            Dim Multiple_ColumnFields As String
            If UBound(arrColumnFields) > 0 Then
                Multiple_ColumnFields = "{"
                For i As Integer = 0 To UBound(arrColumnFields)
                    If Multiple_ColumnFields = "{" Then
                        Multiple_ColumnFields = Multiple_ColumnFields & "'" & arrColumnFields(i) & "'"
                        Multiple_ColumnFields = Multiple_ColumnFields & "," & "'" & arrColumnFields(i) & "'"
                    End If
                Multiple_ColumnFields = Multiple_ColumnFields & "}"
            End If
at was followed by the call
  Me.DataGridView1.DataSource = pvt.PivotData(RowFields, DataField, oAggregate, Multiple_ColumnFields)
you are trying to build 1 variable with a string being concatenated
but that is NOT the way the "paramarray" is working
Avatar of HooKooDooKu

Here is a super simple example of what Guy Hengel is trying to say:
    Private Sub ParamArrayMsgBox(ByVal ParamArray txt())
        Dim myTxt As String
        Dim I As Integer

        myTxt = ""
        I = LBound(txt)
        While I <= UBound(txt)
            myTxt = myTxt & txt(I)
            I = I + 1
        End While

    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Const A As String = "Once You Reach the ParamArray Parameter, "
        Const B As String = "all parameters pasted that point "
        Const C As String = "are collected into an array "
        Const D As String = "to pass as the ParamArray parameter"

        ParamArrayMsgBox(A, B, C, D)
    End Sub

Open in new window

As you can see, I've create a function that takes one ParamArray.
I've also created a bunch of string variables.  
I then call ParamArrayMsgBox that only has one ParamArray parameter by passing 4 parameters.
ParamArray must always be the last parameter because once you hit the ParamArray parameter, all parameters from that point are collected and placed into the ParamArray

Now my example only uses strings, but I believe a ParamArray is an array of variants, so you could have a mixture of text, numbers, and objects that go into the ParamArray.
Thanks but would it be possible to show me how to build a variable with any number of strings that could be passed into a function as a parameter array. Please! This surely isnt that hard if you understand the process
For have a variable that can accept any number of words and pasd that into a single parameter array variable
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks very much for the help