0

I use this code to show data in my DataGridView

dt.Clear();

DGVEdit.DataBindings.Clear();

da = new SqlDataAdapter("SELECT DISTINCT DocDtls.PrimDocNum, DocDtls.DocNum, DocDtls.Warehouse, DocDtls.Orientation, Transactions.Code, Transactions.QtyIn, Transactions.QtyOut, Transactions.BalanceAfter, Transactions.Remarks, Transactions.Unit FROM DocDtls INNER JOIN Transactions ON DocDtls.PrimDocNum = Transactions.DocNum WHERE DocDtls.Warehouse = @Wrhs", Cn);
da.SelectCommand.Parameters.AddWithValue("@Wrhs", cmbWh.Text);

da.Fill(dt);

DataView dv = new DataView(dt);
dv.RowFilter = string.Format("DocNum Like '%{0}%'", txtDocNum.Text);

if (dv.Count > 0)
{
    DGVEdit.DataSource = dv;
    DGVEdit.DataBindings.Add("Text", dv, "PrimDocNum");
    Cm = (CurrencyManager)this.BindingContext[dv];
}
else
{
    MessageBox.Show("No Data");
}

I need to use SqlCommandBuilder to insert, update, delete records easily (I'm a beginner), so I tried this:

try
{
        Cm.EndCurrentEdit();
        cmdb = new SqlCommandBuilder(da);
        da.Update(dt);
        Cm.Refresh();

        MessageBox.Show("Saved successfully", "Done :)", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception Err)
{
    MessageBox.Show("This error occurred :" + Err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

But it throws this error:

Dynamic SQL generation isn't supported against multiple base table

I added DISTINCT to my query as I read in similar post but it didn't work .

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Csharp Newbie
  • 303
  • 1
  • 10
  • Why are you not using Entity Framework? It's easier for newbies. Raw SQL in code has been proven vulnerable over the years, leading to many CVEs. – JHBonarius May 18 '22 at 12:36
  • 5
    I think you already have your answer, the error is clear. Personally, I've never been a fan of SqlCommandBuilder. Just write the queries yourself. – Crowcoder May 18 '22 at 12:36
  • @JHBonarius Thanks for replying , I don't use Entity because I've faced a problem in adding records when you filter the data if I remember correctly , For example the case in this post I won't be able to add new record to the current set of data I got from select query , Am I wrong ? – Csharp Newbie May 18 '22 at 13:01
  • @Crowcoder Thanks for your reply , The sqlCommandBuilder is easier for me , The same code works everywhere update,edit,delete . – Csharp Newbie May 18 '22 at 13:02
  • Also please read these about `AddWithvalue()`: [AddWithValue is Evil](http://www.dbdelta.com/addwithvalue-is-evil/) & [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) – Aaron Bertrand May 18 '22 at 13:04
  • Another point: don't cache the connection object. Create it when you need it, dispose with `using`. Same with `SqlDataAdapter` – Charlieface May 18 '22 at 13:38
  • `SqlCommandBuilder` may be convenient, but it isn't magic; it can't possibly know how to arrange things so that your inserts or updates modify two tables in such a way that the `JOIN` would still yield the correct result. You'll have to figure out yourself what you want to insert in `DocDtls` and/or `Transactions`. You would of course have the same problem if using something like Entity Framework, but that doesn't mean `SqlCommandBuilder` is the better thing to use. – Jeroen Mostert May 18 '22 at 14:11
  • @JeroenMostert Thanks for your reply , I agree with you it seems that I have to write my queries by my self it's time consuming but , The best way in my case , Thanks all. – Csharp Newbie May 18 '22 at 15:56
  • Entity Framework can do everything you want. You can do queries on the tables automatically loading related data and you can add new entities to the collections – JHBonarius May 18 '22 at 17:18

0 Answers0