Imported excel spreadsheet into datatable, added columns, now need to group & count rows in group then update another field with count values RRS feed

  • Question

  • Hi to the Gurus of VB

    I have imported an excel spreadsheet into a datatable which is not part of any dataset. I added some columns to match the table I will load into on my sql server then I use bulkcopy to upload. Usually only a few hundred rows. All tested & working to that point. Now I want to group on one of the fields & count how many records in the group, writing that value to another field in the datatable before the bulkcopy. I started looking at using Linq but I get compile error that the field "class" is not a member of Datarow. I'm lost & need a touch of guidance on how to proceed. Here's the code I have so far.

                    Dim reader As New DataTableReader(ImportFromExcel(gblCompName, "Yes"))
                    Dim table As New DataTable
                    table.Columns.Add("ID", GetType(Integer))
                    table.Columns.Add("SingleE", GetType(Boolean))
                    table.Columns.Add("CouplesInFinal", GetType(Integer))
                    table.Columns.Add("EventNum", GetType(String))
                    table.Columns.Add("EventStruc", GetType(Integer))
                    table.Columns.Add("EventCplID", GetType(Integer))
                    table.Columns.Add("CouplesInClass", GetType(Integer))
                    table.Columns.Add("Valid", GetType(Boolean))
                    Dim i As Integer = 0
                    For i = 0 To table.Rows.Count - 1
                        If table.Rows(i)("Class") Like "*Single*" Then
                            table.Rows(i)("SingleE") = True
                        End If
                        table.Rows(i)("Valid") = True
                    DataGridView1.DataSource = table

    I didn't include the bulkcopy code as my column mappings & writetoserver all work correctly. Unfortunately "class" is a field from the spreadsheet so the naming is not great but below is the code I'm trying to formulate

    Dim ClassCount = From section In table
                                     Order By section.class
                                     Select section.class
                                     Group By section.class
                                         Into Classes = Group

    I'm re-teaching myself from vba to by re-writing one of my Access databases & need guidance please.

    Sunday, May 20, 2018 4:47 PM


  • In order to read a value from a row, you can use Field:

       Dim ClassCount = From section In table

                        Select [class] = section.Field(Of String)("class")

                        Group By [class]

                        Into Classes = Group

                        Order By [class]


    If you want to include the size of each group, then try ‘Into Count, Classes = Group’.

    Sunday, May 20, 2018 6:22 PM