Microsoft Access with Unicode

[stevelanza]

Hi,

I recently upgraded my website from CF5 to CF9 running on a virtual server. Below are the specs.

CF Version: 9,0,0,251028

Edition: Enterprise

Operating System: Windows Server 2008

My old code worked fine except that certain queries were running slow. My hosting provider suggested that I change the driver from "Microsoft Access" to "Microsoft Access with Unicode." As suggested, it did make things much faster. However, there were some pages on my site that didn't work. I did some research and found I had used two "reserved" words for table names that caused the pages to fail when using "Microsoft Access with Unicode" as the driver. Oddly enough, the pages worked fine with the regular "Microsoft Access" driver.

I am now down to just one error left that I cannot resolve. I get the following error ONLY when using the "Microsoft Access with Unicode" driver.

Error Executing Database Query.

Query Of Queries runtime error.
The select column reference [GetList.ItemNo] is not found in table [GetList].

This is the code it is referring to.

<!--- Use QofQ to join queried records against cart data --->
<CFQUERY NAME="GetProducts" DBTYPE="query">
  SELECT

  GetList.ItemNo AS ItemNo,
  GetList.Item AS Item,
  GetList.Grades AS Grades,
  GetList.Price AS Price,
  GetList.OrderQty AS Quantity,
  (GetList.OrderQty * GetList.Price) AS ProductSubTotal
  FROM GetList
</CFQUERY>

Is there an issue with "Microsoft Access with Unicode" not playing nicely with Query Of Queries?

I would greatly appreciate any help with this issue.

Thanks,

Steve

[-__cfSearching__- · Accepted solution]

...  two "reserved" words for table names that caused the pages to fail when using "Microsoft Access with Unicode" as the driver. Oddly enough, the pages worked fine with the regular "Microsoft Access" driver.

Different database drivers can have different reserved words.

Error Executing Database Query.

Query Of Queries runtime error.
The select column reference [GetList.ItemNo] is not found in table [GetList].

This is the code it is referring to.

It seems very unlikely that error has anything to do with driver used in the original query.  What does the original query "GetList" look like? Does it contain the column "ItemNo"  OR contain that column more than once?

[stevelanza]

THANK YOU!  THANK YOU!  THANK YOU!

I never noticed that I had included the column "ItemNo" more than once (as you can see below). I removed the extra and it worked perfectly!

Thanks again!

Steve

<cfquery name="GetList"
DATASOURCE="#request.ds#"
USERNAME="#request.dsuser#"
PASSWORD="#request.dspass#">
SELECT
o.OrderID,
o.OrderDate,
o.ShipState,
o.ShipCountry,
o.TaxExempt,
o.PromotionalCode,
oi.ItemNo,
oi.OrderQty,
oi.Price,
p.ItemNo,
p.Item,
p.Grades 
FROM Orders o,
OrdersItems oi,
Products p
WHERE o.OrderID = #ATTRIBUTES.OrderID#
AND oi.OrderID = o.OrderID
AND p.ItemNo = oi.ItemNo  
</cfquery>