5 Replies Latest reply on Dec 18, 2012 3:59 AM by BKBK

    What is wrong with this query

    benq001

      Hei

      I don know whats is wrong with this query. it give error when run on cfm. but runs fine in sql server.

       

      please look what is the problem.

       

      I have used the same query withMaalgruppe in('barnehage', 'småskoletrinnet') but it give error. please look why.

       

      Maalgruppe ='barnehage' , Maalgruppe ='småskoletrinnet' , Maalgruppe ='mellomtrinnet' , Maalgruppe ='ungdomstrinnet' , Maalgruppe ='videregående og høgskole' , Maalgruppe ='skognæringen' ,

      SELECT Id, Navn, isbn, Pris, Varenr, Ingress, Tekst, Aktiv, Fradato, Tema, Undergruppe, Miniatyr, Lagerstatus, Maalgruppe, Vekt, Type, h, b, d FROM Varer WHERE Tema = 'Lære med skogen' and aktiv =1 and ( Maalgruppe ='barnehage' or Maalgruppe ='småskoletrinnet' or Maalgruppe ='mellomtrinnet' or Maalgruppe ='ungdomstrinnet' or Maalgruppe ='videregående og høgskole' or Maalgruppe ='skognæringen' ) ORDER BY Navn

      Error Executing Database Query.

      struct
      Cause
      struct
      ErrorCode102
      Message[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'barnehage'.
      SQLStateHY000
      StackTracejava.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'barnehage'. at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source) at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source) at macromedia.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source) at macromedia.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source) at macromedia.jdbc.base.BaseStatement.postImplExecute(Unknown Source) at macromedia.jdbc.base.BaseStatement.commonExecute(Unknown Source) at macromedia.jdbc.base.BaseStatement.executeInternal(Unknown Source) at macromedia.jdbc.base.BaseStatement.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:348) at coldfusion.sql.Executive.executeQuery(Executive.java:1224) at coldfusion.sql.Executive.executeQuery(Executive.java:1009) at coldfusion.sql.Executive.executeQuery(Executive.java:940) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:325) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:838) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:528) at cfvareliste2ecfm675555126._factor0(D:\Kunder\Skoleskogen_2012\vareliste.cfm:211) at cfvareliste2ecfm675555126._factor3(D:\Kunder\Skoleskogen_2012\vareliste.cfm:195) at cfvareliste2ecfm675555126.runPage(D:\Kunder\Skoleskogen_2012\vareliste.cfm:1) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:196) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:483) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:282) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:86) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:2 8) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.CfmServlet.service(CfmServlet.java:198) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at jrun.servlet.FilterChain.doFilter(FilterChain.java:86) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42 ) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at jrun.servlet.FilterChain.doFilter(FilterChain.java:94) at jrun.servlet.FilterChain.service(FilterChain.java:101) at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106) at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42) at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286) at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543) at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203) at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320) at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428) at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266) at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)
      TagContext
      array
      1
      struct
      COLUMN0
      IDCFQUERY
      LINE211
      RAW_TRACEat cfvareliste2ecfm675555126._factor0(D:\Kunder\Skoleskogen_2012\vareliste.cfm:211)
      TEMPLATED:\Kunder\Skoleskogen_2012\vareliste.cfm
      TYPECFML
      2
      struct
      COLUMN0
      IDCF_VARELISTE
      LINE195
      RAW_TRACEat cfvareliste2ecfm675555126._factor3(D:\Kunder\Skoleskogen_2012\vareliste.cfm:195)
      TEMPLATED:\Kunder\Skoleskogen_2012\vareliste.cfm
      TYPECFML
      3
      struct
      COLUMN0
      IDCF_VARELISTE
      LINE1
      RAW_TRACEat cfvareliste2ecfm675555126.runPage(D:\Kunder\Skoleskogen_2012\vareliste.cfm:1)
      TEMPLATED:\Kunder\Skoleskogen_2012\vareliste.cfm
      TYPECFML
      Typejava.sql.SQLException
      DataSourceSkogkurs12
      Detail[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'barnehage'.
      ErrorCodeHY000
      MessageError Executing Database Query.
      NativeErrorCode102
      RootCause
      struct
      ErrorCode102
      Message[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'barnehage'.
      SQLStateHY000
      StackTracejava.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'barnehage'. at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source) at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source) at macromedia.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source) at macromedia.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source) at macromedia.jdbc.base.BaseStatement.postImplExecute(Unknown Source) at macromedia.jdbc.base.BaseStatement.commonExecute(Unknown Source) at macromedia.jdbc.base.BaseStatement.executeInternal(Unknown Source) at macromedia.jdbc.base.BaseStatement.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:348) at coldfusion.sql.Executive.executeQuery(Executive.java:1224) at coldfusion.sql.Executive.executeQuery(Executive.java:1009) at coldfusion.sql.Executive.executeQuery(Executive.java:940) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:325) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:838) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:528) at cfvareliste2ecfm675555126._factor0(D:\Kunder\Skoleskogen_2012\vareliste.cfm:211) at cfvareliste2ecfm675555126._factor3(D:\Kunder\Skoleskogen_2012\vareliste.cfm:195) at cfvareliste2ecfm675555126.runPage(D:\Kunder\Skoleskogen_2012\vareliste.cfm:1) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:196) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:483) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:282) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:86) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:2 8) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.CfmServlet.service(CfmServlet.java:198) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at jrun.servlet.FilterChain.doFilter(FilterChain.java:86) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42 ) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at jrun.servlet.FilterChain.doFilter(FilterChain.java:94) at jrun.servlet.FilterChain.service(FilterChain.java:101) at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106) at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42) at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286) at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543) at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203) at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320) at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428) at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266) at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)
      TagContext
      array
      1
      struct
      COLUMN0
      IDCFQUERY
      LINE211
      RAW_TRACEat cfvareliste2ecfm675555126._factor0(D:\Kunder\Skoleskogen_2012\vareliste.cfm:211)
      TEMPLATED:\Kunder\Skoleskogen_2012\vareliste.cfm
      TYPECFML
      2
      struct
      COLUMN0
      IDCF_VARELISTE
      LINE195
      RAW_TRACEat cfvareliste2ecfm675555126._factor3(D:\Kunder\Skoleskogen_2012\vareliste.cfm:195)
      TEMPLATED:\Kunder\Skoleskogen_2012\vareliste.cfm
      TYPECFML
      3
      struct
      COLUMN0
      IDCF_VARELISTE
      LINE1
      RAW_TRACEat cfvareliste2ecfm675555126.runPage(D:\Kunder\Skoleskogen_2012\vareliste.cfm:1)
      TEMPLATED:\Kunder\Skoleskogen_2012\vareliste.cfm
      TYPECFML
      Typejava.sql.SQLException
      SQLStateHY000
      SqlSELECT * FROM Varer WHERE Tema = 'Lære med skogen' and aktiv =1 and Navn like '%%%' and ( Maalgruppe =''barnehage'' or Maalgruppe =''småskoletrinnet'' or Maalgruppe =''mellomtrinnet'' or Maalgruppe =''ungdomstrinnet'' or Maalgruppe =''videregående og høgskole'' or Maalgruppe =''skognæringen'' ) ORDER BY Navn
      StackTracecoldfusion.tagext.sql.QueryTag$DatabaseQueryException: Error Executing Database Query. at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:590) at cfvareliste2ecfm675555126._factor0(D:\Kunder\Skoleskogen_2012\vareliste.cfm:211) at cfvareliste2ecfm675555126._factor3(D:\Kunder\Skoleskogen_2012\vareliste.cfm:195) at cfvareliste2ecfm675555126.runPage(D:\Kunder\Skoleskogen_2012\vareliste.cfm:1) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:196) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:483) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:282) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:86) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:2 8) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.CfmServlet.service(CfmServlet.java:198) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at jrun.servlet.FilterChain.doFilter(FilterChain.java:86) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42 ) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at jrun.servlet.FilterChain.doFilter(FilterChain.java:94) at jrun.servlet.FilterChain.service(FilterChain.java:101) at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106) at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42) at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286) at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543) at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203) at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320) at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428) at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266) at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66) Caused by: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'barnehage'. at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source) at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source) at macromedia.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source) at macromedia.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source) at macromedia.jdbc.base.BaseStatement.postImplExecute(Unknown Source) at macromedia.jdbc.base.BaseStatement.commonExecute(Unknown Source) at macromedia.jdbc.base.BaseStatement.executeInternal(Unknown Source) at macromedia.jdbc.base.BaseStatement.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:348) at coldfusion.sql.Executive.executeQuery(Executive.java:1224) at coldfusion.sql.Executive.executeQuery(Executive.java:1009) at coldfusion.sql.Executive.executeQuery(Executive.java:940) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:325) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:838) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:528) ... 32 more
      TagContext
      array
      1
      struct
      COLUMN0
      IDCFQUERY
      LINE211
      RAW_TRACEat cfvareliste2ecfm675555126._factor0(D:\Kunder\Skoleskogen_2012\vareliste.cfm:211)
      TEMPLATED:\Kunder\Skoleskogen_2012\vareliste.cfm
      TYPECFML
      2
      struct
      COLUMN0
      IDCF_VARELISTE
      LINE195
      RAW_TRACEat cfvareliste2ecfm675555126._factor3(D:\Kunder\Skoleskogen_2012\vareliste.cfm:195)
      TEMPLATED:\Kunder\Skoleskogen_2012\vareliste.cfm
      TYPECFML
      3
      struct
      COLUMN0
      IDCF_VARELISTE
      LINE1
      RAW_TRACEat cfvareliste2ecfm675555126.runPage(D:\Kunder\Skoleskogen_2012\vareliste.cfm:1)
      TEMPLATED:\Kunder\Skoleskogen_2012\vareliste.cfm
      TYPECFML
      TypeDatabase
      queryError[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'barnehage'.
      where[empty string]

       

       

      code

       

      <cfset molgppe="">

       

       

      <cfif b NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='barnehage' ,"></cfif>

      <cfif s NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='småskoletrinnet' ,"></cfif>

      <cfif m NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='mellomtrinnet' ,"></cfif>

      <cfif u NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='ungdomstrinnet' ,"></cfif>

      <cfif v NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='videregående og høgskole' ,"></cfif>

      <cfif h NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='skognæringen' ,"></cfif>

       

      <cfoutput>

      <cfif molgppe NEQ "">

      #molgppe#<br />

       

       

      <cfset molgppe="  and ("&molgppe&")  ">

       

       

        <cfset molgppe=replace(molgppe,",)"," )       ")>

       

       

      <cfset molgppe=replace(molgppe,","," or  ","all")>

       

       

       

       

       

       

      </cfif>

       

       

      </cfoutput>

       

        

       

       

      <cfif gruppe EQ "videregående og høgskole">

                <cfset gruppe = "vgs">

      <cfelseif gruppe EQ "skognæringen">

                <cfset gruppe1 = " skogeier%' OR Maalgruppe LIKE '%funksjonærer">

      </cfif>

       

       

       

      <cfset prisf="">

      <cfif pf NEQ "" > <cfset prisf=" and pris >= #pf# "></cfif>

      <cfset prist="">

      <cfif pt NEQ ""> <cfset prist=" and pris <= #pt# "> </cfif>

       

       

       

       

      <cfset c=0>

       

       

      <cfset GRUPPE1="">

      <cfset ITEM1="">

      <cfset order=" Navn ">

      <cfif pf NEQ "" and pt EQ ""><cfset order="Pris asc ">

      <cfelseif pt NEQ "" and pf EQ ""><cfset order="Pris desc ">

      <cfelseif pf NEQ "" and pt NEQ ""><cfset order="Pris asc ">

      <cfelse>

      <cfset order=" Navn  ">

      </cfif>

       

       

       

       

      <cfif gruppe NEQ ""> <cfset GRUPPE1=" AND Maalgruppe LIKE '%"&gruppe&"%' "></cfif>

      <cfif item NEQ "" and  item NEQ "%"><cfset ITEM1=" and Navn like '%"&item&"%' "></cfif>

       

       

      <cfset query=

      "SELECT

      Id, Navn, isbn, Pris, Varenr, Ingress, Tekst, Aktiv, Fradato, Tema, Undergruppe, Miniatyr, Lagerstatus, Maalgruppe, Vekt, Type, h, b, d

      FROM Varer

      WHERE Tema = 'Lære med skogen'  and aktiv =1 "

      &GRUPPE1&" "&ITEM1&" "&prisf&" "&prist&" "&molgppe&" ORDER BY "&order>

      <cfoutput> #query#</cfoutput>

       

       

      <cftry>

      <cfquery name="varer" datasource="Skogkurs123">

      SELECT *

      FROM Varer

      WHERE Tema = 'Lære med skogen'

      and aktiv =1

      <cfif gruppe NEQ ""> AND Maalgruppe LIKE '%#gruppe#%'</cfif>

      <cfif item NEQ "">and Navn like '%#item#%' </cfif>

       

       

      #prisf##prist#  #molgppe#

       

       

      ORDER BY

       

       

      <cfif pf NEQ "" and pt EQ "">Pris asc

      <cfelseif pt NEQ "" and pf EQ "">Pris desc

      <cfelseif pf NEQ "" and pt NEQ "">Pris asc

      <cfelse>

      Navn

      </cfif>

       

       

      </cfquery>

      <br />

       

       

      <cfoutput>Record: #varer.RecordCount#</cfoutput>

      <cfif varer.RecordCount gt 0>

      <cfset c=#varer.RecordCount#>

      </cfif>

      <cfcatch type="any">

      <cfset c=0>

      <br />

       

       

      <cfoutput> #CFCATCH.Message#</cfoutput>

      <cfdump var="#CFCATCH#">

      </cfcatch>

      </cftry>

       

       

       

       

       


        • 1. Re: What is wrong with this query
          BKBK Adobe Community Professional & MVP

          Look in the error dump, at the row Sql. Its value is

           

          SELECT *

          FROM Varer

          WHERE Tema = 'Lære med skogen' and aktiv =1 and Navn like '%%%' and ( Maalgruppe =''barnehage'' or Maalgruppe =''småskoletrinnet'' or Maalgruppe =''mellomtrinnet'' or Maalgruppe =''ungdomstrinnet'' or Maalgruppe = ''videregående og høgskole'' or Maalgruppe =''skognæringen'' )

          ORDER BY Navn

           

          Look at the string values within the brackets. You will notice that there are double single-quotes where there are supposed to be just single-quotes. That is because your code is sending strings like ''barnehage'', ''småskoletrinnet'', ..., and so on, to the database, instead of 'barnehage', 'småskoletrinnet', ....

          • 2. Re: What is wrong with this query
            benq001 Level 1

            Hei,

            Thanks for reply.

             

            you are right. but I am not putting the doubble cote " its comming automatically. if you see the my code I am not putting any "" in there . where is this coming.

            please see the code

             

             

            <cfset molgppe="">

             

             

            <cfif b NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='barnehage' ,"></cfif>

            <cfif s NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='småskoletrinnet' ,"></cfif>

            <cfif m NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='mellomtrinnet' ,"></cfif>

            <cfif u NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='ungdomstrinnet' ,"></cfif>

            <cfif v NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='videregående og høgskole' ,"></cfif>

            <cfif h NEQ 0><cfset molgppe=molgppe&" Maalgruppe ='skognæringen' ,"></cfif>

             

             

             

            <cfquery name="varer" datasource="Skogkurs123">

            SELECT *

            FROM Varer

            WHERE Tema = 'Lære med skogen'

            and aktiv =1

            <cfif gruppe NEQ ""> AND Maalgruppe LIKE '%#gruppe#%'</cfif>

            <cfif item NEQ "">and Navn like '%#item#%' </cfif>

             

             

            #prisf##prist#  #molgppe#

             

             

            ORDER BY

             

             

            <cfif pf NEQ "" and pt EQ "">Pris asc

            <cfelseif pt NEQ "" and pf EQ "">Pris desc

            <cfelseif pf NEQ "" and pt NEQ "">Pris asc

            <cfelse>

            Navn

            </cfif>

             

             

            </cfquery>

            • 3. Re: What is wrong with this query
              BKBK Adobe Community Professional & MVP

              benq001 wrote:

               

              you are right. but I am not putting the doubble cote " its comming automatically.

              Yes, ColdFusion is designed to 'escape' single-quotes by adding another single-quote. The function preserveSingleQuotes might help.

               

              In the query, replace

               

              #molgppe#

               

              with

               

              #preserveSingleQuotes(molgppe)#

              1 person found this helpful
              • 4. Re: What is wrong with this query
                benq001 Level 1

                Hei,

                 

                Thanks allot  its fixed

                 

                best regards

                • 5. Re: What is wrong with this query
                  BKBK Adobe Community Professional & MVP

                  OK. Good luck.