Solved

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

Posted on 2007-11-30
12
226 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP.net VB.net gather value of text box text change 4 54
Time/Date Query 11 43
Need help to rewrite script 3 94
Connection to multiple databases 13 25
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 …
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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