3 Replies - 272 Views - Last Post: 16 May 2020 - 08:53 PM Rate Topic: -----

#1 ahmedsa   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 08-November 19

why parameter value surrounded by single quotes not work csharp ?

Posted 15 May 2020 - 08:14 PM

why parameter value surrounded by single quotes not work csharp ?

this statement working exactly on SQL server

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


procedure as below

declare @ColumnName Nvarchar(max) = (SELECT 'select ' + STUFF((SELECT ',' + 'Text'+CONVERT(varchar(20),ReportHeaderIndex) + ' '+ '['+ReportHeader +']' 
FROM ReportHeaders where [email protected] order by ReportHeaderIndex 
FOR XML PATH('')) ,1,1,'') + ' , convert(nvarchar(20),[ReportDate]) ReportDate From ReportDetails R where ReportDate >= ''' [email protected]+''' and ReportDate <= '''+ @ReportDateTo +''' and R.ReportID =' + @ReportID + ' and '[email protected]+' and IsHistory=0 order by reportdate desc ' + @SortingColumns AS Txt ) 
exec (@ColumnName) 


when pass 'Text6=''locations''' to paramter @searchstring

give exception error

An expression of non-boolean type specified in a context where a condition is expected, near 'and'.

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;
      }



Is This A Good Question/Topic? 0
  • +

Replies To: why parameter value surrounded by single quotes not work csharp ?

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15743
  • View blog
  • Posts: 63,065
  • Joined: 12-June 08

Re: why parameter value surrounded by single quotes not work csharp ?

Posted 15 May 2020 - 08:18 PM

It's the way special characters are escaped. It's different in SQL vs C#.

Side note - what ever you are doing in that procedure is amazingly bad to read.
Was This Post Helpful? 0
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 705
  • View blog
  • Posts: 4,524
  • Joined: 12-January 10

Re: why parameter value surrounded by single quotes not work csharp ?

Posted 16 May 2020 - 10:59 AM

how are you getting

Quote

exec [dbo].[sp_ReportDetailsGetALL] "2028","2020-05-03","2020-05-11", 'Text6=''locations'''
to work in sql?

mssql uses single ' for strings
the rest of your query is awful -- why dont you have a stored procedure in sql and just call that
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7404
  • View blog
  • Posts: 24,977
  • Joined: 05-May 12

Re: why parameter value surrounded by single quotes not work csharp ?

Posted 16 May 2020 - 08:53 PM

He is calling a stored procedure (see line 14). The issue is that the query in the stored procedure looks terrible as noted in post #2... To complicate matters one of the parameters passed to the stored procedure becomes part of the WHERE constraint. In general, writing a query that way doesn't help the SQL query optimizer, but I'm quite sure the OP or his DBA has reasons for writing the stored procedure that way.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1