Infralution Support Forum Index Infralution Support
Support groups for Infralution products
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Ambiguous column name 'ID'

 
Post new topic   Reply to topic    Infralution Support Forum Index -> Virtual Data Objects Support
View previous topic :: View next topic  
Author Message
Xavier De Donder



Joined: 28 May 2014
Posts: 4

PostPosted: Wed May 28, 2014 2:12 pm    Post subject: Ambiguous column name 'ID' Reply with quote

I bought the LicenseTracker source code to do small adaptations to our needs. After extending the database model I get the error "Ambiguous column name 'ID'" when trying to access a Sale object.

The original model links a sale to a customer. The new model links a sale to a site, which is on its turn linked to a customer. The SaleRecordset.JoinClause was adapted accordingly, adding one more LEFT JOIN. The error is caused by an auto-generated where clause "WHERE ID=20", which is indeed ambiguous (Sale joined with Site, joined with Customer, all 3 tables have an ID column)

1. Why did adding the Site table in the join cause this problem, while the Customer table was already in the join, and is also having an ID column?

2. More important: how can I avoid the error? I tried modifying SaleRecordSet.primaryKey from "ID" to "Sale.ID", but that causes other errors : "Item cannot be found in the collection corresponding to the requested name or ordinal." when calling .VirtualRecord.GetPrimaryKeyValue(Recordset rs)

Thanks,
Xavier
Back to top
View user's profile Send private message
Xavier De Donder



Joined: 28 May 2014
Posts: 4

PostPosted: Wed May 28, 2014 3:33 pm    Post subject: Re: Ambiguous column name 'ID' Reply with quote

Xavier De Donder wrote:
how can I avoid the error?


In order not to be blocked any longer I added a dirty quick-fix, by overriding SaleRecordset.GetQueryString(), and replacing "WHERE ID" with "WHERE Sale.ID", but of course I'd be interested to know how to solve this in a cleaner way....

Thanks,
Xavier
Back to top
View user's profile Send private message
Infralution



Joined: 28 Feb 2005
Posts: 4972

PostPosted: Thu May 29, 2014 1:10 am    Post subject: Reply with quote

Your workaround is probably the correct fix. If you have added a join to another table that has an ID column defined then you can no longer use ID without prefixing it with Sale.ID.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
Xavier De Donder



Joined: 28 May 2014
Posts: 4

PostPosted: Mon Jun 02, 2014 6:45 am    Post subject: Reply with quote

Thanks for the reply!
One thing which I'd like to understand is why the code worked without errors before my changes, while it already had 2 tables with ID column in the join (since my changes involve DB schema updates I can't quickly revert to the original situation to inspect the generated SQL)

Original Sale.JoinClause =
(((Sale LEFT JOIN SaleUpgrade ON Sale.ID=SaleUpgrade.From_ID)
LEFT JOIN Customer ON Sale.Customer_ID=Customer.ID)
LEFT JOIN Distributor ON Sale.Distributor_ID=Distributor.ID)
LEFT JOIN Product ON Sale.Product_ID=Product.ID

Updated Sale.JoinClause =
((((Sale LEFT JOIN SaleUpgrade ON Sale.ID=SaleUpgrade.From_ID)
LEFT JOIN Site ON Sale.Site_ID=Site.ID)
LEFT JOIN Customer ON Site.Customer_ID=Customer.ID)
LEFT JOIN Distributor ON Sale.Distributor_ID=Distributor.ID)
LEFT JOIN Product ON Sale.Product_ID=Product.ID"

Original Sale.ExtendedSelect =
"SELECT Sale.*, SaleUpgrade.To_ID AS UpgradedTo_ID, Customer.LastName, Customer.FirstName, Customer.Email, Customer.Country, Customer.Company, Product.Name AS ProductName, Product.Category1_ID, Product.Category2_ID, Distributor.Name AS DistributorName FROM " + JoinClause;

Updated Sale.extendedSelect =
"SELECT Sale.*, SaleUpgrade.To_ID AS UpgradedTo_ID, Site.Customer_ID, Site.Name AS SiteName, Site.Country, Customer.Name AS CustomerName, Customer.Email, Product.Name AS ProductName, Product.Category1_ID, Product.Category2_ID, Distributor.Name AS DistributorName FROM " + JoinClause;


Thanks,
Xavier
Back to top
View user's profile Send private message
Infralution



Joined: 28 Feb 2005
Posts: 4972

PostPosted: Mon Jun 02, 2014 7:23 am    Post subject: Reply with quote

Normally the ambiguous column is only a problem if multiple columns with the same name appear in the select statement. For example if you selected Sale.* and Site.* and both include an ID. Since you are not selecting Site.ID I'm not sure what the issue is. One way you can see however is to run your actual query inside Access (or SQL Server Management Studio if using SQL Server) and see what columns the query generates. MDAC generates the column names automatically.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
Xavier De Donder



Joined: 28 May 2014
Posts: 4

PostPosted: Tue Jun 03, 2014 7:37 am    Post subject: Reply with quote

The error was not caused by the select statement, but by the where clause. Anyway, running through my code changes I notice a place where the original code created a SaleRecordset without use of "extended properties", while I now do use the extended properties. This explains why the original code didn't give the same "ambiguous column" error, even though the original "extended properties" join also contained multiple columns named "ID". All clear now, thanks for being responsive on this forum.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Infralution Support Forum Index -> Virtual Data Objects Support All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group