WinForms 中的多个基表不支持动态 SQL 生成

2021-09-16 00:00:00 sql vb.net sql-server

我在 VB.NET Windows 应用程序中工作

I am working in VB.NET Windows application

在我的加载事件中，我提供了这样的代码，用于将不同表中的数据加载到我的 DataGridView.

In my load event I am giving code like this for loading data from different table to my DataGridView.

为此，我使用这样的代码:

For that I use code like this:

Dim cmd As New SqlCommand("select M.Model,c.Colname,ma.Make from Model_tbl M join Color_tbl C on c.colid=M.mdlid join Make_tbl ma on ma.mkid=c.colid  where mdlid=5", con.connect)
        Dim builder As SqlClient.SqlCommandBuilder = New SqlCommandBuilder(da)

        da.SelectCommand = cmd
        da.Fill(ds, "MyTable")
        If (ds.Tables(0).Rows.Count > 0) Then
            DGV.DataSource = ds.Tables("MyTable")

        End If

单击更新按钮时，我想将数据从我的 DataGridView 更新到不同的表.所以我在我的更新按钮事件中写了这样的代码.

When clicking update button I want to update data to different tables from my DataGridView. So I wrote code like this in my update button event.

 Me.Validate()
        Me.da.Update(Me.ds.Tables("MyTable"))
        Me.ds.AcceptChanges()

但在这条线上

Me.da.Update(Me.ds.Tables("MyTable"))

我收到一个错误:

不支持针对多个基表生成动态 SQL.

Dynamic SQL generation is not supported against multiple base tables.

我的代码有什么问题?

推荐答案

SqlCommandBuilder 当 SELECT 命令包含两个或多个表之间的 JOINS 时，无法生成 DataAdapter 更新命令所需的 UPDATE/INSERT 语句.

SqlCommandBuilder cannot generate the UPDATE/INSERT statements required for the DataAdapter update command when the SELECT command contains JOINS between two or more tables.

你可以在 MSDN 上阅读

On MSDN you can read

SqlCommandBuilder 自动生成单表命令用于协调对数据集所做的更改与关联的SQL Server 数据库.

SqlCommandBuilder automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database.

解决方法是自己提供属性的命令

The workaround is to provide by yourself the commands for the properties

SqlDataAdapter.UpdateCommand 
SqlDataAdapter.InsertCommand 
SqlDataAdapter.DeleteCommand

相关文章