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

A

Abby Lee

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>
 
J

Jeff Cochran

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?
Click to expand...

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>
Click to expand...
 
R

Rune

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
 
A

Abby Lee

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

Jeff
Click to expand...
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>
Click to expand...
Click to expand...
 
A

Abby Lee

Rune said:
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
Click to expand...

Thanks Rune, I will try this on Monday.
 
A

Abby Lee

Rune said:
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
Click to expand...

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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

so many queries within queries I'm confused 11
Loop results almost right...help. 11
Classic ASP schedule execution on server 1
Client side and server side scripting problem 9

Members online

Total: 46 (members: 3, guests: 43)
Robots: 185

Forum statistics

Threads
473,994
Messages
2,570,223
Members
46,812
Latest member
GracielaWa

Latest Threads

Top