Solved

ASP recordset results to be displayed in realtime once retrieved from the database

Posted on 2007-11-30
12
225 Views
Last Modified: 2008-02-01
Hi Experts,

Right I dont think the title has explained it well enough so here goes.

Scenario:

I have an asp page which has the date period 1/12/2007 to 5/12/2007

It then goes through a loop (this is an example), for each day in that period and does loads of checks on availability etc and then returns the available rooms for each day (some days there can be upto 20 rooms).

do until date1 = date2

      Select location, owner, cost from table1 where date = date1

            if not rst.EOF then
                  response.write "<h3>" & date1 & "</h3>"
                  response.write "<table>"
                  while not rst.eof
                        response.write "<tr><td>" & rst("location") & "</td><td>" & rst("owner") & "</td><td>" & rst("cost") & "</td></tr>"
                  rst.MOveNext
                  wend
                  response.write "</table>"
            end if
date1 = dateadd("d",1,date1)
loop

This is fine if just doing the one day search as it takes 2 sec max!! But if I was to do a 10 day period it can be very time consuming (over a minute) for
which at this time I would expect users to be leaving the site. I do have a loding image which shows the user that something is happening but I dont think this is enough and so want to bring the results back in real-time.

So I was wondering is there a way where I can have each day results to be displayed as they are found eg.

day1
do search
display table to screen

day2
do search
display table to screen

day3
do search
display table to screen

day4
do search
display table to screen

day5
do search
display table to screen

This way the user can see results being returned as each search has been completed for each day.
0
Comment
Question by:FSIFM
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 54

Accepted Solution

by:
b0lsc0tt earned 500 total points
ID: 20386335
FSIFM,

I will need to look at the code you provided closer and maybe get more code to be specific but what you want to do is done with the Buffer and the Flush method.  The start of the ASP script needs to have the line ...

Response.Buffer = True

That allows you to control the buffer (basically the info the server keeps to send to the browser).  In your script, when you are at a point where the html would allow you to send a working part of a page then use the line ...

Response.Flush

Remember, the browser will only have the content to that point so you should not send partial tables (unless you have specified width, columns, etc) or other "broken" elements.  If you do then the page design will be flawed.

Let me know what additional help you need to use this.  Let me know if you have any questions or need more information.

b0lsc0tt
0
 
LVL 54

Assisted Solution

by:b0lsc0tt
b0lsc0tt earned 500 total points
ID: 20386341
FSIFM,

If I use your pseudo code explanation of the page then you would implement my suggestion like ...

 Response.Buffer = True

day1
do search
display table to screen
Response.Flush

day2
do search
display table to screen
Response.Flush

day3
do search
display table to screen
Response.Flush

day4
do search
display table to screen
Response.Flush

day5
do search
display table to screen
Response.Flush

Let me know if you have a question.

b0lsc0tt
0
 
LVL 4

Author Comment

by:FSIFM
ID: 20386527
Thanks b0lsc0tt, I will give that a try and let you know how i get on.

FSIFM
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 3

Expert Comment

by:xizwyck
ID: 20386630
Try this:

SELECT * FROM [table] WHERE [column] BETWEEN #" & variable1 & "# AND #" & variable1 & "#

Variable1 & variable2 should be in MM/DDD/YYYY format.

Then loop through like you already have.
0
 
LVL 28

Expert Comment

by:sybe
ID: 20387400
> This is fine if just doing the one day search as it takes 2 sec max!

Wow. But maybe you should design a better datamodel or a faster query. Because it should not take that long. Create some indexes maybe? From your description it sounds like some I have build, and I really invested some time in creating a good datamodel and fast queries to do the job.

Alternatively you could build up the page with asynchronous requests. For example write a javascript does XMLHTTPRequests to a page to get the data (in XML or JSON) and lket the javascript create the HTML-elements in the page.
0
 
LVL 4

Author Comment

by:FSIFM
ID: 20391919
sybe, there is nothing wrong with the query as there are loads of different searches required for the search. With a total of 6000+ rooms over europe to check and as I need to search each with their own invetory and setup and close down times, work out costs etc etc this is very quick for a one day search it is just when I need to loop through 5 days then I want to post the results from each one day once they have been found.
0
 
LVL 28

Expert Comment

by:sybe
ID: 20392025
> there are loads of different searches required for the search.

You mean that you are querying many different databases? If so, then forget about my comment.

Because 6000 records in a single database does not sound like very much. And I don't see why a query for 5 days would take longer then a query for 1 day. I mean, it's basically the same criteria you are querying on, only one of the values is different. And also, it seems that an important functionality of the database is to search for available rooms. If so, then it pays to give extra attention to the datamodel to make such queries quick. Not sure how many criteria you are searching on, but make at least sure that each of the criteria you search on, has an index.

So maybe you better do a single query and have a single recordset as result. For each day that you want to display, you can set the Recordset.Filter property to fitler out the results for that day.

So this scheme:

Do a search for day 1 - 5, get the results in a recordset

day1
Recordset.Filter = "day 1"
display table to screen

day2
Recordset.Filter = "day 2"
display table to screen

day3
Recordset.Filter = "day 3"
display table to screen

day4
Recordset.Filter = "day 4"
display table to screen

day5
Recordset.Filter = "day 5"
display table to screen

0
 
LVL 54

Assisted Solution

by:b0lsc0tt
b0lsc0tt earned 500 total points
ID: 20396814
Let me know how Response.Flush works for you or if you have a question.

bol
0
 
LVL 4

Author Comment

by:FSIFM
ID: 20410150
bol,

I have added the Response.Flush but it still builds the page once it has retrieved all records not each individual table.

I have built the complete tables with attributes.

Below is my code


	if not rstRooms.EOF then
		
			Response.write "<h3>" & datStartDate & "</h3>"
			Response.Write "<table width='900px' class='clsTable'>"
			Response.Write "<tr class=clsTableTitle>"
			Response.Write "<td align=left>Room</td>"
			Response.Write "<td align=left>Building</td>"
			Response.Write "<td align=left>Description</td>"
			Response.Write "<td align=left>Seats</td></tr>"
		while not rstRooms.EOF			
			Response.Write "<tr class=clsTableItem>"
			Response.Write "<TD>" & rstRooms("Field1") & "</TD>"
			Response.Write "<TD>" & rstRooms("Field2") & "</TD>"
			Response.Write "<TD>" & rstRooms("Field3") & "</TD>"
			Response.Write "<TD>" & rstRooms("Field4") & "</TD></TR>"
			rstRooms.MoveNext					
		wend
		Response.Write "</table>"
		Response.Flush
	end if

Open in new window

0
 
LVL 54

Assisted Solution

by:b0lsc0tt
b0lsc0tt earned 500 total points
ID: 20413199
Is that block the end of the script or just part of the recordset "loop" process?  It seems like it is the end so the Response.Flush doesn't really do anything, the page is done anyways.

You might try adding the Response.Flush just above the MoveNext line.  For example ...

                  Response.Write "<TD>" & rstRooms("Field4") & "</TD></TR>"
                                                Response.Flush
                  rstRooms.MoveNext                              
            wend
            Response.Write "</table>"
      end if

The browser may have a problem rendering the table but try it this way.  Since it is done on a row break I am pretty sure it should be OK.  Test different browsers though to be sure.

If that doesn't work then you will need to rethink the page layout.  Divs and CSS float instead of a table and table columns should produce the same look and will be easier for a browser to render in parts, if it has a problem with the table.  From the first post it seemed like you actually made more than one table (I thought 5) but maybe I was wrong.

Don't forget the Response.Buffer line at the very start of the script.  Let me know how this works or if you have a question.  Response.Flush is the way to do what you want it is just a matter of making sure it will work for you and getting it working.

bol
0
 
LVL 4

Author Comment

by:FSIFM
ID: 20534951
Sorry for the delay in answering back.

Unfortunately it seems that it may be a bug with IE :(

So I may have to rethink the way it is going to be built.

Thanks again for your help bol.

FSIFM
0
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 20536226
Your welcome!  I'm glad I could help.  Thanks for the grade, the points and the fun question.  Good luck with the "rethink" and let us know if you need anymore help. :)

bol
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Prevent URL from printing on header 5 70
Classic ASP + JS 4 98
ASP Focus problem 3 63
Query not working correctly? (or how i want it to... 6 51
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question