GridView sort, using ORDER By 2 times in qry.

Posted by Ashokn under ASP.NET on 2/4/2011 | Points: 10 | Views : 1536 | Status : [Member] | Replies : 2
Hi,

I'm using GridView and binding data from code behind.here I'm trying to sort by writting sql qry in code behind.

query.Append("select RName,round(avg(Convert(decimal(18,2),Level)),2) 'feet',round(avg(round((Convert(decimal(18,2),Level))/3.28,2)),2) 'meter' ");

query.Append("from ActualValues");

query.Append("group by Convert(varchar,DateTimeStamp,1),RName ");
query.Append("order by Convert(varchar,DateTimeStamp,1) desc"); // Sorting to get latest dae 1st.

Again for GridView sort for DatetimeStamp ,I'm appending


query.Append(" ORDER BY " + sortExp);

Whenever Grid is loaded it will be sorted by DatetimeStamp but when I click on DatetimeStamp,as I'm adding one more ORDER BY it's causing error(==> Incorrect syntax near the keyword 'ORDER'.).

Please provide me solution.

Thanks in advance...




Responses

Posted by: Vuyiswamb on: 2/4/2011 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Your Query looks incorrect, if i understand you correctly you are dynamically creating a string

query.Append("select RName,round(avg(Convert(decimal(18,2),Level)),2) 'feet',round(avg(round((Convert(decimal(18,2),Level))/3.28,2)),2) 'meter' ");

query.Append("from ActualValues");
query.Append("group by Convert(varchar,DateTimeStamp,1),RName ");
query.Append("order by Convert(varchar,DateTimeStamp,1) desc"); // Sorting to get latest dae 1st.


as you can see the above has an order by already , so you continues and add to the String

query.Append(" ORDER BY " + sortExp); 


This will mean it will have 2 order by and which is not Correct. if you want to debug this kind of Code, append everything and before you execute the query, add a watch to see what has been built and copy it to the SQL management studio and run it there , you will then see where you are wrong or right.

Thank you for posting at Dotnetfunda

Vuyiswa Maseko


Thank you for posting at Dotnetfunda
[Administrator]

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 2/4/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Hi Ashok,

Creating a dynamic query is a good idea and improves the performance as well... WHat i can suggest is you can write the query as per your requirement in SQL Query anlyzer and then try to make it dynamic in front end this way you can come to know here its getting blocked.

Thanks
Karthik
www.f5Debug.net

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response