problem with refresh

[prog247]

Hey

I got a couple of rows and for example i allways add them
and when i show them on the web page which refreshs every couple of seconds i want to show only 10 of them if there is
more than 10 rows of data in db i want it to delete the top one but if couple of people inserting the data in db it's gonna be more then 10 rows on the page and in db so how can i do that?.

thanx

 

[Javrix]

Whoa turbo, I can't understand what you're saying. Please use a comma or period. ~Javrix

If you gave an infinate number of rednecks, an infinate number of shotguns to shoot at an infinate number of street signs, they would eventually write Romeo and Juliet in braille.

 

[scotth4v]

Instead of using a "while not rs.eof" loop to get the data from your recordset, you could just set up a for loop that runs 10 times, and set the "order by" in your SQL statement to return the 10 records you want (IE 10 most recent, or last 10 by ID number, or by name, whatever...)

Requerying the DB evey 2 seconds is going to be expensive though. Are you building an activeX contol or something that will maintain a connection, or just hitting it with the ASP page every 2 seconds?

-Scott

 

[prog247]

It's a good idea but i need to show only the last 10 rows and delete all else.

I tried to put a variable in the loop which looping throw the db and increment it each time by 1 and then if it's more than 10 delete first row but it's deleting to slow.

I need something else.

 

[vasah20]

Do you have an @@identity field or AutoNumber (depending on backend)

If you do, try this creative SQL

SELECT TOP 10 * FROM (SELECT * FROM mytbl ORDER BY myID DESC)

Since the subquery automatically returns the results in a reverse order, the main query will select only that top 10 and return that. The only drawback is that you need to scan the entire table, and return it's results in reverse fashion, then get the top 10 of that.

You could create a datetime field and have that default to Now(), then limit your query based on that field. The cavat with this is that if you do limit it based on time, there's a chance that the RS returned could be empty (all the relevant records may have 'expired')

At any rate - there's a couple options for you.

good luck -
leo