Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

loop and movenext problem?

Status
Not open for further replies.
jimwarren

jimwarren

ISP
Apr 12, 2003
7
0
0
US
Hi!
I have a page that selects one or more "sourcecodes" from one table, then uses that recordset in another select ....

the queries are working, but when there is more than one sourcecode, my output is just the last one repeated ....
can anyone see what i might be doing wrong with the loops?
or ??
thanks,
(code below...............)

SQL= "SELECT listinfo.SourceCode, listinfo.BreakEven, listinfo.MailingDate, listinfo.Category " &_
" FROM listinfo " &_
" WHERE " &_
"MailingDate = #" & strMDate & "#" & " And Category = '" & strCategory & "' " '&_
'"Order BY listinfo.sourcecode"

set RS1 = objConn.execute(SQL)

strCode = rs1("SourceCode")
strBreak = rs1("BreakEven")
strMDate = rs1("MailingDate")
strCategory = rs1("Category")

'write out table header...............
Response.Write &quot;<html>&quot; & vbCrLf & &quot;<head><title> Reports</title>&quot; & vbCrLf &_
&quot;<style>&quot; & vbCrLf &_
&quot; BODY { background: #D8D8BF; color: black; font-size: 9pt; font-family: arial, sans-serif; }&quot; & vbCrLf &_
&quot; TABLE { color: black; font-size: 9pt; font-family: arial, sans-serif; }&quot; & vbCrLf &_
&quot; TH { color: black; font-size: 9pt; font-family: arial, sans-serif; }&quot; & vbCrLf &_
&quot; TD { color: black; font-size: 9pt; font-family: arial, sans-serif; }&quot; & vbCrLf &_
&quot; P { color: black; font-size: 9pt; font-family: arial, sans-serif; }&quot; & vbCrL &_
&quot; .success { color: #009900; font-weight: bold; }&quot; & vbCrLf &_
&quot; .error { color: #FF3333; font-weight: bold; }&quot; & vbCrLf &_
&quot;</style>&quot; & vbCrLf & &quot;</head>&quot; & vbCrLf
Response.Write &quot;<body>&quot; & vbCrLf
Response.Write &quot;<a href=&quot;&quot;SCj.asp&quot;&quot;><< Select Menu</a></p><center>&quot; & vbCrLf
Response.Write &quot;<font size=+1>Mailing Date: &quot; & strMDate & &quot; and Category: &quot; & strCategory & &quot;</h3></font><br>&quot; & vbCrLf

'display table head
Response.Write &quot;<table BORDER='0' cellspacing='1' cellpadding='2' style='font-family: Arial; font-size: 8pt' bgcolor='#dddddd'><tr>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b>Source code</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b>Break Even</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b># Sales</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b># Returns</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b># Refunds</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b># Net Sales</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b>% Success</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b>% Returns</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b>% Refunds</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b>$ Sales</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b>$ Refunds</b></td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;><b>$ Net Sales</b></td>&quot; & vbCrLf
Response.Write &quot;</tr>&quot; & vbCrLf





do until rs1.eof


'*************************************************

If Len(Request(&quot;Page&quot;)) > 0 Then
intPage = Request(&quot;Page&quot;)
lngFirstRec = (intPage-1) * 100 + 1
Else
intPage = 1
lngFirstRec = 1
End If

OpenDB
Set oRSp=Server.CreateObject(&quot;ADODB.Recordset&quot;)
oRSp.LockType = 1 'adLockReadOnly
oRSp.CursorType = 1 'adOpenKeyset

strSourceCode = strCode

'code - specific SQL
strSQL = &quot;SELECT DISTINCT Ordr.Order_Number, Ordr.Order_Date, Cust.First_Name, Cust.Last_Name, &quot; &_
&quot;Cust.State, Paym.Paid_Amount, &quot; &_
&quot;Retn.Return_Date, Retn.Sale_Amount, Rfnd.Refund_Date, Rfnd.Refund_Amount &quot; &_
&quot;FROM (((Ordr INNER JOIN Cust ON Ordr.Order_Number = Cust.Order_Number) &quot; &_
&quot;INNER JOIN Paym ON Paym.Order_Number= Cust.Order_Number) &quot; &_
&quot;LEFT JOIN Rfnd ON Ordr.Order_Number = Rfnd.Order_Number) &quot; &_
&quot;LEFT JOIN Retn ON Rfnd.Order_Number = Retn.Order_Number &quot; &_
&quot;WHERE Cust.Record_Type='CUST' &quot;
'full retrieval SQL for comparative % calculation
'strSQLAll = strSQL
'use selected source code if specified, otherwise retrieve all
If Len(Trim(strSourceCode)) > 0 Then
strSQL = strSQL & &quot;AND Ordr.Source_Code LIKE '&quot; & strSourceCode & &quot;%' &quot;
End If

'Run the search
oRSp.Open strSQL, objConn

curPayTotal = 0
curRefundTotal = 0
If Not oRSp.BOF Then
blnFound = True
oRSp.MoveFirst
End If

' count up all the sales for this code
Do Until oRSp.EOF
If oRSp(&quot;Paid_Amount&quot;) > 0 Then
curPayTotal = curPayTotal + oRSp(&quot;Paid_Amount&quot;)
lngSaleTotal = lngSaleTotal + 1
End If
If oRSp(&quot;Refund_Amount&quot;) > 0 Then
curRefundTotal = curRefundTotal + oRSp(&quot;Refund_Amount&quot;)
lngRefundTotal = lngRefundTotal + 1
End If
If oRSp(&quot;Sale_Amount&quot;) > 0 Then
lngReturnTotal = lngReturnTotal + 1
End If
oRSp.MoveNext
Loop
oRSp.Close

strDisplaySourceCode = strSourceCode

'begin page
'display per-code content
Response.Write &quot;<tr>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;>&quot; & strDisplaySourceCode & &quot; </td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & strBreak & &quot;</td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & lngSaleTotal & &quot;</td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & lngReturnTotal & &quot;</td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & lngRefundTotal & &quot;</td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & FormatNumber(lngSaleTotal-lngRefundTotal,0) & &quot;</td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & FormatNumber(lngSaleTotal-lngRefundTotal,0) & &quot;</td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & FormatPercent(lngReturnTotal/lngSaleTotal) & &quot;</td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & FormatPercent(lngRefundTotal/lngSaleTotal) & &quot;</td>&quot;
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & FormatCurrency(curPayTotal,2) & &quot;</td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & FormatCurrency(curRefundTotal,2) & &quot;</td>&quot; & vbCrLf
Response.Write &quot;<td bgcolor=&quot;&quot;white&quot;&quot; align=&quot;&quot;middle&quot;&quot;> &quot; & FormatCurrency(curPayTotal-curRefundTotal,2) & &quot;</td>&quot; & vbCrLf



rs1.movenext
loop

'end table
Response.Write &quot;</table>&quot; & vbCrLf
'end the page
Response.Write &quot;</center></body>&quot; & vbCrLf & &quot;</html>&quot; & vbCrLf

rs1.close
set rs1=nothing
%>
 
Sort by date Sort by votes
>>the queries are working, but when there is more than one sourcecode, my output is just the last one repeated ....
can anyone see what i might be doing wrong with the loops?


bit more clear please?

Known is handfull, Unknown is worldfull
 
Upvote 0 Downvote
you bet, the result table/output comes out like this:
Code: 
Source code Break Even # Sales .........
PIM-1F        20.5         5   etc........   
PIM-1F        20.5         10  etc.........    

but it should be like this......(there are two different source codes in the first recordset...)

Source code Break Even # Sales .........
OFR-1F        18.5         7   etc........      
PIM-1F        20.5         5   etc.........
 
Upvote 0 Downvote
this seems to be a sql query error...

did u try running ur query in ur database?


Known is handfull, Unknown is worldfull
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
67
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
76
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
62
leifoet
leifoet
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
76
JScannell
JScannell
leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
192
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top