R
RobA
Thanks, that looks really impressive - where there is a will there is a way.
Unfortunately I have restructed the table and adjusted the pages
accordingly, but I can't get the records to update with the different
Scores.
I am either getting an unrecognised column or nvchar numeric error. Losing
my hair and the will to live at the moment.
Really appreciate the input.
Rob
Unfortunately I have restructed the table and adjusted the pages
accordingly, but I can't get the records to update with the different
Scores.
I am either getting an unrecognised column or nvchar numeric error. Losing
my hair and the will to live at the moment.
Really appreciate the input.
Rob
Kevin Spencer said:You could certainly do a UNION. The result of a SELECT statement is a
cursor, or table, if you will. You can perform a UNION of all three
queries in one SELECT statement: I put the following together with the SQL
Server Northwind Database:
SELECT 'Products1' As TableName,
productName As Product,
unitsinstock As Units
FROM PRODUCTS P1
WHERE SupplierID = 8
UNION
SELECT 'Products2' As TableName,
productName As Product,
unitsinstock As Units
FROM PRODUCTS P2
WHERE SupplierID = 9
UNION
SELECT 'Products3' As TableName,
productName As Product,
unitsinstock As Units
FROM PRODUCTS P2
WHERE SupplierID = 9
ORDER BY TableName
--
HTH,
Kevin Spencer
Microsoft MVP
.Net Developer
Complex things are made up of
Lots of simple things.
Stefan B Rusynko said:Kevin
He is using 1 db/table w/ 3 rs calls on it
aSQLstr = "SELECT QID, Score FROM iaVals WHERE QLID= 'A' AND iauID= " &
UID
Set aScrRst = objConn.Execute (aSQLstr)
eSQLstr = "SELECT QID, Score FROM iaVals WHERE QLID= 'E' AND iauID= " &
UID
Set eScrRst = objConn.Execute (aSQLstr)
iSQLstr = "SELECT QID, Score FROM iaVals WHERE QLID= 'I' AND iauID= " &
UID
Set iScrRst = objConn.Execute (aSQLstr)
As I have pointed out to him in my other responses, there is no
correlation of his 3 "sets" being in sync or all having valid data
- I think he needs to go back to basics and get his DB table correct to
simplify the process
(probably by generating the data for all 3 scores in 1 record as aScore,
eScore, iScore rather than using the QLID as a qualifier,
or using QID, if it links the 3 related sets, as a grouping of the data)
--
_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________
| What database are you using?
|
| --
| HTH,
|
| Kevin Spencer
| Microsoft MVP
| .Net Developer
| Complex things are made up of
| Lots of simple things.
|
| | > Thanks,
| >
| > Could you point me to a reference on join or union query which could
| > create the singel recordset.
| >
| > Rob
| >
| >
| > | >> Just a side note: This looks like it could be done with a single
| >> RecordSet, and a JOIN or UNION query.
| >>
| >> --
| >> HTH,
| >>
| >> Kevin Spencer
| >> Microsoft MVP
| >> .Net Developer
| >> Complex things are made up of
| >> Lots of simple things.
| >>
| >> | >>> Before you start, learn about your data being returned by your
nested
| >>> loops
| >>> Just paste this in a page (where you have opened your 3
connections)
| >>>
| >>> <table align="center" border="1" cellpadding="0" cellspacing="0"
| >>> id="table2">
| >>> <tr><th>Info</th><th>Score a</th><th>Score e</th><th>Score
i</th></tr>
| >>> <%
| >>> Do While not aScrRst.EOF
| >>> aTot = aScrRst("Score")
| >>> Do While not eScrRst.EOF
| >>> eTot = eScrRst("Score")
| >>> Do While not iScrRst.EOF
| >>> iTot = iScrRst("Score")
| >>> %>
| >>> <tr><td>Data / Type</td>
| >>> <td><%=aTot & " / " & Typename(aTot)%></td>
| >>> <td><%=eTot & " / " & Typename(eTot)%></td>
| >>> <td><%=iTot & " / " & Typename(iTot)%></td>
| >>> </tr>
| >>> <%
| >>> aScrRst.MoveNext
| >>> eScrRst.MoveNext
| >>> iScrRst.MoveNext
| >>> Loop
| >>> Loop
| >>> Loop
| >>> %>
| >>> </table>
| >>>
| >>> It also appears you need to start by correctly grouping the data
for the
| >>> 3 QLIDs (a,e,i) based on something in your table like QID
| >>> - consider creating 1 recordset and building an array
| >>>
| >>> PS
| >>> Also look at your math - you need to use () to set precedence of
| >>> calculations to get correct results
| >>> SubTotal = aTot + eTot * eTot
| >>> is not the same as
| >>> SubTotal = (aTot + eTot) * eTot
| >>> --
| >>>
| >>> _____________________________________________
| >>> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| >>> "Warning - Using the F1 Key will not break anything!" (-;
| >>> To find the best Newsgroup for FrontPage support see:
| >>>
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
| >>> _____________________________________________
| >>>
| >>>
| >>> | >>> |I did test it, however, I had the same value in each of the fields
so I
| >>> | couldn't really see what you were meaning.
| >>> |
| >>> | I now realise that the I am nutter lol. It is back to the
drawing
| >>> board by
| >>> | the looks of things.
| >>> |
| >>> |
| >>> | Thanks for you help though.
| >>> |
| >>> | Rob
| >>> |
| >>> |
| >>> | | >>> | > Did you really test it w/i each of the loops using the VBscript
| >>> function:
| >>> | > TypeName("yourvarname")
| >>> | > - per my 1st post?
| >>> | >
| >>> | > PS
| >>> | > A zero in some of the fields will give you divide by 0 errors
unless
| >>> you
| >>> | > test for them 1st
| >>> | >
| >>> | > And again your whole logic appears to be flawed since there is
| >>> nothing
| >>> | > controlling the order (or grouping) of the numbers you are
| >>> | > doing math on - the results will be totally random
| >>> | > --
| >>> | >
| >>> | > _____________________________________________
| >>> | > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| >>> | > "Warning - Using the F1 Key will not break anything!" (-;
| >>> | > To find the best Newsgroup for FrontPage support see:
| >>> | >
| >>>
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
| >>> | > _____________________________________________
| >>> | >
| >>> | >
| >>> | > | >>> | > | It is a decimal field in the database that is being pulled
into
| >>> the
| >>> | > | recordset.
| >>> | > |
| >>> | > | There are the same amount of records in each recordset.
| >>> | > |
| >>> | > | I have tested it without the calculations and all of the
recordset
| >>> | > values
| >>> | > | are returned and can be written, just can't do any
calculation.
| >>> | > |
| >>> | > | Rob
| >>> message
| >>> | > | | >>> | > | > What is the dat type of these fields?
| >>> | > | >
| >>> | > | > --
| >>> | > | > HTH,
| >>> | > | >
| >>> | > | > Kevin Spencer
| >>> | > | > Microsoft MVP
| >>> | > | > .Net Developer
| >>> | > | > Complex things are made up of
| >>> | > | > Lots of simple things.
| >>> | > | >
| >>> | > | > | >>> | > | >> Cheers for that Stefan,
| >>> | > | >>
| >>> | > | >> I had been working along the same principle - but I must
be
| >>> getting
| >>> | > | >> something wrong. I have included my script !!!
| >>> | > | >> The db where the data is being drawn from requires a value
for
| >>> each
| >>> | > of
| >>> | > | >> the recordset fields (it is a , so there shouln't be a
return
| >>> of an
| >>> | > EOF,
| >>> | > | >> this page can't be ran until all the values are input.
| >>> (although I am
| >>> | > | >> hoping a value of 0 wont be returned as a NULL).
| >>> | > | >>
| >>> | > | >> I just keep getting a
| >>> | > | >>
| >>> | > | >> Type mismatch
| >>> | > | >> /ia/calc01.asp, line 175
| >>> | > | >>
| >>> | > | >> which seems to happen whenever I attempt any
caluculations.
| >>> | > | >>
| >>> | > | >> <%
| >>> | > | >> Dim aTot, eTot, iTot, pSubTot, aStrengthSubTot, PTotal,
| >>> SubTotal,
| >>> | > | >> Total, aStrengthTotal
| >>> | > | >>
| >>> | > | >> aTot = 0
| >>> | > | >> eTot = 0
| >>> | > | >> iTot = 0
| >>> | > | >> pSubTot = 0
| >>> | > | >> aStrengthSubTot = 0
| >>> | > | >> pTotal = 0
| >>> | > | >> SubTotal = 0
| >>> | > | >> aStrengthTotal = 0
| >>> | > | >>
| >>> | > | >> 'Create Table Header
| >>> | > | >> Response.Write "<Table align=center border=1 cellpadding=0
| >>> | > | >> cellspacing=0>"
| >>> | > | >> Response.Write "<TR ALIGN=CENTER ><TD WIDTH=60><FONT
Face=Arial
| >>> | > | >> Size=3>Score</Font></TD><TD WIDTH=60><FONT Face=Arial
| >>> | > | >> Size=3>Score</TD><TD WIDTH=60 ><FONT Face=Arial
| >>> Size=3>Score</TD><TD
| >>> | > | >> WIDTH=120 ><FONT Face=Arial Size=3>Potential Value</TD<TD
| >>> WIDTH=120
| >>> | > | >> ><TD><FONT Face=Arial Size=3>Calculated Value</TD><TD
| >>> | > WIDTH=120><FONT
| >>> | > | >> Face=Arial Size=3>Asset Strength</TD></TR>"
| >>> | > | >>
| >>> | > | >> ' Start Processes
| >>> | > | >> 'Transfer recordset values to equation values
| >>> | > | >> Do While not aScrRst.EOF
| >>> | > | >> aTot = aScrRst("Score")
| >>> | > | >> Do While not eScrRst.EOF
| >>> | > | >> eTot = eScrRst("Score")
| >>> | > | >> Do While not iScrRst.EOF
| >>> | > | >> iTot = iScrRst("Score")
| >>> | > | >>
| >>> | > | >>
| >>> | > | >>
| >>> | > | >>
| >>> | > | >>
| >>> | > | >> 'The Calculated Value = 1st and 2nd scores added then
| >>> multiplied by
| >>> | > 3rd
| >>> | > | >> (this calculation is where the error is returned)
| >>> | > | >> SubTotal = aTot + eTot * eTot
| >>> | > | >>
| >>> | > | >> 'The Potential Value Sub Total = 3rd score multiplied by
20
| >>> | > mulitplied by
| >>> | > | >> 20
| >>> | > | >> pSubTot = iTot * 20 * 20
| >>> | > | >>
| >>> | > | >> 'The Asset Strength Sub Total = Calculated Value devided
by the
| >>> | > Potential
| >>> | > | >> Value multiplied 100
| >>> | > | >> aStrengthSubTot = SubTotal / pSubTot * 100
| >>> | > | >>
| >>> | > | >> 'Total = The sum of all the SubTotals
| >>> | > | >> Total = Total + Subtotal
| >>> | > | >>
| >>> | > | >> 'Potential Total
| >>> | > | >> pTotal = pTotal + pSubTot
| >>> | > | >>
| >>> | > | >>
| >>> | > | >> Response.Write "<Table align=center border=1 cellpadding=0
| >>> | > | >> cellspacing=0>"
| >>> | > | >>
| >>> | > | >> Response.Write "<TR ALIGN=CENTER>"
| >>> | > | >>
| >>> | > | >> Response.Write "<TD WIDTH=60>"
| >>> | > | >> Response.Write aTot
| >>> | > | >> Response.Write "</TD>"
| >>> | > | >>
| >>> | > | >> Response.Write "<TD WIDTH=60>"
| >>> | > | >> Response.Write eTot
| >>> | > | >> Response.Write "</TD>"
| >>> | > | >>
| >>> | > | >> Response.Write "<TD WIDTH=60>"
| >>> | > | >> Response.Write iTot
| >>> | > | >> Response.Write "</TD>"
| >>> | > | >>
| >>> | > | >> Response.Write "<TD WIDTH=120>"
| >>> | > | >> Response.Write pSubTot
| >>> | > | >> Response.Write "</TD>"
| >>> | > | >>
| >>> | > | >> Response.Write "<TD WIDTH=120>"
| >>> | > | >> Response.Write SubTotal
| >>> | > | >> Response.Write "</TD>"
| >>> | > | >>
| >>> | > | >> Response.Write "<TD WIDTH=120>"
| >>> | > | >> Response.Write aStrength
| >>> | > | >> Response.Write "</TD>"
| >>> | > | >>
| >>> | > | >> Response.Write "</TR>"
| >>> | > | >>
| >>> | > | >> Response.Write "<Table>"
| >>> | > | >>
| >>> | > | >>
| >>> | > | >> aScrRst.MoveNext
| >>> | > | >> eScrRst.MoveNext
| >>> | > | >> iScrRst.MoveNext
| >>> | > | >>
| >>> | > | >>
| >>> | > | >> Loop
| >>> | > | >> Loop
| >>> | > | >> Loop
| >>> | > | >> Response.Write "Total=" & Total
| >>> | > | >> Response.Write "Potential Total= " & PTotal
| >>> | > | >> aStrengthTotal = Total /PTotal * 100
| >>> | > | >> Response.Write "Asset Strength= " & aStrengthTotal
| >>> | > | >>
| >>> | > | >>
| >>> | > | >> %>
message
| >>> | > | >> | >>> | > | >>> Validate your data types in each loop while testing using
say:
| >>> | > | >>>
| >>> | > | >>> <%
| >>> | > | >>> Do While not aScrRst.EOF
| >>> | > | >>> aScore = aScrRst("Score")
| >>> | > | >>> Response.write "aScrRst = " & TypeName("aScore")
&"<br>"
| >>> 'test
| >>> | > line
| >>> | > | >>> Do While not eScrRst.EOF
| >>> | > | >>> eScore = eScrRst("Score")
| >>> | > | >>> Response.write "eScrRst = " & TypeName("eScore")
&"<br>"
| >>> 'test
| >>> | > line
| >>> | > | >>> Do While not iScrRst.EOF
| >>> | > | >>> iScore = iScrRst("Score")
| >>> | > | >>> Response.write "iScrRst = " & TypeName("iScore")
&"<br>"
| >>> 'test
| >>> | > line
| >>> | > | >>> Total = iScore + eScore * aScore
| >>> | > | >>> Response.Write "Total: " & Total & "<br>" 'test line
| >>> | > | >>> GrandTotal = GrandTotal + Total
| >>> | > | >>> iScrRst.MoveNext
| >>> | > | >>> eScrRst.MoveNext
| >>> | > | >>> aScrRst.MoveNext
| >>> | > | >>> Loop
| >>> | > | >>> Loop
| >>> | > | >>> Loop
| >>> | > | >>> Response.Write "GrandTotal: " & GrandTotal
| >>> | > | >>> %>
| >>> | > | >>>
| >>> | > | >>> I suspect 1 of your loops may be returning an EOF since
your
| >>> logic
| >>> | > is
| >>> | > | >>> requiring all 3 recordsets to have 3 "identical" sets of
| >>> | > | >>> qualifying data in each loop (a null value or empty
string in
| >>> any
| >>> | > | >>> recordset will also cause the loops to fail)
| >>> | > | >>>
| >>> | > | >>> PS
| >>> | > | >>> Plus your logic will result in unreliable results since
there
| >>> is no
| >>> | > | >>> attempt to order or group the recordset results in any
| >>> | > | >>> associated way that is consistent or related to iScore,
| >>> eScore, or
| >>> | > | >>> aScore as a group
| >>> | > | >>> So you will be doing math on 3 random numbers (depending
on
| >>> each
| >>> | > | >>> recordset order returned)
| >>> | > | >>> --
| >>> | > | >>>
| >>> | > | >>> _____________________________________________
| >>> | > | >>> SBR @ ENJOY (-: [ Microsoft MVP -
FrontPage ]
| >>> | > | >>> "Warning - Using the F1 Key will not break anything!"
(-;
| >>> | > | >>> To find the best Newsgroup for FrontPage support see:
| >>> | > | >>>
| >>> | >
| >>>
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
| >>> | > | >>> _____________________________________________
| >>> | > | >>>
| >>> | > | >>>
| >>> | > | >>> | >>> | > | >>> | Thanks but I am still getting a type mismatch error.
| >>> | > | >>> | "Kevin Spencer" <[email protected]>
wrote in
| >>> | > message
| >>> | > | >>> | | >>> | > | >>> | > <%
| >>> | > | >>> | > Dim Total
| >>> | > | >>> | > Dim GrandTotal
| >>> | > | >>> | > GrandTotal = 0
| >>> | > | >>> | > Do While not aScrRst.EOF
| >>> | > | >>> | > Do While not eScrRst.EOF
| >>> | > | >>> | > Do While not iScrRst.EOF
| >>> | > | >>> | > Total = iScrRst("Score") + eScrRst("Score") *
| >>> aScrRst("Score")
| >>> | > | >>> | > GrandTotal = GrandTotal + Total
| >>> | > | >>> | >
| >>> | > | >>> | > Response.Write "Total: " & Total
| >>> | > | >>> | >
| >>> | > | >>> | > aScrRst.MoveNext
| >>> | > | >>> | > eScrRst.MoveNext
| >>> | > | >>> | > iScrRst.MoveNext
| >>> | > | >>> | >
| >>> | > | >>> | > Loop
| >>> | > | >>> | > Loop
| >>> | > | >>> | > Loop
| >>> | > | >>> | > Response.Write "GrandTotal: " & GrandTotal
| >>> | > | >>> | >
| >>> | > | >>> | > --
| >>> | > | >>> | > HTH,
| >>> | > | >>> | >
| >>> | > | >>> | > Kevin Spencer
| >>> | > | >>> | > Microsoft MVP
| >>> | > | >>> | > .Net Developer
| >>> | > | >>> | > A watched clock never boils.
| >>> | > | >>> | >
| >>> | > | >>> | >
| >>> | > | >>> | > | >>> | > | >>> | >> Can anyone point me in the direction of some
guidance for
| >>> | > handling
| >>> | > | >>> | >> recordset values.
| >>> | > | >>> | >>
| >>> | > | >>> | >> I am trying to calculate a resultant value (total)
of
| >>> adding 2
| >>> | > | >>> values and
| >>> | > | >>> | >> mulitplying by the third.
| >>> | > | >>> | >>
| >>> | > | >>> | >> Eventually I wanted to get total for the resultant
value
| >>> (all
| >>> | > the
| >>> | > | >>> totals
| >>> | > | >>> | >> added together) for the whole recordset!
| >>> | > | >>> | >>
| >>> | > | >>> | >> <%
| >>> | > | >>> | >> Dim Total
| >>> | > | >>> | >> Do While not aScrRst.EOF
| >>> | > | >>> | >> Do While not eScrRst.EOF
| >>> | > | >>> | >> Do While not iScrRst.EOF
| >>> | > | >>> | >> Total = iScrRst("Score") + eScrRst("Score") *
| >>> aScrRst("Score")
| >>> | > | >>> | >>
| >>> | > | >>> | >> Response.Write "Total"
| >>> | > | >>> | >>
| >>> | > | >>> | >> aScrRst.MoveNext
| >>> | > | >>> | >> eScrRst.MoveNext
| >>> | > | >>> | >> iScrRst.MoveNext
| >>> | > | >>> | >>
| >>> | > | >>> | >>
| >>> | > | >>> | >> Loop
| >>> | > | >>> | >> Loop
| >>> | > | >>> | >> Loop
| >>> | > | >>> | >> %>
| >>> | > | >>> | >>
| >>> | > | >>> | >
| >>> | > | >>> | >
| >>> | > | >>> |
| >>> | > | >>> |
| >>> | > | >>>
| >>> | > | >>>
| >>> | > | >>
| >>> | > | >>
| >>> | > | >
| >>> | > | >
| >>> | > |
| >>> | > |
| >>> | >
| >>> | >
| >>> |
| >>> |
| >>>
| >>>
| >>
| >>
| >
| >
|
|