CF, Access, WHERE var NOT LIKE var; help please m(-!-;)m

S

Samuraiken

Guppy
I have a table with 2 columns in it.
- column 1 "licensed" has software on the network that we have licenses for (approx. count is 150).
- column 2 "software_list" has a list of software on the network overall...owned by the company or not (><) (approx count is 6951).

Most of this software is installed by users spread throughout the building on their machines, and it was not bought by us...therefore they problably have a license for it, but its not owned by us. But i need to search through that list and hunt down the people who have games, etc installed (via SMS).

Anyway, the goal of this is to cut through the software_list, and only show software that we do not already have a license for.

Only thing is, i keep getting this error when i try to match the two lists.

My thinking on this would be to show everything in software_list that was NOT LIKE anything in licensed...but i seem to be going about this the wrong way.

i have removed #'s, added them, added/removed '''s, etc, and cannot pinpoint what i am doing wrong. any help on this would be greatly appreciated!


code:
Code: 
	<cfquery name="match" datasource="#dsn#">
		SELECT *
			FROM licensed
			WHERE software_list NOT LIKE '% #licensed# %'
	</cfquery>
	<table border="1" cellspacing="0" cellpadding="0">
		<cfoutput query="match">
			#match.licensed#<br>
		</cfoutput>
		<cfoutput>
			#match.recordcount#
		</cfoutput>



Error:
Code: 
Error Diagnostic Information
An error occurred while evaluating the expression: 
#software_list#
Error near line 12, column 25.
 
oops. wrong error. this ist he correct error.

Error:
Code: 
Error Diagnostic Information
An error occurred while evaluating the expression: 
#licensed#
Error near line 12, column 25.
[/QUOTE]
 
Try this.

Code: 
<cfquery name="match" datasource="#dsn#">
		SELECT *
			FROM licensed
			WHERE software_list NOT LIKE '%' + licensed + '%'
	</cfquery>
	<table border="1" cellspacing="0" cellpadding="0">
		<cfoutput query="match">
			#match.licensed#<br>
		</cfoutput>
		<cfoutput>
			#match.recordcount#
		</cfoutput>
 
Thanks Jussin, i appreciate the help.

I'm out of the office right now but i'll try it first thing tomorrow.

Looking at the code though i just had a brain fart..

in order to display all the records that meet the requirments (say, all the id's that are >not< licensed), would i use
Code: 
#match.licensed#
or
Code: 
#match.software_list#
?

Match.licensed, i believe, would still only print out the licensed list, and .software_list would give me that...but what about what the query returned (ie, the individual records in the recordcount).

So say out of 6951 pieces of software, 5000 matched the words in the licensed table...so i would like to display the other 1951 records.

it might just be me, but i think i overlooked that. Or maybe its so simple that i just dont remember how to do it (i've been away from using coldfusion, but its almost like riding a bike!)

Thanks!
 
You must log in or register to reply here.
Back
Top