Updating multiple base data
-
hi, I have fetched two database tables "MTQABenchMarks" and "Users" in the datagridview. This is a windows application. Now I have to update the "MTQABenchMark" database after making changes to the datatable present in the application. When I run the code it is throwing an exception "Dynamic SQL generation is not supported against multiple base tables." How to solve this. the code is public Form1() { InitializeComponent(); } static SqlConnection Con = new SqlConnection("Data Source= 192.168.100.234; Initial Catalog=gishyd; User ID=sa; password=gis*2005"); SqlDataAdapter Da = new SqlDataAdapter("SELECT dbo.Users.UserID, dbo.MTQABenchMark.Id as EmpID, dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Name, dbo.MTQABenchMark.NoofMinPerDay,dbo.MTQABenchMark.PerMonth FROM dbo.Users INNER JOIN dbo.MTQABenchMark ON dbo.Users.UserID = dbo.MTQABenchMark.EID ", Con); DataSet Ds = new DataSet(); private void Form1_Load(object sender, EventArgs e) { Da.Fill(Ds, "MTQABenchMark"); dataGridView1.DataSource = Ds.Tables["MTQABenchMark"]; } private void button2_Click(object sender, EventArgs e) { SqlCommandBuilder CmdBld = new SqlCommandBuilder(Da); Da.InsertCommand = CmdBld.GetInsertCommand(); Da.Update(Ds, "MTQABenchMark"); MessageBox.Show(" Database is Updated"); } } } Please Help. Nitin Raj Bidkikar
Nitin Raj Bidkikar
-
hi, I have fetched two database tables "MTQABenchMarks" and "Users" in the datagridview. This is a windows application. Now I have to update the "MTQABenchMark" database after making changes to the datatable present in the application. When I run the code it is throwing an exception "Dynamic SQL generation is not supported against multiple base tables." How to solve this. the code is public Form1() { InitializeComponent(); } static SqlConnection Con = new SqlConnection("Data Source= 192.168.100.234; Initial Catalog=gishyd; User ID=sa; password=gis*2005"); SqlDataAdapter Da = new SqlDataAdapter("SELECT dbo.Users.UserID, dbo.MTQABenchMark.Id as EmpID, dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Name, dbo.MTQABenchMark.NoofMinPerDay,dbo.MTQABenchMark.PerMonth FROM dbo.Users INNER JOIN dbo.MTQABenchMark ON dbo.Users.UserID = dbo.MTQABenchMark.EID ", Con); DataSet Ds = new DataSet(); private void Form1_Load(object sender, EventArgs e) { Da.Fill(Ds, "MTQABenchMark"); dataGridView1.DataSource = Ds.Tables["MTQABenchMark"]; } private void button2_Click(object sender, EventArgs e) { SqlCommandBuilder CmdBld = new SqlCommandBuilder(Da); Da.InsertCommand = CmdBld.GetInsertCommand(); Da.Update(Ds, "MTQABenchMark"); MessageBox.Show(" Database is Updated"); } } } Please Help. Nitin Raj Bidkikar
Nitin Raj Bidkikar
You have 2 options. 1. Don't use INNER JOIN in your query. Get both tables separately and make relation between them at .NET level. Here you can select like a INNER JOIN only related records in both tables. 2. Don't generate and get INSERT or UPDATE command from Command Builder. Build manually INSERT or UPDATE commands for the ADAPTER. For more information on these approaches GOOGLE. Regards