Can't sort on Field Contact...getting ambiguous error

[Jeremy Kinser]

Hello,
I have a two table database that is giving me a headache. The two tables are
Jobs & Contacts
In each table there is a Contact field so I use a Join statement:

SELECT Distinct Jobs.* FROM Jobs, Contacts WHERE Jobs.Contact = Contacts.Contact

When I go to do a sort on the column by clicking on the column heading, I get this error...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'Contact'.
/employment/clientview.asp, line 78

Any help is greatly appreciated... here is my code as well....


---------------------------------------------------------------------------
<%
Set MyDb = Server.CreateObject(&#34;AspDB.EP&#34
MyDb.dbDSN = &#34;DSN=JobsDB; UID=sqluser; PWD=;&#34;
MyDb.DbUnit=100
MyDb.DBColor=&#34;2,auto,steelblue&#34;
MyDb.dbFilterFlds = &#34;Facility,Department,Job_Title,Job_Type,Closin g_Date,Shift,Hours/PP&#34;
MyDb.DbFilterDropFlds = &#34;Facility,,Jobs,Facility,,,,,Distinct;Job_Type ,,Jobs,Job_Type,,,,,Distinct;Contact,,Jobs,Contact ,,,,,Distinct;Shift,,Jobs,Shift,,,,,Distinct;Posti ng_Date,,Jobs,Posting_Date,,,,,Distinct;Department ,,Jobs,Department,,,,,Distinct;Job_Title,,Jobs,[Job_Title],,,,,Distinct;Hours/PP,,Jobs,[Hours/PP],,,,,Distinct;Closing_Date,,Jobs,Closing_Date,,,,, Distinct&#34;
MyDb.dbFilterParams = &#34;Help=False, Assistant=True&#34;
MyDb.dbFilterHideFlds = &#34;Contact,Job_ID,Job_Code&#34;
MyDb.dbFormHideFlds = &#34;Job_ID,Job_Code&#34;
MyDb.dbGridHideFlds = &#34;Job_Type, Shift, Requirements, Job_ID,Posting_Date,Job_Code&#34;
MyDb.DbGridTableTag=&#34;border=0 cellspacing=3 cellpadding=4&#34;
MyDb.DbGridIndex = false
MyDb.DbGridInc = &#34;10&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbMagicCell=&#34;Contact,,<A href=resume.asp?Contact=#(Contact)#>#Contact#,Job_ ID=#(Job_ID)#>#Job_ID#</a>;Facility,,#Facility#,index,, &#34;
MyDb.DbMode=&#34;both&#34;
MyDb.DbNavigation=&#34;top&#34;
MyDb.DbNavigationItem = &#34;top,prev,next,bottom,gridrow,filter,color,rel oad&#34;
MyDb.DbNavigationIcon = &#34;gif, grid, form, top, prev, next, bottom, rowplus, rowminus, gridplus, gridminus, formplus,formminus, search, resetfilter, download, color, reload, tables, edit, add, update, delete, save, open, updatebatch&#34;
MyDb.DbStartup = &#34;Filter&#34;
Mydb.dbSQL = &#34;SELECT Distinct Jobs.* FROM Jobs, Contacts WHERE Jobs.Contact = Contacts.Contact&#34;
MyDb.DBStatusBar = true
s = DefaultDelimiters & zHead & &#34;,<Center><h3>Add My record</h3></Center>; &#34;
s = s & zDelHead & &#34;,<CENTER><h3>Delete This record</h3></CENTER>; &#34;
s = s & zAddhead & &#34;,<h3>Master Edit Screen</h3>; &#34;
s = s & zFilter & &#34;,Query String = ; &#34;
s = s & zFilterHead & &#34;,<CENTER><strong><Font size=3 face=arial color=red>Directions:</strong></font><br><Font size=2 face=arial>If you wish to search by keyword without the drop down boxes simply click the <strong>Drop</strong> button.</font></CENTER><font size=2 face=arial>The more fields you choose to search by the narrower your results will be.<br>For more results search by the Facility Field only.</font>; &#34;
s = s & zApplyFilter & &#34;,Go do my Search; &#34;
s = s & zResetFilter & &#34;,Reset this filter screen; &#34;
s = s & zHead & &#34;,<CENTER><strong><font size=&#39;3&#39; face=&#39;arial&#39;>Current Jobs -</font></strong><font size=&#39;2&#39; arial=&#39;face&#39;> Click on the <strong><font face=&#39;arial&#39; size=&#39;2&#39; color=&#39;red&#39;>Facility Field</font></strong> for more detail on a job. Click on the <strong><font face=&#39;arial&#39; size=&#39;2&#39; color=&#39;red&#39;>Contact field</font></strong> to apply online.</font></CENTER><br>; &#34;
s = s & zEdit & &#34;,<FONT SIZE=5>EDIT<BR>NOW!</FONT>; &#34;
s = s & zUpdate & &#34;,<I>Update<BR>This Record<I>;&#34;
s = s & zClear & &#34;,Start Over;&#34;
s = s & zDelete & &#34;,Go Delete; &#34;
s = s & zReturn & &#34;,Return!; &#34; &#39; was Return w/o Change;
s = s & zUpdateHead & &#34;,Update This Record; &#34;
s = s & zCopyRec & &#34;,Make a Copy; &#34;
s = s & zUpdateRec & &#34;,UPDATE-REC!; &#34;
s = s & zDrop & &#34;,Toggle DropDown; &#34;
s = s & zReset & &#34;,Reset Fields!; &#34; &#39; reset button on the Edit screen
s = s & zMemoText & &#34;,Memo;&#34;
s = s & zField & &#34;,The Name of the Fields:;&#34;
s = s & zNewRec & &#34;,NEW RECORD!;&#34;
s = s & zCurrentRec & &#34;,This is the Current Record Now ...;&#34;
s = s & zNotes & &#34;,Notes in this area;&#34;
s = s & zMsgFilterPassThruHelp & &#34;,<B><center></Center></B>;&#34;
MyDb.dbUserLocalText = s
MyDb.aspDBEP

%>

[Mark]

Jeremy,
I think you need to add a dbNameMap to allow sorting on joined columns.
See Programming Example 18 on the Enterprise site at:
http://www.aspdb.com/V2

You will need something like this:
Mydb.dbNameMap = &#34;FldName,FldName,,Table1.FldName;&#34;

Mark.

------------
Jeremy Kinser at 2/28/00 12:09:37 PM

Hello,
I have a two table database that is giving me a headache. The two tables are
Jobs & Contacts
In each table there is a Contact field so I use a Join statement:

SELECT Distinct Jobs.* FROM Jobs, Contacts WHERE Jobs.Contact = Contacts.Contact

When I go to do a sort on the column by clicking on the column heading, I get this error...

Microsoft OLE DB Provider for ODBC Drivers error &#39;80040e14&#39;
[Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name &#39;Contact&#39;.
/employment/clientview.asp, line 78

Any help is greatly appreciated... here is my code as well....


---------------------------------------------------------------------------
<%
Set MyDb = Server.CreateObject(&#34;AspDB.EP&#34
MyDb.dbDSN = &#34;DSN=JobsDB; UID=sqluser; PWD=;&#34;
MyDb.DbUnit=100
MyDb.DBColor=&#34;2,auto,steelblue&#34;
MyDb.dbFilterFlds = &#34;Facility,Department,Job_Title,Job_Type,Closin g_Date,Shift,Hours/PP&#34;
MyDb.DbFilterDropFlds = &#34;Facility,,Jobs,Facility,,,,,Distinct;Job_Type ,,Jobs,Job_Type,,,,,Distinct;Contact,,Jobs,Contact ,,,,,Distinct;Shift,,Jobs,Shift,,,,,Distinct;Posti ng_Date,,Jobs,Posting_Date,,,,,Distinct;Department ,,Jobs,Department,,,,,Distinct;Job_Title,,Jobs,[Job_Title],,,,,Distinct;Hours/PP,,Jobs,[Hours/PP],,,,,Distinct;Closing_Date,,Jobs,Closing_Date,,,,, Distinct&#34;
MyDb.dbFilterParams = &#34;Help=False, Assistant=True&#34;
MyDb.dbFilterHideFlds = &#34;Contact,Job_ID,Job_Code&#34;
MyDb.dbFormHideFlds = &#34;Job_ID,Job_Code&#34;
MyDb.dbGridHideFlds = &#34;Job_Type, Shift, Requirements, Job_ID,Posting_Date,Job_Code&#34;
MyDb.DbGridTableTag=&#34;border=0 cellspacing=3 cellpadding=4&#34;
MyDb.DbGridIndex = false
MyDb.DbGridInc = &#34;10&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbMagicCell=&#34;Contact,,<A href=resume.asp?Contact=#(Contact)#>#Contact#,Job_ ID=#(Job_ID)#>#Job_ID#</a>;Facility,,#Facility#,index,, &#34;
MyDb.DbMode=&#34;both&#34;
MyDb.DbNavigation=&#34;top&#34;
MyDb.DbNavigationItem = &#34;top,prev,next,bottom,gridrow,filter,color,rel oad&#34;
MyDb.DbNavigationIcon = &#34;gif, grid, form, top, prev, next, bottom, rowplus, rowminus, gridplus, gridminus, formplus,formminus, search, resetfilter, download, color, reload, tables, edit, add, update, delete, save, open, updatebatch&#34;
MyDb.DbStartup = &#34;Filter&#34;
Mydb.dbSQL = &#34;SELECT Distinct Jobs.* FROM Jobs, Contacts WHERE Jobs.Contact = Contacts.Contact&#34;
MyDb.DBStatusBar = true
s = DefaultDelimiters & zHead & &#34;,<Center><h3>Add My record</h3></Center>; &#34;
s = s & zDelHead & &#34;,<CENTER><h3>Delete This record</h3></CENTER>; &#34;
s = s & zAddhead & &#34;,<h3>Master Edit Screen</h3>; &#34;
s = s & zFilter & &#34;,Query String = ; &#34;
s = s & zFilterHead & &#34;,<CENTER><strong><Font size=3 face=arial color=red>Directions:</strong></font><br><Font size=2 face=arial>If you wish to search by keyword without the drop down boxes simply click the <strong>Drop</strong> button.</font></CENTER><font size=2 face=arial>The more fields you choose to search by the narrower your results will be.<br>For more results search by the Facility Field only.</font>; &#34;
s = s & zApplyFilter & &#34;,Go do my Search; &#34;
s = s & zResetFilter & &#34;,Reset this filter screen; &#34;
s = s & zHead & &#34;,<CENTER><strong><font size=&#39;3&#39; face=&#39;arial&#39;>Current Jobs -</font></strong><font size=&#39;2&#39; arial=&#39;face&#39;> Click on the <strong><font face=&#39;arial&#39; size=&#39;2&#39; color=&#39;red&#39;>Facility Field</font></strong> for more detail on a job. Click on the <strong><font face=&#39;arial&#39; size=&#39;2&#39; color=&#39;red&#39;>Contact field</font></strong> to apply online.</font></CENTER><br>; &#34;
s = s & zEdit & &#34;,<FONT SIZE=5>EDIT<BR>NOW!</FONT>; &#34;
s = s & zUpdate & &#34;,<I>Update<BR>This Record<I>;&#34;
s = s & zClear & &#34;,Start Over;&#34;
s = s & zDelete & &#34;,Go Delete; &#34;
s = s & zReturn & &#34;,Return!; &#34; &#39; was Return w/o Change;
s = s & zUpdateHead & &#34;,Update This Record; &#34;
s = s & zCopyRec & &#34;,Make a Copy; &#34;
s = s & zUpdateRec & &#34;,UPDATE-REC!; &#34;
s = s & zDrop & &#34;,Toggle DropDown; &#34;
s = s & zReset & &#34;,Reset Fields!; &#34; &#39; reset button on the Edit screen
s = s & zMemoText & &#34;,Memo;&#34;
s = s & zField & &#34;,The Name of the Fields:;&#34;
s = s & zNewRec & &#34;,NEW RECORD!;&#34;
s = s & zCurrentRec & &#34;,This is the Current Record Now ...;&#34;
s = s & zNotes & &#34;,Notes in this area;&#34;
s = s & zMsgFilterPassThruHelp & &#34;,<B><center></Center></B>;&#34;
MyDb.dbUserLocalText = s
MyDb.aspDBEP

%>