Ambiguous column name during filter

I am trying to filter the following SQL command using a filter drop down:

Mydb.dbSQL="SELECT Clients.ClientID, Clients.ClientName, ClientOrders.OrderID FROM Clients INNER JOIN ClientOrders ON Clients.ClientID = ClientOrders.ClientID"
MyDb.dbFilterDropFlds="(;|)Clients.ClientID||| |SELECT Clients.ClientId, Clients.ClientName FROM Clients;"
Mydb.dbFilterFlds="OrderID, Clients.ClientID"
Mydb.AspDBEP


And I get this error:
----------------------------------------
Error # = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'ClientID'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 209
----------------------------------------


So using Mydb.dbDebug=5 I see this:
----------------------------------------
Get_RS_get_Count, SQL=SELECT Clients.ClientID, Clients.ClientName, ClientOrders.OrderID FROM Clients, ClientOrders WHERE ( ClientID = 1) AND Clients.ClientID = ClientOrders.ClientID
----------------------------------------

And it shows CLIENTID=1, not Clients.ClientID like I want it to. How can I set the filter to show Clients.ClientID and not just ClientID, which IS ambiguous.

I'm using version 2.2003EP

Thanks,

Pete

Hi Pete,

There were some problems with the table owner not being maintained. But they
were supposedly all corrected. Here's what I'd like you to do:

1) Reproduce the problem with the SQL PUBS database
2) If successful, contact us at (248) 350-9177 and report it.
3) We'll test it and verify if the latest version fixes it or not.
4) Either way, we'll fix it and send you an update with the fix at no charge.

Thanks,
John


------------
Pete Grivas at 1/21/00 2:02:30 PM

I am trying to filter the following SQL command using a filter drop down:

Mydb.dbSQL="SELECT Clients.ClientID, Clients.ClientName, ClientOrders.OrderID FROM Clients INNER JOIN ClientOrders ON Clients.ClientID = ClientOrders.ClientID"
MyDb.dbFilterDropFlds="(;|)Clients.ClientID||| |SELECT Clients.ClientId, Clients.ClientName FROM Clients;"
Mydb.dbFilterFlds="OrderID, Clients.ClientID"
Mydb.AspDBEP


And I get this error:
----------------------------------------
Error # = 80040E14(-2147217900)
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'ClientID'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 209
----------------------------------------


So using Mydb.dbDebug=5 I see this:
----------------------------------------
Get_RS_get_Count, SQL=SELECT Clients.ClientID, Clients.ClientName, ClientOrders.OrderID FROM Clients, ClientOrders WHERE ( ClientID = 1) AND Clients.ClientID = ClientOrders.ClientID
----------------------------------------

And it shows CLIENTID=1, not Clients.ClientID like I want it to. How can I set the filter to show Clients.ClientID and not just ClientID, which IS ambiguous.

I'm using version 2.2003EP

Thanks,

Pete

Looks like a SQL JOIN syntax issue. Go to your SQL query and put in the SQL to debug. ASP-db is a pass thru tool. It submit the SQL via the DB Connector. You are using ODBC which is very old, OLEDB is also too old. You should be using native connector like SQLClient.

Frank