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