datareader to select three rows at a time

[tariqi]

Hi,

Am currently creating an interactive dating sms service using vb.net.but when i get to the point for user to find requested matching records,am only able to get the first 3 records. i want it to be able to move to the next 3 and the following 3 and so forth until end of requested records each time the user sends 'next'.am using a 'do loop while' and a datareader but its not working how i want,below is the code:

ElseIf TextSend.Text.Contains("next") Or TextSend.Text.Contains("NEXT") Or TextSend.Text.Contains("Next") Then
If TextFrom.Text = "" Or TextTo.Text = "" Then
MsgBox("Please make sure you have entered in all the required fields", MsgBoxStyle.Exclamation)
Else
str4 = str6
strArray4 = str4.Split("#")
town = strArray4(2)
str5 = strArray4(1)
strArray5 = str5.Split("-")
age1 = Convert.ToInt32(strArray5(0))
age2 = Convert.ToInt32(strArray5(1))

myCmd1 = New SqlCommand("select top 3 * from (select row_number() over(order by Tel) as RowNumber,Tel,Name,Age,Sex,Town,Description from members) as myResults where RowNumber>'" & counter & "' and Sex='f' and Town ='" & town & "' and Age >= '" & age1 & "' and Age <= '" & age2 & "'", myCon)
myCmd2 = New SqlCommand("select count Tel,Name,Age,Description from members where Sex='f' and Town ='" & town & "' and Age >= '" & age1 & "' and Age <= '" & age2 & "'", myCon)
totalrows = CInt(myCmd1.ExecuteScalar())
less = totalrows - 3
counter = counter + 3

dr = myCmd1.ExecuteReader

Do
While dr.Read
dis1 = (dr(1).ToString())
dis2 = (dr(2).ToString())
dis3 = (dr(3).ToString())
dis4 = (dr(5).ToString())
disall += dis2 & " aged " & dis3 & " " & dis1 & ","
End While

While dr.Read
dis1 = (dr(1).ToString())
dis2 = (dr(2).ToString())
dis3 = (dr(3).ToString())
dis4 = (dr(5).ToString())
disall += dis2 & " aged " & dis3 & " " & dis1 & ","
End While

While dr.Read
dis1 = (dr(1).ToString())
dis2 = (dr(2).ToString())
dis3 = (dr(3).ToString())
dis4 = (dr(5).ToString())
disall += dis2 & " aged " & dis3 & " " & dis1 & ","
End While

' TextSend.Text = disall & ". Send next to 5001 to receive detailsof the remaining " & less & " ladies"
Loop While dr.NextResult()
TextSend.Text = disall & ". Send NEXT to 5001 to receive details of the remaining " & less & " ladies"
count = count - 3
End If

any assistance will be highly appreciated.

[Alexpap]

From a quick check on your code i thought that you should change your SQL Query:select top 3 * from (select row_number(). Instead you should try selecting all the rows from your table, and then "page" them, just like the GridView Paging functionality.

Hope i helped. :)