Put multiple row values in object in VB.NET

Question

I have a stored procedure and often that procedure will return multiple rows, for example:

ID     Type    Price
1234    A      2.260
1234    B      2.690
1234    C      2.990
1234    D      2.690
1234    D      2.790
1234    D      2.650
1234    D      2.680

And I want to output the latest value for each type. In my data reader I have:

While dr.Read
    result.price.TypeA= dr("price")
    result.price.TypeB= dr("price")
    result.price.TypeC= dr("price")
    result.price.TypeD= dr("price")
End While

and my query looks like:

select  sm_id,
        type,
        price
from    ** WITH (NOLOCK)
where   id = @id
order by id desc

I'm not sure how to store all of my results into my object so I can access them in my front end.

LINQ. LINQ is your answer. See here stackoverflow.com/questions/157786/… or stackoverflow.com/questions/14502428/… . I tried to type out an answer but there were just too many assumptions and unknowns to be coherent. One of the problems with your query is that all of the IDs appear to be the same. If that is not a typo, you will need to have a unique identifier; I suggest ROW_NUMBER() — rlb.usa
– rlb.usa, Commented Aug 26, 2015 at 16:40
Also, your query will be to select the max ID grouped by type — rlb.usa
– rlb.usa, Commented Aug 26, 2015 at 16:40
@T.S. After posting this I realized I needed to query for another column. I now have update date as a part of the query. — ryandonohue
– ryandonohue, Commented Aug 26, 2015 at 17:03

T.S. · Accepted Answer · 2015-08-26 20:53:18Z

0

This is may be not most performant query but it will do the job as what you described it

select t1.id, t1.t, t1.maxDt, t2.price
from 
    (select id, [TYPE] t, MAX(ud) maxDt 
     from dbo.a it
     where id =1 
     group by id, [type]) t1 
         left join
     dbo.a t2 on t1.id = t2.id and t1.t = t2.[type] and t1.maxDt = t2.ud

Now, storing results in object and displaying it

Public Class MyClass
    Public Property Id As Integer
    Public Property [Type] As String
    Public Property [Date] As DateTime
    Public Property Price As Decimal
End Class

. . . . . 

Dim myList As New List(Of MyClass)()
While dr.Read()
    Dim item As New MyClass()
    item.Id = dr("id")
    item.[Type] = dr("t")
    item.[Date] = dr("maxDt")
    item.Price = dr("price")
    myList.Add(item)
End While

myDataGrid.DataSource = myList

This is what you, approximately, need

edited Aug 26, 2015 at 20:53

answered Aug 26, 2015 at 17:50

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rlb.usa Over a year ago

It's the obvious answer, yes, but it sounds like changing his query isn't what he wants to do. It sounds like he wants his cake and eat it too, which can be accomplished, but is more difficult.

T.S. Over a year ago

@rlb.usa Lest see what he says

ryandonohue Over a year ago

@T.S. I'm totally fine with changing the query. Is there anyway I can just pass back an entire object? Is that what grouping accomplishes?

T.S. Over a year ago

@ryandonohue Please see my updated answer. This should be good start for you. Grouping accomplishes selecting max date / per type

Collectives™ on Stack Overflow

Put multiple row values in object in VB.NET

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related