CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.

Knowledge Base

Breadrumbs:
>>Development (Web and Desktop) >> Web Development >> ASP (Active Server Pages)
Microsoft OLE DB Provider for SQL Server (0x80040E14) Ambiguous column name

Share With Friends ^{(Updated 6-8-2010)}

Article ID:
75
Date Created
Monday, December 20, 2010
Last Updated
Wednesday, January 5, 2011
This Article Has been Viewed
1854 times
Short Desc
Writing SQL Statements can get a little intense at times and mistakes are made.
Luckily they are simple to figure out mistakes, like what we have here.
Details
When writing out your SQL Statement, you will sometimes add in your column name more than once, when this happens, you will receive an error like this.

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Ambiguous column name 'InsertDate'.
/sitemap.asp, line 11
Recreate Issue
To recreate the issue.

Scenario #1:
<%
sql.commandtext="select col1, col2, col2, col3, col4 from table1"
%>

As you can see in the above SQL Statement, we have 2 col2

Scenario #2:
<%
sql.commandtext="select col1.tab1, col2.tab1, col2.tab2, col3.tab2 from tab1 INNER JOIN tab2 ON tab1.col2 = tab2.col2 where col2=?"
%>

In #2, you will see at the end our WHERE statement only has the column name but not the table that it goes to.
Resolve Issue
To correct this issue
Scenario #1:
<%
sql.commandtext="select col1, col2, col3, col4 from table1"
%>

As you can see, removing the duplicate column name corrects the SQL Statement.

Scenario #2:
<%
sql.commandtext="select col1.tab1, col2.tab1, col2.tab2, col3.tab2 from tab1 INNER JOIN tab2 ON tab1.col2 = tab2.col2 where tab1.col2=?"
%>

In #2, we added the table name to our WHERE statement to correctly write our SQL Statement.

All Topics