Coldfusion and MS SQL - like clause returns erratic results

I have a query that sometimes is case sensitive and sometimes is not.  At least that's what I'm seeing.  I can type "may" in the input element "SelectKeyword" and the query returns 0 rows.  I can type "May" and it returns several rows.  It depends on timing I think.  If I type "promo" it returns several rows.  When I type "may" again it may or may not return rows.



<cfinvoke component="cfc.basic" method="getEmailers" returnvariable="qPages">
	<cfinvokeargument name="year" value="#attributes.year#">
	<cfinvokeargument name="type_id" value="#attributes.type_id#">
	<cfinvokeargument name="status_id" value="#attributes.status_id#">
	<cfinvokeargument name="SelectDate" value="#attributes.SelectDate#">
	<cfinvokeargument name="SelectMailerID" value="#val(attributes.SelectMailerID)#">
	<cfinvokeargument name="SelectKeyword" value="#attributes.SelectKeyword#">
	<cfinvokeargument name="sort_order" value="#attributes.sort_order#">
	<cfinvokeargument name="sort_col" value="#attributes.sort_col#">
	<cfinvokeargument name="start" value="#variables.start#">
	<cfinvokeargument name="stop" value="#variables.stop#">
</cfinvoke>





<cffunction name="getEmailers">
		<cfargument name="eID" required="False" type="Numeric" default="0">
		<cfargument name="service_id" required="False" type="Numeric" default="0">
		<cfargument name="year" required="False" type="string" default="">
		<cfargument name="type_id" required="False" type="string" default="">
		<cfargument name="status_id" required="False" type="string" default="">
		<cfargument name="SelectDate" required="False" type="string" default="">
		<cfargument name="SelectMailerID" required="False" type="string" default="">
		<cfargument name="SelectKeyword" required="False" type="string" default="">
		<cfargument name="sort_order" required="False" type="string" default="Asc">
		<cfargument name="sort_col" required="False" type="string" default="">
		<cfargument name="start" required="False" type="Numeric" default="1">
		<cfargument name="stop" required="False" type="Numeric" default="10">
		<cfset local.qResults = "">
		<cfif arguments.SelectDate neq "">

		</cfif>
		<cfquery name="local.qResults" datasource="#request.dsn#">
			SELECT *
			FROM (
				SELECT (ROW_NUMBER() OVER (
					<cfif arguments.sort_col neq "">
						ORDER BY #arguments.sort_col# #arguments.sort_order#, a.ID DESC
					<cfelse>
						ORDER BY A.ID DESC
					</cfif>
				 )) AS ROWNUM
				 , A.id
				 , A.name
				 , A.description
				 , A.subject
				 , A.send_priority_id
				 , A.created_date
				 , A.created_by_id
				 , A.ab_flag
				 , A.from_id
				 , A.replyto_id
				 , A.calllist_id
				 , A.type_id
				 , A.status_id
				 , A.service_id
				 , A.comm_file_flag
				 , A.cms_flag
				 , A.drip_count
				 , A.unsubscribe_email_id
				 , A.preheader
				 , B.name AS create_name
				 , C.name AS reply_name
				 , A.date_last_run
				FROM emailers AS A LEFT OUTER JOIN
						admin_users AS B ON A.created_by_id = B.adminID LEFT OUTER JOIN
							admin_users AS C ON A.replyto_id = C.adminID
				WHERE 0=0
					<cfif val(arguments.eID) NEQ "0">AND A.id = <cfqueryparam value="#arguments.eID#" /></cfif>
					<cfif val(arguments.service_id) NEQ "0">AND A.service_id = <cfqueryparam value="#arguments.service_id#" /></cfif>
					<!---<cfif arguments.year neq "">AND A.created_date = <cfqueryparam value="#arguments.year#" /></cfif>--->
					<cfif arguments.type_id neq "">AND A.type_id = <cfqueryparam value="#arguments.type_id#" /></cfif>
					<cfif arguments.status_id neq "">
						AND A.status_id = <cfqueryparam value="#arguments.status_id#" />
					<cfelse>
						AND A.status_id <> <cfqueryparam value="4" />
					</cfif>
					<cfif arguments.SelectDate neq ""> and DATEADD(dd, DATEDIFF(dd, 0, date_last_run), 0) = '#replace(arguments.SelectDate,",","")#'</cfif>
					<cfif val(arguments.SelectMailerID) gt 0>
						AND A.id = <cfqueryparam value="#val(arguments.SelectMailerID)#" />
					</cfif>
					<cfif arguments.SelectKeyword neq "">AND A.name LIKE <cfqueryparam value="%#arguments.SelectKeyword#%" cfsqltype="varchar" /></cfif>
				) AS Team2
			WHERE ROWNUM BETWEEN #arguments.start# and #arguments.stop#
		</cfquery>
		<cfreturn local.qResults />
	</cffunction>

Open in new window

lantervAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
It's unclear what you mean by "when I type May"  do you mean when you use the word "May" for the parameter:  arguments.SelectKeyword ?

SQL Server is not case sensitive.

I do see that  you are limiting the number of rows you return, that could be the reason for hiding and showing certain values.   If you are only showing 10 records, than the results may be hidden if they would appear after the 10th record.   I would test by changing it so all records appear and not varying any other parameter (sort order, etc) until you are sure of your results.   Using Coldfusion debug you should be able to look at the query in the debug and see what the query looks like to see if the where clause  or order is changing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lantervAuthor Commented:
As usual, you are right.  One stupid keystroke in the code.  I hate wasting your time and mine.  Thank you very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.