(Sorry I'm creating this as a topic. For the life of me, I can't find where to start a new question!)

I am working on a project where I've decided to keep the project database separate from the DNN database. All good... connection string set up in web.config... all templates and forms happily connecting.

Now I want to grab the DNN profile images of the various users and display them on their associated pages with info from the XMP database. It just so happens that I have set up the usernames in DNN to be email addresses, and those same email addresses are stored in the non-DNN database.

Using SSMS, I was able to produce a SELECT query that pulled the associated UserID from the DNN database. This UserID can be used by BBImageHandler to spit out the associated user's profile pic. Here's the SQL statement that worked:

SELECT a.*, b.RegionName, ISNULL (c.UserID, 0) FROM buaofem.buaofe.Umpires a INNER JOIN buaofem.buaofe.Regions b ON a.Region=b.RegionID LEFT OUTER JOIN buaofe.dbo.Users c ON a.Email=c.UserName WHERE a.UmpireID=[some id]

My problem came when I tried to put this into the DetailDataSource, like this:

<DetailDataSource CommandText="SELECT a.*, b.RegionName, ISNULL (c.UserID, 0) FROM buaofem.buaofe.Umpires a INNER JOIN buaofem.buaofe.Regions b ON a.Region=b.RegionID LEFT OUTER JOIN buaofe.dbo.Users c ON a.Email=c.UserName WHERE a.UmpireID=@UmpireID" ConnectionString="[[ConnectionString:XModPro]]">

I can use any of the fields from "a" and I can use RegionName from "b", but if I try and refer to UserID from "c" (the DNN user ID that I need), the page breaks with the message: "The given key was not present in the dictionary."

I presume this is because it hasn't created UserID because the SELECT statement didn't find it as it wasn't able to connect to the DNN database. I further presume that this is because I have ConnectionString:XModPro, so it can't find database buaofe (the DNN database), which uses another connection string.

I can make this work by creating a column in my XMP database for the DNN IDs, and then manually populate these. However, I'd love to get my cross-database join to work and give me the result I need, so if anyone has any ideas let me know (or perhaps Kelly can let me know if it's not possible based on the way XMP handles the connection string).