|
dzirkelb1 -> RE: Adding values to array question (4/19/2005 15:52:55)
|
Ok, ran into sort of a wierd problem with the sql statement...first off, here is the entire code:
<%
DIM myStoreIDCount
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/apps/msu_database/secure all location data.mdb"
sqlStoreIDs = "SELECT [KFC ID], [TB ID], SEQ1, RT1 FROM [MASTER ROUTE TABLE 1998] WHERE (RT1=10848) ORDER BY seq1 ASC"
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rsStoreIDs=conntemp.execute(sqlStoreIDs)
on error resume next
IF rsStoreIDs.eof THEN
response.write ("ERROR - Problem with using: " & sqlStoreIDs )
rsStoreIDs.close
set rsStoreIDs=nothing
conntemp.close
set conntemp=nothing
ELSE
arrayStoreIDs=rsStoreIDs.getrows
myStoreIDCount = ubound(arrayStoreIDs,2)
END IF
DIM myMaxDateArray(195)
set conntemp1=server.createobject("adodb.connection")
conntemp1.open myDSN
For r = 0 TO ubound(arrayStoreIDs, 2)
sqlMaxEvalDates = "SELECT MAX(EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] WHERE ([KFC ID]='"&arrayStoreIDs(0,r)&"') OR ([TB ID]="&arrayStoreIDs(1,r)&")"
set rsMaxEvalDates=conntemp1.execute(sqlMaxEvalDates )
on error resume next
IF rsMaxEvalDates.eof THEN
myMaxDateArray(r) = ""
ELSE
myMaxDateArray(r) = rsMaxEvalDates(0)
END IF
next
Response.write(sqlMaxEvalDates)
Response.write("<table border=1>" & VbCrLf)
Response.write("<tr>")
For Each F In rsStoreIDs.Fields
Response.write("<th>" & F.Name & "</th>" & VbCrLf)
Next
Response.write("<th>MaxEvalDate</th>" & VbCrLf)
Response.write("<th>Clear to Shop?</th>" & VbCrLf)
Response.write("<th>Date Shop is Clear</th>" & VbCrLf)
Response.write("</tr>")
FOR r = 0 TO ubound(arrayStoreIDs,2)
Response.write("<tr>" & VbCrLf)
FOR c = 0 TO ubound(arrayStoreIDs,1)
Response.write("<td>" & arrayStoreIDs(c,r) & "</td>" & VbCrLf)
NEXT
Response.write("<td>" & myMaxDateArray(r) & "</td>" & VbCrLf)
Response.write("<td>")
IF now()-myMaxDateArray(r)>=7 then
response.write("Yes")
else
response.write("No")
end if
Response.write("</td>" & VbCrLf)
Response.write("<td>" & myMaxDateArray(r)+7 & "</td>" & VbCrLf)
Response.write("</tr>" & VbCrLf)
NEXT
Response.write("</table>" & VbCrLf)
response.write("<br><br>")
rsStoreIDs.close
set rsStoreIDs=nothing
conntemp.close
set conntemp=nothing
response.write(myStoreIDCount&"<BR>")
rsMaxEvalDates.close
set rsMaxEvalDates=nothing
conntemp1.close
set conntemp1=nothing
%>
Now, if the first record is a KFC store, then it shows no max eval date UNTIL it loops to and runs into a TB store...then it shows dates for TB and KFC. If I remove the OR clause, then it works correctly for all KFC stores. If I change it to display TB instead of KFC, then it works correctly. If I have the OR clause ikn there, and sort the first statement by TB ID Desc, (meaning first part of array is a TB store) then everything works correctly....this doens't make much sense to me. What should I use instead of the OR clause? Basically, needs to say if KFC ID is blank, then use TB ID and find max eval record, and vise versa...I could use an AND IIF statement, but am really unsure of the syntax on this one..but I'll try to put something together for it.
|
|
|
|
