locked
why search string 'Text6=''locations''' not work from web API and give excption but it work from sql ? RRS feed

  • Question

  • User696604810 posted
    public DataTable GetReportDetailsSearch(string ReportID, string FromDate, string ToDate, string SearchString)
          {
             
              List<SqlParameter> param = new List<SqlParameter>()
              {
                  new SqlParameter("@ReportID", ReportID),
                  new SqlParameter("@ReportDateFrom", FromDate),
                  new SqlParameter("@ReportDateTo", ToDate),
                  new SqlParameter("@SearchString",SearchString),
              
    
              };
    
      DataTable ReportDetailsSearch = SQLDAL.ReturnDataTableByProcedure("sp_ReportDetailsGetALL", param);
              return ReportDetailsSearch;
          }
    
    
    
    [Route("ReportDetailsSearch")]
           [HttpPost]
           public IActionResult GetSearchedData([FromBody] dynamic DataObjectSearch)
           {
    
               try
               {
                   string ReportId = DataObjectSearch.reportID;
                   string FromDate = DataObjectSearch.startdate;
                   string StartDate = FromDate.Substring(0, 10);
                   string todate = DataObjectSearch.enddate;
                   string EndDate = todate.Substring(0, 10);
                   string Searchdata = DataObjectSearch.searchstring;
    
                   var PostSearch = _reportservice.GetReportDetailsSearch(ReportId, StartDate, EndDate, Searchdata);
    
                   return Ok(PostSearch);
    
    
           }
    



    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 success as below


    exec [dbo].[sp_ReportDetailsGetALL] "2028","2020-05-03","2020-05-11", 'Text6=''locations'''


    exactly issue on the following line


    'Text6=''locations'''


    not work if as below


    "'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 :

    { 
    "startdate": "2020-05-03T22:00:00Z", 
    "enddate": "2020-05-11T22:00:00Z", 
    "searchstring": "'Text6=''MFG'''", 
    "reportID": "2028" 
    } 



    procedure getreportdetail as following :

    declare @ColumnName Nvarchar(max) = (SELECT 'select ' + STUFF((SELECT ',' + 'Text'+CONVERT(varchar(20),ReportHeaderIndex) + ' '+ '['+ReportHeader +']' 
    FROM ReportHeaders where ReportID=@ReportID order by ReportHeaderIndex 
    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 ) 
    exec (@ColumnName) 




    Saturday, May 16, 2020 12:35 AM

All replies

  • User-854763662 posted

    Hi ahmedbarbary ,

    For reproducing the issue , what's your model design and the customized SQLDAL.ReturnDataTableByProcedure() method?

    If you want the community to review and debug the code , please share the relevant code that can reproduce the issue.

    Best Regards,

    Sherry

    Saturday, May 16, 2020 3:24 AM
  • User-474980206 posted

    You need to produce valid sql. Search string should be

    "searchstring": "Text6='MFG'", 
    Saturday, May 16, 2020 8:37 PM