Coldfusion query of queries with row numbers passed to second query

Question

I'm doing the following query of queries. The first dump of the query shows the entire excel sheet, the second dump shows the results of the second query.

I'm doing a validation check of the excel sheet to make sure there are no more pit bulls breeds brought into the system and I need to be able to tell the user which row on the excel sheet has the pit-bull.

How do I get the row numbers from the first row to appear in the second row? So the user can make changes on the excel sheet. Do I have to resort to editing the excel document when it was first uploaded into the server and add a row number column to it? There is probably a better way to accomplish this.

  <cffunction name="validateExcelSheet" access="public" output="yes" returnType="void" 
hint="check dogs">

    <cfspreadsheet
    action="read"
    src="#SESSION.theFile#"
    headerrow= "1"
    excludeHeaderRow = "true"
    query = "allData"
    rows = "1-#lastRow#" />


<cfscript>    
 pitBullcheck = new Query(
        sql ="SELECT * FROM allData where breed like 'Pit%' ",
        dbtype = "query",
        allData = allData);
        pitBullresult = pitBullcheck.execute().getResult();
   </cfscript>

</cffunction>

Here's a tag based version of cfquery

<cfquery name="pitBullresult" dbtype="query">
SELECT *
FROM allData
WHERE breed LIKE 'Pit'
</cfquery>

...what is the `` intended to be? You need to do a couple of things: 1) proof read what you write before hitting post/save. 2) save your sample code to a file and try to run it. (Both things that should be done for all questions.) Also, dropping into script to run a query is insane. Why are you not using [cfquery](https://wikidocs.adobe.com/wiki/display/coldfusionen/cfquery)? :/ — Peter Boughton, Feb 17 '14 at 00:18
was supposed to be . I'm dropping into script because I've only been exposed to coldfusion for a few weeks and still haven't figured out what I'm doing. I didn't use cfquery because this was the only way I was informed how to do it, based on other questions stackoverflow users were able to answer. — ConfusedDeer, Feb 17 '14 at 00:36
Either way works, but since you are already using cfml, `cfquery` would be more natural. (You may have missed that suggestion on your other thread.) — Leigh, Feb 17 '14 at 01:50
@ConfusedDeer I added the tag based version of `cfquery` to your question. It's much simpler to read and write imo. — Matt Busche, Feb 17 '14 at 02:19
(Edit) Not to get side tracked from the original question, but when you use [`LIKE`](http://www.techonthenet.com/sql/like.php) without any wild cards, you are actually using an equality comparison (implicitly): ie `WHERE breed = 'Pit'`. I do not know if that is the result you want.. — Leigh, Feb 17 '14 at 02:32
I would be more concerned about returning the correct results from the query ;-) If the basic validation logic is wrong, the rest does not really matter .. — Leigh, Feb 17 '14 at 03:24

score 2 · Accepted Answer · edited May 23 '17 at 12:10

That is not something you can do with cfspreadsheet. CFSpreadsheet only returns the cell values. It does not provide the physical row numbers, within the spreadsheet, that contained those values.

Also, something else to keep in mind is that CFSpreadsheet only returns "logical" (ie populated) rows/cells. That is not the same as the "physical" row numbers 1,2,3,... and column headers A,B.C.... that you see in Excel. Since users can enter values anywhere within a spreadsheet, logical and physical are not always the same thing.

For example, create a blank spreadsheet. Then enter values in cells A2 and A25. Now run your code above. While you might expect the resulting query to contain twenty-five (25) records, it will only contain two (2), because only two cells were populated.

   query
   Row  | COL_1
    1   | Value in cell A2 (physical row 2)
    2   | Value in cell 25 (physical row 25)

I think best you could do with cfspreadsheet is to loop through the query and display the relative row number within the results ie query.currentRow. If the populated data always starts within the first few rows, that might be good enough for your purposes. If not, it could get a bit confusing ...

That said, technically you could get the real physical row numbers. However, it requires much lower level code, which quite honestly ... seems like a lot of work, for very little gain.

I'll either add a row to the excel sheet or create a new excelsheet with rows. — ConfusedDeer, Feb 17 '14 at 04:36
*add a row* You mean column with row numbers? If yes, using what tool? Unless you are talking manual entry or using low level POI code, you will still have the same issue. — Leigh, Feb 17 '14 at 05:19

Coldfusion query of queries with row numbers passed to second query

1 Answers1