ASP.NET sorting for jQuery Grid

Question

hi, i want to do sorting for jQGrid in ASP.NET. For that i have pass the sidx and sord for column name and sorting order.but it was giving error "Incorrect syntax near '0'. Statement(s) could not be prepared." i wrote query for this select Image,CategoryName,Status,CategoryId from Category Order by {0} {1}

Answer 1

I do not think this is valid Sql query:

"select Image,CategoryName,Status,CategoryId from Category Order by {0} {1}"

Instead replace the curly brackets and the numbers with actual names:

"select Image,CategoryName,Status,CategoryId from Category Order by CategoryName, Status"

Dick

Answer 2

It would be better if you post a code fragment which you use to near SELECT statement. It seems that you either forgot to make String.Format with "select Image,CategoryName,Status,CategoryId from Category Order by {0} {1}" or you don't define a default sort column in jqGrid and not test in your code whether sidx is empty or null.

If you generate AdHoc SQL statements, you should be very careful in testing of the input parameters sidx and sord. For example, should be permitted only two values for sord: "desc" or "asc". The sidx can be either empty or null or one value from the list of permitted for sorting columns (Image should be probably not permitted for sorting). In the advanced version you can allow sidx be like "CategoryName asc, Status" if you want. In this case you should parse sidx and verify for the permitted syntax.

At the end you should take in consideration permitted case of column names and possible existence of special characters in the column names or using reserved names in the column names. You receive much more safe SQL statements if you place '[' and ']' over column names.