[求助][原创]引用“数据保存时的Sql语句合成函数”出错 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

改代码

Dim tb As Table = Args(0)        '要提取的表格
Dim kName As String = Args(1)    '后台表格的名称
Dim tbid As String = Args(2)     '表格中的主键字段名
Dim kid As String = Args(3)      '后台表格的主键字段名
Dim bgzd As String = Args(4)     '表格中的字段,用|分割
Dim kbgzd As String = Args(5)    '对应的后台表中的字段,用|分割
Dim lx As Integer = Args(6)      '数据源的类型,Access=1,sql=2

'************ 生成字段对应表 *****************************************************************

Dim bgzds() As String = bgzd.Split("|")
Dim kbgzds() As String = kbgzd.Split("|")
If bgzds.Length <> kbgzds.Length Then Return Nothing '长度不等,说明设置错误,退出
Dim _表格_字段对应表 As New Dictionary(Of String, String) '新建一个用于存放字段对应的字典
For Index As Integer = 0 To bgzds.Length - 1
_表格_字段对应表.Add(bgzds(Index),kbgzds(Index))
Next

'******************** 生成字段类型表 ***************************************************
Dim _表格_字段类型表 As New Dictionary(Of String, String) '新建一个用于存放字段类型
'*************遍历获取列的类型
Dim s1 As String
For Each s As String In _表格_字段对应表.Keys
    s1 = "未知"
    With tb.DataTable.basetable.columns(s)
        If .datatype.name = "String" Then
            s1 = "文本"
        ElseIf .datatype.name = "DateTime" Then
            s1 = "日期"
        ElseIf .datatype.name = "boolean" Then
            s1 = "逻辑"
        Else
            s1 = "数值"
        End If
    End With
    _表格_字段类型表.Add(s, s1)
Next
'
'获取主键列的类型
'
If tbid = "_Identify" Then
    s1 = "_Identify"
Else
    s1 = "未知"
    With tb.DataTable.basetable.columns(tbid)
        If .datatype.name = "String" Then
            s1 = "文本"
        ElseIf .datatype.name = "DateTime" Then
            s1 = "日期"
        ElseIf .datatype.name = "boolean" Then
            s1 = "逻辑"
        Else
            s1 = "数值"
        End If
    End With
End If
_表格_字段类型表.Add(tbid, s1)

Dim strlist As New List(Of String) '定义一个集合,用于返回

'
'获取对象
'
Dim systable As System.Data.DataTable = tb.DataTable.BaseTable
'

Dim sql As String
Dim str As String
Dim ChangeTable As System.Data.DataTable = systable.GetChanges(System.Data.DataRowState.Added) '获取新增记录

If ChangeTable IsNot Nothing Then '如果有新增记录
    '
    '循环获取新增语句

    Dim sql11 As String = "INSERT INTO " & kName & " ("

    '生成语句前导
    For Each str1 As String In _表格_字段对应表.Values
        sql11 += str1 & ","
    Next
    sql11 = sql11.Trim(",") & ") VALUES ("

    For Each dr As System.Data.DataRow In ChangeTable.Rows
        '遍历设置列值
        sql = sql11
        For Each str1 As String In _表格_字段对应表.Keys
            str = "'" & dr(str1) & "',"
            Select Case _表格_字段类型表(str1)
                Case "数值"
                    str = dr(str1) & ","
                Case "逻辑"
                    If dr(str1) Then
                        str = "1,"
                    Else
                        str = "0,"
                    End If
                Case "日期"
                    If lx = 1 Then   'Access
                        str = "#" & dr(str1) & "#,"
                    End If
            End Select
            If str.Trim(","," ").Trim = "" OrElse str.Trim(",","#"," ") = "" OrElse str.Trim(",","'"," ")= "" Then
                sql += "null,"
            Else
                sql += str
            End If
        Next
        sql = sql.Trim(",") & ")"
        strlist.Add(sql)
    Next
End If
Dim strkey As String
'***********   合成修改语句 *********************
ChangeTable = systable.GetChanges(System.Data.DataRowState.Modified)   ' 获取修改记录集
'
'如果没有修改记录,返回空值
'
If ChangeTable IsNot Nothing Then
    For Each dr As System.Data.DataRow In ChangeTable.Rows    '循环获取修改语句
        '获取主键值
        strkey = dr(tbid)
        Select Case _表格_字段类型表(tbid)
            Case "文本"
                strkey = "'" & dr(tbid) & "'"
            Case "日期"
                If lx = 1 Then
                    strkey = "#" & dr(tbid) & "#"
                Else
                    strkey = "'" & dr(tbid) & "'"
                End If
        End Select

        '遍历设置列值
        sql = "UPDATE " & kName & " Set "
        For Each str1 As String In _表格_字段对应表.Keys
            str = "'" & dr(str1) & "'"
            Select Case _表格_字段类型表(str1)
                Case "数值"
                    str = dr(str1)
                Case "逻辑"
                    If dr(str1) Then
                        str = "1"
                    Else
                        str = "0"
                    End If

                Case "日期"
                    If lx = 1 Then   'Access
                        str = "#" & dr(str1) & "#"
                    Else
                        str = "'" & dr(str1) & "'"
                    End If
            End Select
            If str.Trim(","," ").Trim = "" OrElse str.Trim(",","#"," ") = "" OrElse str.Trim(",","'"," ")= "" Then

                sql += _表格_字段对应表(str1) & "=null, "
            Else
                sql += _表格_字段对应表(str1) & "=" & str & ", "
            End If
        Next
        sql = sql.Trim(" ",",") & " Where " & kid & "=" & strkey
        strlist.Add(sql)
    Next
End If

'获取删除记录
'
ChangeTable = systable.GetChanges(System.Data.DataRowState.Deleted)
'
'如果没有删除记录,返回空值
'
If ChangeTable IsNot Nothing Then
    '
    '循环获取删除语句

    For Each dr As System.Data.DataRow In ChangeTable.Rows
        '获取主键值
        strkey = dr(tbid,System.Data.datarowversion.original).tostring
        'strkey = dr(tbid)
        Select Case _表格_字段类型表(tbid)
            Case "文本"
                strkey = "'" & strkey & "'"
            Case "日期"
                If lx = 1 Then
                    strkey = "#" & strkey & "#"
                Else
                    strkey = "'" & strkey & "'"
                End If
        End Select
        sql = "DELETE FROM " & kName & " WHERE " & kid & "=" & strkey
        strlist.Add(sql)
    Next

End If

'
'返回集合
'
Return strlist

主题：[求助][原创]引用“数据保存时的Sql语句合成函数”出错