Problem outputting recordset

I am passing a variable to an output page via a url. For some reason I cannot get any output from the resulting recordset which is generated using the url paramater.  This is something really stupid - but after 3 hours I have to give up and wonder if someone could look at the output page, it is something trivial, but I cant see it.

What I know
- The paramater cdid_trkNo is being passed correctly from the previous page. This is not the problem.

- I have tested the recordset1 using the dreamweaver facility and the recordset generates the correct result when I give it a cdid_trkID value

Can't see the problem at all ahhhhhhhhh

Appreciate any help

james


<cfquery name="Recordset1" datasource="SDance">
SELECT *
FROM CDtunes
WHERE cdid_trkNo = <cfqueryparam value="#URL.cdid_trkNo#" cfsqltype="cf_sql_clob" maxlength="255"> 
</cfquery>


<cfoutput>#url.cdid_trkNo#</cfoutput>//TESTING FOR THE CDID_trkNo - output is correct, so url paramater value is being passed


 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Tunes Listing</title>
</head>

<body>

<cfoutput> #RECORDSET1.Tune1# TUNE1</cfoutput>
<cfoutput query="Recordset1">
#Tune1#<br />

#Tune2#<br />
#Tune3#<br />
#Tune4#<br />
#Tune5#<br />
#Tune6#<br />
#Tune7#<br />
#Tune8#<br />
#Tune9#<br />
#Tune10#<br />
</cfoutput>

Open in new window

jameskaneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Start simply with some debugging.

1.  Before the query, dump the url trackNo. Make sure it's valid
<cfoutput>#url.cdid_trkNo#</cfoutput>

2.  Dump the query. Does it contain any results? If not, that's why you're not seeing any output.
     So you're probably not passing a valid trackNo. Or perhaps's you're using the wrong data type

     <cfdump var="#Recordset1#">

> WHERE cdid_trkNo = <cfqueryparam value="#URL.cdid_trkNo#" cfsqltype="cf_sql_clob" maxlength="255">

    What's the data type of the cdid_trkNo column: text, numeric,...? I'm doubting its a CLOB
0
_agx_Commented:
> //TESTING FOR THE CDID_trkNo - output is correct,

    Never mind about #1. Obviously the coffee hasn't fully kicked in yet ;-)

0
jameskaneAuthor Commented:
Thanks for getting back agx

The data type of he cdid_trkNo is TEXT.

I am getting the url.cdid_trkNo output - see line 8, that proves it.

I have tested the query within dreamweaver - and its good.

Its got to be the cfsqltype

james
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

_agx_Commented:
Try it without the cfqueryparam.  If it doesn't work that way, the variable value is wrong (or maybe contains extra spaces,etc..).  That's one of the problems with using strings. They're very, very. sensitive.  I prefer numerics for unique id's.

SELECT *
FROM CDtunes
WHERE cdid_trkNo = '#URL.cdid_trkNo#'


... OR

SELECT *
FROM CDtunes
WHERE cdid_trkNo = '#Trim(URL.cdid_trkNo)#'

0
jameskaneAuthor Commented:
Nope, no luck. The first line below is what I get when run your  first scenario above. Notice, I get the value for the cdid_trkNo which I passed from the previous page, confirming that I am passing the variable.

The second line(s) below is the error message when I ran your second scenario.

15S_17//TESTING FOR THE CDID_trkNo - output is correct, so url paramater value is being passed TUNE1

 Error Executing Database Query.
Syntax error in string in query expression 'cdid_trkNo = '15S_11'.

James
0
_agx_Commented:
I think you've got an extra quote somewhere in the second query, causing a syntax error.  But it's the same as the first query, except for the trim().

Let's eliminate the obvious here.  You say it works in Dreamweaver. Are you using that exact same value here? If not, do.  

1. Query MS Access directly using that same hard coded value. Does the query return any results?

SELECT *
FROM    CDtunes
WHERE cdid_trkNo = '15S_17'

2. Use the same query in your CF page. CFDUMP the query. Does it return any results?

<cfquery name="Recordset1" datasource="SDance">
SELECT *
FROM    CDtunes
WHERE cdid_trkNo = '15S_17'
</cfquery>

<cfdump var="#Recordset1#">

0
jameskaneAuthor Commented:
that worked !!
0
_agx_Commented:
What were the results of the tests? I'm in the dark here ;-)
0
jameskaneAuthor Commented:
Alastair Ross Taylor
Balfron Boy
Corpach Ceilidh, The

It returned the above 3 tunes from which track 15S_17 is comprised. The track is a cd track and the dance music on this particular track is made up of 3 tunes.

So the idea is, one selects a track on the launch page and the track number is passed via url to this page that I can't get to work.
0
_agx_Commented:
> 1. Query MS Access directly using that same hard coded value. Does the query return any results?
> 2. Use the same query in your CF page. CFDUMP the query. Does it return any results?

Okay ... but I meant for which step #1, #2 or both?  Did _both_ return results?

0
jameskaneAuthor Commented:


Number 1 returned results, Number 2 returned error

____________________________________________________

Error Executing Database Query.

Syntax error in string in query expression 'cdid_trkNo = '15S_15'.
0
_agx_Commented:
It doesn't look like you are using the same query for both.  It's important to use the same one, to eliminate possible causes here.  So just copy + paste the same query you ran in Access. Hard coded values and all.

0
jameskaneAuthor Commented:
1. Query MS Access directly using that same hard coded value. Does the query return any results?

SELECT *
FROM    CDtunes
WHERE cdid_trkNo = '15S_17'

The way I did this was to use dreamweaver to create a recordset and then test that directly on the database with dreamweaver. I got correct results

--------------------------------------------------------------------------------------
2. Use the same query in your CF page. CFDUMP the query. Does it return any results?

<cfquery name="Recordset1" datasource="SDance">
SELECT *
FROM    CDtunes
WHERE cdid_trkNo = '15S_17'
</cfquery>

<cfdump var="#Recordset1#">

I got the same results when I ran this via the page as I got for 1. above

So, these tests give correct results
0
_agx_Commented:
Okay. So we've confirmed the track number is valid. Since the query returns results, I assume the cfoutput generates results, yes?

1. If so, next try adding back the variable. But still hard code it. Does the cfoutput produce the expected results?

(Not tested)

<cfset URL.cdid_trkNo = "15S_17">
<cfquery name="Recordset1" datasource="SDance">
SELECT *
FROM    CDtunes
WHERE cdid_trkNo = '#Trim(URL.cdid_trkNo)#'
</cfquery>

<cfdump var="#Recordset1#">
... rest of code ...

2.  Next try it with cfqueryparam, but using CF_SQL_VARCHAR. Does the cfoutput still produce the expected results?

(Not tested)

<cfquery name="Recordset1" datasource="SDance">
SELECT *
FROM    CDtunes
WHERE cdid_trkNo = <cfqueryparam value="#Trim(URL.cdid_trkNo)#" cfsqltype="cf_sql_varchar">
</cfquery>

<cfdump var="#Recordset1#">
... rest of code ...



0
_agx_Commented:
Obviously create the variable first in test # 2.

ie <cfset URL.cdid_trkNo = "15S_17">
... rest of code...
0
jameskaneAuthor Commented:
YES !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Problem solved. Thanks a million for help and patience !!!

james
0
_agx_Commented:
You're welcome.  Now you're a trouble shooting guru, and can apply this same approach to any problem :)
0
jameskaneAuthor Commented:
ah, but I may have got a bit too excited. I have tried it with putting the url value passed to it and it does not seem to work, like it did when I hard wired a value into it. Going to try it again.
WOOps Sorry
0
_agx_Commented:
Are you doing it manually, or testing from the launch page you mentioned? If from the launch page, can you post the code used to generate the links to this page?
0
jameskaneAuthor Commented:
Nope,  afraid the problem is still there. Here is what I did

for the successful attempt, when I thought it was solved. This is what was run

<cfset URL.cdid_trkNo = "15S_17">
 <cfquery name="Recordset1" datasource="SDance">
SELECT *
FROM CDtunes
WHERE cdid_trkNo = '#url.cdid_trkNo#'
</cfquery>
 <cfdump var="#Recordset1#">


But of course I need the value of URL.cdid_trkNo to be set dynamically.

Now the following expression evaluates to  the selected cdid_trkNo
<cfoutput> #url.cdid_trkNo# sessssssssssssssss</cfoutput> I get the correct output.  SO my question is why does the expression #url.cdid_trkNo# evaluate above but NOT in the code

 <cfquery name="Recordset1" datasource="SDance">
SELECT *
FROM CDtunes
WHERE cdid_trkNo = '#url.cdid_trkNo#'
</cfquery>
 <cfdump var="#Recordset1#">

That is where the problem likes, I think ?
0
_agx_Commented:
First, are you testing the same value ie 15S_17? If you _are_ testing that same value, it could be something as simple as an extra space in the value. "15S_17" is not the same as "15S_17(space)".  Can you post the code used to generate the link you're testing?
0
_agx_Commented:
Also, any change if you TRIM() the variable first?

 <cfquery name="Recordset1" datasource="SDance">
     SELECT *
     FROM CDtunes
     WHERE cdid_trkNo = '#TRIM(url.cdid_trkNo)#'
</cfquery>
 <cfdump var="#Recordset1#">
0
jameskaneAuthor Commented:


This is the code for the page used to select the value of cdid_trkNo.

The trim does not make a difference. I do not think the problem is here.

THANKS for help !!!!!!!!!!!!!!!!!!!!!!



<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<cfparam name="cdid_trkNo" default="">
<cfparam name="tracktime" default="">
<cfparam name="dancetype" default="">
<cfparam name="format" default="">
<cfparam name="dancetitle" default="">

<cfquery name="dances" datasource="SDance">
SELECT cdid_trkNo, DanceTitle
FROM CDtunes 
</cfquery>
<cfquery name="MusicLink" datasource="SDance">
SELECT cdid_trkNo, MusicLink
FROM MUSICLINKS 
</cfquery>
<cfquery name="danceDefinition" datasource="SDance">
SELECT cdid_trkNo, TrackTime, DanceType, Format
FROM Dances
</cfquery>


<cfquery name="DanceBrowser" dbtype="query">
select
dances.cdid_trkNo,
     dances.DanceTitle,
     <!---MusicLink.MusicLink,--->
     danceDefinition.tracktime,
     danceDefinition.DanceType,
     danceDefinition.Format
from
     dances, danceDefinition
where
     
    dances.cdid_trkNo = dancedefinition.cdid_trkNo
     
ORDER BY  cdid_trkNo asc
</cfquery>

<!---EXPERIMENTATION--->
<cfquery name="DanceBrowserPlus" dbtype="query">
select
dancebrowser.cdid_trkNo,
     dancebrowser.DanceTitle,
     <!---MusicLink.MusicLink,--->
     dancebrowser.tracktime,
     dancebrowser.DanceType,
     dancebrowser.Format,
     musiclink.musiclink
from
     dancebrowser, musiclink
where
     
    musiclink.cdid_trkNo = dancebrowser.cdid_trkNo
     
<!---ORDER BY  DanceTitle asc--->
ORDER BY  cdid_trkNo asc
</cfquery>




<body>

<table width="100%" border="1">
  <tr>
    <td>Dance</td>
    <td>cdid_trkNo</td>
<td>Music Link</td>
   <td>Dance Time</td>
    <td>Dance Type</td>
    <td>Format</td>
    <!---<td>Tunes</td>
    <td>Artist</td>--->
  </tr>
  
  <cfoutput query="dancebrowserplus">
  <tr>
    <td>#dancetitle#</td>
     
    <td><a href="tunesListing.cfm?cdid_trkNo= #DanceBrowserPlus.cdid_trkNo#"> Tunes Listing</a></td>
  <td><a href="#Musiclink#" title="testing link" target="_blank">Click here</a></td>
    <td>#tracktime#</td>
    <td>#dancetype#</td>
    <td>#format#</td>

  </tr>
  </cfoutput>
</table>

Open in new window

0
_agx_Commented:
> <a href="tunesListing.cfm?cdid_trkNo=(**space**)#DanceBrowserPlus.cdid_trkNo#"> Tunes Listing</a>

1. Well, you do have an extra space in front of the value. White space is easy to overlook visually. But it would mess up your search.  Remove the space and try it again.  

(Though if you're passing anything other than simple numbers, you should really URL encode the value)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jameskaneAuthor Commented:
You were right !!!!!!!!!!!!!!!!!!!!!!!
just realized that the trim op works !!!!!! reason I thought it did not was due to browser cache !!

SO ITS DEFINITELY FIXED NOW

THANKS A MILLION

james
0
jameskaneAuthor Commented:
great help, no way I could have solved this on my own !!

0
_agx_Commented:
> reason I thought it did not was due to browser cache !!
   Ugh, don't you just hate that?!  Dreamweaver is also bad about caching.  At least from what I hear.  
   Glad it's working now.

   Don't forget to encode the url variables too. Just so the page doesn't error out if it hits anything unusual:

...
<a href="tunesListing.cfm?cdid_trkNo=#URLEncodedFormat(DanceBrowserPlus.cdid_trkNo)#">
....
0
jameskaneAuthor Commented:
will do !!

james
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.