主题：保存问题

Dim st1 As Date = Date.Now
Dim strlist As  List(Of String)  = Functions.Execute("sql提取函数",Tables("成绩"),"成绩","_Identify","[_Identify]","单位名称|学校|年级代码|班级|姓名|学生ID|身份证件号|学籍号|班级全称|学段|考试名称|年级|类别|考号|学校班级|学校代码|生物_|地理_|考试室","单位名称|学校|年级代码|班级|姓名|学生ID|身份证件号|学籍号|班级全称|学段|考试名称|年级|类别|考号|学校班级|学校代码|生物_|地理_|考试室",1)
Dim sql As String
Dim cmd As New SQLCommand
cmd.C
Dim Count As Integer = 0
try
    Connections("180.153.108.50").BeginTransaction
    For Each s As String In strlist
        cmd.CommandText = s
        Count += cmd.ExecuteNonQuery()
        output.show(s)
    Next
    Connections("180.153.108.50").Commit '提交事务,所有操作生效
Catch ex As Exception '如果出错
    msgbox(ex.message)
    Connections("180.153.108.50").Rollback() '回滚事务,撤销所有操作
End Try
Tables("成绩").DataTable.BaseTable.AcceptChanges()   '提交修改
MessageBox.Show("亲,我正在为节省您的每一秒钟努力,本次保存共耗时: " & (Date.Now - st1).TotalSeconds & "秒")
Tables("初中报名_table1").AllowEdit = False

 

 

.NET Framework 版本：2.0.50727.5477
Foxtable 版本：2017.8.19.1
错误所在事件：自定义函数sql提取函数
详细错误信息：
调用的目标发生了异常。
从类型“DBNull”到类型“String”的转换无效。
老师，请问保存加了红色的列就出现上面错误，不要就不提示，是什么原因？

 

赋值的时候的时候，修改

 

Case "文本"
                strkey = "'" & dr(tbid) & "'"

 

改成

 

Case "文本"
                strkey = "'" & dr(tbid).Tostring & "'"

结果一样呢

 

无论是新增还是更新，都要像2楼那样改。

 

贴出你 sql提取函数 的代码。

 

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:sql.txt

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.DataCols(s)
        If .IsString Then s1 = "文本"
        If .IsNumeric Then s1 = "数值"
        If .IsDate Then s1 = "日期"
        If .IsBoolean Then s1 = "逻辑"
    End With
    _表格_字段类型表.Add(s, s1)
Next
'
'获取主键列的类型
'
If tbid = "_Identify" Then
    s1 = "_Identify"
Else
    With tb.DataTable.DataCols(tbid)
        s1 = "未知"
        If .IsString Then s1 = "文本"
        If .IsNumeric Then s1 = "数值"
        If .IsDate Then s1 = "日期"
    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
           
            If Typeof dr(str1) Is dbnull Then
                str = "null,"
            else
                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
            end if
            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)
        If Typeof dr(tbid) Is dbnull Then
            str = "null,"
        else
            Select Case _表格_字段类型表(tbid)
                Case "文本"
                    strkey = "'" & dr(tbid) & "'"
                Case "日期"
                    If lx = 1 Then
                        strkey = "'" & dr(tbid) & "'"
                    Else
                        strkey = "'" & dr(tbid) & "'"
                    End If
            End Select
        end if
        '遍历设置列值
        sql = "UPDATE " & kName & " Set  "
        For Each str1 As String In _表格_字段对应表.Keys
            If Typeof dr(tbid) Is dbnull Then
                str = "null"
            else
                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
            end if
            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

还是不行，效果一样

 实例发上来测试。

自行加入msgbox定位错代码位置。

Dim strlist As List(Of String) = Functions.Execute("sql提取函数",Tables("成绩"),"成绩","_Identify","[_Identify]","单位名称|学校|年级代码|班级|姓名|学生ID|身份证件号|学籍号|班级全称|学段|考试名称|年级|类别|考号|学校班级|学校代码|生物_|地理_|考试室","单位名称|学校|年级代码|班级|姓名|学生ID|身份证件号|学籍号|班级全称|学段|考试名称|年级|类别|考号|学校班级|学校代码|生物_|地理_|考试室",1)

 

把这句要保存列只留红色“考试室”这列就没有问题，这是什么原因