updating database
-
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"); } } }
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"); } } }
Nitin Raj Bidkikar
Select query can be run against multiple tables, while Update query can be run only one table. In your application you select data from 2 tables, and store it in a dataset. When you try to update the database you use the same dataset. It's not possible, because the application tries to update 2 tables at the same time. You should do a separate update for each table, with values from your dataset. Hope it helps.
I will use Google before asking dumb questions
-
Select query can be run against multiple tables, while Update query can be run only one table. In your application you select data from 2 tables, and store it in a dataset. When you try to update the database you use the same dataset. It's not possible, because the application tries to update 2 tables at the same time. You should do a separate update for each table, with values from your dataset. Hope it helps.
I will use Google before asking dumb questions
-
Split the dataset into 2 datasets or datatables that contain fields from only one table, and then use these to update the database.
I will use Google before asking dumb questions