Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

VB.net 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

Visual Basic.NET

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

that is a "paramarray", which accepts any number of arguments:
example here:
Guy Hengel [angelIII / a3]

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

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Guy Hengel [angelIII / a3]

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

Murray Brown

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
Guy Hengel [angelIII / a3]

the next line from above would be:
grdCompanyYear.DataSource = pvt.PivotData("Company", "CTC", AggregateFunction.Count, myValues)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Murray Brown

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
Murray Brown

at was followed by the call
  Me.DataGridView1.DataSource = pvt.PivotData(RowFields, DataField, oAggregate, Multiple_ColumnFields)
Guy Hengel [angelIII / a3]

you are trying to build 1 variable with a string being concatenated
but that is NOT the way the "paramarray" is working
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

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.
Murray Brown

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
Murray Brown

For example.you have a variable that can accept any number of words and pasd that into a single parameter array variable
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Murray Brown

Thanks very much for the help