Nested SQL Loops in Classic ASP

I suggest making a stored procedure that will take a list of JobIDs as a parameter and then return the results you want.  This way you need only make a single call to the database instead of multiple calls, which is much slower.

try this

<%
                 
      set rs2 = objConn.execute( "SELECT distinct JobID FROM QryJobHyperlinks WHERE JobShellId=" & strJobShellID & " and JobPage = '" & strJobPage & "'")      

%>      
<%

 do while not rs2.eof or rs2.bof


%>

<%

      set rs = objConn.execute( "SELECT JobID, Hyperlink, HyperlinkID, JobPage, DateUploaded FROM QryJobHyperlinks WHERE JobId=" & rs2("JobID") & " and JobPage = '" & strJobPage & "'")

%>

<table width="100%" border="0" cellPadding="3" cellSpacing="3" class="DisplayTaskTable">
 <tr>
  <td colspan="2">

<% do while not rs.eof or rs.bof %>

     
<table width="100%" border=0 cellPadding=3 cellSpacing=3 class="DisplayTaskTable">  
      <tr>
     <td colspan="5" height="30" class="bodyheader"><%=rs("Hyperlink")%></td>
    </tr>
  </table>

<%
              rs.movenext
        loop
      rs.close
%>      
</td></tr></table>
 
<p>
<%
              rs2.movenext
        loop
      rs2.close
%>

Hey Guys

That works but the problem is my code is a little more tricky as I need to put the rs("Hyperlink") into a string and then add it to another strong, e.g:

<%
  do while not rs.eof or rs.bof
 %>

<%      
      filename2 = rs("Hyperlink")
      strHyperlink = FileDump & filename2
                     Response.write strHyperlink
%>


<%
              rs.movenext
        loop
      rs.close
%>            

By adding it to a string I get the following error:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/cmsdev/includes/DisplayFileLinks.asp, line 134

Thanks in advance
Neilos

can you mark which line is line 134.

OK, here's the full code for the inner loop, you can ignore most of it

<%
  do while not rs.eof or rs.bof
 %>

<%      
<!-- /////////////////////////////////////////////////////////////////////////////////////////////////////// -->
'Line134 >           filename2 = rs("Hyperlink")
<!-- /////////////////////////////////////////////////////////////////////////////////////////////////////// -->
if InStr(strJobPage, "T") then
strTab = "Task"
strJobPage = "TasksOnly.asp"
end if
if InStr(strJobPage, "Q") then
strTab = "Quote"
strJobPage = "TasksQuotes.asp"
end if
if InStr(strJobPage, "P") then
strTab = "Client PO"
strJobPage = "TasksPOC.asp"
end if
if InStr(strJobPage, "S") then
strTab = "Supplier PO"
strJibPage = "TasksPOS.asp"
end if
if InStr(strJobPage, "W") then
strTab = "Warranty"
strJobPage = "TasksWarranty.asp"
end if
if InStr(strJobPage, "R") then
strTab = "Repair"
strJobPage = "TasksRepair.asp"
end if    

Set fs=Server.CreateObject("Scripting.FileSystemObject")

      strHyperlink = FileDump & filename2
      livelink =       replace("" & filename2, "%20", " ")
      livelink = strBaseURL & "FilelinkDump/" & livelink
      
If (fs.FileExists(strHyperlink)) = true Then
      
Set f=fs.GetFile(strHyperlink)      
      
      mylinkarray = Split(strHyperlink, ".")
      strFileName = mylinkarray(0)
      mylinkarray2 = split(strFileName,"\")
    InstrCount = UBound(mylinkarray2)
      strFileName = mylinkarray2(InstrCount)      
      strSize = f.size / 1000
      if mylinkarray(1) <> "pdf" or mylinkarray(1) <> "zip" or mylinkarray(1) <> "msg" or mylinkarray(1) <> "doc" or mylinkarray(1) <> "docx" or mylinkarray(1) <> "docx" or mylinkarray(1) <> "xlsx" then
      strType = "<img src=" & strBaseURL & "images/unknownfile.gif height=16 width=16 border=0 />"
      end if
      if mylinkarray(1) = "pdf" then
      strType = "<img src=" & strBaseURL & "images/pdf.gif height=16 width=16 border=0 alt=pdf />"
      end if
      if mylinkarray(1) = "zip" then
      strType = "<img src=" & strBaseURL & "images/zip.gif height=16 width=16 border=0 alt=zip />"
      end if
      if mylinkarray(1) = "msg" then
      strType = "<img src=" & strBaseURL & "images/msg.gif height=16 width=16 border=0 alt=msg />"
      end if
      if mylinkarray(1) = "doc" then
      strType = "<img src=" & strBaseURL & "images/word.gif height=16 width=16 border=0 alt=word />"
      end if
      if mylinkarray(1) = "docx" then
      strType = "<img src=" & strBaseURL & "images/word.gif height=16 width=16 border=0 alt=word />"
      end if      
      if mylinkarray(1) = "xls" then
      strType = "<img src=" & strBaseURL & "images/excel.gif height=16 width=16 border=0 alt=excel />"
      end if
      if mylinkarray(1) = "xlsx" then
      strType = "<img src=" & strBaseURL & "images/excel.gif height=16 width=16 border=0 alt=excel />"
      end if                              
      strDC = f.DateCreated
      strDLM = f.DateLastModified
      strDU = rs("DateUploaded")
      
Else

      strFileName = "<b>File Not Found</b><br/>" & strHyperlink
      strSize = "0"
      strType = "&nbsp;"
      strDC = "&nbsp;"
      strDLM = "&nbsp;"

End If      
      
%>

     
<table width="100%" border=0 cellPadding=3 cellSpacing=3 class="DisplayTaskTable">  
      <tr>
     <td colspan="5" height="30" class="bodyheader"><%  if isBlank(strJobID) then %>File Linked To:&nbsp;<a class="bodyheader" href="<%=strJobPage%>?CID=<%=strClientID%>&JobShellID=<%=strJobShellID%>&JobID=<%=strJobID%>&St=<%=strSt%>&Order=<%=strOrder%>&Show=<%=request("Show")%>"><%=strTab%>&nbsp;<%= strJobID2 %></a><% else %>File Link<% end if %></td>
    </tr>
      <tr>
       <td width="15%" class="taskfield"  >Date Created</td>
       <td width="15%" class="taskbox"  ><%= strDC %></td>
     <td width="15%" class="taskfield" >Filename</td>
       <td width="55%" class="taskbox" ><a href="<%=Livelink%>" target="_blank"><%=strFileName%></a>&nbsp;</td>
      <td width="5%" align="right"><a href="<%=strThisPage%>?CID=<%=strClientID%>&JobShellID=<%=strJobShellID%>&JobID=<%=strJobID%>&FileLink=D&HID=<%=rs("HyperlinkID")%>&Show=<%=request("Show")%>"  onClick="return confirmdeletefile();" ><img src="../../images/delete.png" alt="Delete" border="0" style="padding-top:10px;" /></a></td>
    </tr>
    <tr>
       <td class="taskfield"  >Date Last Modified</td>
       <td class="taskbox"  ><%= strDLM %></td>
       <td class="taskfield"  >Type</td>
       <td class="taskbox"  ><a href="<%=Livelink%>" target="_blank"><%= strType %></a></td>
       <td width="5%">&nbsp;</td>
    </tr>
    <tr>
       <td class="taskfield"  >Date Uploaded</td>
       <td class="taskbox"  ><%= strDU %>&nbsp;</td>
       <td class="taskfield"  >Size</td>
       <td class="taskbox"  ><%= strSize %>Kb</td>
       <td width="5%">&nbsp;</td>
      </tr>
<%
set filename2=nothing
set f=nothing
set fs=nothing
%>
  </table>

<%
              rs.movenext
        loop
      rs.close
%>      

If you can, it would be much more efficient to build the string up in the SQL query.
It depends if all the components of the string are accessible from SQL.

Hey Chrismc,

Thanks for the advice, would you be able to give me an example on exactly what you mean, as I don't quite follow.

Just to give you a bit more background, I'm a little limited as far as the backend as we are still using Microsoft Access, therefore don't have the benefits of stored procedures but obviously am using Qry to pull out the data.

So I'm not sure if that makes a difference.

Let us know your thoughts / advice
Thanks again in advance
Neilos

P.S. Migrating to SQL frontend is not an option at the moment.
P.S.S. I've raised the points to 100 for whoever gets this resolved :-)

Hi neilos,
Looking at your code a bit deeper and the fact your using Access limits it a bit but what I'm suggesting is to do as much of the string processing in SQL, in general this is more efficiently done here.

I'm not that hot on the Access version of SQL but it's close enough to standard SQL.

I can't exactly work out the full string. I can't figure out where strBaseURL is set. Also I doubt you can do the "FileExists" check here, maybe create a table that is populated overnight with a flag for each possible value to say whether it exists or not.

Either way, I'm just trying to show the principle here.

SELECT JobID, Hyperlink, HyperlinkID, JobPage, DateUploaded,
            Tab = Case JobPage
                             When 'T' Then 'Task'
                             When 'Q' Then 'Quote'
                       End,
             Job = Case JobPage
                             When 'T' Then 'TasksOnly.asp'
                             When 'Q' Then 'TasksQuotes.asp'
                        End,
     FROM QryJobHyperlinks WHERE JobId=" & rs2("JobID") & " and JobPage = '" & strJobPage & "'"

Hey chrismc,

I encountered an issue where I would have absolutely loved to use the CASE option as it would have been perfect in another area of this project however Access does not support CASE, not good.  So I had to get a workaround which does suit this circumstance.

Any other advice or recommendations are very grateful.
Neilos

Well the best advice I can give is don't use Access ;-P

The only other advice is that file existence checking is quite slow, so if you can make that more of an off line process by doing it as a batch job and storing the results in a table and using that table in your routine then that would be better.

Agreed however, in this instance the file existence checking is irrelevant as I just wish to be able to nest the loops and use rs("Hyperlinks") from the first loop in the second and by doing so it would have to be connected to another string which there lies my problem, if I'm just calling the rs("hyperlinks") and displaying it thats fine but as soon as I wish to add it to a sting the problem arises...;-(

Thanks again for all your help and further suggestions are very welcome.
Neilos

Hey Guys,

I'm raising the points for anybody who can help :-)

Thanks in advance
Neilos

try changing
 filename2 = rs("Hyperlink")

to

 filename2 = "" & rs("Hyperlink")
or
 filename2 = cstr("" & rs("Hyperlink"))

Really you need to call a second query passing in the hyperlink from the first. BUT you need to do this in the VB because it's dependant on a File Existance check.

Because you have Access your options are more limited.

You really need to get the existence check and anything else external into the DB then you can do joins to produce stuff you want.

Hi All,

Appari, thanks for the advice, gave it a try and no luck unfortunately.

Chrismc, I agree that its definitely limited due to Access, you mentioned about getting the existence check into the DB, would that then work, is it worth going down that track, would I have to be farily proficient in VB (which I'm not)?

Thanks again for all your help so far, I appreciate your assistence.
Neilos

Hey chrismc,

You lost me when you mentioned periodic routine, is that like a stored procdure that you can call, and if so how does one go about creating one in Access?  I agree once the results are in access as a table then it can be more easily manipulated.

Thanks in advance for you advice,
Neilos

I don't really know how to do this in Access but I think it's just a "Query" but you can do an Insert query or an Update query.

And by periodic, I mean a routine you can schedule to run at a set time or frequency. Again I don't know how to do it in Access.

I'd post some specific questions in the Access zone for things like querying the file system and scheduling.

This is just to help you in future and I'm not suggesting you alter the points on this question but if these things are urgent, you should give as many points as you can. When someone answers a question and has it accepted, they get multiples of the points based on the grade given. So if  you accept a solution and award 125 points at Grade A it's multiplied by 4 so becomes 500 points to answerer. Someone offering 500 points, the answerer, if given grade A would get 2000 points. Someone with time to answer only one or a couple of questions will look at the 500's before the 125's. Look at what others in the zone are offering, you're in competition with them for the "Experts" time.