Modifying Row Values based on queries.

[jimctr]

I have a series of columns whose values will need to be updated based upon queries. I need to update starting from the first row and going through the last. The only way I could think to do this was to add a unique identifier to each row (basically a row number) and based on queries referencing this number, update the cell appropriately. The approach seems to work, but I don't know if it is the best solution. I will need to modify a number of other columns and will have to do it in a similar manner.

Here's the relevant code:

count = 0
For Each dtRow In dt.Rows 'I believe this is the main structure for updating all rows in table based on queries
Dim ThisYear = (From Row As DataRow In dt.Rows Where Row.Item("RowCnt") = count Select Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year).ToArray
Dim TotalYearsSum As UInt16 = (From Row As DataRow In dt.Rows Where Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year = ThisYear(0) _
Select Row.Field(Of UInteger)("HRBlk1")).Count 'Can Refer to a random Column. Will count all rows irrespective of value
dtRow.Item("YearFreq") = TotalYearsSum
count += 1
Next

 

[Dunfiddlin]

Huh? Reinitialise the DB Adapter with the new query. Fill the table from the Adapter. Job done!

 

[jimctr]

Not certain how to do that. Sorry. Newbie

 

[Dunfiddlin]

So how did you get a datatable in the first place?

 

[jimctr]

Dim dt As New DataTable With {.TableName = "ExifView"}

Are you suggesting to create a brand new datatable, rather than modify the old, and port static cells over while updating the new?

 

[Dunfiddlin]

I'm beginning to suspect that we're talking at cross purposes. Can you break the whole process down into something a 3 year-old could understand.? Beginning at the beginning ...

 

[Herman]

Let's break this down first...

    Dim ThisYear = (From Row As DataRow In dt.Rows
                    Where Row.Item("RowCnt") = count
                    Select Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year).ToArray

This query is weird. In this form, you want to get the year part of the Date field but only for records where the field RowCnt is equal to an arbitrary loop counter? Somehow it doesn't seem completely right...

    Dim TotalYearsSum As UInt16 = (From Row As DataRow In dt.Rows
                                   Where Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year = ThisYear(0)
                                   Select Row.Field(Of UInteger)("HRBlk1")).Count

In this second part, you want to get the count of every field HRBlk1 for a particular year.

First, the two parts are not necessary. If all you want is the sum of the field HRBlk1 for all records that match the current year, try this:

    Dim QueryGetSumHRBlk1 = (From Row As DataRow in dt.Rows
                             Where Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year = DateTime.Now.Year
                             Select Row.Field(Of UInteger)("HRBlk1")).Sum

Also, remember you can use variables in Linq queries as you like, so once you establish a query, just sit it inside a function that returns an IEnumerable or a datatable, and takes for example a year parameter. Just use your variable in place of DateTime.Now.Year. That goes for other queries too. For example, if your first query was:

Dim ThisYear = From Row As DataRow In dt.Rows Where Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year = DateTime.Now.Year Select Row

You could requery that subset:

Dim HRBlk1SumThisYear = (From Row In ThisYear Select Row.Field(Of UInteger)("HRBlk1")).Sum

The query itself is only executed when the variable is used, so the above two lines were the same as:

Dim FullQuery = (From Row In (From Row As DataRow In dt.Rows Where Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year = DateTime.Now.Year Select Row) Select Row.Field(Of UInteger)("HRBlk1")).Sum

 

[jimctr]

Thanks Herman:

There's a reason you have so many circles under your name on this forum.

The counter is not really arbitrary, because each record or row is provided a unique integer identifier in the first column as part of the data table and the counter is controlled by for each row .. next so should mirror the correct number of entries and each entry should be called out only once. That said, it is a hack. I guess what I was looking for was some sort of indexing scheme and since I didn't find it, I created my own. (e.g., what is the year referred to by the nth row?)

The problem with the DateTime.Now.Year is it points to this year (2012) but the records may go farther back. What I wanted to do was to say what is the relevant year for the particular row I am indexing, and then get the frequency of activity for that particular year using .count method. I did something similar for month which seemed to work. Where it is failing right now is recording the correct freq for Hour Block but need to look at it further(this is where I use the .Sum method). I used .sum(Function(ThisRow) ThisRow) when I had created the class structure and it seemed to work, so I must be doing something stupid. Always when I use .sum() by itself, by the way, this generates an error, claiming an incorrect number of arguments.

Thanks

 

[Herman]

Sorry I had to cut the previous post a bit short, I was at work and out of time.

The counter is not really arbitrary, because each record or row is provided a unique integer identifier in the first column as part of the data table and the counter is controlled by for each row .. next so should mirror the correct number of entries and each entry should be called out only once.

In a relational database model, the absolute position of a record in a table is irrelevant, because the records are chosen by the relation of certain fields to others. You are right that each row needs a unique identifier however, and in a DBMS context this is called the primary key. When you have multiple tables, you use the primary key and a foreign key (in another table) to match the records to one another. The absolute value of the primary key however is irrelevant, it is only relevant that a field equals another field. In your case, since you have only one table, the primary key is provided by the For Each...Next loop, but the Each keyword makes it invisible. By definition each field is already processed only once. In your case the primary key is pretty much irrelevant since you have no link to other tables to make. However if for example you kept a separate table to contain moon cycles (name, picture, dates, etc...), you would use that table's primary key and link it to a foreign key in your Events table:

From e In Events Join mc In MoonCycles On mc.PrimaryKey Equals e.MoonCycleKey Select e.*, mc.MoonCycleName

The problem with the DateTime.Now.Year is it points to this year (2012) but the records may go farther back.

In that case, like I suggested above, just enclose the query in a function, and pass it the year you want when you call it.

Private dt As DataTable

Private Function GetEventsForYear(ByVal intYear As Integer) As IEnumerable(Of DataRow)
    GetEventsForYear = From Row As DataRow In dt.Rows Where Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year = intYear Select Row
End Function

What I wanted to do was to say what is the relevant year for the particular row I am indexing, and then get the frequency of activity for that particular year using .count method. I did something similar for month which seemed to work.

In that case, let's just modify the previous function a little bit:

Private dt As DataTable

Private Function GetEventsCountForYear(ByVal intYear As Integer) As Integer
    GetEventsCountForYear = (From Row As DataRow In dt.Rows Where  Date.ParseExact(Row.Item("Date"), "yyyy:MM:dd", Nothing).Year = intYear  Select Row).Count()
End Function

Or even better, to reduce code duplication:

Dim EventsCount As Integer = GetEventsForYear(2012).Count()

Where it is failing right now is recording the correct freq for Hour Block but need to look at it further(this is where I use the .Sum method). I used .sum(Function(ThisRow) ThisRow) when I had created the class structure and it seemed to work, so I must be doing something stupid. Always when I use .sum() by itself, by the way, this generates an error, claiming an incorrect number of arguments.

I'm not sure it's event the Sum you need here, but yeah that was my fault, napkin code and all. There might be errors or typos here and there...

Enumerable.Sum(TSource) Method (IEnumerable(TSource), Func(TSource, Double)) (System.Linq)

Hope this helps!