Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dear My friends.
I have a gridview. It is binded to a sql data bank. Sql has a table with some columns (name, photo, score, rank, id). My gridveiw shows some pictures which is saved in sql. The problem is that I could not show the rank. for example, I have two pictures, picture A has a score of 6 and picture B has a score of 8. the rank of picture A is 2 and the rank of picture B is 1. Gridview shows the pictures and the rank (by using of datareader and " order by desc" for sorting the pictures based on score). But, when the table in sql is updated and for example the score of picture A changed to 9, its rank must be 1 and the rank of picture B must be 2. But by using of "order by score desc" the column of rank does not update. Therefore the gridview could not show the true rank of each picture. How to use update of table with sorting the table order by score?
Best Wishes
Posted
Updated 10-Jul-15 23:44pm
v3

With your actual table design, when you change the score of a record, you have to recompute the ranks through all the table.

You may consider getting rid of your Rank column, and compute the rank in the query instead. Something like:
SQL
SELECT
   t.Id
 , t.Score
 , ROW_NUMBER() OVER (ORDER BY t.Score DESC) AS Rank
 , t.Name
 , t.Photo
FROM TableName AS t

Hope this helps.

[edit] Corrected OVER clause. [/edit]
 
Share this answer
 
v2
Comments
Member 11796666 11-Jul-15 17:39pm    
Dear ....
I used this and it was useful but there is an error as below:
"THE OVER SQL construct or statement is not supported"
but an additional column(1-2-3-...) is added to my table in sql.
what about the error?
best wishes
phil.o 11-Jul-15 18:34pm    
Which database system are you using?
Member 11796666 12-Jul-15 7:50am    
Dear Phil.o
It is Microsoft SQl Serevr 2008
phil.o 12-Jul-15 8:20am    
I did a mistake, my bad. Please see my updated solution.
Member 11796666 12-Jul-15 10:56am    
Dear Phil.o
When I used the query("OW_NUMBER() OVER (ORDER BY t.Score DESC) AS Rank") in my webform, a problem is occured with my e.commandargument. It was arranged (0-1-2-3-...). In fact, e.commnadargument should be depends on clicking mouse click on the gridview column(as TemplateField which is binded to my table).
You don't "order tables" in SQL - you order them when you pull them from the DB as part of the SELECT query that you populate your tables and views with. If you do not specify an order then, then SQL is at liberty to server rows in any order it finds convenient - and that may not be the same if you issue the same query twice in a row.

So look for the SELECT query that you use when you bind the GridView, and make sure it is of the form:
SQL
SELECT * FROM MyTable ORDER BY MyRankingColumn DESC
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900