dynamic sql generation is not supported against multiple base tables

Question

I tried to add a new row to a Table in an SQL DB, but I had a problem :

dynamic sql generation is not supported against multiple base tables

this is the code I tried :

private MyClass myClass = new MyClass();
private SqlDataAdapter adapter;
private SqlDataAdapter adapter2;

private void GestionCollections_Load(object sender, EventArgs e)
{
     adapter = new SqlDataAdapter("select Id_Collection ID, Libelle_Collection Collection,Libelle_Editeur Editeur from Collection_ left join Editeur on Id_Editeur = Collection_.Id_Editeur_Editeur", myClass.cnx);
     adapter.Fill(myClass.ds, "Collection_");

     adapter2 = new SqlDataAdapter("Select Id_Editeur ID,Libelle_Editeur Editeur from Editeur", myClass.cnx);
     adapter2.Fill(myClass.ds, "Editeur");
}

private void AjouterBarButton_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
    String newKeyWordCollectionName = ajoutCollection.KeyWordCollectionName;
    String newKeyWordAEditeurName = ajoutCollection.KeyWordEditeurName;        
    DataRow row = myClass.ds.Tables["Collection_"].NewRow();
    row[1] = newKeyWordCollectionName;

    foreach(var myRow in myClass.ds.Tables["Editeur"].AsEnumerable())
    {
         if (newKeyWordAEditeurName == myRow[1] as String)
              row[2] = (int)myRow[0];
    }
     myClass.ds.Tables["Collection_"].Rows.Add(row);
     SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
     adapter.Update(myClass.ds, "Collection_");

}

Solution 1

Change your select query and add distinct with inner join.

For example there are two query from which you can understand that what I want to tell you

Wrong query

select iop.pob_id, iop.pob_product_id, iop.pob_qty, iop.pob_unit_id
    , iop.pob_rate, iop.pob_value, iop.pob_fiscalyear_id
    , **p.product_desc** as orderBy from inv_product_open_balc iop
left join inv_product p on iop.pob_product_id = p.product_id
where p.product_desc like 'Air Freshner%' and iop.pob_fiscalyear_id = 3

Correct query

select distinct iop.pob_id, iop.pob_product_id, iop.pob_qty
    , iop.pob_unit_id, iop.pob_rate, iop.pob_value, iop.pob_fiscalyear_id
    , **(select Product_desc from** inv_product p where p.product_id = iop.pob_product_id )as orderBy
from inv_product_open_balc iop
inner join inv_product p on iop.pob_product_id = p.product_id
where p.product_desc like 'Air Freshner%' and iop.pob_fiscalyear_id = 3

Solution 2

You can't use an SqlCommandBuilder here:

Automatically generates single-table commands that are used to reconcile changes made to a DataSet with...

The key words here being "single-table". It has no way to reverse engineer from the SELECT statement how a specific update should be applied (e.g. if you NULL all of the columns from the right side of a left join, should it delete the row, or set each column to null.

You need to author appropriate Insert, Update and Delete commands on the SqlDataAdapter.

Comments

[Aghilas Yakoub]

Nota : In your Update selectCommand, you defined left join query, and so you can create left join query, replace this query with just select.

[Aghilas Yakoub]

Left Join query is not single table , you have two tables or more

[Spoon Yukina]

I don't want just select statement I must use the left join query, check this : link

[Aghilas Yakoub]

with SqlCommandBuilder and left join you can't Spoon generate your queries

[Spoon Yukina]

So isn't there any solution ? because I need to use this left join query to give me the right results that I want

[Aghilas Yakoub]

you want impossible solution Spoon, i understand you why you have bug , i bring you answer about your problem

[Fergal Moran]

This is a great answer, surprised it doesn't have more upvotes.

[Sidupac]

worked fine for me but i didn't need to include the inner join or distinct: select iop.pob_id,iop.pob_product_id,iop.pob_qty,iop.pob_unit_id, iop.pob_rate,iop.pob_value,iop.pob_fiscalyear_id,(select Product_desc from inv_product as p where p.product_id = iop.pob_product_id ) as orderBy from inv_product_open_balc as iop where p.product_desc like 'Air Freshner%' and iop.pob_fiscalyear_id = 3