|
| |
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
 |
RE: Using global date functions - 12/26/2002 14:12:38
<html>
<head>
<meta name=" GENERATOR" content=" Microsoft FrontPage 4.0" >
<title>REC</title>
</head>
<body>
<% DIM myTableNames, myArray , alldata, rstemp, conntemp
myTableNames = " RFI,CDM"
myArray = split(myTableNames," ," )
FOR i = lBound(myArray) to uBound(myArray)
set conntemp=server.createobject(" adodb.connection" )
conntemp.open (" DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath(" ..\fpdb\scribevision.mdb" ))
set rstemp=conntemp.execute(" SELECT * FROM " & myArray(i) & " WHERE (timestamp BETWEEN #" & DateAdd(" n" ,-60,Now()) & " # AND #" & Now() & " #) ORDER BY timestamp DESC"
IF rstemp.eof THEN
response.write " No reports submitted in past hour ..."
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
ELSE
' Now lets grab all the records
alldata=rstemp.getrows
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
numrows=ubound(alldata,2)
FOR rowcounter= 0 TO numrows
response.write " <font size=' 1' face=' Verdana' >REC #: " & alldata(0,rowcounter) & " </font><br>"
NEXT
END IF
NEXT
%>
</body></html>
Try that....
< Message edited by rdouglass -- 12/26/2002 2:13:02 PM >
|
|
|
|
ScribeVision
Posts: 274
From: None
Status: offline
|
RE: Using global date functions - 12/26/2002 14:46:14
Amazing!
It is now working! Now I need to work on formatting the output the way I need it and try expanding it to the other reports.
Is there a limit to the number of tables I can add " myArray" ?
Thanks again for the " above and beyond" assistance on this.
| |
|
|
|
ScribeVision
Posts: 274
From: None
Status: offline
|
RE: Using global date functions - 12/26/2002 15:11:49
A followup issue ...
The results of this code is that only the record id is written out:
response.write " <font size=' 1' face=' Verdana' >REC #: " & alldata(0,rowcounter) & " </font><br>"
How do I tell it to add an additional field, such as the timestamp?
Even more importantly, how can I have it include a report title as well?
Using the table name from MyArray would not be descriptive enough.
I do have a table called Reports in the database that has all of the report titles.
Is there a way to modify the DIM statement to obtain the report title and the table name from the Reports table? That would eliminate the need to enter in the 50+ report tables into the MyArray and it would automatically adjust as reports are added or deleted from the Reports table.
Needless to say, I need to get a better grasp of creating database results outside the DRW box!
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Using global date functions - 12/26/2002 15:47:01
So its working? Have you tried to add more tables to the list? I just want to confirm all our looping stuff is working before we add more to it.
So I browsed to the page you posted earlier and I got 2 lines:
None
REC #: 32
I' ll guess that it is the results from 2 tables. Getting your table list from another table is probably possible, but let' s not try that quite yet. Let' s resolve your display issues first. To outline some of my method and to help you add to it, let me explain a few things about the code (now that it works!!! ). To start with, we have an inner loop and an outer loop. The inner loop handles the fields of each table and the outer loop handles the tables themselves. So if we want to access more fields from each table, we would do it in the section:
FOR rowcounter= 0 TO numrows
response.write " <font size=' 1' face=' Verdana' >REC #: " & alldata(0,rowcounter) & " </font><br>"
NEXT
Here, alldata(0,rowcounter) represents the first field of the ' rowcounter' record. (Remember, arrays are 0-based meaning the first item is 0 and not 1) So, to access our second field, we' d use alldata(1,rowcounter), the third field use alldata(2,rowcounter), etc. To continue this further, if the timestamp field was the 5th field let' s say, you' d grab it something like alldata(4,rowcounter) and use it something like:
response.write " <font size=' 1' face=' Verdana' >REC #: " & alldata(0,rowcounter) & " TIMESTAMP: " & alldata(4,rowcounter) & " </font><br>"
Does that make sense? Great! Now for table items (for instance, if you want headers), you can do something like:
....
FOR i = lBound(myArray) to uBound(myArray)
response.write " <font size=' 1' face=' Verdana' >TABLE:" & myArray(i) & " </font><br>"
set conntemp=server.createobject(" adodb.connection" )
...
See if you can get those 2 things to work and that may give you a better understanding of what we' re doing and where things will display. If we can work this out, then we may tackle linking to a list of tables...
EDIT: Spooky, you were posting while I was typing...
< Message edited by rdouglass -- 12/27/2002 9:09:21 AM >
| |
|
|
|
ScribeVision
Posts: 274
From: None
Status: offline
|
RE: Using global date functions - 12/26/2002 17:30:59
Thanks!
I' m adding a couple more tables to test the looping ....
I do see an issue with using the array - if I wanted to include specific fields such as timestamp for all reports, I would need to modify all the tables to ensure the timestamp field is in the same location for all reports (ie alldata(1,rowcounter) )
Will try out the other information you provided.
Thanks again.
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Using global date functions - 12/27/2002 9:07:15
quote:
I do see an issue with using the array - if I wanted to include specific fields such as timestamp for all reports, I would need to modify all the tables to ensure the timestamp field is in the same location for all reports (ie alldata(1,rowcounter) )
Yes you would. In terms of an ASP script, this one is not very ' intelligent' . Things have to be in the right place in all the tables. We could, in theory that is..., build a function that reviews all the db field names until it finds ' timestamp' . Then pass that array item number to the loop. Adds more complexity, but will do it. Problem is, more complexity = slower response.
Sounds like you' re going to end up with quite a few queries on that page as it is. If it were me, once I got all the tables added and working, I' d attempt to do all my queries with only one (or maybe 2) open connections to the DB. But if performance is not really a problem, then don' t bother with it - it is not a trivial procedure IMO.
I say that with a disclaimer: you could bring the host down if things didn' t work correctly! If the loops fail somehow and you attempt to open 500 connections without closing any, you will most certainly cause problems with it...[:' (][:' (][:' (]
| |
|
|
|
ScribeVision
Posts: 274
From: None
Status: offline
|
RE: Using global date functions - 12/28/2002 13:05:45
Hi,
I think I' ve got it working as needed for now on the tables where I' ve rearranged the fields to be in the same consistent order. I just need to bite the bullet and go through the rest of the tables. That' s fine because it forces me to double check some of the other parameters of the tables any way.
Thanks for all your help. I' m sure I will be addressing a similar topic in the near future because the users are now asking for a query that not only lists all reports added in the past hour, but will list all reports added since the user last logged on to the site!
| |
|
 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¥ 