Did you ORDER BY counted_column DESC or something more complicated?

I'll show you after I eat supper. I can't remember really.

 

But I rebuilt the whole code and cleaned it all up. It required much less math and variables. So I thank you for that! Plus I formatted it as you suggested and that helps a lot.

This is what I did. It let me eliminate a bunch of if's and other math.

<CFQUERY NAME="sluth" DATASOURCE="isiswood">
	SELECT ID=rudID, Category=rudCategory, Seq=rudSeq, Type=rudType, Product_ID=rudProductID, Description=rudDescrip, 
	PcsPerPkg=rudPcsPerPkg, Tally=rudTally, UOM=rudBaseUnitID, Pieces=rudPieces, Volume=rudVolume, NetVolume=rudNetVolume, Cost=rudCost, AddedCost= rudAddedCost, MktValue=rudMarketValue, Total=rudTotal, NetThick=rudNetThick, 
	NetWidth=rudNetWidth, NetLength=rudNetLength, NetType=rudVolType, LocationID=rudLocationID,rudFIFOVolume,rudForceCost,rudBoomID,prdLengthID=(SELECT prdLengthID 
	FROM ProductMaster WHERE prdID=rudProductID) FROM ProductionDetail WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip NOT LIKE '%boards%' AND rudDescrip NOT LIKE '%blocking%'
	ORDER BY Seq;
</cfquery>

Here is my code after your help.

Much better... though still a whole bunch of stuff I want to change there. :$

But anyway, yeah, if you make it to "ORDER BY WoodCount DESC" in your count query, then all you need is <cfset WoodType = Count.WoodType[1] /> instead of that cfoutput loop.

(Or, you can skip that entirely, and later on just use #Count.WoodType[1]# directly.)

You can probably save a small bit of memory by putting a "TOP 1" after the SELECT too, since by then you'll only care about the first row, but it'll still work without that, just not as efficiently.


All this is just an internal tool, yes - not something that will be publicly available?

In general, you should never directly do #form.whatever# or #url.whatever# or #cookie.whatever# inside a cfquery, because it potentially allows people to inject SQL, which is bad.

Solution is to use query parameters, like this:

AND rudID = <cfqueryparam value="#form.batch#" />

Which prevents SQL injection (and sometimes makes it easier for a database to optimise queries, so can make it faster too).

Same thing applies on the HTML side - to prevent people injecting HTML/JavaScript you need to use HtmlEditFormat for any unsanitized user input:

Run ID = <cfoutput>#HtmlEditFormat(form.batch)#</cfoutput>

Of course, that matters more for stuff on the actual internet - if this is a local tool then it's a question of if you can trust your work colleagues, but both of them are good habits to get into. :)

Yes just an internal tool so I'm not really concerned about injections, but you're right. Get in the habit!

 

I have no doubt there are things that need changed. I haven't used CF in about 2 years and even then I wasn't what I would consider much good at it.

Any idea why this would work:

WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip NOT LIKE '%boards%' AND rudDescrip NOT LIKE '%blocking%'

And this wouldn't?

WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip LIKE '%boards%' AND rudDescrip LIKE '%blocking%'

It tells me this:

It's not the NOTs that are making it not work (and if it was, they would give an SQL error instead).

The error message says you're trying to use a Query variable as if it were a String, and a query can't be implicitly converted (like numbers,booleans,etc can), so it falls over and complains.

Did you do the change in msg:38393.84 and/or anything else?

You've only got two variables shown, form.batch and WoodType - and neither of them should be queries based on the previous code, so either the code has changed, or the data has changed and is causing different behaviour in the code.

Is that definitely the line that the error is pointing to?
If so, are there any other variables inside the cfquery block?

No the only thing I changed was removing the NOT.

This works:

<CFQUERY NAME="woodtype" DATASOURCE="isiswood">
	SELECT ID=rudID, Category=rudCategory, Seq=rudSeq, Type=rudType, Product_ID=rudProductID, Description=rudDescrip, 
	PcsPerPkg=rudPcsPerPkg, Tally=rudTally, UOM=rudBaseUnitID, Pieces=rudPieces, Volume=rudVolume, NetVolume=rudNetVolume, Cost=rudCost, AddedCost= rudAddedCost, MktValue=rudMarketValue, Total=rudTotal, NetThick=rudNetThick, 
	NetWidth=rudNetWidth, NetLength=rudNetLength, NetType=rudVolType, LocationID=rudLocationID,rudFIFOVolume,rudForceCost,rudBoomID,prdLengthID=(SELECT prdLengthID 
	FROM ProductMaster WHERE prdID=rudProductID) FROM ProductionDetail WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip NOT LIKE '%boards%' AND rudDescrip NOT LIKE '%blocking%'
	ORDER BY Seq;
</cfquery>

This doesn't:

<CFQUERY NAME="bandb" DATASOURCE="isiswood">
	SELECT ID=rudID, Category=rudCategory, Seq=rudSeq, Type=rudType, Product_ID=rudProductID, Description=rudDescrip, 
	PcsPerPkg=rudPcsPerPkg, Tally=rudTally, UOM=rudBaseUnitID, Pieces=rudPieces, Volume=rudVolume, NetVolume=rudNetVolume, Cost=rudCost, AddedCost= rudAddedCost, MktValue=rudMarketValue, Total=rudTotal, NetThick=rudNetThick, 
	NetWidth=rudNetWidth, NetLength=rudNetLength, NetType=rudVolType, LocationID=rudLocationID,rudFIFOVolume,rudForceCost,rudBoomID,prdLengthID=(SELECT prdLengthID 
	FROM ProductMaster WHERE prdID=rudProductID) FROM ProductionDetail WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip LIKE '%boards%' OR rudDescrip LIKE '%blocking%'
	ORDER BY Seq;
</cfquery>

And the name of the query. :P

 

Do you happen to have both queries in the code, but the second query is after the first one?

 

Cus if so, once the first query has executed, the WoodType variable will be replaced with that query, and so the second one will fall over when it tries to use the original WoodType string.

 

(Which is exactly what the error is suggesting.)

 

A simple way to avoid it is to prefix or suffix all query variables, (so you could call it qryWoodType or WoodTypeQry and it wouldn't conflict with the original WoodType string), or you can give it a completely different name.

I'm a dumbass. Thanks, that was it!

Pete you any good with the dates in CF? I need to search by month and year. I'm pretty sure I can use #LEFT and #MID. Is that how you would approach it?

Treat dates as dates, not strings. Convert to dates as soon as possible ( parseDate or parseDateTime ) and don't convert back to string until you have to.

If you're doing database date stuff, look up the native stuff ( DatePart or DateTrunc or whatever ), and use cfqueryparam with either cf_sql_date or cf_sql_timestamp (for dates/datetimes as appropriate).

Unless you've got *just* doing month and year stuff, in which case it's integers and Year and Month functions.

Do you have a few minutes today to assist me with searching for dates?

I think it's a tall order asking Pete to find one date, let alone two...

:')

 

Pete is a sexy man, he probably gets a lot of tail! (angel)

I take that back! I got my query sorted on my own so Peter is no longer sexy!

Hey! :'(

(hug)

Pete do you know of a way to do this:

 

Say one of my users generates a report. I then ask them to add it to the monthly report. If they forget or something I want it to warn them if they click the back button or close the browser. Is there some Java or CF code that will do that?