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:
-> fldUserID int IDENTITY(1,1) Unique Key
-> fldName nvarchar(128)
-> 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 :-)
SqlConnection conn = CreateConnection();
SqlDataAdapter adapter = new SqlDataAdapter(
"SELECT tblAddresses.* " +
"FROM tblAddresses " +
"INNER JOIN tblUsers ON tblAddresses.fldUserID = tblUsers.fldUserID " +
"WHERE tblUsers.fldName = @name"
adapter.SelectCommand.Parameters.AddWithValue("name", "Tony Nother");
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand(true);