EOF/BOF if record values are different

[endoflux]

I'm storing two different field values in variables, closing my connection, opening a different connection and selecting records based on one of the variables...store values in variables...close connection, reopen and select by the second variable, store values in variables, then print variables in desired format. Not sure if that's the best way to go about this, but it seems to work...

...as long as the values in both variables are the same. If "From" and "RetTo" both = 1007 (or any identical value), my page prints as expected. If "From" = 1007 and "RetTo" = 1008, I get an EOF/BOF error on line 27 (bold below). Ideas?

[COLOR=green]' Store Field Values from Main Page & close connection[/color]
Dim From
Dim RetTo
From = objPagingRS("Plant_Num").value
RetTo = objPagingRS("ReturnTo").value
objPagingRS.Close

[COLOR=green]' Select record from different table & open connection[/color]
strSQL = "SELECT * FROM Plants WHERE Plant_Num = '" & From & "'"
objPagingRS.Open strSQL

Dim FCompany
Dim FAdd
Dim FCity
Dim FState
Dim FZip
Dim FPhone
Dim FFax
Dim TCompany
Dim TAdd
Dim TCity
Dim TState
Dim TZip
Dim TATTN

[COLOR=green]' Store results in variables and close connection[/color]
[b]     FCompany = objPagingRS("Plant_Name").value[/b]
        FAdd = objPagingRS("Plant_Add1").value
        FCity = objPagingRS("Plant_City").value
        FState = objPagingRS("Plant_State").value
        FZip = objPagingRS("Plant_Zip").value
        FPhone = objPagingRS("Plant_Phone").value
        FFax = objPagingRS("Plant_Fax").value

objPagingRS.Close

[COLOR=green]' Select different records from same table & open connection[/color]
strSQL = "SELECT * FROM Plants WHERE Plant_Num = '" & RetTo & "'"
objPagingRS.Open strSQL

[COLOR=green]' Store results in variables[/color]
        TCompany = objPagingRS("Plant_Name").value
        TAdd = objPagingRS("Plant_Add1").value
        TCity = objPagingRS("Plant_City").value
        TState = objPagingRS("Plant_State").value
        TZip = objPagingRS("Plant_Zip").value
        TATTN = objPagingRS("Plant_ATTN").value


         %>

[COLOR=green]' Print variables in desired format[/color]
         <tr>
             <td width="380" align="center"><span class=size3><b>CUSTOMER INFORMATION</b></span></td>
             <td width="380" align="center"><span class=size3><b>POST PROCESSING SHIP-TO INFORMATION</b></span></td>
         </tr>
         <tr>
             <td width="380" align="left"><span class=btext>&nbsp;Completed By: &nbsp;&nbsp;</span></td>
             <td width="380" align="left"><span class=btext>&nbsp;Company:&nbsp;&nbsp;<% Response.Write TCompany %></span></td>
         </tr>
         <tr>
             <td width="380" align="left"><span class=btext>&nbsp;Company: &nbsp;&nbsp;<% Response.Write FCompany %></span></td>
             <td width="380" align="left"><span class=btext>&nbsp;Address: &nbsp;&nbsp;<% Response.Write TAdd %></span></td>
         </tr>
         <tr>
             <td width="380" align="left"><span class=btext>&nbsp;Address: &nbsp;&nbsp;<% Response.Write FAdd %></span></td>
             <td width="380" align="left"><span class=btext>&nbsp;City:&nbsp;&nbsp;<% Response.Write TCity %>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;State&nbsp;&nbsp;<% Response.Write TState%>&nbsp;&nbsp;Zip&nbsp;&nbsp;<% Response.Write TZip %></span></td>
         </tr>
         <tr>
             <td width="380" align="left"><span class=btext>&nbsp;City:&nbsp;&nbsp;<% Response.Write FCity %>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;State&nbsp;&nbsp;<% Response.Write FState%>&nbsp;&nbsp;Zip&nbsp;&nbsp;<% Response.Write FZip %></span></td>
             <td width="380" align="left"><span class=btext>&nbsp;Attn: &nbsp;&nbsp;<% Response.Write TATTN %></span></td>
         </tr>
         <tr>
             <td width="380" align="left"><span class=btext>&nbsp;Phone#: &nbsp;&nbsp;<% Response.Write FPhone %></span></td>
             <td width="380" rowspan="2" align="left"><span class=size3><b>&nbsp;PO#</b> &nbsp;&nbsp;</span></td>
         </tr>
         <tr>
             <td width="380" align="left"><span class=btext>&nbsp;Fax#: &nbsp;&nbsp;<% Response.Write FFax %></span></td>
         </tr>
         </table>

 

[Sheco]

Maybe add a line in to check for EOF like this:

 < snip>

IF objPagingRS.EoF THEN
  Response.Write "No record found."
  Response.End
ELSE
  FCompany = objPagingRS("Plant_Name").value
  FAdd = objPagingRS("Plant_Add1").value
  FCity = objPagingRS("Plant_City").value
  FState = objPagingRS("Plant_State").value
  FZip = objPagingRS("Plant_Zip").value
  FPhone = objPagingRS("Plant_Phone").value
  FFax = objPagingRS("Plant_Fax").value
END IF

  <snip>

 

[Sheco]

If you add temporary debugging code to check the SQL syntax, you might find that the value of your variable is not what you expect:

  <snip>

' Select record from different table & open connection
strSQL = "SELECT * FROM Plants WHERE Plant_Num = '" & From & "'"
[red]Response.Write strSQL : Response.End[/red]
objPagingRS.Open strSQL

  <snip>

 

[endoflux]

Sheco - you were right...and I always forget to try that! My variable had no sotred value because in the shuffle, I was requesting a field name from the wrong table...thanks!