count number of times record is viewed...

[Mike010]

What I would like to do is have a field in a given record increment itself every time that record is selected. For example, say you have a table that stores items in the inventory: each item has a times_search_for_field.

Now, lets say a user runs a search which results in the following SQL:

SELECT * FROM Inventory WHERE experation_date=Today();

Now I want to have every record returned by that SQL statement increment it's times_search_for_field incremented by one. I'm just looking for the most efficiant way to do this.

I know I chould just have my asp .net code loop through the results and call an SQL UPDATE statement for each record, but it seems there should be a more efficient way of doing this.

Mike.

[f_eriksen]

use a stored proc, do the update on the table, followed by the select that will return the resultset to the client.

[Mike010]

Thanks for that reply. It had never occured to me that something like:

UPDATE Items SET view_count = view_count+1;

was even possible. I thought I'd have to select the view_count field in order to get the value of view_count and then update the view_count with the returned value+1 (and thus, have to iterate through each item in a seperate query).

This is what I was looking for, thanks.