sql - SqlCommand: Incorrect syntax near the keyword 'OVER' -

i'm trying data database show in asp.net datagrid.

it's showing above mentioned error.

this select command:

  public masterjoblist getlistforgrid(int reccount, int pageno, string orderby)         {             strsql = "with temptable as(select jobdetails.jobcode,jobdetails.currentstatus,mastermodel.name modelnumber,mastermodel.code modelcode,masterbrand.code brandcode,masterbrand.name brandname,masterdevicetype.code devicecode,masterdevicetype.name dtype,row_number() on (order " + orderby + ") rownumber jobdetails jobdetails inner join masterdevicetype on jobdetails.dtype = masterdevicetype.code inner join masterbrand on jobdetails.bcode = masterbrand.code inner join mastermodel on jobdetails.modelnumber = mastermodel.code 1 = 1) select * temptable rownumber between {2} , {3}";               masterjoblist objlist = new masterjoblist();             datatable dt = new datatable();             dt = objdb.getdatatablefromsql(strsql);             if (dt != null)             {                 foreach (datarow dr in dt.rows)                 {                     jobs obj = new jobs();                     obj.jobcode =convert.toint32(dr["jobcode"].tostring());                      if (dr["dtype"] != dbnull.value)                         obj.dtype = dr["dtype"].tostring();                     else                         obj.dtype = "";                      if (dr["brandname"] != dbnull.value)                         obj.brandname = dr["brandname"].tostring();                     else                         obj.brandname = "";                      if (dr["modelnumber"] != dbnull.value)                         obj.modelnumber = dr["modelnumber"].tostring();                     else                         obj.modelnumber = "";                      if (dr["currentstatus"] != dbnull.value)                         obj.currentstatus = dr["currentstatus"].tostring();                     else                         obj.currentstatus = "";                    objlist.add(obj);                 }             }             return objlist;         } 

the exact error is:

exception details: system.data.sqlclient.sqlexception: incorrect syntax near keyword 'over'.

please full code...

your row_number-"column" comes after joins.

change to:

string sql = @" temptable  (     select         jobdetails.jobcode,        jobdetails.currentstatus,        mastermodel.name modelnumber,        mastermodel.code modelcode,        masterbrand.code brandcode,        masterbrand.name brandname,        masterdevicetype.code devicecode,        masterdevicetype.name dtype,        row_number() on (order {0}) rownumber     jobdetails jobdetails       inner join masterdevicetype         on jobdetails.dtype = masterdevicetype.code      inner join masterbrand         on jobdetails.bcode = masterbrand.code      inner join mastermodel         on jobdetails.modelnumber = mastermodel.code,      {1} )  select * temptable rownumber between {2} , {3}";   

now use sql = string.format(sql, orderby, filter, rnstart, rnend) assign values.

the variables be:

string orderby = "modelnumber asc"; string filter  = "brandname = 'sony'"; int rnstart = pageno == 1 ? 1 : ((pageno - 1) * reccount) + 1; int rnend   = pageno == 1 ? reccount : pageno * reccount; 

---

update: show complete method (at least relevant code) according edit.

public masterjoblist getlistforgrid(int reccount, int pageno, string orderby) {     string strsql = @" temptable  (     select         jobdetails.jobcode,        jobdetails.currentstatus,        mastermodel.name modelnumber,        mastermodel.code modelcode,        masterbrand.code brandcode,        masterbrand.name brandname,        masterdevicetype.code devicecode,        masterdevicetype.name dtype,        row_number() on (order {0}) rownumber     jobdetails jobdetails       inner join masterdevicetype         on jobdetails.dtype = masterdevicetype.code      inner join masterbrand         on jobdetails.bcode = masterbrand.code      inner join mastermodel         on jobdetails.modelnumber = mastermodel.code,      {1} )  select * temptable rownumber between {2} , {3}";      int rnstart = pageno == 1 ? 1 : ((pageno - 1) * reccount) + 1;     int rnend   = pageno == 1 ? reccount : pageno * reccount;     strsql = string.format(strsql, orderby, "1=1", rnstart, rnend);      datatable dt = objdb.getdatatablefromsql(strsql);     // ...     return objlist; }