Dataview and databinding help required

[MichaelJamesJassi]

Hi Experts,

I need help with a databinding in a text box, which draws a column from a dataview.

the sql statement used for the dataview is:

SELECT ArtistID, SUM(Popularity) from ArtistPopularity WHERE NOW >= PeriodStart AND NOW <= PeriodEnd GROUP BY ArtistID

The dataview is row filtered by artist id. The artist id can be selected through a combobox.

The crux of the issue is that not all artists will have a popularity, so this means there will be null values for some artists. So when you scroll through the artist id's in the combobox, only ones with popularities should come up in the dataview.

First i got an indexoutofrange exception, stating that there is no row at positiong 2, this is down to the fact that position 2 in the dataview does not have a value so i caught the exception like so:

Try

TextBox13.Text = oArtistPopularity3DT.Rows(TextBox1.Text).Item("Popularity")

Catch ex As Exception
End Try

But now the problem i face, is that the textbox does not update to the values inside of the datview now. It just remains the same as the last value, before it catches the index out of range.

How do i make it show the value in the textbox, when there is a value in the dataview.


Any help is much appreciated

thanks.

 

[MattP]

I'd fix your SQL rather than trying the approach you're looking at. It sounds like you only want to display artists where the sum of the popularity is > 0.

SELECT ArtistID, SUM(Popularity)
FROM ArtistPopularity
WHERE NOW BETWEEN PeriodStart AND PeriodEnd
HAVING SUM(Popularity) > 0
GROUP BY ArtistID

 

[cjard]

I support MattP's notion that this would be solved via SQL, though the HAVING clause in Oracle at least, comes after the GROUP BY

However your line here:

The crux of the issue is that not all artists will have a popularity, so this means there will be null values for some artists. So when you scroll through the artist id's in the combobox, only ones with popularities should come up in the dataview.

Er.. What? So you want your combo to show all artists, but if the user selects an artist with a null popularity, you want the DGV to show nothing? Isnt that a little confusing? ALso MattP SQl will exclude all those artists so they wont show in the Combo

Please rethink what youre asking us and be more clear. Also, your databinding leaves a little to be desired.. Setting the .Text of a textbox to a string you find in a datatable, is NOT databinding