Dynamic SQL generation is not supported against multiple base tables - ERROR

[Laura]

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!

[Cerebrus]

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.

[Laura]

Thanks for that.

I wrote some code as follows:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnSave.Click

Dim modifiedChildRecords As DataSet2.TodaysAbsenteesDataTable
= _
CType(DataSet2.TodaysAbsentees.GetChanges
(Data.DataRowState.Modified), _
DataSet2.TodaysAbsenteesDataTable)

Try
If modifiedChildRecords IsNot Nothing Then
TodaysAbsentessTableAdapter.Update
(modifiedChildRecords)
End If
TodaysAbsentessTableAdapter.Update
(DataSet2.TodaysAbsentees)
DataSet2.AcceptChanges()
MsgBox("Update Successful!")
Catch ex As Exception
MsgBox("Update Failed!")
Finally
If modifiedChildRecords IsNot Nothing Then
modifiedChildRecords.Dispose()
End If

End Try
End Sub

However it's not updating. Am I missing something?
From the code i have just added, I'm obviously trying to update the
tableadabter, is that right? If the tableadapter is updated, does that
mean that the related tables get updated?

Thanks,

> > Thanks!- Hide quoted text -
>
> - Show quoted text -

[Cerebrus]

Please check your email.

--
Cerebrus.

[Cerebrus]

It appears that you are not missing anything. The TableAdapter doesn't
itself update, it's job is only to update the database with the
changes from the Dataset.

What happens when you comment out the following line ?
---
TodaysAbsentessTableAdapter.Update(DataSet2.TodaysAbsentees)
---

It seems to me that this line should not be there at all... if
required, you should be updating the Master table, not the child table
again.