Linked Data source wont return any fields

Question

I have created a few data connections and wish to use a dataview web part to display the information. Currently I am able to display any given table on its own.

I have a database connection table containing the field 'locID' datatype 'number(15,0)' and a second table which also contains a 'locID' datatype number(15,0). The purpose of these fileds was to create foriegn keys as it is all related data.

When I create a linked data source and use a 'join' type based on 'locID' I get a popup 'selected fields do not contain matching data'. No fields are returned

If I used 'Merge' it simply does not return anything

If I create a DVWP based on one dataconnection & add a related source I get an error 'Object moved, Object moved here'

Why won't anything connect my data, the values are good

Answer 1

I was able to overcome this issue by inserting a dvwp with a single data connection & then changing the select command manually (this workaround onlu succeds if all data is in same database)

Change

      <SharePoint:SPSqlDataSource SelectCommand="SELECT [TableA].* FROM [TableA] ".......

To

      <SharePoint:SPSqlDataSource SelectCommand="SELECT [TableA].* , [TableB].* FROM [TableA] LEFT JOIN [TableB] ON [TableA].[locID] = [TableB].[locID ]" .......