Dynamic SQL generation is not supported against multiple base tables

Raabi Anony

Hello everybody My following code seems very non-cooperative and issues the error: Dynamic SQL generation is not supported against multiple base tables

Dim conxnString As String = "Data Source=Raabi\\SQLEXPRESS; Initial Catalog=StaffReport; Integrated Security=True"
Dim conxn As New SqlConnection(conxnString)
Dim cmd As SqlCommand = conxn.CreateCommand()
Dim StaffInfoDS As New DataSet()
Dim StaffInfoAdapter As New SqlDataAdapter()
Dim StaffInfoTbl As DataTable

Private Sub PopulateStaffInfoDGV()
Dim cmdText As String
cmdText = "SELECT StaffID, StaffName, tblCampuses.CampusName, Designation, DOB, Gender, NIC, DateOfJoining
FROM tblStaffInfo
INNER JOIN tblCampuses
ON tblStaffInfo.CampusID = tblCampuses.CampusID"

    Try
        cmd.CommandText = cmdText
        StaffInfoAdapter.SelectCommand = cmd
        conxn.Open()
        StaffInfoAdapter.Fill(StaffInfoDS, "StaffInfoTbl")
        StaffInfoDGV.DataSource = StaffInfoTbl
        conxn.Close()
    Catch ex As Exception
        MsgBox("Error: " & ex.Message)
    End Try

End Sub

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim cmdbldr As New SqlCommandBuilder(StaffInfoAdapter)

    cmdbldr.GetUpdateCommand()

' Causes Error: Dynamic SQL generation is not supported against multiple base tables.

    StaffInfoAdapter.Update(StaffInfoDS, "StaffInfoTbl")

End Sub

Any help is highly appreciated.

Peter Leow

According to SqlCommandBuilder Class (System.Data.SqlClient)[^]

Quote:

Automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database. This class cannot be inherited.

The key word here is "single-table". You have to use the appropriate InsertCommand, UpdateCommand, or DeleteCommand to do the job as shown in Updating Data Sources with DataAdapters[^]

Peter Leow https://www.amazon.com/author/peterleow

Raabi Anony

Thanks, Peter, for the attention. Let me try the stuff at the link, provided by you. I may get back, if further guidance is needed. Regards

Mycroft Holmes

As the error states you cannot update the 2 tables via a dataadaptor. The inner join is getting data from 2 table and presenting it to you UI. You need to only update the tblStaffInfo as the campus name is there for display purposes only. I used to do the following: get an empty datatable record by passing in Select * from tblStaffInfo where 1=-1 Add a record to the enpty datatable populate it with the updated content from your joined datatable pass the new record to the dtataadaptor to do the update. Clumsy and ugly but it gets the job done. This is a learning process for you and will be invaluable in the future giving you a grounding in database manipulation.

Never underestimate the power of human stupidity RAH

Raabi Anony

Thank, RAH, for hinting towards a new direction for solution. I will try to implement the idea. Unfortunately, I am almost a novice in databases and therefore I have to struggle a lot in order to grasp the hints. I will appreciate, if you could emphasize a bit more on your point. Regards

Mycroft Holmes

Too much info required for a forum post but here is an article that will help if you work through it. DAL Class and Transact-SQL Generator for C# and VB.NET[^]

Never underestimate the power of human stupidity RAH

Raabi Anony

Thanks, Mycroft, for the link. I will try to follow it and may get back, if needed. Regards