Returning Value from Filled Dataset

[falconsorceror]

Hi,

I cannot seem to figure out how to search through a created/filled dataset for a certain value which exists in column(1), returning Column(0)'s value into a textbox.

My code looks like this:

Dim connStringdg44 As String = "server=(local);database=Cricket;trusted_connection=yes"
Dim conndg44 As New SqlConnection(connStringdg44)

Dim strSQLdg44 As String = "select ROW_NUMBER() OVER (ORDER BY (cast (sum(GamePoints.WinIndicator *100.00)/count(gamePoints.GameID*100.00) as varchar (50)))+'%' DESC) as 'Rank', Team,COUNT(gamePoints.GameID) as 'Played', SUM(GamePoints.WinIndicator) as 'Won',cast (sum(GamePoints.WinIndicator *100.00)/count(gamePoints.GameID*100.00) as varchar (50))+'%' as 'Efficiency' FROM gamePoints INNER JOIN DomesticTeam ON GamePoints.Team = DomesticTeam.DomTeam GROUP BY GamePoints.Team order by 'Efficiency' desc"
Dim dadg44 As New SqlDataAdapter(strSQLdg44, conndg44)
Dim dsdg44 As New DataSet
dadg44.Fill(dsdg44, "DomesticRanking")
conndg44.Close()

The SQL query looks into a current SQL DB, populating the new ds with the following values:

1 Northerns 11 9 81.818181%
2 Natal 10 8 80.000000%
3 Eastern Province 8 5 62.500000%
4 Western Province 8 5 62.500000%
5 Mpumalanga 10 6 60.000000%
6 Boland 7 4 57.142857%
7 ETVL 6 3 50.000000%
8 Gauteng 6 3 50.000000%
9 Griquas 12 6 50.000000%
10 SETVL 8 4 50.000000%
11 Free State 7 3 42.857142%
12 North West 7 3 42.857142%
13 Limpopo 12 5 41.666666%
14 WTVL 8 3 37.500000%
15 Northern Natal 12 3 25.000000%
16 SWD 10 1 10.000000%

All I want to do is to search through the second column for the existence of a value, or "Team" which I have in a combobox, and return the corresponding Rank which is in column 1. My combobox is already populated with the teams from column 2.

[hericles]

Use a loop to run through checking the dataset's 2nd col for the name, when a match is found get that rows first column.

Dim teamStr as String
teamStr = comboBox value
for(int x = 0; x < dsdg44.Rows.Count; x++) {
   if(dsdg44.Rows[x][1].ToString == teamStr) {
       // rank is now equal to dsdg44.Rows[x][0].ToString();
       x = dsdg44.Rows.Count; // finish the loop rather than continue 
   }
}

Thats c# code - I did it again, wrong code for the forum. Anyway, it's easy to switch back to VB. But that will run through the rows in the dataset checking the second column for the specified name and, once found, get the rank from the first column.
Hope that helps,

[falconsorceror]

Thanks hericles, appreciate your quick response. I am very very new to coding and am muddling my way through Visual Studio 2008, using VB. Could you post your reply in VB?

[hericles]

For x As Integer = 1 To dsdg44.Rows.Count - 1
if dsdg44.Rows(x,1).ToString() == teamStr Then
// rank is now equal to dsdg44.Rows(x,0).ToString();
x = dsdg44.Rows.Count; // finish the loop rather than continue
}
Next

[falconsorceror]

Got it sorted, following code did the trick!:

For Each _row As DataRow In dsdg44.Tables("DomesticRanking").Rows
If _row(1) = CboDomTeam2.Text Then
lblRank.Text = _row.Item(0)
End If
Next