Query Results give Empty Output?

I am trying to create a text file from a coldfusion output table. The followng code is returning an empty set, when in fact I know there are records returned.

What make you say this?  IE: what process did you go through to determine that records have been returned to CF?  I don't know of a situation in which a recordset is returned to CF and when looping over it yields unexpected results (ie: none).

I more suspect your query doesn't return any records.  I know some of the other respondent have leapt forward from here, but let's go back to basics. How did you establish your CFQUERY returned records?  Straight after your CFQUERY, do a CFDUMP of your record set. Does it output a recordset?  I suspect not.

So... why not?  Your CFDUMP will also output the SQL that was passed to the DB driver.  Does that SQL seem right?  If it doesn't, assess why it doesn't.  You've spotted that there's an unexpected value in your filter statement: 1N3W,View Text.  You almost certainly don't want that.  But how did "1N3W,View Text" get into form.text?  DOing a CFDUMP of the FORM scope will confirm its value.  But - assuming you wanted one or the other of those comma-delimited values, why are there two values there?  A quick google through the docs about how forms work yields this page: http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec22c24-6eb7.html#WSc3ff6d0ea77859461172e0811cbec0f76c-7fed, and down the bottom it says this:

{quote}

If multiple controls have the same name, one form variable is passed to the action page with a comma-delimited list of values.

{quote}

So that's likely to be the cause of that then.  Time to check your form.

[etc]

I'm repeating a lot of stuff other people have said here, but hopefully it's a more cohesive example of how to troubleshoot your problems.

Tips:

* don't start with asserts like "it should be doing x".  Start with conceding "it's not doing x, so why not?"

* RTFM.  I've been doing CF for over ten years now, and a day would not go by that something I don't expect happens, and my first port of call is to RTFM.  Even when answering questions here, I RTFM before I make a statement,  I've done three searches through the docs in the course of writing this reply, because I wasn't sure of what my expectations were.  So I checked them.

* work through a problem in small steps.  Identify something unexpected, and then work out why that unexepcted thing happened to be.  Don't just go "humph... why did this not work?"

From a code perspective:

* read up on MVC.  You should never have a CFQUERY tag in a template which also has HTML in it.

* read up on CFQUERYPARAM.  Do not hard-code dynamic values in your SQL string.

* don't do SELECT * unless you actually will be using every single column.  This is very seldom the case.

* do not specify the dbtype attribute of CFQUERY.  It's deprecated.  CF doesn't use ODBC any more, so suggesting that it does / ought to is just plain wrong (unless, of course, you're using CF5 or earlier, I mean.  I suspect not).

* structKeyExists() is more efficient than isDefined()

* learn when and when not to use #-signs.  Your isDefined() is almost certainly wrong.

--

Adam

<cfif by_tr.recordcount greater than 0>

  should be

<cfif by_tr.recordcount gt 0>

And, yes, as long as it's not used within a loop, CFDUMP can be your best friend when troubleshooting.

CFDUMP inside a loop can immediately become your worst enemy, as a developer.

^_^

Hard to tell without seeing the data that is in the DB but I would start by remving some of the condition in the query and then add them back in one at a time.

Run this and see if you get any results:

<CFQUERY name="by_tr" datasource="surveyalpha" dbtype="odbc">

select *
FROM sire.dbo.control_doc 
</CFQUERY>

If you do, the run this:

<CFQUERY name="by_tr" datasource="surveyalpha" dbtype="odbc">

select *
FROM sire.dbo.control_doc

WHERE sire.dbo.control_doc.publish = 'yes'
</CFQUERY>

Keep adding conditions to the query until it stops returning values. You should then know why nothing is being returned.

Use <cfdump var="#by_tr#"> to see the results.