Issue with formating a query broken into columns

Report · Oct 28, 2010

Hello;

I have 2 issues actually. I'm breaking my query down into 2 columns. That all works, it's outputting it and everyhting looks nice... But now, I'm trying to do a little formating to the code to make things look correct (the out put data) and I'm not getting the proper information for some reason.. Can someone help me figure out what I need to do?

This is my code for the query, and the columns, this all works:

<cfquery name="GetList" datasource="#APPLICATION.dataSource#" dbtype="ODBC">
SELECT liveVideo.ID AS ID, liveVideo.title, liveVideo.MYVideo, liveVideo.fileURL, liveVideo.MYFile, liveVideo.Body, liveVideo.dateDD
FROM liveVideo
ORDER BY liveVideo.dateDD
</cfquery>

</td>
</cfif>

</td></cfif>
          <td width="40%" align="center" valign="top"> </td>
          </tr>
          </cfoutput>
      </table>

My first issue is to try and get the date to show up like this January 12, 2010 When I try and set the formatting, it doesn't like how I'm coding it...I tried this, and a few other variations.. isn't there a better way, like making a cfset change it and then use the name of the set in the output instead of the dateformat? this is the experimental non working code: #GetList.dateformat(dateDD[CurrentRow],'mm/dd/yyyy')#

Also, I can 't seem to use cf to find out if a db cell has data in it, this code works on a normal query or a loop, but I can't get it to work with breaking up a query like this...
<cfif isDefined("GetList.fileURL")>#GetList.fileURL[Evaluate(CurrentRow+halfrecords)]#</cfif> - Doesn't work
<cfif isDefined("GetList.fileURL")> - desn't work

I realize, I need to look at each column, so I need to make the if statememnt work for how the rcords are being broken down...Can anyone help me with the logic here?

Thank you.

Report · Oct 29, 2010

DateFormat has the ability to show the date the way you want. The manual will tell you what mask to use.

To see if a db cell has data in it, run your query and then do this:

it's empty. This works for any data type.

By the way, this:

Evaluate(CurrentRow+halfrecords)

is the same as this:

CurrentRow+halfrecords

You don't need the evaluate function to add two numbers.

Having said that, your halfrecords variable is going to be a floating point number when your recordcount is an odd number. That will cause a problem when you use it to specify a query row.

Report · Oct 29, 2010

Thank you.. I know about dateformat, I've been trying this code:

<p>#dtDate[CurrentRow]#</p>

</cfoutput>

and this isn't working, I know this is how it's supposed to be written, but I'm missing something. Any ideas?

This is the error:

You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.

Report · Oct 29, 2010

I see a couple of different things going on in your code:

The CFOUTPUT references "myquery" but nothing inside of the loop references it

The dateFormat() call references getList.dateDD - where does this come from?

The cfset is putting a value into dtDate as if it were a scaler, but the <p> tag is referencing dtDate as a structure. Is there a dtdate=structNew() above the CFOUTPUT loop? this is probably the source of your error

referencing currentRow without scoping to the specific query can lead to confusion, but whould not cause this error.

Can you show us a alrger code snippet so we can see the context that the CFOUTPUT is operating in?

-reed

Report · Oct 29, 2010

This is my code.. it works by breaking down the query into columns, but when I try and add formatting to it, is when I get errors like this.

<cfquery name="GetList" datasource="#APPLICATION.dataSource#" dbtype="ODBC">
SELECT liveVideo.ID AS ID, liveVideo.title, liveVideo.MYVideo, liveVideo.fileURL, liveVideo.MYFile, liveVideo.Body, liveVideo.dateDD
FROM liveVideo
ORDER BY liveVideo.dateDD
</cfquery>

<p>#dtDate[CurrentRow]#</p>

</div>

</td>
</cfif>

<p>#dtNowDate[Evaluate(CurrentRow+halfrecords)]#</p>

</div>

       </td></cfif>
          <td width="40%" align="center" valign="top"> </td>
          </tr>
          </cfoutput>
      </table>

The error, is posted in my last post. I'll address the Evaluate(currentRow+Halfrecords) when i get this figured out. So you're saying this will work? #myquery.cell [currentRow+Halfrecords]# works the same as what I have now? or better actually?

Report · Oct 29, 2010

The error is caused by this line.

You have to specify a row number.

Report · Oct 29, 2010

so how do I fix this? That's my question. How do I write it so it will work.

Report · Oct 29, 2010

Are you saying you don't know how to specify a row number?

Report · Oct 29, 2010

haven't had to use it this way yet.. is this close?

Report · Oct 29, 2010

Specifying a query row is always done the same way.

queryname.fieldname[row number]

Look at the line that I said was causing your error. Do you see queryname.fieldname without a row number anywhere?

Report · Oct 29, 2010

So I would write it like this?
<cfset dtDate = DateFormat(GetList.dateDD[67], "mm/dd/yyyy" ) />

What if there is 2 rows that need it? use 2 cfsets?

Report · Oct 29, 2010

When cfoutputing or cflooping through a query, the currentrow variable is your freind.

Adobe Community

Issue with formating a query broken into columns

You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.