Server Side Searchpanes with Sql View
Server Side Searchpanes with Sql View
sarooptrivedi
Posts: 62Questions: 19Answers: 2
Server Side SearchPanes trying to look for table in table query rather than view field. It is throwing the error on view field which is not available inside the table. In below code I made the Year base on the EntryDate of table. on the fly in View query. but I got the error that unknown column Year, when I am trying to filter the value from the searchpanes. Is the Searchpanes support the SQL View at server side?
var response = new Editor(db, "TblUser", pkey)
.ReadTable(TblNames."V_User")
.Field(new Field("Id"))
.Field(new Field("UserName"))
.Field(new Field("FullName"))
.Field(new Field("EntryDate").Validator(Validation.DateFormat("yyyy-MM-dd")).SetFormatter(Format.NullEmpty()).GetFormatter(Format.DateSqlToFormat("yyyy-MM-dd")).SetFormatter(Format.DateFormatToSql("yyyy-MM-dd")))
.Field(new Field("Year").SearchPaneOptions(new SearchPaneOptions()))
.TryCatch(true).Debug(true)
.Process(httpRequest).Data();
Replies
SELECT * FROM [TblUser] WHERE [Year] = @where_0Above query generated in debug while apply the Search Panes filter. expectation with SQL View
SELECT * FROM [V_User] WHERE [Year] = @where_0You need to use the methods of the
SearchPaneOptionsclass to tell it explicitly what table / view, field name and value you want to use as the data source. Have a look at the second example on that page to see how the code for that would look.Regards,
Allan
Hey @allan ,
I changed the column as per your suggestion. But still in debug I get the same error that unknow column Year because in the debug it is shows the actual table name rather than the view.
.Field(new Field("Year").SearchPaneOptions(new SearchPaneOptions().Table(TblNames.V_User).Value("Year").Label("Year")))The debug result
SELECT * FROM [TblUser] WHERE [Year] = @where_0Above query generated in debug while apply the Search Panes filter.
expectation with SQL View
SELECT * FROM [V_User] WHERE [Year] = @where_0The Year column is in the view but not in the table. I am making Year column inside the view by SQL Function.