search within a search within a search - looking for better way...my script times out

Discussion in 'ASP General' started by Abby Lee, Jul 29, 2004.

  1. Abby Lee

    Abby Lee Guest

    My code does what I want (works unless there is a lot of
    volume...works for this month cause not a lot of items marked paid
    yet...) but the page times out for last month because there is just
    so many items. Is there a better way to do this?

    my page gets all distinct Oganizations.
    within each organization it gets each distinct Fund
    within each fund it gets each distinct Program
    then it lists each item that is paid for the month being
    checked

    So the end result is a list of each account that has been paid for
    each Org/Fund/Program in order.

    Oganization 000001
    Fund 000001
    Program 000001
    Account 000001 MonthPd 0704 PdAmount 140.00
    Account 000001 MonthPd 0704 PdAmount 370.66
    Account 000001 MonthPd 0704 PdAmount 370.66

    Oganization 000001
    Fund 000001
    Program 000001
    Account 000002 MonthPd 0704 PdAmount 1.00
    Account 000002 MonthPd 0704 PdAmount 880.60
    ..
    ..<snip> Went threw all accounts for Program One move to Program 2
    ..

    Oganization 000001
    Fund 000001
    Program 000002
    Account 000001 MonthPd 0704 PdAmount 1.00
    Account 000001 MonthPd 0704 PdAmount 880.60
    ..
    ..<snip> Went threw all accounts/programs for fund 1 move on to fund 2
    ..

    Oganization 000001
    Fund 000002
    Program 000001
    Account 000001 MonthPd 0704 PdAmount 1.00
    Account 000001 MonthPd 0704 PdAmount 880.60
    ..
    ..
    ..

    My Code...sorry that I could not shorten it much more

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    [Next line is file telling where db is]
    <!--#include file="include.asp"-->

    <%

    Dim connect, rs
    Dim sSQL
    Dim connect2, rs2
    Dim sSQL2
    Dim connect3, rs3
    Dim sSQL3
    Dim ThePD 'Date Paid for monthly reconciling
    Dim TheYear 'Table in the database - each year is in different table
    'Hard codeded for testing
    ThePD = "0704"
    'Hard coded for testing
    TheYear = "AllExpenses2004"

    Set connect = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateOBject("ADODB.Recordset")
    connect.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sDataSource
    connect.Open

    Set connect2 = Server.CreateObject("ADODB.Connection")
    Set rs2 = Server.CreateOBject("ADODB.Recordset")
    connect2.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sDataSource
    connect2.Open

    Set connect3 = Server.CreateObject("ADODB.Connection")
    Set rs3 = Server.CreateOBject("ADODB.Recordset")
    connect3.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sDataSource
    connect3.Open

    Set connect4 = Server.CreateObject("ADODB.Connection")
    Set rs4 = Server.CreateOBject("ADODB.Recordset")
    connect4.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sDataSource
    connect4.Open
    %>

    <html>
    <head>
    </head>
    <body>
    <table border="0">
    <%
    sSQL3 = "SELECT Distinct Organization" & _
    " FROM " & TheYear & _
    " WHERE MonthPD = '" & ThePD & "'" & _
    " ORDER BY Organization"
    set rs3 = Connect.Execute(sSQL3)
    %>
    <% Do until rs3.eof %>

    <%
    sSQL2 = "SELECT Distinct Fund" & _
    " FROM " & TheYear & _
    " WHERE MonthPD = '" & ThePD & "'" & _
    " ORDER BY Fund"
    set rs2 = Connect.Execute(sSQL2)
    %>
    <% Do until rs2.eof %>

    <%
    sSQL4 = "SELECT Distinct Fund, Organization, Program" & _
    " FROM " & TheYear & _
    " WHERE MonthPD = '" & ThePD & "'" & _
    " ORDER BY Organization, Fund, Program"
    set rs4 = Connect.Execute(sSQL4)
    %>
    <% Do until rs4.eof %>

    <tr>
    <td>Organization</td>
    <td colspan="11"><%=rs4("Organization")%></td>
    </tr>
    <tr>
    <td>Fund</td>
    <td colspan="11"><%=rs4("Fund")%></td>
    </tr>
    <tr>
    <td>Program</td>
    <td colspan="11"><%=rs4("Program")%></td>
    </tr>
    <tr>
    <td>Account</td>
    <td>Month PD</td>
    <td>PD Amount</td>
    </tr>

    <%
    TheFund = rs4("Fund")
    TheOrg = rs4("Organization")
    TheProg = rs4("Program")

    sSQL = "SELECT *" & _
    " FROM " & TheYear & _
    " WHERE MonthPD = '" & ThePD & "'" & _
    " and Fund = '" & TheFund & "'"
    set rs = Connect.Execute(sSQL)

    Do until rs.eof
    %>
    <tr bgcolor="<%=sBackgroundColor%>">
    <td class="text"><%=rs("Account")%></td>
    <td class="text"><%=rs("MonthPd")%></td>
    <td class="text"><%=rs("PdAmount")%></td>
    </tr>

    <% End If
    rs.MoveNext
    Loop %>
    </tr>
    <tr><tdnbsp;</td></tr>

    <% rs4.MoveNext
    Loop %>
    <% rs2.MoveNext
    Loop %>
    <% rs3.MoveNext
    Loop %>
    </table>
    <p>&nbsp;</p>
    </body>
    </html>
     
    Abby Lee, Jul 29, 2004
    #1
    1. Advertising

  2. Abby Lee

    Jeff Cochran Guest

    On 29 Jul 2004 12:26:29 -0700, (Abby Lee) wrote:

    >My code does what I want (works unless there is a lot of
    >volume...works for this month cause not a lot of items marked paid
    >yet...) but the page times out for last month because there is just
    >so many items. Is there a better way to do this?


    Hard to tell. How about a database type/version, and your query?
    Table structure and sample data might help as well.

    Jeff

    >
    >my page gets all distinct Oganizations.
    > within each organization it gets each distinct Fund
    > within each fund it gets each distinct Program
    > then it lists each item that is paid for the month being
    >checked
    >
    >So the end result is a list of each account that has been paid for
    >each Org/Fund/Program in order.
    >
    >Oganization 000001
    >Fund 000001
    >Program 000001
    >Account 000001 MonthPd 0704 PdAmount 140.00
    >Account 000001 MonthPd 0704 PdAmount 370.66
    >Account 000001 MonthPd 0704 PdAmount 370.66
    >
    >Oganization 000001
    >Fund 000001
    >Program 000001
    >Account 000002 MonthPd 0704 PdAmount 1.00
    >Account 000002 MonthPd 0704 PdAmount 880.60
    >.
    >.<snip> Went threw all accounts for Program One move to Program 2
    >.
    >
    >Oganization 000001
    >Fund 000001
    >Program 000002
    >Account 000001 MonthPd 0704 PdAmount 1.00
    >Account 000001 MonthPd 0704 PdAmount 880.60
    >.
    >.<snip> Went threw all accounts/programs for fund 1 move on to fund 2
    >.
    >
    >Oganization 000001
    >Fund 000002
    >Program 000001
    >Account 000001 MonthPd 0704 PdAmount 1.00
    >Account 000001 MonthPd 0704 PdAmount 880.60
    >.
    >.
    >.
    >
    >My Code...sorry that I could not shorten it much more
    >
    >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    >[Next line is file telling where db is]
    ><!--#include file="include.asp"-->
    >
    ><%
    >
    >Dim connect, rs
    >Dim sSQL
    >Dim connect2, rs2
    >Dim sSQL2
    >Dim connect3, rs3
    >Dim sSQL3
    >Dim ThePD 'Date Paid for monthly reconciling
    >Dim TheYear 'Table in the database - each year is in different table
    >'Hard codeded for testing
    >ThePD = "0704"
    >'Hard coded for testing
    >TheYear = "AllExpenses2004"
    >
    >Set connect = Server.CreateObject("ADODB.Connection")
    >Set rs = Server.CreateOBject("ADODB.Recordset")
    >connect.ConnectionString = _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & sDataSource
    >connect.Open
    >
    >Set connect2 = Server.CreateObject("ADODB.Connection")
    >Set rs2 = Server.CreateOBject("ADODB.Recordset")
    >connect2.ConnectionString = _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & sDataSource
    >connect2.Open
    >
    >Set connect3 = Server.CreateObject("ADODB.Connection")
    >Set rs3 = Server.CreateOBject("ADODB.Recordset")
    >connect3.ConnectionString = _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & sDataSource
    >connect3.Open
    >
    >Set connect4 = Server.CreateObject("ADODB.Connection")
    >Set rs4 = Server.CreateOBject("ADODB.Recordset")
    >connect4.ConnectionString = _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & sDataSource
    >connect4.Open
    >%>
    >
    ><html>
    ><head>
    ></head>
    ><body>
    ><table border="0">
    ><%
    > sSQL3 = "SELECT Distinct Organization" & _
    > " FROM " & TheYear & _
    > " WHERE MonthPD = '" & ThePD & "'" & _
    > " ORDER BY Organization"
    > set rs3 = Connect.Execute(sSQL3)
    >%>
    ><% Do until rs3.eof %>
    >
    ><%
    > sSQL2 = "SELECT Distinct Fund" & _
    > " FROM " & TheYear & _
    > " WHERE MonthPD = '" & ThePD & "'" & _
    > " ORDER BY Fund"
    > set rs2 = Connect.Execute(sSQL2)
    >%>
    ><% Do until rs2.eof %>
    >
    ><%
    > sSQL4 = "SELECT Distinct Fund, Organization, Program" & _
    > " FROM " & TheYear & _
    > " WHERE MonthPD = '" & ThePD & "'" & _
    > " ORDER BY Organization, Fund, Program"
    > set rs4 = Connect.Execute(sSQL4)
    >%>
    ><% Do until rs4.eof %>
    >
    ><tr>
    ><td>Organization</td>
    ><td colspan="11"><%=rs4("Organization")%></td>
    ></tr>
    ><tr>
    ><td>Fund</td>
    ><td colspan="11"><%=rs4("Fund")%></td>
    ></tr>
    ><tr>
    ><td>Program</td>
    ><td colspan="11"><%=rs4("Program")%></td>
    ></tr>
    ><tr>
    > <td>Account</td>
    > <td>Month PD</td>
    > <td>PD Amount</td>
    ></tr>
    >
    ><%
    >TheFund = rs4("Fund")
    >TheOrg = rs4("Organization")
    >TheProg = rs4("Program")
    >
    >sSQL = "SELECT *" & _
    > " FROM " & TheYear & _
    > " WHERE MonthPD = '" & ThePD & "'" & _
    > " and Fund = '" & TheFund & "'"
    > set rs = Connect.Execute(sSQL)
    >
    >Do until rs.eof
    >%>
    ><tr bgcolor="<%=sBackgroundColor%>">
    > <td class="text"><%=rs("Account")%></td>
    > <td class="text"><%=rs("MonthPd")%></td>
    > <td class="text"><%=rs("PdAmount")%></td>
    ></tr>
    >
    ><% End If
    >rs.MoveNext
    >Loop %>
    ></tr>
    ><tr><tdnbsp;</td></tr>
    >
    ><% rs4.MoveNext
    >Loop %>
    ><% rs2.MoveNext
    >Loop %>
    ><% rs3.MoveNext
    >Loop %>
    ></table>
    ><p>&nbsp;</p>
    ></body>
    ></html>
     
    Jeff Cochran, Jul 29, 2004
    #2
    1. Advertising

  3. Abby Lee

    Rune Guest

    I don't know about a better way, but if the problem is that your page times
    out, then perhaps Response.Flush can help?

    Try to replace this:

    <% rs3.MoveNext
    Loop %>

    With this:

    <%
    Response.Flush
    rs3.MoveNext
    Loop
    %>

    If it still times out, try to put Response.Flush before rs2.MoveNext
    instead.

    Let me know if it helps.
    (Err, not that I have other ideas if it doesn't help...)

    Best regards,
    Rune
     
    Rune, Jul 30, 2004
    #3
  4. Abby Lee

    Abby Lee Guest

    (Jeff Cochran) wrote in message news:<>...
    > On 29 Jul 2004 12:26:29 -0700, (Abby Lee) wrote:
    >
    > >My code does what I want (works unless there is a lot of
    > >volume...works for this month cause not a lot of items marked paid
    > >yet...) but the page times out for last month because there is just
    > >so many items. Is there a better way to do this?

    >
    > Hard to tell. How about a database type/version, and your query?
    > Table structure and sample data might help as well.
    >
    > Jeff

    Access 2000
    13477 records
    Fields: Chart=Number, Indexcode=Text, Fund=Text, Organization=Text,
    Acount=Text, Program=Text, GrantIndex=Text, Activity=Text,
    location=Text, Division=Text, Log=Text, Voucher=Text, Vendor=Text,
    Item=Text, Vamount=Curreny, RecDate=Date/Time, Vdate=Date/Time
    Discount=Currency, MonthPd=Text, PdAmount=Currency

    I've included my queries between the body tags...the only place they
    will work. Only thing I did not include was the file that tells my
    query where to find my db ><!--#include file="include.asp"-->

    My include.asp file:
    <%
    Dim sDataSoure
    sDataSource = "f:\dbs\Access_Files\expense.mdb"
    %>

    <%
    Function fncStrConv(str)
    fncStrConv = Replace(Replace(str, "'", "''"), CHR(34), "&quote;")
    End Function
    %>


    >
    > >
    > >my page gets all distinct Oganizations.
    > > within each organization it gets each distinct Fund
    > > within each fund it gets each distinct Program
    > > then it lists each item that is paid for the month being
    > >checked
    > >
    > >So the end result is a list of each account that has been paid for
    > >each Org/Fund/Program in order.
    > >
    > >Oganization 000001
    > >Fund 000001
    > >Program 000001
    > >Account 000001 MonthPd 0704 PdAmount 140.00
    > >Account 000001 MonthPd 0704 PdAmount 370.66
    > >Account 000001 MonthPd 0704 PdAmount 370.66
    > >
    > >Oganization 000001
    > >Fund 000001
    > >Program 000001
    > >Account 000002 MonthPd 0704 PdAmount 1.00
    > >Account 000002 MonthPd 0704 PdAmount 880.60
    > >.
    > >.<snip> Went threw all accounts for Program One move to Program 2
    > >.
    > >
    > >Oganization 000001
    > >Fund 000001
    > >Program 000002
    > >Account 000001 MonthPd 0704 PdAmount 1.00
    > >Account 000001 MonthPd 0704 PdAmount 880.60
    > >.
    > >.<snip> Went threw all accounts/programs for fund 1 move on to fund 2
    > >.
    > >
    > >Oganization 000001
    > >Fund 000002
    > >Program 000001
    > >Account 000001 MonthPd 0704 PdAmount 1.00
    > >Account 000001 MonthPd 0704 PdAmount 880.60
    > >.
    > >.
    > >.
    > >
    > >My Code...sorry that I could not shorten it much more
    > >
    > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    > >[Next line is file telling where db is]
    > ><!--#include file="include.asp"-->
    > >
    > ><%
    > >
    > >Dim connect, rs
    > >Dim sSQL
    > >Dim connect2, rs2
    > >Dim sSQL2
    > >Dim connect3, rs3
    > >Dim sSQL3
    > >Dim ThePD 'Date Paid for monthly reconciling
    > >Dim TheYear 'Table in the database - each year is in different table
    > >'Hard codeded for testing
    > >ThePD = "0704"
    > >'Hard coded for testing
    > >TheYear = "AllExpenses2004"
    > >
    > >Set connect = Server.CreateObject("ADODB.Connection")
    > >Set rs = Server.CreateOBject("ADODB.Recordset")
    > >connect.ConnectionString = _
    > > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > > "Data Source=" & sDataSource
    > >connect.Open
    > >
    > >Set connect2 = Server.CreateObject("ADODB.Connection")
    > >Set rs2 = Server.CreateOBject("ADODB.Recordset")
    > >connect2.ConnectionString = _
    > > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > > "Data Source=" & sDataSource
    > >connect2.Open
    > >
    > >Set connect3 = Server.CreateObject("ADODB.Connection")
    > >Set rs3 = Server.CreateOBject("ADODB.Recordset")
    > >connect3.ConnectionString = _
    > > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > > "Data Source=" & sDataSource
    > >connect3.Open
    > >
    > >Set connect4 = Server.CreateObject("ADODB.Connection")
    > >Set rs4 = Server.CreateOBject("ADODB.Recordset")
    > >connect4.ConnectionString = _
    > > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > > "Data Source=" & sDataSource
    > >connect4.Open
    > >%>
    > >
    > ><html>
    > ><head>
    > ></head>
    > ><body>
    > ><table border="0">
    > ><%
    > > sSQL3 = "SELECT Distinct Organization" & _
    > > " FROM " & TheYear & _
    > > " WHERE MonthPD = '" & ThePD & "'" & _
    > > " ORDER BY Organization"
    > > set rs3 = Connect.Execute(sSQL3)
    > >%>
    > ><% Do until rs3.eof %>
    > >
    > ><%
    > > sSQL2 = "SELECT Distinct Fund" & _
    > > " FROM " & TheYear & _
    > > " WHERE MonthPD = '" & ThePD & "'" & _
    > > " ORDER BY Fund"
    > > set rs2 = Connect.Execute(sSQL2)
    > >%>
    > ><% Do until rs2.eof %>
    > >
    > ><%
    > > sSQL4 = "SELECT Distinct Fund, Organization, Program" & _
    > > " FROM " & TheYear & _
    > > " WHERE MonthPD = '" & ThePD & "'" & _
    > > " ORDER BY Organization, Fund, Program"
    > > set rs4 = Connect.Execute(sSQL4)
    > >%>
    > ><% Do until rs4.eof %>
    > >
    > ><tr>
    > ><td>Organization</td>
    > ><td colspan="11"><%=rs4("Organization")%></td>
    > ></tr>
    > ><tr>
    > ><td>Fund</td>
    > ><td colspan="11"><%=rs4("Fund")%></td>
    > ></tr>
    > ><tr>
    > ><td>Program</td>
    > ><td colspan="11"><%=rs4("Program")%></td>
    > ></tr>
    > ><tr>
    > > <td>Account</td>
    > > <td>Month PD</td>
    > > <td>PD Amount</td>
    > ></tr>
    > >
    > ><%
    > >TheFund = rs4("Fund")
    > >TheOrg = rs4("Organization")
    > >TheProg = rs4("Program")
    > >
    > >sSQL = "SELECT *" & _
    > > " FROM " & TheYear & _
    > > " WHERE MonthPD = '" & ThePD & "'" & _
    > > " and Fund = '" & TheFund & "'"
    > > set rs = Connect.Execute(sSQL)
    > >
    > >Do until rs.eof
    > >%>
    > ><tr bgcolor="<%=sBackgroundColor%>">
    > > <td class="text"><%=rs("Account")%></td>
    > > <td class="text"><%=rs("MonthPd")%></td>
    > > <td class="text"><%=rs("PdAmount")%></td>
    > ></tr>
    > >
    > ><% End If
    > >rs.MoveNext
    > >Loop %>
    > ></tr>
    > ><tr><tdnbsp;</td></tr>
    > >
    > ><% rs4.MoveNext
    > >Loop %>
    > ><% rs2.MoveNext
    > >Loop %>
    > ><% rs3.MoveNext
    > >Loop %>
    > ></table>
    > ><p>&nbsp;</p>
    > ></body>
    > ></html>
     
    Abby Lee, Jul 31, 2004
    #4
  5. Abby Lee

    Abby Lee Guest

    "Rune" <rune[insert current year]@runevision.com> wrote in message news:<>...
    > I don't know about a better way, but if the problem is that your page times
    > out, then perhaps Response.Flush can help?
    >
    > Try to replace this:
    >
    > <% rs3.MoveNext
    > Loop %>
    >
    > With this:
    >
    > <%
    > Response.Flush
    > rs3.MoveNext
    > Loop
    > %>
    >
    > If it still times out, try to put Response.Flush before rs2.MoveNext
    > instead.
    >
    > Let me know if it helps.
    > (Err, not that I have other ideas if it doesn't help...)
    >
    > Best regards,
    > Rune


    Thanks Rune, I will try this on Monday.
     
    Abby Lee, Jul 31, 2004
    #5
  6. Abby Lee

    Abby Lee Guest

    "Rune" <rune[insert current year]@runevision.com> wrote in message news:<>...
    > I don't know about a better way, but if the problem is that your page times
    > out, then perhaps Response.Flush can help?
    >
    > Try to replace this:
    >
    > <% rs3.MoveNext
    > Loop %>
    >
    > With this:
    >
    > <%
    > Response.Flush
    > rs3.MoveNext
    > Loop
    > %>
    >
    > If it still times out, try to put Response.Flush before rs2.MoveNext
    > instead.
    >
    > Let me know if it helps.
    > (Err, not that I have other ideas if it doesn't help...)
    >
    > Best regards,
    > Rune


    Ok this gets me closer...I put a Response.Flush before each
    rs#.MoveNext
    However I still get timed out after only part of the data is printed.
    Any other ideas?


    Active Server Pages error 'ASP 0113'
    Script timed out

    /eforms/reconcile/reconcile.asp

    The maximum amount of time for a script to execute was exceeded. You
    can change this limit by specifying a new value for the property
    Server.ScriptTimeout or by changing the value in the IIS
    administration tools.
     
    Abby Lee, Aug 2, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?bWF2cmlja18xMDE=?=

    SetAuthCookie works some times and fails some times?

    =?Utf-8?B?bWF2cmlja18xMDE=?=, Mar 23, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    567
    =?Utf-8?B?bWF2cmlja18xMDE=?=
    Mar 23, 2006
  2. =?Utf-8?B?bWF2cmlja18xMDE=?=

    Forms Authentication Fails some times and not some times???

    =?Utf-8?B?bWF2cmlja18xMDE=?=, Mar 28, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    515
    =?Utf-8?B?bWF2cmlja18xMDE=?=
    Mar 28, 2006
  3. =?ISO-8859-2?Q?Martin_MOKREJ=A9?=
    Replies:
    22
    Views:
    604
    =?windows-1252?Q?Martin_MOKREJ=8A?=
    Mar 9, 2005
  4. Replies:
    5
    Views:
    223
    Randy Kobes
    Oct 12, 2005
  5. Replies:
    2
    Views:
    69
    Mark H Harris
    May 13, 2014
Loading...

Share This Page