Dynamic SQL generation is not supported against multiple base tables.

[sundog1]

Hi Guys,

I've just picked up an old projcet I was working on (about 6 months ago) and trying to get my head around my own messy N00b code.

I've managed to get the grid views working with links back to the tables and showing data in the correct places.
However, I have some tables with values in which seem to be passing correctly but don't seem to update on the order tables.

on the 'Save' button I am using the following code:

      private void btnOrderSave_Click(object sender, EventArgs e)
        {

            System.Data.OleDb.OleDbCommandBuilder cb;
            cb = new System.Data.OleDb.OleDbCommandBuilder(cashDA);


            DataRow DRO = cashOrdersDS.Tables["cashOrders"].NewRow();


            DRO["cashQTY"] = Int64.Parse(txtOrderQTY.Text);
            DRO["cashDescription"] = txtBoxDescrip.Text.ToString();
            DRO["cashSupplier"] = txtBoxSupplier.Text.ToString();
            DRO["cashDate"] = dateTimePicker1.Value.ToString();
            DRO["cashCost"] = Convert.ToDouble(txtBoxCost.Text);
            DRO["cashSell"] = Convert.ToDouble(txtBoxSell.Text);
            DRO["cashAccountRef_FKID"] = Int64.Parse(textBox1.Text);

            cashOrdersDS.Tables["cashOrders"].Rows.Add(DRO);

            cashCustom.Open();
            cashDA.Update(cashOrdersDS.Tables["cashOrders"]);
            cashCustom.Close();

            MessageBox.Show("New Order Line Added.");

            btnEditOrder.Enabled = true;
        }

Whilst stepping through it stops on the following part of code:

 cashDA.Update(cashOrdersDS.Tables["cashOrders"]);

Along with the following error message (see attached screenshot for details).

Any help would be great at this point.

Many thanks
Mark.

[Momerath]

What this means is that the cashOrders table is actually based off information from two tables (or more). What's the statement you use to get the information for the cashOrders table?

[sundog1]

Hi Momerath,

The following SQL is being run to populate the grid views.

                String cashOrdersSQL = "SELECT cashID, cashQTY, cashDescription, cashSupplier, cashDate, cashCost, cashSell, CashAccountRef_FKID from cashOrders INNER JOIN cashCustomers ON cashOrders.CashAccRef_FKID=cashCustomers.CashAccRef WHERE cashCustomers.CashAccRef = " + CustomerID.ToString();
                cashDA = new System.Data.OleDb.OleDbDataAdapter(cashOrdersSQL, cashCustom);


                cashDA.Fill(cashOrdersDS.Tables["cashOrders"]);


                dataGridView2.DataSource = cashOrdersDS.Tables["cashOrders"];

[Momerath]

See, two tables as you are doing an join on them. The built in SQL generator can't deal with it, you'll have to provide your own Update, Insert and Delete statement for the table.

[sundog1]

Ahh. ok

Many thanks for you help on this.. I will have to re-construct an update statemen of its own I guess?

[Momerath]

Here is an article that talks about what you can do, but it's in VB so you'll have to translate :)