944,070 Members | Top Members by Rank

Web Development > ColdFusion > the simplest of all queries
Ad:
  • ColdFusion Discussion Thread
  • Unsolved
  • Views: 2634
  • ColdFusion RSS
Permalink
Apr 4th, 2007
0

the simplest of all queries

Expand Post »
Hi,

I use to be a bit of a wiz at coldfusion 4, but I havent used it in quite a WHILE! Now its at version 7 and what I remember doesnt seem to work anymore...

I am trying to write a 'news' page, it takes data from two tables... news and users.

It will display the title and story from the news table and the username from the users table. The two tables will be joined by the userid which is the same in both tables....

Here is my query, that use to work in old version 4!

ColdFusion Syntax (Toggle Plain Text)
  1. <cfquery datasource=#db# name=test>
  2. SELECT *
  3. FROM news, users
  4. WHERE news.userid = users.userid
  5. </cfquery>

But it just comes up with this error:

Quote ...
Error Executing Database Query.
Type mismatch in expression.

The error occurred in C:\CFusionMX7\wwwroot\cfdocs\jericho\index.cfm: line 1

1 : <cfquery datasource=#db# name=test>
2 : SELECT *
3 : FROM news, users
Any help would be greatly appreciated, and once I have got over this rather easy milestone, I will be able to get on with it!

Thanks,
William
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
willyc is offline Offline
1 posts
since Apr 2007
Permalink
Apr 6th, 2007
0

Re: the simplest of all queries

I have not used CF much since version 5... I would try running your query against the database outside of ColdFusion and fetching all the rows. That may help isolate where the problem is. If the problem turns out to be with the query, you may want to check the data type of the columns you are joining. Some database will automatically convert a string to a number which works fine until you bump into a string that cannot be converted because it has alpha characters in it. If they have different data types, try adding a TO_CHAR or TO_NUMBER conversion.
Reputation Points: 12
Solved Threads: 0
Light Poster
Memento is offline Offline
30 posts
since Jan 2007
Permalink
Apr 12th, 2007
0

Re: the simplest of all queries

You still need to set star per table even though you want all from each.

<cfquery datasource=#db# name=test>
SELECT news.*, users.*
FROM news, users
WHERE news.userid = users.userid
</cfquery>
Reputation Points: 10
Solved Threads: 0
Newbie Poster
k-sea is offline Offline
1 posts
since Apr 2007
Permalink
Apr 12th, 2007
0

Re: the simplest of all queries

Click to Expand / Collapse  Quote originally posted by k-sea ...
You still need to set star per table even though you want all from each.

<cfquery datasource=#db# name=test>
SELECT news.*, users.*
FROM news, users
WHERE news.userid = users.userid
</cfquery>
Perhaps in some databases, but I know it is not the case with Oracle at least. However, your point makes me wonder what ColdFusion does when it gets back two columns named "userid". I could see how the confusion from that might cause an error.
Reputation Points: 12
Solved Threads: 0
Light Poster
Memento is offline Offline
30 posts
since Jan 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in ColdFusion Forum Timeline: Run exe file using Coldfusion
Next Thread in ColdFusion Forum Timeline: Passing a structure into an Oracle Procedure





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC