Solved

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

Posted on 2007-11-30
12
223 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now