Code is always storing first record

[loveday]

Good people, I am really stumped this time and need your urgent help.

I am doing street inventories whereby a street name (we call it route_name in this code) can be associated with one or more block_names.

What I have done so far is code it in such a way that if you select a route_name, all the blocks associated with that route_name is displayed and you can actually see a count of how many blocks are associated with a given block_name (called blockcount).
So far, so good.

Problem comes in when I attempt to store a given route_name along with its associated block_Name.

It doesn't matter what block_name is selected, the very first one always gets selected.

For instance, let's say that I have a route_name called Jim Watts Street, and let's say also that there are 5 block_names associated with this route_name.

Let's further say that block_names are paired like the following:
From block_A to block_B,
From followed b to block_C,
From C to Block_D,
From Block_D to block_E, and
From block_E to block_F

Finally, let's say that I wanted to process only from
block_C to block_D, when I complete the process and store the results in the db, I expect to see block_C to block_D.

Instead, I always see From block_A to blkc_B.

Doesn't matter what block pair I processed.

Can you please take a look at my code snippet.

The code is long but I will just post a small portion.

Thanks in advance for your assistance.

 

[loveday]

sorry, I forgot to post the code snip.

<a href="chooseRoute.asp"><font color="red"><b>CLICK HERE</b> to choose another Project Name</font></a>&nbsp;&nbsp; <b>OR</b>&nbsp;&nbsp;<a href="default.asp"><font color="red"><b>CLICK HERE</b> to go to Main Menu</font></a>
<%
' from the prior page, we get the requested route_ID:
Dim routeID
routeID = Request("routeID")
' except for now, if there isn't one we'll use a default of 3:
If routeID = "" Then
    Response.Write "You must choose a project name.  Go back and try again."
    Response.End
End If

routeID = CLNG(routeID)

' get current blockID, if any:
blockID = Request("blockID")
If blockID = "" Then blockID = 0 Else blockID = CLng(blockID)

       dim Conn,SQLstr
       Function OpenDBFConn(Path)
       Dim Conn: Set Conn = CreateObject("ADODB.Connection")
        Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & Path & ";" & _
                   "Extended Properties=""DBASE IV;"";"
       Set OpenDBFConn = Conn
       End Function

      Dim DBConn
      Set DBConn = OpenDBFConn("c:\arcgis")
      'Open recordset from rte)intr table

' first, get list of all blocks in the project name:
Dim SQL
SQL = "SELECT block_id, from_name, to_name FROM rte_intr.dbf where route_ID = " & routeID & " order by From_Meas"

Dim RSsel
Set RSsel = DBConn.Execute(SQL)

If(RSsel.eof) Then 'No records were returned
    response.redirect "error.asp?msg=" & server.urlencode("<font color='darkOrange'>This street name doesn't exist. Select another street name from the dropdown menu listbox.</font>")
End If

' if there is no current block, use the first one in this route!
If blockID = 0 Then blockID = RSsel("block_id")

' okay, ready to generate form...
%>
<FORM Name="MainForm" Method=Post Action="process1121.asp" onSubmit="return validateForm(this);">

<%
' Get info about the currently selected block
SQL = "SELECT * FROM rte_intr.dbf WHERE route_id = " & routeID & " AND block_id = " & blockID
Set RS = dbConn.Execute( SQL )
If RS.EOF Then
     Response.Write "Something went wrong!<P>SQL: " & SQL & "<P>Produced no matching record!<HR>"
     Response.End
End If

' copy values from RS to variables:
FromName = RS("From_Name")
FromMeas = RS("From_Meas")
FromRec = RS("From_Recno")
ToName = RS("To_Name")
ToMeas = RS("To_Meas")
ToRec = RS("To_Recno")
RouteName = RS("Route_Name")

%>
<input type=hidden name=routeID value = "<%=routeID%>">
<input type=hidden name=blockID value = "<%=blockID%>">
<input type=hidden name=routeName value = "<%=routeName%>">
<input type=hidden name=USENEW id=USENEW value="NO">


<br>
<table border=0 CellPadding=10>
<tr>
    <th align=right>System ID:</th><th align=left><%=routeId%></th>
    <th align=left>Project Name:</th><th align=left><%=routeName%></th>
</tr>
<tr id="FLIP1">
    <th align=right>Block ID:</th><th align=left><%=blockID%></th>
    <th align=right>Project ID:</th>
    <th align=right>Project ID:</th>
    <th align=left><input size="10" name="projID" value="<%=Request("projID")%>"></th>
</tr>
<tr id="FLOP1" style="display: none" bgcolor="yellow">
    <th align=right>Block ID:</th>
    <th align=left><input name="new_block_ID"></th>
    <th align=left>&nbsp;</th>
</tr>
<tr id="FLIP2">
    <th align=right>From:</th><th align=left>[<%=FromRec%>] <%=FromName%></th>
    <th align=left>From measure:</th><th align=left><%=FromMeas%></th>
</tr>
<tr id="FLOP2" style="display: none" bgcolor="yellow">
    <th align=right>From:</th>
    <th align=left><input name="new_from_name"></th>
    <th align=left>From measure:</th>
    <th align=left><input name="new_from_meas"></th>
</tr>
<tr id="FLIP3">
    <th align=right>To:</th><th align=left>[<%=ToRec%>] <%=ToName%></th>
    <th align=left>To measure:</th><th align=left><%=ToMeas%></th>
</tr>
<tr id="FLOP3" style="display: none" bgcolor="yellow">
    <th align=right>To:</th>
    <th align=left><input name="new_to_name"></th>
    <th align=left>To measure:</th>
    <th align=left><input name="new_to_meas"></th>
</tr>
</table>
<hr>
<table border=0 cellpadding=5>
<tr>
</tr>
<tr id="FLIP4">
    <td align=left><INPUT Type=Button Value="<< Prior Block" onClick="goBlock(-1);"></td>
    <td colspan=2 align=center>
    Select a block:
    <SELECT Name="selectBlock" onChange="goDirect(this);" ID="Select1">
    <option value=0>--select a block--</option>
<%
blockCount = 0
selBlock = 1
Do Until RSsel.EOF
    blockCount = blockCount + 1
    full_Name = RSsel("From_Name") & " : " & RSsel("To_Name")
    id = RSsel("block_ID")
    If id = blockID Then
        sel = " SELECTED"
        selBlock = blockCount
    Else
        sel = ""
    End If
    Response.Write "<OPTION Value=""" & RSsel("block_ID") & """ " & sel & ">" & full_Name & vbNewLine
    RSsel.MoveNext
Loop
RSsel.Close
%>
    </SELECT>

As I indicated in my first post, the code is long.

I feel this is the relevant part of the code.

 

[Genimuse]

You indicated that there are multiple block IDs per route ID, right? As such, when you use this SQL:

SQL = "SELECT block_id, from_name, to_name FROM rte_intr.dbf where route_ID = " & routeID & " order by From_Meas"

you're going to end up with multiple records, one for every block ID. Your code, though, only processes the first record returned. Though I don't quite understand what you're trying to do, I suspect that you want your SQL to actually be:

SQL = "SELECT block_id, from_name, to_name FROM rte_intr.dbf where route_ID = " & routeID & "[COLOR=blue] AND block_id = " & block_id & "[/color] order by From_Meas"

though again, I could be wrong. Still, my explanation above is why you're always getting the first one.