|
| |
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
 |
Adding values to array question - 4/19/2005 10:50:25
I have the following script set up<%
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=10873) ORDER BY SEQ1 DESC"
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
END IF
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("</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("</tr>" & VbCrLf)
NEXT
Response.write("</table>" & VbCrLf)
response.write("<br><br>")
rsStoreIDs.close
set rsStoreIDs=nothing
conntemp.close
set conntemp=nothing
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)&"')"
set rsMaxEvalDates=conntemp1.execute(sqlMaxEvalDates )
on error resume next
IF rsMaxEvalDates.eof THEN
response.write ("ERROR - Problem with using: " & sqlMaxEvalDates )
rsMaxEvalDates.close
set rsMaxEvalDates=nothing
conntemp1.close
set conntemp1=nothing
ELSE
arrayMaxEvalDates=rsMaxEvalDates.getrows
END IF
next
response.write(arrayMaxEvalDates(0,0))
rsMaxEvalDates.close
set rsMaxEvalDates=nothing
conntemp1.close
set conntemp1=nothing
%>
The first portion of the script works perfectly, it displays stuff and orders it fine. The next portion of the script doen't error out; however, it only assigns the array one value, meaning on the response.write(arrayMaxEvalDate(0,0)) it prints correctly, but doesn't print any other values as the value of the array is only one record..it isn't looping through correctly.
I wish to have it loop something like this:
for r=0 to ubound(arrayStoreIDs,2)
run sql statement
arrayMaxEvalDate(0,r)=results of sql statement so that when I response.write arrayMaxEvalDate(0,5) it will display the data for the 5th row of getrows.
It is looping, but just assinging the last row of data into the array...how do I change that? Thanks!
|
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Adding values to array question - 4/19/2005 11:24:40
quote:
arrayMaxEvalDates=rsMaxEvalDates.getrows
Are you trying to update the previous array or build a new array?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/19/2005 11:27:17
Build a new array with the max eval date of a store id pulled from the previous array
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Adding values to array question - 4/19/2005 11:58:43
quote:
Build a new array with the max eval date of a store id pulled from the previous array
But if it's only 1 field from 1 record why the array?
If you pull all MaxDates, then I'd pop 'em into an array and loop to check for matches.
If you're pulling just 1, why not use rsMaxEvalDates(0)?
However, if I might suggest, unless you're only looping thru a few records, you'll put (IMO) unnecessary overhead on your DB. Why not pull all MaxDates at once then loop thru the second array (MaxDates) matching store IDs?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/19/2005 12:06:43
there are approximately 110000 records in the database to loop through if I do it the second way, so I think I need to pull the store id's and then find their max eval dates.
I guess I dont' need an array, I can use the rsMaxEvalDate and use the key to display them...
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)&"')"
set rsMaxEvalDates=conntemp1.execute(sqlMaxEvalDates )
on error resume next
IF rsMaxEvalDates.eof THEN
response.write ("ERROR - Problem with using: " & sqlMaxEvalDates )
rsMaxEvalDates.close
set rsMaxEvalDates=nothing
conntemp1.close
set conntemp1=nothing
ELSE
rsMaxEvalDates(r).getrows
END IF
next
response.write(rsMaxEvalDates(0))
that again only puts the last record of the previous array into the rsMaxEvalDates as if I change it to display anything else but rsMaxEvalDates(0) then it is blank.
Its not adding the records...its running the query, assinging the value, then looping back and overwriting the value it previously assigned...I need it to add the value. What am I doing wrong?
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Adding values to array question - 4/19/2005 13:05:52
quote:
I need it to add the value. What am I doing wrong?
I don't think you're doing anything wrong per se.
How about this:
<%
DIM myMaxDateArray(ubound(arrayStoreIDs, 2))
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)&"')"
set rsMaxEvalDates=conntemp1.execute(sqlMaxEvalDates )
on error resume next
IF rsMaxEvalDates.eof THEN
myMaxDateArray(i) = ""
ELSE
myMaxDateArray(i) = rsMaxEvalDates(0)
END IF
next
rsMaxEvalDates.close
set rsMaxEvalDates=nothing
conntemp1.close
set conntemp1=nothing
%>
Then call your Max Date by using myMaxDateArray(i) in the same loop that you're displaying the Store info. Haven't tested the syntax at all but I think my theory is sound.
1. Build a second array as big as the first.
2. Store Max Date for each row in first array in corresponding row of second array.
3. Call second array item when calling first with same ordinal number.
Does that make any sense? Any help?
EDIT:
PS: I still think that with any large number of records you'll do a lot of DB thrashing. However, I did kinda' change your code to support 1 connection open for all that.
Have you ever considered an IN statement instead using all the ID's of the first found set?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/19/2005 13:17:59
The theory is pretty much exactly what I was thinking...make the first array, build a second array with its key value same to the key value of the first (so I can call record 5 and it will be same info in a sense of other array on record 5).
the code above produces the following error, however:
Error Type:
Microsoft VBScript compilation (0x800A0402)
Expected integer constant
/shopdatestest.asp, line 42, column 19
DIM myMaxDateArray(ubound(arrayStoreIDs, 2))
------------------^
you can view it in action at http://www.msushoppers.com:81/shopdatestest.asp if you choose.
the whole code is <%
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=10873) ORDER BY SEQ1 DESC"
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
END IF
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("</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("</tr>" & VbCrLf)
NEXT
Response.write("</table>" & VbCrLf)
response.write("<br><br>")
rsStoreIDs.close
set rsStoreIDs=nothing
conntemp.close
set conntemp=nothing
DIM myMaxDateArray(ubound(arrayStoreIDs, 2))
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)&"')"
set rsMaxEvalDates=conntemp1.execute(sqlMaxEvalDates )
on error resume next
IF rsMaxEvalDates.eof THEN
myMaxDateArray(i) = ""
ELSE
myMaxDateArray(i) = rsMaxEvalDates(0)
END IF
next
rsMaxEvalDates.close
set rsMaxEvalDates=nothing
conntemp1.close
set conntemp1=nothing
%>
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Adding values to array question - 4/19/2005 13:23:55
quote:
arrayStoreIDs
A quick thing I'd try:
1. At the very top of this code, DIM a variable to store the ubound value:
DIM myStoreIDCount
2. Right below this line:
arrayStoreIDs=rsStoreIDs.getrows
Put this line:
myStoreIDCount = ubound(arrayStoreIDs,2)
3. Change the line:
DIM myMaxDateArray(ubound(arrayStoreIDs, 2))
to:
DIM myMaxDateArray(myStoreIDCount)
That any better?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/19/2005 13:25:04
Also, dwayne just got me an sql statement that works with what I sort of wish to do, its statement is:
myStoreIDs = "SELECT Q1.[KFC ID], Q1.[TB ID], Q1.MaxEvalDate, Q2.SEQ1 FROM (SELECT SEQ1, [KFC ID], [TB ID] FROM [MASTER ROUTE TABLE 1998] WHERE RT1=10873) AS Q2 INNER JOIN (SELECT [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] WHERE [MASTER ROUTE TABLE 1998].[KFC ID] IN (SELECT [KFC ID] FROM [MASTER ROUTE TABLE 1998] WHERE RT1 = 10873) OR [MASTER ROUTE TABLE 1998].[TB ID] IN (SELECT [TB ID] FROM [MASTER ROUTE TABLE 1998] WHERE RT1 = 10873) GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID]) AS Q1 ON Q1.[KFC ID]=Q2.[KFC ID] OR Q1.[TB ID]=Q2.[TB ID] ORDER BY SEQ1 ASC"
thats a really slow running statement and I hope this will speed it up a tad
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/19/2005 13:38:42
ya, sorry bout that, I posted right after you posted...the sql statement is slow.
Your code gave me the same error...however, I modified it a bit to see what the problem is:
<%
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=10873) ORDER BY SEQ1 DESC"
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
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("</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("</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>")
DIM myMaxDateArray(67)
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)&"')"
set rsMaxEvalDates=conntemp1.execute(sqlMaxEvalDates )
on error resume next
IF rsMaxEvalDates.eof THEN
myMaxDateArray(i) = ""
ELSE
myMaxDateArray(i) = rsMaxEvalDates(0)
END IF
next
for i=0 to myStoreIDCount
response.write(myMaxDateArray(i)&"<BR>")
next
rsMaxEvalDates.close
set rsMaxEvalDates=nothing
conntemp1.close
set conntemp1=nothing
%>
I found that the value is 67, so I made the arrray 67. however, it still doesn't work correctly.
for i=0 to myStoreIDCount
response.write(myMaxDateArray(i)&"<BR>")
next
that only displays one record...the record it displays is the MaxEvalDate for the last record in the arrayStoreIDs.
if you view www.msushoppers.com:81/shopdates.asp you will see the results of the dates and what they should be. then, you can compare them to www.msushoppers.com:81/shopdatestest.asp and see what is happening.
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/19/2005 14:56:42
Ok, I got it to work with the KFC ID (had to replace i with r :)
so, that works correctly. Now, I wish to place the TB ID max eval date into the same array output...so, I tried the following on my sql code:
sqlMaxEvalDates = "SELECT MAX(EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] WHERE ([KFC ID]='"&arrayStoreIDs(0,r)&"') OR ([TB ID]='"&arrayStoreIDs(1,r)&"')"
and it just displays nothing...if, howver, I remove the OR clause, then it works correctly...it displays the max eval date of the KFC ID's...Can I do this or will I have to create another array to store the tb id maxeval dates in?
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Adding values to array question - 4/19/2005 15:17:04
Have you tried Response.write(sqlMaxEvalDates) to be sure you're getting the SQL you expect?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/19/2005 15:21:34
hehe, i looked at that many times and didn't see anything wrong..until I realized TB Id is numeric and KFC ID is text...that solved my problem !
Now, still stuck on defining the size of the array...if I manually put in a number, then it works. If I put in
DIM myMaxDateArray(myStoreIDCount) then I get that error saying its expecting an integer.
current 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=10873) ORDER BY SEQ1 DESC"
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(67)
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
For Each F In rsMaxEvalDates.Fields
Response.write("<th>" & F.Name & "</th>" & VbCrLf)
Next
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("</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
%>
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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.
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Adding values to array question - 4/19/2005 16:13:51
quote:
DIM myMaxDateArray(myStoreIDCount)
Can you try this:
DIM myMaxDateArray(cint(myStoreIDCount))
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/19/2005 16:18:43
same error :(
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/20/2005 10:38:25
Ok, I fixed the problem with the sql query, I replaced it with a simple if then statement and it works fine...here is the current code:
<%
DIM intStoreIDCount
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/apps/msu_database/secure all location data.mdb"
set connStoreIDs=server.createobject("adodb.connection")
set connMaxKFCEvalDates=server.createobject("adodb.connection")
set connMaxTBEvalDates=server.createobject("adodb.connection")
connStoreIDs.open myDSN
connMaxKFCEvalDates.open myDSN
connMaxTBEvalDates.open myDSN
sqlStoreIDs = "SELECT [KFC ID], [TB ID], SEQ1, RT1 FROM [MASTER ROUTE TABLE 1998] WHERE (RT1=10837) ORDER BY [TB ID] ASC"
set rsStoreIDs=connStoreIDs.execute(sqlStoreIDs)
on error resume next
IF rsStoreIDs.eof THEN
response.write ("ERROR - Problem with using: " & sqlStoreIDs )
ELSE
arrayStoreIDs=rsStoreIDs.getrows
intStoreIDCount = ubound(arrayStoreIDs,2)
END IF
DIM arrayMaxEvalDate(97)
For r = 0 TO ubound(arrayStoreIDs, 2)
sqlMaxKFCEvalDates = "SELECT MAX(EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] WHERE ([KFC ID]='"&arrayStoreIDs(0,r)&"')"
sqlMaxTBEvalDates = "SELECT MAX(EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] WHERE ([TB ID]="&arrayStoreIDs(1,r)&")"
set rsMaxKFCEvalDates=connMaxKFCEvalDates.execute(sqlMaxKFCEvalDates)
on error resume next
IF rsMaxKFCEvalDates.eof THEN
arrayMaxEvalDate(r) = ""
ELSE
IF rsMaxKFCEvalDates(0)<>"" THEN
arrayMaxEvalDate(r) = rsMaxKFCEvalDates(0)
ELSE
set rsMaxTBEvalDates=connMaxTBEvalDates.execute(sqlMaxTBEvalDates)
arrayMaxEvalDate(r) = rsMaxTBEvalDates(0)
END IF
END IF
next
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>")
IF arrayMaxEvalDate(r)<>"" then
Response.write(arrayMaxEvalDate(r)&"</td>" & VbCrLf)
ELSE
response.write("Never Shopped</td>" & VbCrLf)
end if
Response.write("<td>")
IF arrayMaxEvalDate(r)<>"" then
IF now()-arrayMaxEvalDate(r)>=7 then
response.write("Yes")
else
response.write("<b><font color='red'>No</font></b>")
end if
else
Response.write("Yes")
end if
Response.write("</td>" & VbCrLf)
IF now()-arrayMaxEvalDate(r)>=7 then
response.write("<td></td>")
else
Response.write("<td>" & arrayMaxEvalDate(r)+7 & "</td>" & VbCrLf)
end if
Response.write("</tr>" & VbCrLf)
NEXT
Response.write("</table>" & VbCrLf)
response.write("<br><br>")
rsStoreIDs.close
rsMaxKFCEvalDates.close
rsMaxTBEvalDates.close
connStoreIDs.close
connMaxKFCEvalDates.close
connMaxTBEvalDates.close
set rsStoreIDs=nothing
set rsMaxKFCEvalDates=nothing
set rsMaxTBEvalDates=nothing
set connStoreIDs=nothing
set connMaxKFCEvalDates=nothing
set connMaxTBEvalDates=nothing
response.write(myStoreIDCount&"<BR>")
%>
I'm still stuck on the integer of couting the array...I have no idea why that isn't working, otherwise, once that is fixed, this script is good to go :)
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/20/2005 10:52:33
On this line of code:
DIM arrayMaxEvalDate(97)
If I replace it with:
DIM arrayMaxEvalDate( 97)
then I get the same error, so I tried these two combinations:
intStoreIDCount = trim(ubound(arrayStoreIDs,2))
intStoreIDCount = cint(trim(ubound(arrayStoreIDs,2)))
along with
DIM arrayMaxEvalDate(intStoreIDCount)
and I am still receiving the same error, expecint an integer. How else can I force it to be an integer or what the heck is going on? :)
Also, for giggles, I did:
test=97
test="97"
and
arrayMaxEvalDate(test)
and received the same error again..so, it has to be a problem with using a variable in defining the array as the number is set correctly...i'm lost on this one
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Adding values to array question - 4/20/2005 11:01:55
intStoreIDCount = ubound(arrayStoreIDs,2)
END IF
DIM arrayMaxEvalDate(97)
For r = 0 TO ubound(arrayStoreIDs, 2)
Not sure I follow everything that is going on, but if you are creating a variable that contains the upper value of the array, then the above code should be changed to this:
intStoreIDCount = ubound(arrayStoreIDs,2)
END IF
DIM arrayMaxEvalDate(97)
For r = 0 TO intStoreIDCount
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/20/2005 11:39:03
thanks dwayne...its amazing how that works using intStoreIDCount but it doesn't when I do arrayMaxEvalDate(intStoreIDCount).
Also, I noticed by my current code (I think) that if my page errors out, then my connections stay open (it seems). is that true? If it is, I'm assuming I would want to close the connections upon error...where do I put em or is it ok as is?
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/21/2005 16:42:21
I currently ahve it set as:
DIM arrayMaxEvalDate(500)
there will never be a route larger than that. So, it works just fine so far; however, is it bad to create an array of 500 adn only fill it to, say, 50?
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Adding values to array question - 4/21/2005 17:18:40
hrmm, rediming did the trick...anyone know why?
DIM arrayMaxEvalDate()
ReDim arrayMaxEvalDate(intStoreIDCount)
that works where
arrayMaxEvalDate(intStoreIDCount)
didn't...
eitehr way, thanks!!
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 