ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 275.4k

search string not work if i pass it arrounded single quotes

May 15 2020 7:30 PM
When execute web api search string added to it double quotes why and how to solve issue ?
I work on web api asp.net core 2.2 I face this error
An expression of non-boolean type specified in a context where a condition is expected, near 'and'.
procedure  work from sql succes as below
  1. exec [dbo].[sp_ReportDetailsGetALL] "2028","2020-05-03","2020-05-11"'Text6=''locations'''  
exactly issue on the following line
  1. 'Text6=''locations'''  
not work if as below
  1. "'Text6=''MFG'''"  
on web api i think it add double quotes on start and end so that it not work
json i passed to web api as following :
  1. {  
  2. "startdate""2020-05-03T22:00:00Z",  
  3. "enddate""2020-05-11T22:00:00Z",  
  4. "searchstring""'Text6=''MFG'''",  
  5. "reportID""2028"  
  6. }  
procedure getreportdetail as following :
  1. declare @ColumnName Nvarchar(max) = (SELECT 'select ' + STUFF((SELECT ',' + 'Text'+CONVERT(varchar(20),ReportHeaderIndex) + ' ''['+ReportHeader +']'  
  2. FROM ReportHeaders where ReportID=@ReportID order by ReportHeaderIndex  
  3. FOR XML PATH('')) ,1,1,'') + ' , convert(nvarchar(20),[ReportDate]) ReportDate From ReportDetails R where ReportDate >= ''' +@ReportDateFrom+''' and ReportDate <= '''+ @ReportDateTo +''' and R.ReportID =' + @ReportID + ' and '+@SearchString+' and IsHistory=0 order by reportdate desc ' + @SortingColumns AS Txt )  
  4. exec (@ColumnName)  
What I have tried:
  1. public DataTable GetReportDetailsSearch(string ReportID, string FromDate, string ToDate, string SearchString)  
  2. {  
  3. List<SqlParameter> param = new List<SqlParameter>()  
  4. {  
  5. new SqlParameter("@SearchString",SearchString),  
  6.   
  7. };  
  8.   
  9. DataTable ReportDetailsSearch = SQLDAL.ReturnDataTableByProcedure("sp_ReportDetailsGetALL", param);  
  10. return ReportDetailsSearch;  
  11. }  
  12.   
  13.   
  14.   
  15. [Route("ReportDetailsSearch")]  
  16. [HttpPost]  
  17. public IActionResult GetSearchedData([FromBody] dynamic DataObjectSearch)  
  18. {  
  19.   
  20. try  
  21. {  
  22. string Searchdata = DataObjectSearch.searchstring;  
  23.   
  24. var PostSearch = _reportservice.GetReportDetailsSearch(ReportId, StartDate, EndDate, Searchdata);  
  25.   
  26. return Ok(PostSearch);  
  27.   
  28.   
  29. }  

Answers (1)