-- 作者:刘林
-- 发布时间:2017/10/26 13:38:00
-- 保存问题
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”的转换无效。 老师,请问保存加了红色的列就出现上面错误,不要就不提示,是什么原因?
|
-- 作者:有点甜
-- 发布时间:2017/10/26 17:33:00
--
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
|
-- 作者:刘林
-- 发布时间:2017/10/26 21:17:00
--
Dim strlist As List(Of String) = Functions.Execute("sql提取函数",Tables("成绩"),"成绩","_Identify","[_Identify]","单位名称|学校|年级代码|班级|姓名|学生ID|身份证件号|学籍号|班级全称|学段|考试名称|年级|类别|考号|学校班级|学校代码|生物_|地理_|考试室","单位名称|学校|年级代码|班级|姓名|学生ID|身份证件号|学籍号|班级全称|学段|考试名称|年级|类别|考号|学校班级|学校代码|生物_|地理_|考试室",1)
把这句要保存列只留红色“考试室”这列就没有问题,这是什么原因
|