0
Answer

use datareader to select three rows at a time

tariq ondego

tariq ondego
14y
1.5k
1

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:

VB.NET Syntax 
  1. ElseIf TextSend.Text.Contains("next") Or TextSend.Text.Contains("NEXT") Or TextSend.Text.Contains("Next") Then
  2. If TextFrom.Text = "" Or TextTo.Text = "" Then
  3. MsgBox("Please make sure you have entered in all the required fields", MsgBoxStyle.Exclamation)
  4. Else
  5. str4 = str6
  6. strArray4 = str4.Split("#")
  7. town = strArray4(2)
  8. str5 = strArray4(1)
  9. strArray5 = str5.Split("-")
  10. age1 = Convert.ToInt32(strArray5(0))
  11. age2 = Convert.ToInt32(strArray5(1))
  12.  
  13. 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)
  14. myCmd2 = New SqlCommand("select count Tel,Name,Age,Description from members where Sex='f' and Town ='" & town & "' and Age >= '" & age1 & "' and Age <= '" & age2 & "'", myCon)
  15. totalrows = CInt(myCmd1.ExecuteScalar())
  16. less = totalrows - 3
  17. counter = counter + 3
  18.  
  19. dr = myCmd1.ExecuteReader
  20.  
  21. Do
  22. While dr.Read
  23. dis1 = (dr(1).ToString())
  24. dis2 = (dr(2).ToString())
  25. dis3 = (dr(3).ToString())
  26. dis4 = (dr(5).ToString())
  27. disall += dis2 & " aged " & dis3 & " " & dis1 & ","
  28. End While
  29.  
  30. While dr.Read
  31. dis1 = (dr(1).ToString())
  32. dis2 = (dr(2).ToString())
  33. dis3 = (dr(3).ToString())
  34. dis4 = (dr(5).ToString())
  35. disall += dis2 & " aged " & dis3 & " " & dis1 & ","
  36. End While
  37.  
  38. While dr.Read
  39. dis1 = (dr(1).ToString())
  40. dis2 = (dr(2).ToString())
  41. dis3 = (dr(3).ToString())
  42. dis4 = (dr(5).ToString())
  43. disall += dis2 & " aged " & dis3 & " " & dis1 & ","
  44. End While
  45.  
  46. ' TextSend.Text = disall & ". Send next to 5001 to receive detailsof the remaining " & less & " ladies"
  47. Loop While dr.NextResult()
  48. TextSend.Text = disall & ". Send NEXT to 5001 to receive details of the remaining " & less & " ladies"
  49. count = count - 3
  50. End If
any assistance will be highly appreciated.
Next Recommended Forum