Solved

Nested SQL Loops in Classic ASP

Posted on 2008-06-16
20
725 Views
Last Modified: 2008-09-14
Hey All,

I've got an issue where I am pulling out an inital SQL query that yields the following results (rs2) :

JobID             Hyperlink
6529      Test1
6529      Test2
6546      Test3

I want to then separate this result into two queries somehow based on JobID, or for example if there was 10 records with 5 different jobids then separate it out into five queries  (loops may make more sense once you see the code below).  This is why I've tried nesting the two loops however it doesn't work as once it's completed the first inner loop it then tries to loop again on the outer loop by from the first jobid again and not the next one.  How can I get around this?

<%
                  
      set rs2 = objConn.execute( "SELECT JobID, Hyperlink 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
%>

Many thanks in advance.
Regards
Neil
0
Comment
Question by:neilos
  • 8
  • 6
  • 3
  • +1
20 Comments
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
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.
0
 
LVL 39

Expert Comment

by:appari
Comment Utility
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
%>
0
 

Author Comment

by:neilos
Comment Utility
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
0
 
LVL 39

Expert Comment

by:appari
Comment Utility
can you mark which line is line 134.
0
 

Author Comment

by:neilos
Comment Utility

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
%>      
0
 
LVL 18

Expert Comment

by:chrismc
Comment Utility
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.

0
 

Author Comment

by:neilos
Comment Utility

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 :-)
0
 
LVL 18

Expert Comment

by:chrismc
Comment Utility
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 & "'"

0
 

Author Comment

by:neilos
Comment Utility
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
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 18

Expert Comment

by:chrismc
Comment Utility
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.
0
 

Author Comment

by:neilos
Comment Utility

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
0
 

Author Comment

by:neilos
Comment Utility

Hey Guys,

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

Thanks in advance
Neilos
0
 
LVL 39

Expert Comment

by:appari
Comment Utility
try changing
 filename2 = rs("Hyperlink")

to

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

0
 
LVL 18

Expert Comment

by:chrismc
Comment Utility
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.
0
 

Author Comment

by:neilos
Comment Utility

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
0
 
LVL 18

Accepted Solution

by:
chrismc earned 125 total points
Comment Utility
I'm not an expert in Access. But I think the code you've got has all the elements in you need with the exception of creating a record from it. You need to change that into a periodic routine that will build a table of results.

That table of results can then be incorporated into the query for the report with no VB needed.

The fact you need VB in between to get your data set is your issue here. Get that outside and then you'll be able to achieve what you want.
 
0
 

Author Comment

by:neilos
Comment Utility

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
0
 
LVL 18

Expert Comment

by:chrismc
Comment Utility
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.

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now