The problem is the CommandBuilder. It's job under normal conditions is to generate your Insert, Update and Delete statements by analyzing the Select statement you provide. However, when multiple tables are involved, it just cannot do the "dynamic SQL generation". (Yeah, it's pretty lame. I've NEVER used it. As I often say, the CommandBuilder is only for lazy people! ;-) )
Your solution is obvious: Create your own Insert, Update and Delete statements(you can test if they work by running them in SQL server) which use the appropriate parameters and then assign these Commands to the InsertCommand, UpdateCommand and DeleteCommand properties of your TableAdapter. On Feb 8, 9:17 pm, Laura <[email protected]> wrote: > Hi, > > I have a datagridview on a windows form that displays the results from > a query. I need the user to be able to edit data in the datagridview > and thus save any changes made. > > The query uses data from 3 seperate tables. > > I keep getting the error: "Dynamic SQL generation is not supported > against multiple base tables." when I try to update. > > I've been googling this all day, do I have to have seperate insert > statments or something? I understand that I can't do an update to > multiple tables, but how can I get around it? > > This is my code: > > Public Class frmTodaysAbsentees > Dim objConnection As New OleDb.OleDbConnection( _ > "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= FYP.mdb") > Dim TodaysAbsentessTableAdapter As New OleDb.OleDbDataAdapter > ("Select * from TodaysAbsentees", objConnection) > Dim TodaysAbsenteesCommandBuilder As New OleDb.OleDbCommandBuilder > (TodaysAbsentessTableAdapter) > > Private Sub frmTodaysAbsentees_Load(ByVal sender As System.Object, > ByVal e As System.EventArgs) Handles MyBase.Load > Me.TodaysAbsentessTableAdapter.Fill > (Me.DataSet2.TodaysAbsentees) > End Sub > > Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e > As System.EventArgs) Handles btnSave.Click > > TodaysAbsentessTableAdapter.Update(DataSet2.TodaysAbsentees) > DataSet2.AcceptChanges() > > End Sub > End Class > > Thanks!
