Add extra column to dataset table in asp.net vb.net and bind to repeater

Hi,

I'm trying to figure out how to add an extra row to my dataset so that I can bind to a repeater.   I'm querying SQL for a list of records based on a Zip Code search.  A function that I have returns a value called 'Distance'.  I need this to appear next to the listings.

My Code is here:

<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        ' ----------- STEP 1: GET LONGITUDE AND LATITUDE OF THE USER'S PROVIDED ZIP CODE -----------
        Dim DBConnection As SqlConnection
        Dim DBAdapter As SqlDataAdapter
        Dim DBDataSet As DataSet
        Dim SQLString As String
        DBConnection = New SqlConnection(ConfigurationManager.AppSettings("apdtConnect2"))
        SQLString = "SELECT Latitude, Longitude FROM ZIPCodes WHERE ZIPCode = '92352'"
        DBAdapter = New SqlDataAdapter(SQLString, DBConnection)
        DBDataSet = New DataSet
        DBAdapter.Fill(DBDataSet, "ZIPCodes")
        Dim Latitude As Double = DBDataSet.Tables("ZIPCodes").Rows(0)("Latitude")
        Dim Longitude As Double = DBDataSet.Tables("ZIPCodes").Rows(0)("Longitude")
        lblLatitude.Text = Latitude
        lblLongitude.Text = Longitude
       
        ' ----------- STEP 2: GET LIST OF TRAINERS THAT MATCH ZIP CODE -----------
        ' Check to see if any records exist
        Dim ZIPExists As Boolean = Not (DBDataSet.Tables("ZIPCodes") Is Nothing)
        If ZIPExists Then
            ' Trainers exist, get info about trainers
            Dim Miles As Double = 25
            Dim zcdRadius As New ZIPCodeDownload.RadiusAssistant(Latitude, Longitude, Miles)
            SQLString = "" _
            & "      SELECT * FROM vwTrainerSearchUSRev WHERE" _
            & "            Latitude >= " & zcdRadius.MinLatitude _
            & "            AND Latitude <= " & zcdRadius.MaxLatitude _
            & "            AND Longitude >= " & zcdRadius.MinLongitude _
            & "            AND Longitude <= " & zcdRadius.MaxLongitude _
            & "            AND CityType = 'D'"
            DBAdapter.SelectCommand.CommandText = SQLString
            DBAdapter.Fill(DBDataSet, "ZipCodeOutput")
            Dim zcdDistance As New ZIPCodeDownload.DistanceAssistant()
            Dim thisRow As System.Data.DataRow
            DBDataSet.Tables("ZipCodeOutput").Columns.Add("ShowMiles")
           
            For Each thisRow In DBDataSet.Tables("ZipCodeOutput").Rows
                Dim Latitude2 As Double = CDbl(thisRow("Latitude"))
                Dim Longitude2 As Double = CDbl(thisRow("Longitude"))
                Dim Distance As Double = zcdDistance.Distance(Latitude, Longitude, Latitude2, Longitude2)
                ' THIS IS WHAT RETURNS THE 'DISTANCE' VALUE.  THIS GIVES THE MILES.  I need it to be added to the repeater output on the page.
               
                If Distance <= Miles Then
                    ' Trainer is within distance, so process the info here
                    DBDataSet.Tables("ZipCodeOutput").Rows(2)("ShowMiles") = Distance
                    Repeater1.DataSource = DBDataSet.Tables("ZipCodeOutput")
                    Repeater1.DataBind()
                Else
                    ' Trainer is further than the preference, so do nothing.
                End If
            Next
           
        Else ' Else statement on If ZIPExists
            ' No trainers exist, therefore do nothing
        End If ' End Sub If ZIPExists        
    End Sub ' End Sub Page Load

</script>



AND ON THE PAGE...



    <form id="form1" runat="server">
       <asp:Label ID="lblLatitude" runat="server"></asp:Label> and <asp:Label ID="lblLongitude" runat="server"></asp:Label>
        <br />
        <br />
        <asp:Repeater ID="Repeater1" runat="server">
        <ItemTemplate>
        <%#DataBinder.Eval(Container, "DataItem.City")%>   <%#DataBinder.Eval(Container, "DataItem.ShowMiles")%>  
        <br />
        </ItemTemplate>
        </asp:Repeater>
 
    </form>



Working page of output results is here: http://dev.apdt.com/po/ts/test4.aspx

Basically, i just need to have this list sorted by DISTANCE from shortest to farthest, with the distance displaying NEXT TO EACH CITY.

Much thanks in advance for any advice!

Sincerely,

ChrisStart Free Trial