SQL Server error '80040e14' Ambiguous column name

Question

I have an admin page to search for products to edit, but the page keeps returning the error:

Microsoft OLE DB Provider for SQL Server error '80040e14' Ambiguous column name 'prod_id'. /__admin/searchproducts.asp, line 89

I'm unsure why this error is cropping up, because the page and site is a direct copy of another website and associated MSSQL database and the search product page works on that site.

This is the code in question (not sure if it will be easy to read here though);

if request("fldSubmitted") <> "" then 
if request("fldprodid") <> "" and isNumeric(request("fldprodid")) then
    SQL = "select * from products where prod_id = " & cdbl(request("fldprodid"))
else
    SQL = "select "
    if request("showtop") <> "all" then
        SQL = SQL & " top " & request("showtop") & " " & replace(replace(request("orderby")," asc","")," desc","") & ", "
    end if
    SQL = SQL & "prod_name, prod_id, prod_code, prod_icon, prod_thumb, prod_numViews, prod_archived"
    if request("fldLabel") <> "" then SQL = SQl & ", label_name"
    if request("fldCat") <> "" then SQL = SQL & ", cat_name"
    if request("fldSubcat") <> "" then SQL = SQL & ", subcat_name"
    SQL = SQL & " from products"
    if request("fldLabel") <> "" then SQL = SQL & ", labels"
    if request("fldCat") <> "" then SQL = SQL & ", categories"
    if request("fldSubcat") <> "" then SQL = SQl & ", subcategories"
    sql = sql & " where 1=1"
    if request("fldLabel")<> "" then SQL = SQL & "and prod_label = label_id "
    if request("fldCat") <> "" then SQL = SQL & "and prod_category = cat_id "
    if request("fldSubcat") <> "" then SQL = SQL & "and prod_subcategory = subcat_id "
    if request("fldName") <> "" then SQL = SQL & " and (prod_name like '%" & replace(request("fldName"),"'","''") & "%')"
    if request("fldCode") <> "" then SQL = SQL & " and (prod_code like '%" & replace(request("fldCode"),"'","''") & "%')"
    if request("fldLabel") <> "" then SQL = SQL & " and prod_label = " & request("fldLabel")
    if request("fldCat") <> "" then SQL = SQL & " and prod_category = " & request("fldCat")
    if request("fldSubcat") <> "" then SQL = SQL & " and prod_subcategory = " & request("fldSubcat")
    if request("fldArchived") = "No" then 
        SQL = SQL & " and prod_archived = 0"
        if request("instock") = "No" then SQL = SQL & " and prod_numleft > 0"
    end if

    SQL = SQL & " order by " & request("orderby")
end if

Answer 1

The problem is that the query will select the column prod_id, but more than one of the tables referenced has a column with that name.

The query returns results from more than one table, but exactly which table depends on the values of the various parameters. So, that might explain why it works in one circumstance but not another.

You can make the prod_id reference unambiguous by prefixing it with the table name, e.g.

myTable.prod_id

I presume it's fairly obvious (to you) which table is the right one (of course it has to be a table that's ALWAYS part of the query, not one that's only there under certain conditions).

Answer 2

In the second half of the query the you could possibly select from the tables products, labels, categories and subcategories. If any of these tables have a prod_id the DBMS won't know which one you are referring to. A simple revision is to alias each table, e.g products p, labels l, etc.

An even better refacotoring would be to alter the query to us joins:

SELECT p.prod_name, l.label_name
FROM products p
JOIN labels l
    ON l.label_id = p.label_id
WHERE p.in_stock > 0

One other tip when dealing with these problems is to response.write the SQL string and copy it in to Management Studio instead, this will help you see passed the string manipulation to the error.

Finally, I would suggest looking at Stored Procedures so you can remove the SQL from your application.

Edit

Following on from some chat in the comments, if Stored Procedures are out of the question then a parameterized query would be a good step forward. This will bring a performance gain as the query plan will be cached and avoids the most basic forms of SQL Injection attack.

Answer 3

Are you sure that there is only one table with a column named prod_id in the list of tables that you're using in the query?

Answer 4

The code is a little difficult to read and work through but unless you can guarantee the same query runs in both the working and non-working scenarios then I would put it down to that.

This error is usually seen when you have more than one table or view in your query with the same column name and have not explicitly said which one you wanted to use. You should get in the habit of prefixing all columns with the table/view if there is going to be more than one to avoid ambiguity.

EDIT: in this example, obviously your products table has a prod_id but you'll probably be able to confirm that labels, categories or subcategories also has a prod_id column

Answer 5

Thank you for all of the replies. Sorry for not replying soon, but I haven't received any alerts to say that people had replied. Lol.

I seem to have fixed the error now. I removed 'prod_id' from the following line; SQL = SQL & "prod_name, prod_id, prod_code, prod_icon, prod_thumb, prod_numViews, prod_archived"

Seems to work okay now. A little odd that the other site works with the exact same code, but all sorted now. :D

Once again, many thanks for the replies. Very much appreciated.