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 bidkikarPosted Nov 26, 2007, 5:54 AM
Its throwing the following exception when i implement ur soln
Object reference not set to an instance of an object.
Mahesh JoshiPosted Nov 23, 2007, 6:51 AM
You are using a CommndBuider object which works fine with simple queries for selecting rows from the database. When you use GetInsertCommand method it requires to execute select command to generate metadata for updating, inserting and deleting data in the table. For your DataAdapter you are using select command which fetches rows from two table and this is the problem. So the solution for this is to use direct update command instead of using GetInsertCommand method of CommandBuilder. e.g.
DataAdapter.UpdateCommand.CommandText = "update set ..........where ....."