Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB.net Error calling function

Posted on 2014-01-29
2
Medium Priority
?
610 Views
Last Modified: 2014-01-29
Hi

I get the following error when running the following code. The problem may be that the three functions in my class Pivot.vb are called PivotData
The error is at the line marked 'ERROR

Error      32      Overload resolution failed because no accessible 'PivotData' can be called without a narrowing conversion:
    'Public Function PivotData(rowField As String, dataField As String, aggregate As AggregateFunction, showSubTotal As Boolean, ParamArray columnFields() As String) As System.Data.DataTable': Argument matching parameter 'dataField' narrows from 'Elements.AggregateFunction' to 'String'.
    'Public Function PivotData(rowField As String, dataField As String, aggregate As AggregateFunction, showSubTotal As Boolean, ParamArray columnFields() As String) As System.Data.DataTable': Argument matching parameter 'aggregate' narrows from 'String' to 'Elements.AggregateFunction'.
    'Public Function PivotData(rowField As String, dataField As String, aggregate As AggregateFunction, showSubTotal As Boolean, ParamArray columnFields() As String) As System.Data.DataTable': Argument matching parameter 'showSubTotal' narrows from 'String' to 'Boolean'.
    'Public Function PivotData(rowField As String, dataField As String, aggregate As AggregateFunction, ParamArray columnFields() As String) As System.Data.DataTable': Argument matching parameter 'dataField' narrows from 'Elements.AggregateFunction' to 'String'.
    'Public Function PivotData(rowField As String, dataField As String, aggregate As AggregateFunction, ParamArray columnFields() As String) As System.Data.DataTable': Argument matching parameter 'aggregate' narrows from 'String' to 'Elements.AggregateFunction'.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\Elements\Elements\TaskPane_PivotData.vb      262      43      Elements


    Private Sub BindGridView()
        Try

            Dim oConnectionString As String = Globals.ThisAddIn.oRIGHT.lblConnectionString.Text
            ' Retrieve the data table from Excel Data Source.
            Dim dt As DataTable = Pivot_DataTable.GetDataTable("Select * From Table_Pivot", oConnectionString)
            'Me.DataGridView1.DataSource = dt

            Dim pvt As New Pivot(dt)
   
            Dim DataField As String = ListView_items_string(Me.ListView4)
            Dim RowFields As String = ListView_items_string(Me.ListView3)
            Dim ColumnFields As String = ListView_items_string(Me.ListView2)
            Dim oAggregate As AggregateFunction
            If Me.ComboBox1.Text = "Count" Then
                oAggregate = AggregateFunction.Count
            ElseIf Me.ComboBox1.Text = "Sum" Then
                oAggregate = AggregateFunction.Sum
            ElseIf Me.ComboBox1.Text = "First" Then
                oAggregate = AggregateFunction.First
            ElseIf Me.ComboBox1.Text = "Last" Then
                oAggregate = AggregateFunction.Last
            ElseIf Me.ComboBox1.Text = "Average" Then
                oAggregate = AggregateFunction.Average
            ElseIf Me.ComboBox1.Text = "Max" Then
                oAggregate = AggregateFunction.Max
            ElseIf Me.ComboBox1.Text = "Min" Then
                oAggregate = AggregateFunction.Min
            ElseIf Me.ComboBox1.Text = "Exists" Then
                oAggregate = AggregateFunction.Exists
            End If
       
            Me.DataGridView1.DataSource = pvt.PivotData(DataField, oAggregate, RowFields, ColumnFields) 'ERROR


        Catch ex As Exception
            MsgBox("Problem pivoting data" & ex.Message & " hhh888")
        End Try
    End Sub

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Data

Public Enum AggregateFunction
    Count = 1
    Sum = 2
    First = 3
    Last = 4
    Average = 5
    Max = 6
    Min = 7
    Exists = 8
End Enum

Public Class Pivot

    Private _SourceTable As New DataTable()
    Private _Source As IEnumerable(Of DataRow) = New List(Of DataRow)()

    Public Sub New(SourceTable As DataTable)
        _SourceTable = SourceTable
        _Source = SourceTable.Rows.Cast(Of DataRow)()
    End Sub

    ''' <summary>
    ''' Pivots the DataTable based on provided RowField, DataField, Aggregate Function and ColumnFields.//
    ''' </summary>
    ''' <param name="rowField">The column name of the Source Table which you want to spread into rows</param>
    ''' <param name="dataField">The column name of the Source Table which you want to spread into Data Part</param>
    ''' <param name="aggregate">The Aggregate function which you want to apply in case matching data found more than once</param>
    ''' <param name="columnFields">The List of column names which you want to spread as columns</param>
    ''' <returns>A DataTable containing the Pivoted Data</returns>
    Public Function PivotData(rowField As String, dataField As String, aggregate As AggregateFunction, ParamArray columnFields As String()) As DataTable
        Dim dt As New DataTable()
        Dim Separator As String = "."
        Dim rowList As List(Of String) = _Source.[Select](Function(x) x(rowField).ToString()).Distinct().ToList()
        ' Gets the list of columns .(dot) separated.
        Dim colList = _Source.[Select](Function(x) (columnFields.[Select](Function(n) x(n)).Aggregate(Function(a, b) a & Separator & b.ToString())).ToString()).Distinct().OrderBy(Function(m) m)

        dt.Columns.Add(rowField)
        For Each colName As Object In colList
            dt.Columns.Add(colName)
        Next
        ' Cretes the result columns.//
        For Each rowName As String In rowList
            Dim row As DataRow = dt.NewRow()
            row(rowField) = rowName
            For Each colName As String In colList
                Dim strFilter As String = (Convert.ToString(rowField & Convert.ToString(" = '")) & rowName) + "'"
                Dim strColValues As String() = colName.Split(Separator.ToCharArray(), StringSplitOptions.None)
                For i As Integer = 0 To columnFields.Length - 1
                    strFilter += " and " + columnFields(i) + " = '" + strColValues(i) + "'"
                Next
                row(colName) = GetData(strFilter, dataField, aggregate)
            Next
            dt.Rows.Add(row)
        Next
        Return dt
    End Function

    Public Function PivotData(rowField As String, dataField As String, aggregate As AggregateFunction, showSubTotal As Boolean, ParamArray columnFields As String()) As DataTable
        Dim dt As New DataTable()
        Dim Separator As String = "."
        Dim rowList As List(Of String) = _Source.[Select](Function(x) x(rowField).ToString()).Distinct().ToList()
        ' Gets the list of columns .(dot) separated.
        Dim colList As List(Of String) = _Source.[Select](Function(x) columnFields.Aggregate(Function(a, b) (x(a).ToString() & Separator) + x(b).ToString())).Distinct().OrderBy(Function(m) m).ToList()

        If showSubTotal AndAlso columnFields.Length > 1 Then
            Dim totalField As String = String.Empty
            For i As Integer = 0 To columnFields.Length - 2
                totalField += Convert.ToString(columnFields(i) + "(Total)") & Separator
            Next
            Dim totalList As List(Of String) = _Source.[Select](Function(x) totalField & x(columnFields.Last()).ToString()).Distinct().OrderBy(Function(m) m).ToList()
            colList.InsertRange(0, totalList)
        End If

        dt.Columns.Add(rowField)
        colList.ForEach(Function(x) dt.Columns.Add(x))

        For Each rowName As String In rowList
            Dim row As DataRow = dt.NewRow()
            row(rowField) = rowName
            For Each colName As String In colList
                Dim filter As String = (Convert.ToString(rowField & Convert.ToString(" = '")) & rowName) + "'"
                Dim colValues As String() = colName.Split(Separator.ToCharArray(), StringSplitOptions.None)
                For i As Integer = 0 To columnFields.Length - 1
                    If Not colValues(i).Contains("(Total)") Then
                        filter += " and " + columnFields(i) + " = '" + colValues(i) + "'"
                    End If
                Next
                row(colName) = GetData(filter, dataField, If(colName.Contains("(Total)"), AggregateFunction.Sum, aggregate))
            Next
            dt.Rows.Add(row)
        Next
        Return dt
    End Function

    Public Function PivotData(DataField As String, Aggregate As AggregateFunction, RowFields As String(), ColumnFields As String()) As DataTable
        Dim dt As New DataTable()
        Dim Separator As String = "."
        Dim RowList = _SourceTable.DefaultView.ToTable(True, RowFields).AsEnumerable().ToList()
        For index As Integer = RowFields.Count() - 1 To 0 Step -1
            RowList = RowList.OrderBy(Function(x) x.Field(Of Object)(RowFields(index))).ToList()
        Next
        ' Gets the list of columns .(dot) separated.
        Dim ColList = (From x In _SourceTable.AsEnumerable() _
                             Select New With {.Name = ColumnFields.[Select](Function(n) x.Field(Of Object)(n)).Aggregate(Function(a, b) a & Separator & b.ToString()) _
        }).Distinct().OrderBy(Function(m) m.Name)

        'dt.Columns.Add(RowFields)
        For Each s As String In RowFields
            dt.Columns.Add(s)
        Next

        'For Each col As Object In ColList
        'dt.Columns.Add(col.Name.ToString())
        'Next

        'CODE CORRECTION
        On Error Resume Next

        For Each col In ColList
            dt.Columns.Add(col.Name.ToString())
        Next

        ' Creates the result columns.//
        For Each RowName As Object In RowList
            Dim row As DataRow = dt.NewRow()
            Dim strFilter As String = String.Empty

            For Each Field As String In RowFields
                row(Field) = RowName(Field)
                strFilter += (Convert.ToString(" and ") & Field) + " = '" + RowName(Field).ToString() + "'"
            Next
            strFilter = strFilter.Substring(5)

            For Each col As Object In ColList
                Dim filter As String = strFilter
                Dim strColValues As String() = col.Name.ToString().Split(Separator.ToCharArray(), StringSplitOptions.None)
                For i As Integer = 0 To ColumnFields.Length - 1
                    filter += " and " + ColumnFields(i) + " = '" + strColValues(i) + "'"
                Next
                row(col.Name.ToString()) = GetData(filter, DataField, Aggregate)
            Next
            dt.Rows.Add(row)
        Next
        Return dt
    End Function

    ''' <summary>
    ''' Retrives the data for matching RowField value and ColumnFields values with Aggregate function applied on them.
    ''' </summary>
    ''' <param name="Filter">DataTable Filter condition as a string</param>
    ''' <param name="DataField">The column name which needs to spread out in Data Part of the Pivoted table</param>
    ''' <param name="Aggregate">Enumeration to determine which function to apply to aggregate the data</param>
    ''' <returns></returns>
    Private Function GetData(Filter As String, DataField As String, Aggregate As AggregateFunction) As Object
        Try
            Dim FilteredRows As DataRow() = _SourceTable.[Select](Filter)
            Dim objList As Object() = FilteredRows.[Select](Function(x) x.Field(Of Object)(DataField)).ToArray()

            Select Case Aggregate
                Case AggregateFunction.Average
                    Return GetAverage(objList)
                Case AggregateFunction.Count
                    Return objList.Count()
                Case AggregateFunction.Exists
                    Return If((objList.Count() = 0), "False", "True")
                Case AggregateFunction.First
                    Return GetFirst(objList)
                Case AggregateFunction.Last
                    Return GetLast(objList)
                Case AggregateFunction.Max
                    Return GetMax(objList)
                Case AggregateFunction.Min
                    Return GetMin(objList)
                Case AggregateFunction.Sum
                    Return GetSum(objList)
                Case Else
                    Return Nothing
            End Select
        Catch ex As Exception
            Return "#Error"
        End Try
    End Function

    Private Function GetAverage(objList As Object()) As Object
        Return If(objList.Count() = 0, Nothing, DirectCast(Convert.ToDecimal(GetSum(objList)) / objList.Count(), Object))
    End Function
    Private Function GetSum(objList As Object()) As Object
        Return If(objList.Count() = 0, Nothing, DirectCast(objList.Aggregate(New Decimal(), Function(x, y) x & Convert.ToDecimal(y)), Object))
    End Function
    Private Function GetFirst(objList As Object()) As Object
        Return If((objList.Count() = 0), Nothing, objList.First())
    End Function
    Private Function GetLast(objList As Object()) As Object
        Return If((objList.Count() = 0), Nothing, objList.Last())
    End Function
    Private Function GetMax(objList As Object()) As Object
        Return If((objList.Count() = 0), Nothing, objList.Max())
    End Function
    Private Function GetMin(objList As Object()) As Object
        Return If((objList.Count() = 0), Nothing, objList.Min())
    End Function
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 2000 total points
ID: 39817792
The problem may be that the three functions in my class Pivot.vb are called PivotData
That is called method overloading and just means you can call that method with a number of different parameter sets. The one you have chosen seems to be the 3rd one and that one needs string arrays as 3rd and 4th argument, for example:
Me.DataGridView1.DataSource = pvt.PivotData(DataField, oAggregate, New String() {RowFields}, New String() {ColumnFields})

Open in new window

Another possibility is that you need to use Split() to create arrays from those 2 strings but without knowing what's in there it's hard to know if that could be the solution.
0
 

Author Closing Comment

by:Murray Brown
ID: 39817799
Thanks very much for the help
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question