Converting an ID# to Name, Address, etc.

[Guest]

I am working on an output page that displays orders. My query calls on a field called ID# which is the primary key for another table named Dealers.

Here is my dilemma, I want to convert the ID to the actual name, address, etc. of the dealer for dispaly on my output page. I know how to run a query to accomplish this but my question is how do this within a cfoutput of a different query. My page displays a grouped output query of two tables, neither of which is the Dealers table. How would I insert the converted ID number within this output? Can you turn an output query on and off midstream?

[Mi-ul-MVAtZo]

Assuming your dealers query (qDealer) only produces one row

<cfoutput query="qOrders">

<h1>OrderID: #orderID#</h1>

<p>Order date: #datePlaced# by #qDealer.name#. #qDealerAddress#</p>

etc.

</cfoutput>

or

<cfoutput>

<h1>OrderID: #qOrders.orderID#</h1>

<p>Order date: #qOrders.datePlaced# by #qDealer.name#. #qDealerAddress#</p>

</cfoutput>

<cfoutput query="qOrderDetails">

<tr><td>#lineID#</td><td>#lineDescription#</td><td>#lineQty#</td></tr>

</cfoutput>

[Dan_Bracuk]

Why don't you simply select it in the original query?

[Guest]

Okay, I am trying to select it within one query...here is my code;

<cfquery name="qGetDaily_Orders" datasource="manna_premier">
SELECT Orders.ID AS Orders_ID,
       US_Dealers.ID AS US_Dealers_ID,
    US_Dealers.DealerName,
    US_Dealers.DealerAddress,
    US_Dealers.DealerCity,
    US_Dealers.DealerState,
    US_Dealers.DealerZIPCode,
    ProductOrders.OrderID,
    ProductOrders.Product,
    ProductOrders.Quantity,
    ProductOrders.BagsCases,
    ProductOrders.NewExisting,
    Orders.SaleDate,
    Orders.TerritoryManager,
    Orders.Distributor,
    Orders.DealerID,
    Orders.PONumber,
    Orders.Variable,
    Orders.ISOCheck,
    Orders.ISOCheckNumber,
    Orders.ISOCheckAmount,
    Orders.UserID
FROM (US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID]) INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID]
WHERE SaleDate = #CreateODBCDate(URL.SaleDate)# AND UserID = #URL.UserID#
ORDER BY Orders_ID, OrderID
</cfquery>

And when executed I get this error;

Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

The error occurred in D:\Inetpub\mannapremier\daily_report2.cfm: line 51

49 :         Orders.UserID 50 : FROM (US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID]) INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID] 51 : WHERE SaleDate = #CreateODBCDate(URL.SaleDate)# AND UserID = #URL.UserID# 52 : ORDER BY Orders_ID, OrderID 53 : </cfquery>

SQLSTATE   07002 SQL    SELECT Orders.ID AS Orders_ID, US_Dealers.ID AS US_Dealers_ID, US_Dealers.DealerName, US_Dealers.DealerAddress, US_Dealers.DealerCity, US_Dealers.DealerState, US_Dealers.DealerZIPCode, ProductOrders.OrderID, ProductOrders.Product, ProductOrders.Quantity, ProductOrders.BagsCases, ProductOrders.NewExisting, Orders.SaleDate, Orders.TerritoryManager, Orders.Distributor, Orders.DealerID, Orders.PONumber, Orders.Variable, Orders.ISOCheck, Orders.ISOCheckNumber, Orders.ISOCheckAmount, Orders.UserID FROM (US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID]) INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID] WHERE SaleDate = {d '2009-11-02'} AND UserID = 10124 ORDER BY Orders_ID, OrderID VENDORERRORCODE   -3010 DATASOURCE   manna_premier

Resources:

What am I doing wrong here?

[ilssac]

What happens if you take the SQL from that error message, and run it directly in your database?

SELECT Orders.ID AS Orders_ID, US_Dealers.ID AS US_Dealers_ID,
US_Dealers.DealerName, US_Dealers.DealerAddress, US_Dealers.DealerCity,
US_Dealers.DealerState, US_Dealers.DealerZIPCode,
ProductOrders.OrderID, ProductOrders.Product, ProductOrders.Quantity,
ProductOrders.BagsCases, ProductOrders.NewExisting, Orders.SaleDate,
Orders.TerritoryManager, Orders.Distributor, Orders.DealerID,
Orders.PONumber, Orders.Variable, Orders.ISOCheck,
Orders.ISOCheckNumber, Orders.ISOCheckAmount, Orders.UserID FROM
(US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID])
INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID] WHERE
SaleDate = {d '2009-11-02'} AND UserID = 10124 ORDER BY Orders_ID,
OrderID 

[-__cfSearching__-]

What happens if you take the SQL from that error message,

and run it directly in your database?

SaleDate = {d '2009-11-02'}

Just get rid of "{d" and "}" first. IIRC, the query builder does not understand the odbc format.

[Guest]

I removed the ODBC stuff and now I get this error message;

Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'SaleDate = 2009-11-02 00:00:00.0 AND UserID = 10124'.

The error occurred in D:\Inetpub\mannapremier\daily_report2.cfm: line 51

49 :         Orders.UserID 50 : FROM (US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID]) INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID] 51 : WHERE SaleDate = #URL.SaleDate# AND UserID = #URL.UserID# 52 : ORDER BY Orders_ID, OrderID 53 : </cfquery>

SQLSTATE   42000 SQL    SELECT Orders.ID AS Orders_ID, US_Dealers.ID AS US_Dealers_ID, US_Dealers.DealerName, US_Dealers.DealerAddress, US_Dealers.DealerCity, US_Dealers.DealerState, US_Dealers.DealerZIPCode, ProductOrders.OrderID, ProductOrders.Product, ProductOrders.Quantity, ProductOrders.BagsCases, ProductOrders.NewExisting, Orders.SaleDate, Orders.TerritoryManager, Orders.Distributor, Orders.DealerID, Orders.PONumber, Orders.Variable, Orders.ISOCheck, Orders.ISOCheckNumber, Orders.ISOCheckAmount, Orders.UserID FROM (US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID]) INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID] WHERE SaleDate = 2009-11-02 00:00:00.0 AND UserID = 10124 ORDER BY Orders_ID, OrderID

I am totally confused!!

[Adam Cameron.]

Look @ the SQL in the erorr message.

Does this look like valid SQL to you:

WHERE SaleDate = 2009-11-02 00:00:00.0

?

The SQL string you pass from the <cfquery> tag to the DB must resolve to valid SQL.

You should also not hard-code your dynamic values in your SQL string: use <cfqueryparam> instead.

Oh... and stop using MS Access if you possibly can.  I't not intended to be a server DB solution, it's just a desktop app.

--

Adam

[Guest]

Unfortunately it does look like valid SQL to me which is why I can't figure it out on my own. I am a total newbie here so please bear with me. I adjusted the code but am still getting an error message;

WHERE SaleDate = <cfqueryPARAM value = "#URL.SaleDate#" CFSQLType = "CF_SQL_DATE"> AND UserID = #URL.UserID#

Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

The error occurred in D:\Inetpub\mannapremier\daily_report2.cfm: line 51

49 :         Orders.UserID 50 : FROM (US_Dealers INNER JOIN Orders ON US_Dealers.ID = Orders.DealerID) INNER JOIN ProductOrders ON Orders.ID = ProductOrders.OrderID 51 : WHERE SaleDate = <cfqueryPARAM value = "#URL.SaleDate#" CFSQLType = "CF_SQL_DATE"> AND UserID = #URL.UserID# 52 : ORDER BY Orders_ID, OrderID 53 : </cfquery>

SQLSTATE   07002 SQL    SELECT Orders.ID AS Orders_ID, US_Dealers.ID AS US_Dealers_ID, US_Dealers.DealerName, US_Dealers.DealerAddress, US_Dealers.DealerCity, US_Dealers.DealerState, US_Dealers.DealerZIPCode, ProductOrders.OrderID, ProductOrders.Product, ProductOrders.Quantity, ProductOrders.BagsCases, ProductOrders.NewExisting, Orders.SaleDate, Orders.TerritoryManager, Orders.Distributor, Orders.DealerID, Orders.PONumber, Orders.Variable, Orders.ISOCheck, Orders.ISOCheckNumber, Orders.ISOCheckAmount, Orders.UserID FROM (US_Dealers INNER JOIN Orders ON US_Dealers.ID = Orders.DealerID) INNER JOIN ProductOrders ON Orders.ID = ProductOrders.OrderID WHERE SaleDate = (param 1) AND UserID = 10124 ORDER BY Orders_ID, OrderID

I'm obviously doing something wrong...again!

[Adam Cameron.]

OK, well your SQL looks fine to me now, too.  But then again Access is weird, and I never use it, so that hardly counts for anything 😉

Also put your other dynamic value into a <cfqueryparam> too.  You should pretty much always use <cfqueryparam> tags for dynamic values.

--

Adam

[ilssac]

Post up the SQL code from Access that worked.

[Guest]

Here is the SQL from Access:

SELECT Orders.ID AS Orders_ID, US_Dealers.ID AS US_Dealers_ID, US_Dealers.DealerName, US_Dealers.DealerAddress, US_Dealers.DealerCity, US_Dealers.DealerState, US_Dealers.DealerZIPCode, ProductOrders.OrderID, ProductOrders.Product, ProductOrders.Quantity, ProductOrders.BagsCases, ProductOrders.NewExisting, Orders.SaleDate, Orders.TerritoryManager, Orders.Distributor, Orders.DealerID, Orders.PONumber, Orders.Variable, Orders.ISOCheck, Orders.ISOCheckNumber, Orders.ISOCheckAmount, Orders.UserID

FROM (US_Dealers INNER JOIN Orders ON US_Dealers.[ID] = Orders.[DealerID]) INNER JOIN ProductOrders ON Orders.[ID] = ProductOrders.[OrderID]

WHERE (((Orders.SaleDate)=#11/2/2009#) AND ((Orders.UserID)=10124));

[-__cfSearching__-]

Here is the SQL from Access:

ORDER BY Orders_ID, OrderID

And what happens when you add the ORDER BY clause that you have in your cfquery?

[Guest]

When I try to run the query it prompts me to enter the parameter value Orders_ID.

[ilssac]

That's becuase Orders_ID does not exist in your database schema.  It is an alias in your select clause and you are not allowed to use an alias in the Order By clause you have to use the actual table.fieldName you want to order by.

[-__cfSearching__- · Accepted solution]

That's becuase Orders_ID does not exist in your database

schema.  It is an alias in your select clause and you are

not allowed to use an alias in the Group By clause you have

to use the actual table.fieldName you want to order by.

Another reason to use MS SQL, MySQL, etcetera. They do let you ORDER BY an alias

[Guest]

Here's a weird one. The problem was with the ORDER BY clause. Another case of me failing to see the obvious... Orders.ID and ProductOrders.OrderID are the same value. However, I had to remove the cfqueryparam's and put back the odbc in order for this to work. The cfqueryparams only returned param1 and param2. Oh well, it works. Thanks cf, you always point me in the right direction.

[Adam Cameron.]

Ah.  That's an alias.  You might want to use the original column name, ie: Orders.ID.

?

--

Adam

[ilssac]

And one BIG question I didn't notice being asked.

Which Database driver are you using in your DSN.  There are two Access drivers that ship with ColdFusion and I hear that one is much more problematic then the other.

[-__cfSearching__-]

Unfortunately it does look like valid SQL to me which is

why I can't figure it out on my own.

Access is finicky. Try commenting out different parts of the query to see if you can hone in on the source of the problem

What happens if you temporarily comment out the SELECT ColumnNames ... part and replace it with SELECT *?

ie

SELECT *

FROM (US_Dealers INNER JOIN Orders ON US_Dealers.ID = Orders.DealerID) INNER JOIN ProductOrders ON Orders.ID = ProductOrders.OrderID

... rest of the query ...

[ilssac]

You weren't supposed to remove the ODBC stuff from the CFML.  That was for when you run taht code in access sql view, it might not understand the odbc syntax.

[Guest]

It gives me the recordset I expect. I couldn't run the SQL with the WHERE clause, but when I added the criteria in design view I get the result I want. What does that mean?

[ilssac]

Looking at the SQL View for the query you built with design view, how does it compare to the SQL produced by your CFML.

I.E. the actual generated SQL in the table at the bottom of the page not the SQL and CFML in the <cfquery..> tag that builds this SQL.

[Dan_Bracuk]

The square brackets in your where clause might be the problem.