11 Replies Latest reply on Oct 29, 2010 4:18 PM by Dan Bracuk

    Issue with formating a query broken into columns

    cfsetNewbie Level 1

      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>

       

       

      <cfset halfrecords = GetList.RecordCount/2>
            <table width="90%" border="0" cellspacing="0" cellpadding="0">
              <cfoutput query="GetList">
              <tr>
              <cfif CurrentRow lte halfrecords>
                <td width="40%" align="center" valign="top">
                <div align="center">
                <p><img src="../images/live/#GetList.MYFile[CurrentRow]#" width="100" border="0" /></p></div>

                </td>
                </cfif>


                <cfif Evaluate(CurrentRow+halfrecords) lte GetList.RecordCount>
                <td width="50%" align="center" valign="top">
                <div align="center">
                <p><img src="../images/live/#GetList.MYFile[Evaluate(CurrentRow+halfrecords)]#" width="100" border="0" /></p></div>

      </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.

        • 1. Re: Issue with formating a query broken into columns
          Dan Bracuk Level 5

          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:

           

          <cfif YourQuery.YourColumn[rownumber] is "">

          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.

          • 2. Re: Issue with formating a query broken into columns
            cfsetNewbie Level 1

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

             

            <cfoutput query="myQuery">

            <cfset dtDate = DateFormat(GetList.dateDD, "mm/dd/yyyy" ) />

            <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.

            • 3. Re: Issue with formating a query broken into columns
              Reed Powell Level 3

              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

              • 4. Re: Issue with formating a query broken into columns
                cfsetNewbie Level 1

                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>

                 

                 

                <cfset halfrecords = GetList.RecordCount/2>
                      <table width="90%" border="0" cellspacing="0" cellpadding="0">
                        <cfoutput query="GetList">

                <cfset dtDate = DateFormat(GetList.dateDD, "mm/dd/yyyy" ) />
                        <tr>
                        <cfif CurrentRow lte halfrecords>
                          <td width="40%" align="center" valign="top">
                          <div align="center">
                          <p><img src="../images/live/#GetList.MYFile[CurrentRow]#" width="100" border="0" /></p>

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

                          </div>

                          </td>
                          </cfif>


                          <cfif Evaluate(CurrentRow+halfrecords) lte GetList.RecordCount>
                          <td width="50%" align="center" valign="top">
                          <div align="center">
                           <p><img  src="../images/live/#GetList.MYFile[Evaluate(CurrentRow+halfrecords)] #"  width="100" border="0" /></p>

                          <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?

                • 5. Re: Issue with formating a query broken into columns
                  Dan Bracuk Level 5

                  The error is caused by this line.

                  <cfset dtDate = DateFormat(GetList.dateDD, "mm/dd/yyyy" ) />

                   

                  You have to specify a row number.

                  • 6. Re: Issue with formating a query broken into columns
                    cfsetNewbie Level 1

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

                    • 7. Re: Issue with formating a query broken into columns
                      Dan Bracuk Level 5

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

                      • 8. Re: Issue with formating a query broken into columns
                        cfsetNewbie Level 1

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

                         

                        <cfset OutputQueryRow( dtDate ) />

                        • 9. Re: Issue with formating a query broken into columns
                          Dan Bracuk Level 5

                          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?

                          • 10. Re: Issue with formating a query broken into columns
                            cfsetNewbie Level 1

                            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?

                            • 11. Re: Issue with formating a query broken into columns
                              Dan Bracuk Level 5

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