troubleshooting Question

SqlCommandBuilder on query with Joins and indexes

Avatar of oobayly
oobaylyFlag for United Kingdom of Great Britain and Northern Ireland asked on
.NET ProgrammingMicrosoft SQL Server 2005
3 Comments1 Solution1488 ViewsLast Modified:
I'm trying to use the SqlCommandBuilder to create insert command for a query including joins.
Before people mention it, I appreciate that the SqlCommandBuilder isn't incredibly "intelligent".

I've 2 tables:
tblUsers
-> fldUserID int IDENTITY(1,1) Unique Key
-> fldName nvarchar(128)

tblAddresses
-> fldAddressID int IDENTITY(1,1) Unique Key
-> fldUserID int
-> fldAddress nvarchar(1024)

When I try executing the code below, I get a "Dynamic SQL generation is not supported against multiple base tables." exception at line 11.
Even though I'm executing the query on 2 tables, I'm returning results from only one table.

However, when I change the Index for tblUsers.fldUserID to to an non-unique index, the InsertCommand can be created.

Can anyone explain why the SqlCommandBuilder fails when the joined table contains unique index, and if they can, can they suggest a work around (other than not using unique indexed :-)

Many thanks,
John
SqlConnection conn = CreateConnection();
      conn.Open();
 
      SqlDataAdapter adapter = new SqlDataAdapter(
        "SELECT tblAddresses.* " +
        "FROM tblAddresses " +
        "INNER JOIN tblUsers ON tblAddresses.fldUserID = tblUsers.fldUserID " +
        "WHERE tblUsers.fldName = @name"
        , conn);
      adapter.SelectCommand.Parameters.AddWithValue("name", "Tony Nother");
      SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
      adapter.InsertCommand = builder.GetInsertCommand(true);
 
      conn.Close();
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros