GridView sort, using ORDER By 2 times in qry.

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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...

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

Latest Posts