[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Problem outputting recordset

Posted on 2010-03-30
28
Medium Priority
?
239 Views
Last Modified: 2012-05-09
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

0
Comment
Question by:jameskane
  • 15
  • 13
28 Comments
 
LVL 53

Expert Comment

by:_agx_
ID: 29102056
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
 
LVL 53

Expert Comment

by:_agx_
ID: 29102825
> //TESTING FOR THE CDID_trkNo - output is correct,

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

0
 

Author Comment

by:jameskane
ID: 29102868
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 53

Expert Comment

by:_agx_
ID: 29103087
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
 

Author Comment

by:jameskane
ID: 29104002
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
 
LVL 53

Expert Comment

by:_agx_
ID: 29104596
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
 

Author Comment

by:jameskane
ID: 29104956
that worked !!
0
 
LVL 53

Expert Comment

by:_agx_
ID: 29105015
What were the results of the tests? I'm in the dark here ;-)
0
 

Author Comment

by:jameskane
ID: 29105408
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
 
LVL 53

Expert Comment

by:_agx_
ID: 29106433
> 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
 

Author Comment

by:jameskane
ID: 29106957


Number 1 returned results, Number 2 returned error

____________________________________________________

Error Executing Database Query.

Syntax error in string in query expression 'cdid_trkNo = '15S_15'.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 29107275
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
 

Author Comment

by:jameskane
ID: 29108946
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
 
LVL 53

Expert Comment

by:_agx_
ID: 29109781
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
 
LVL 53

Expert Comment

by:_agx_
ID: 29109838
Obviously create the variable first in test # 2.

ie <cfset URL.cdid_trkNo = "15S_17">
... rest of code...
0
 

Author Comment

by:jameskane
ID: 29110268
YES !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

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

james
0
 
LVL 53

Expert Comment

by:_agx_
ID: 29110582
You're welcome.  Now you're a trouble shooting guru, and can apply this same approach to any problem :)
0
 

Author Comment

by:jameskane
ID: 29110760
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
 
LVL 53

Expert Comment

by:_agx_
ID: 29111734
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
 

Author Comment

by:jameskane
ID: 29111909
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
 
LVL 53

Expert Comment

by:_agx_
ID: 29112307
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
 
LVL 53

Expert Comment

by:_agx_
ID: 29112378
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
 

Author Comment

by:jameskane
ID: 29113058


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
 
LVL 53

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 29113574
> <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
 

Author Comment

by:jameskane
ID: 29113758
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
 

Author Closing Comment

by:jameskane
ID: 31708965
great help, no way I could have solved this on my own !!

0
 
LVL 53

Expert Comment

by:_agx_
ID: 29114164
> 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
 

Author Comment

by:jameskane
ID: 29115940
will do !!

james
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question