Do until EOF loop with records in VB 2010

Question

I have a program below that doesn't seem to be doing what I want it to do. In general, the pseudocode is: enter the number of miles (miles.text), click button, check: is the mileage entered equal to or less than the mileage radius (milestotextbox) in the database? If so, grab the truckload rate that corresponds to that radius (truckloadratetext) and display it in a textbox called "rate" (rate.text) and if not, continue looking until EOF. I've shown the code below. It lets me enter the mileage but won't check and display the result.

The data in the table looks like this:

ID  MILESTO TRUCKLOADRATE
1    50        200
2    100       300
3    200       700
4    300       800

So if someone enters a mileage like 10, I want it to take the truckload rate of $200. If someone enters 250, the rate would then be 800. I'm not too hung up right now about what happens if a mileage is out of range. Just trying to figure out why the mechanics of something like this isn't working. It's my first time using records with a LOOP command so I'm trying to keep it straightforward with my program.

What could I be doing wrong? Thank you in advance and hope all has a great New Years!

Public Class Form1

    Private Property EOF As Boolean

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the '_test_2DataSet.test' table. You can move, or remove it, as needed.
        Me.TestTableAdapter.Fill(Me._test_2DataSet.test)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Do Until EOF()
            If Val(MilestoTextBox.Text) <= Val(Miles.Text) Then
                rate.Text = TruckloadTextBox.Text
            End If
        Loop
    End Sub
End Class

Solution 1

Sorry for the delay in answering the question here and I want to thank everyone who answered, especially Tim.

In summary, Where a user wants to search through a dataset and compare a user-inputted value against some kind of index (in my case, if a mileage entered is less than or equal to some boundary value) and get the corresponding record that satisfies that criteria, the solution that works (thank you Tim!) is:

Dim mileAge = Int32.Parse(Miles.Text) 
Dim rate = 0
For Each row In _test_2DataSet.test
    If mileAge <= row.MILESTO Then
        rate = row.TRUCKLOADRATE
        Exit For
    End If
Next
If rate <> 0 Then
    TxtRate.Text = rate.ToString
End If

In the example, mileage is converted to a value that can be used to compare against a column in the dataset called MILESTO, which is a mile radius that corresponds to a price for transporting goods called TRUCKLOADRATE. The program iterates through each row in the dataset until the condition that mileage <= row.MILESTO is satisfied.

My original thought was using a search until EOF and this method works better.

Thanks all and again my apologies for the delay in summing this up for other users.

Solution 2

I neither know where you set the EOF variable nor do i understand its purpose. Have a look at following example which shows how to loop all rows of a DataTable(ORDER BY MILESTO ASC) to find the closest value greater than the given value:

Dim mileAge = Int32.Parse(Miles.Text) 
Dim rate = 0
For Each row In _test_2DataSet.test
    If mileAge <= row.MILESTO Then
        rate = row.TRUCKLOADRATE
        Exit For
    End If
Next
If rate <> 0 Then
    TxtRate.Text = rate.ToString
End If

If you cannot order by MILESTO initially or you simply want to see another approach that is not a database query, try this LINQ-To-DataSet approach:

rate = (From r In _test_2DataSet.test Order By r.MILESTO
        Where mileAge <= r.MILESTO
        Select r.TRUCKLOADRATE).FirstOrDefault

If you want to query the database, follwoing SQL returns the nearest TRUCKLOADRATE that is greater/equal than the @MileAge-parameter:

SELECT     TOP (1) TRUCKLOADRATE
FROM         Test
WHERE     (MILESTO >= @MileAge)
ORDER BY MILESTO - @MileAge

Add a query to your DataAdapapter that returns a single value and has a meaningful name like getTruckloadRateByMileAge. Then it's that simple:

Dim loadRate = DirectCast(daTest.getTruckloadRateByMileAge(mileAge), Decimal)