Simple Query Not Working

[WhozitsPop]

I have a pretty straight forward query and for the life of me, when I try to output data from a field, I get an error:

Variable VEHICLESTOCK is undefined. (this pops up after inserting into page)

Here is my query:

<cfquery name="getUsedVehicles" datasource="#datasource#">
SELECT *
FROM tbl_vehicle, tbl_images
WHERE tbl_vehicle.vehicleStock = tbl_images.vehicleStock
AND tbl_vehicle.vehicleCondition = 'trade'
</cfquery>

When I try to use vehicleStock anywhere in the page, I get that error. I have checked db and fields are text. Not sure what I'm doing wrong.

Thanks
Terry

[Guest]

Well your pulling info from 2 tables with the same column name. I think you have to tell the query which table information to pull from like

SELECT tbl_vehicle.vehicleStock as Stock1, tbl_images.vehicleStock as Stock1
FROM tbl_vehicle, tbl_images
WHERE tbl_vehicle.vehicleStock = tbl_images.vehicleStock
AND tbl_vehicle.vehicleCondition = 'trade'

and then output that #getUsedVehicle.Stock1# or
#getUsedVehicle.Stock2#

[Newsgroup_User]

SELECT *

This may be causing problems. There are expected problems with this do
to the various systems trying their best to improve performance and thus
caching what they can. What happens is that when the query is run the
fields that are returned by SELECT * are cached. Then if the the
database design is changed, new fields are not found because the cached
list does not request them or or removed fields are attempted to be
returned causing another type of error.

Try listing out the required fields in the SELECT statement.

If that does not work, <cfdump var="#getUsedVehicles#"> is a great way
to make sure you are actually getting the expected record set returned
by the database.

Finally if your problem is with output, showing the output code is very
helpful.

[WhozitsPop]

SELECT
tbl_vehicle.vehicleStock as Stock1,
tbl_vehicle.vehicleMake,
tbl_vehicle.vehicleModel,
tbl_vehicl.vehicleCondition,
tbl_images.vehicleStock as Stock2,
tbl_images.vehicleImage
FROM tbl_vehicle, tbl_images
WHERE tbl_vehicle.vehicleStock = tbl_images.vehicleStock
AND tbl_vehicle.vehicleCondition = 'trade'




But I get this error:

Error Executing Database Query.
No value given for one or more required parameters.







I just modified the query to this for the time being to see if I could see the dump. I have not referenced the vehicleStock in any cfoutput tag yet. The following URL shows the dump. I know the query won't work, but I had to do something to get the cfdump to display.

http://www.buysmp.ca/tradein.cfm


SELECT tbl_vehicle.*, tbl_images.*
FROM tbl_vehicle, tbl_images
WHERE tbl_vehicle.vehicleStock = tbl_images.vehicleStock
AND tbl_vehicle.vehicleCondition = 'trade'

[-__cfSearching__-]

WhozitsPop wrote:
> But I get this error:
> Error Executing Database Query.
> No value given for one or more required parameters.

That is Access's not-very-informative way of saying the sql contains a column reference it does not understand.

One of the column references in your select is misspelled. It should probably be tbl_vehicle.vehicleCondition instead of tbl_vehicl.vehicleCondition,

[WhozitsPop]

Typo fixed, but still doing it. Is it possible I'm not listing all fields necessary?

[-__cfSearching__-]

Still doing which, returning an error "No value given for ..." or saying VEHICLESTOCK is undefined? VEHICLESTOCK is not a column in your new query. You renamed those columns to: Stock1 and Stock2. So those are the variable names you must use in your output.

SELECT
tbl_vehicle.vehicleStock as Stock1,
...
tbl_images.vehicleStock as Stock2,

[WhozitsPop]

<cfquery name="getUsedVehicles" datasource="buysmp">
SELECT tbl_vehicle.vehicleStock as stock1, tbl_vehicle.vehicleMake, tbl_vehicle.vehicleModel, tbl_images.vehicleStock as stock2, tbl_images.vehicleImage
FROM tbl_vehicle, tbl_images
WHERE tbl_vehicle.vehicleCondition = 'trade'
AND tbl_vehicle.vehicleStock = tbl_images.vehicleStock
</cfquery>



it's working now thank you. Just decided to retype everything. Apparently I need a stronger prescription for my contacts 🙂 Thank you for your help.

[WhozitsPop]

I have more questions comng 🙂