I need to get the row number that contains a specific value in a sql server recordset via vb6. This is what I am using but it appears to be generating a value of 1 each time. Note that what I am trying to get is the place a runner finishes in a race. Here is what I am using:
x = 0 Set rsx = New ADODB.Recordset sqlx = "SELECT Row_Number() OVER (ORDER BY FnlScnds) AS IndRsltsID_DETER FROM IndRslts WHERE RacesID = " & iThisRace sqlx = sqlx & " AND Fnlscnds > 0 AND Bib = " & iMyBib rsx.Open sqlx, conn, 1, 2 x = rsx(0).Value rsx.Close Set rsx = Nothing GetPlace = x
A brute force approach which would generate the desired value would be:
x = 0 Set rsx = New ADODB.Recordset sqlx = "SELECT Bib FROM IndRslts WHERE RaceSrvrID = " & iThisRace & " AND FnlScnds > 0 ORDER BY FnlScnds" rsx.Open sqlx, conn, 1, 2 Do While Not rsx.EOF x = x + 1 If rsx(0).Value = iMyBib Then Exit Do rsx.MoveNext Loop rsx.Close Set rsx = Nothing GetPlace = x
Any help would be much appreciated!
Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>