老师好，使用论坛上“数据保存时的Sql语句合成函数”时用到了“_Locked  ”隐藏列，列名使用[]或者不用都报错，如下

此主题相关图片如下：qqq111.png

要用到系统隐藏列，该怎么修改代码呢？

1、你的凭证表有_Locked列吗？看你的数据库那里；

2、贴出你写的代码。

有点甜老师，有的，我用的就是你的论坛例子，只加上了_locked列而已。

改代码

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

\'
\'返回集合
\'
Return strlist

有点甜老师，还是上面的函数问题。我用的SQL Server数据库，同一个表修改保存，字段有“Null”值，调用函数会报错。

结合这个帖子，把DbNull类型的列处理一下即可

If Typeof dr(str1) Is dbnull Then

http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=108639&authorid=0&page=0&star=1

有点甜老师，解决了，谢谢！