Infralution Support

[Xavier De Donder]

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

[Xavier De Donder]

[Infralution]

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

[Xavier De Donder]

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

[Infralution]

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

[Xavier De Donder]

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.