Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > so many queries within queries I'm confused

Reply
Thread Tools

so many queries within queries I'm confused

 
 
Abby Lee
Guest
Posts: n/a
 
      08-04-2004
1st sorry about leangth...couldn't really cut anymore.

I want the output to be
Organization 320000
Fund 100004
Program 777777
Account1 7234.55
Account2 -347.99
Account3 823.55

Program 888888
Account1 8745.99
Account2 -9878.33
.....on and on.

<b>What I can get to work is</b>
Organization 320000
Fund 100004
Program 777777
Account1 7234.55

Organization 320000
Fund 100004
Program 777777
Account2 -347.99
.........on and on.

The code below looks right but the Organization, Fund, Program numbers
never change when they should. I've been looking at this for two
days...help!

<table border="0">
<%
sSQL3 = "SELECT Distinct Organization" & _
" FROM " & TheYear & _
" WHERE MonthPD = '" & ThePD & "'"
set rs3 = Connect.Execute(sSQL3)
%>
<tr>
<td> <font color="#0000FF">Organization </font></td>
<td colspan="10"><%=rs3("Organization")%></td>
</tr>
<% Do until rs3.eof %>
<%
sSQL2 = "SELECT Distinct Fund" & _
" FROM " & TheYear & _
" WHERE MonthPD = '" & ThePD & "'"
set rs2 = Connect.Execute(sSQL2)
%>
<tr>
<td> <font color="#0000FF">Fund </font></td>
<td colspan="10"><%=rs2("Fund")%></td>
</tr>
<% Do until rs2.eof %>
<%
sSQL4 = "SELECT Distinct Program, Fund, Organization" & _
" FROM " & TheYear & _
" WHERE MonthPD = '" & ThePD & "'" & _
" ORDER BY Organization, Fund, Program"
set rs4 = Connect.Execute(sSQL4)
TheProg = rs4("Program")
%>
<tr>
<td> <font color="#0000FF">Program</font> </td>
<td colspan="10"><%=TheProg%></td>
</tr>
<% Do until rs4.eof %>
<tr>
<td>Account</div></td>
<td><%=TheAct%> </td>
<td><%=TotalAmt%></td>
</tr>
<%
TheFund = rs4("Fund")
TheOrg = rs4("Organization")
TheProg = rs4("Program")
SQL = "SELECT *" & _
"FROM " & TheYear & _
"WHERE MonthPD = '" & ThePD & "'" & _
" and Organization = '" & TheOrg & "'" & _
" and Fund = '" & TheFund & "'" & _
" and Program = '" & TheProg & "'" & _
" ORDER BY Account"
set rs = Connect.Execute(sSQL)
TotalAmt=0
Do until rs.eof
TheAct = rs("Account")
TotalAmt = TotalAmt + rs("PdAmount")

Response.Flush
rs.MoveNext
Loop

Response.Flush
rs4.MoveNext
Loop

Response.Flush
rs2.MoveNext
Loop

Response.Flush
rs3.MoveNext
Loop %>
</table>
 
Reply With Quote
 
 
 
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      08-04-2004
You can do this with a single recordset (see http://www.aspfaq.com/2241 ).
I'll leave the HTML pretty-ifying up to you.

<%
sql = "SELECT Organization, Fund, Program, Account, PdAmount" & _
"FROM " & TheYear & _
" ORDER BY Organization, Fund, Program, Account"

cOrg = "": cFund = "": cProg = "": cAcc = ""

set rs = conn.execute(sql)

do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)

if nOrg <> cOrg then ' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg
end if

if nFund <> cFund then ' new fund
response.write "<br>Fund:" & nFund
cFund = nFund
end if

if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if

response.write "<br>" & nAcc & ":" & nPd

rs.movenext
loop
%>

Are your tables really named by year? This is horrible design, you really
should have ONE table and have a column for year. You're really messing up
the whole idea of relational design and entity modeling...

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Abby Lee" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 1st sorry about leangth...couldn't really cut anymore.
>
> I want the output to be
> Organization 320000
> Fund 100004
> Program 777777
> Account1 7234.55
> Account2 -347.99
> Account3 823.55
>
> Program 888888
> Account1 8745.99
> Account2 -9878.33
> ....on and on.
>
> <b>What I can get to work is</b>
> Organization 320000
> Fund 100004
> Program 777777
> Account1 7234.55
>
> Organization 320000
> Fund 100004
> Program 777777
> Account2 -347.99
> ........on and on.
>
> The code below looks right but the Organization, Fund, Program numbers
> never change when they should. I've been looking at this for two
> days...help!
>
> <table border="0">
> <%
> sSQL3 = "SELECT Distinct Organization" & _
> " FROM " & TheYear & _
> " WHERE MonthPD = '" & ThePD & "'"
> set rs3 = Connect.Execute(sSQL3)
> %>
> <tr>
> <td> <font color="#0000FF">Organization </font></td>
> <td colspan="10"><%=rs3("Organization")%></td>
> </tr>
> <% Do until rs3.eof %>
> <%
> sSQL2 = "SELECT Distinct Fund" & _
> " FROM " & TheYear & _
> " WHERE MonthPD = '" & ThePD & "'"
> set rs2 = Connect.Execute(sSQL2)
> %>
> <tr>
> <td> <font color="#0000FF">Fund </font></td>
> <td colspan="10"><%=rs2("Fund")%></td>
> </tr>
> <% Do until rs2.eof %>
> <%
> sSQL4 = "SELECT Distinct Program, Fund, Organization" & _
> " FROM " & TheYear & _
> " WHERE MonthPD = '" & ThePD & "'" & _
> " ORDER BY Organization, Fund, Program"
> set rs4 = Connect.Execute(sSQL4)
> TheProg = rs4("Program")
> %>
> <tr>
> <td> <font color="#0000FF">Program</font> </td>
> <td colspan="10"><%=TheProg%></td>
> </tr>
> <% Do until rs4.eof %>
> <tr>
> <td>Account</div></td>
> <td><%=TheAct%> </td>
> <td><%=TotalAmt%></td>
> </tr>
> <%
> TheFund = rs4("Fund")
> TheOrg = rs4("Organization")
> TheProg = rs4("Program")
> SQL = "SELECT *" & _
> "FROM " & TheYear & _
> "WHERE MonthPD = '" & ThePD & "'" & _
> " and Organization = '" & TheOrg & "'" & _
> " and Fund = '" & TheFund & "'" & _
> " and Program = '" & TheProg & "'" & _
> " ORDER BY Account"
> set rs = Connect.Execute(sSQL)
> TotalAmt=0
> Do until rs.eof
> TheAct = rs("Account")
> TotalAmt = TotalAmt + rs("PdAmount")
>
> Response.Flush
> rs.MoveNext
> Loop
>
> Response.Flush
> rs4.MoveNext
> Loop
>
> Response.Flush
> rs2.MoveNext
> Loop
>
> Response.Flush
> rs3.MoveNext
> Loop %>
> </table>



 
Reply With Quote
 
 
 
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      08-04-2004
Well, there's a little bit of logic to fit here, e.g. if you have two orgs
with the same program or two programs with the same account, etc. You can
solve that by setting children to "" whenever you hit a new tier, e.g.

<%
sql = "SELECT Organization, Fund, Program, Account, PdAmount" & _
"FROM " & TheYear & _
" ORDER BY Organization, Fund, Program, Account"

cOrg = "": cFund = "": cProg = "": cAcc = ""

set rs = conn.execute(sql)

do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)

if nOrg <> cOrg then ' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg: cFund="": cProg=""
end if

if nFund <> cFund then ' new fund
response.write "<br>Fund:" & nFund
cFund = nFund: cProg=""
end if

if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if

response.write "<br>" & nAcc & ":" & nPd

rs.movenext
loop
%>

--
http://www.aspfaq.com/
(Reverse address to reply.)


 
Reply With Quote
 
Abby Lee
Guest
Posts: n/a
 
      08-05-2004
Aaron,

You are the sweetest thing...I could just kiss you.

Abby
 
Reply With Quote
 
Abby Lee
Guest
Posts: n/a
 
      08-06-2004
"Aaron [SQL Server MVP]" <(E-Mail Removed)> wrote in message news:<(E-Mail Removed)>...
> Well, there's a little bit of logic to fit here, e.g. if you have two orgs
> with the same program or two programs with the same account, etc. You can
> solve that by setting children to "" whenever you hit a new tier, e.g.
>
> <%
> sql = "SELECT Organization, Fund, Program, Account, PdAmount" & _
> "FROM " & TheYear & _
> " ORDER BY Organization, Fund, Program, Account"
>
> cOrg = "": cFund = "": cProg = "": cAcc = ""
>
> set rs = conn.execute(sql)
>
> do while not rs.eof
> nOrg = rs(0): nFund = rs(1): nProg = rs(2)
> nAcc = rs(3): nPd = rs(4)
>
> if nOrg <> cOrg then ' new org
> response.write "<p>Organization:" & nOrg
> cOrg = nOrg: cFund="": cProg=""
> end if
>
> if nFund <> cFund then ' new fund
> response.write "<br>Fund:" & nFund
> cFund = nFund: cProg=""
> end if
>
> if nProg <> cProg then
> response.write "<br>Program:" & nProg
> cProg = nProg
> end if
>
> response.write "<br>" & nAcc & ":" & nPd
>
> rs.movenext
> loop
> %>


I get an Object Required error when I reach the line:
set rs = conn.execute(sql)

Do I needt to make a change to my script that connects to my db?
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
 
Reply With Quote
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      08-06-2004
> I get an Object Required error when I reach the line:
> set rs = conn.execute(sql)
>
> Do I needt to make a change to my script that connects to my db?
> 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


Well, do you want to use the name conn, or the name connect? Pick one.

set conn = CreateObject("ADODB.Connection")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDataSource
conn.open connStr
....
set rs = conn.execute(sql)

or

set connect = CreateObject("ADODB.Connection")
connectStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDataSource
connect.open connStr
....
set rs = connect.execute(sql)

I think you'll find that the most common convention is to use the word conn
(or oConn) as opposed to connect, but you could call it baker or airplane or
bobizumi if you wanted to... just stick to one name.

--
http://www.aspfaq.com/
(Reverse address to reply.)


 
Reply With Quote
 
Abby Lee
Guest
Posts: n/a
 
      08-06-2004
I used this code...which worked...but gave me strange output.
Instead of a list of Accounts with totals I got pages of this...
Organization:Organization
Fund:Fund
Programrogram
Account:MonthPD
Account:MonthPD
Account:MonthPD
Account:MonthPD
Account:MonthPD

<%
sql = "SELECT 'Organization','Fund','Program','Account','MonthPD '" & _
"FROM AllExpenses2004 " & _
" WHERE MonthPD = '0604'" & _
" ORDER BY 'Organization', 'Fund', 'Program', 'Account'"

cOrg = "": cFund = "": cProg = "": cAcc = ""

set rs = connect.execute(sql)


do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)

if nOrg <> cOrg then ' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg: cFund="": cProg=""
end if

if nFund <> cFund then ' new fund
response.write "<br>Fund:" & nFund
cFund = nFund: cProg=""
end if

if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if

response.write "<br>" & nAcc & ":" & nPd

rs.movenext
loop
%>
 
Reply With Quote
 
Bob Lehmann
Guest
Posts: n/a
 
      08-06-2004
Remove the ticks from around your column names.

Bob Lehmann

"Abby Lee" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> I used this code...which worked...but gave me strange output.
> Instead of a list of Accounts with totals I got pages of this...
> Organization:Organization
> Fund:Fund
> Programrogram
> Account:MonthPD
> Account:MonthPD
> Account:MonthPD
> Account:MonthPD
> Account:MonthPD
>
> <%
> sql = "SELECT 'Organization','Fund','Program','Account','MonthPD '" & _
> "FROM AllExpenses2004 " & _
> " WHERE MonthPD = '0604'" & _
> " ORDER BY 'Organization', 'Fund', 'Program', 'Account'"
>
> cOrg = "": cFund = "": cProg = "": cAcc = ""
>
> set rs = connect.execute(sql)
>
>
> do while not rs.eof
> nOrg = rs(0): nFund = rs(1): nProg = rs(2)
> nAcc = rs(3): nPd = rs(4)
>
> if nOrg <> cOrg then ' new org
> response.write "<p>Organization:" & nOrg
> cOrg = nOrg: cFund="": cProg=""
> end if
>
> if nFund <> cFund then ' new fund
> response.write "<br>Fund:" & nFund
> cFund = nFund: cProg=""
> end if
>
> if nProg <> cProg then
> response.write "<br>Program:" & nProg
> cProg = nProg
> end if
>
> response.write "<br>" & nAcc & ":" & nPd
>
> rs.movenext
> loop
> %>



 
Reply With Quote
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      08-06-2004
Why did you change SELECT Organization, ... to SELECT 'Organization', ...

???

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Abby Lee" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> I used this code...which worked...but gave me strange output.
> Instead of a list of Accounts with totals I got pages of this...
> Organization:Organization
> Fund:Fund
> Programrogram
> Account:MonthPD
> Account:MonthPD
> Account:MonthPD
> Account:MonthPD
> Account:MonthPD
>
> <%
> sql = "SELECT 'Organization','Fund','Program','Account','MonthPD '" & _
> "FROM AllExpenses2004 " & _
> " WHERE MonthPD = '0604'" & _
> " ORDER BY 'Organization', 'Fund', 'Program', 'Account'"
>
> cOrg = "": cFund = "": cProg = "": cAcc = ""
>
> set rs = connect.execute(sql)
>
>
> do while not rs.eof
> nOrg = rs(0): nFund = rs(1): nProg = rs(2)
> nAcc = rs(3): nPd = rs(4)
>
> if nOrg <> cOrg then ' new org
> response.write "<p>Organization:" & nOrg
> cOrg = nOrg: cFund="": cProg=""
> end if
>
> if nFund <> cFund then ' new fund
> response.write "<br>Fund:" & nFund
> cFund = nFund: cProg=""
> end if
>
> if nProg <> cProg then
> response.write "<br>Program:" & nProg
> cProg = nProg
> end if
>
> response.write "<br>" & nAcc & ":" & nPd
>
> rs.movenext
> loop
> %>



 
Reply With Quote
 
Abby Lee
Guest
Posts: n/a
 
      08-06-2004
When I use SELECT Organization,
I get the error message:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'OrganizationFROM
AllExpenses2004 WHERE MonthPD = '0604' ORDER BY 'Organization''.

But the SELECT 'Organization', has its own problems.

I finally did this:
SELECT *
and changed this...to fit my db:
nOrg = rs(3): nFund = rs(2): nProg = rs(5)
nAcc = rs(4): nPd = rs(19)

I hate having to grabb all the extra information but this works.
Thanks.

"Aaron [SQL Server MVP]" <(E-Mail Removed)> wrote in message news:<(E-Mail Removed)>...
> Why did you change SELECT Organization, ... to SELECT 'Organization', ...
>
> ???
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Confused about logger config from within Python (3) andrew cooke Python 10 12-30-2012 01:08 AM
Why are so many people confused about "Enterprise" software? Kyle Schmitt Ruby 9 11-14-2007 03:07 PM
Adding Queries within the DataSet Designer (VS2005) daokfella ASP .Net 0 02-27-2007 07:58 PM
xslt queries in xml to SQL queries Ian Roddis Python 3 02-26-2006 06:49 PM
Confused about how many exams tod MCAD 1 02-04-2004 02:29 AM



Advertisments