遇到了楼主同样的问题,参照蓝老师提供的方法,弹出每一句合成的sql语句,发现数值列变动,涉及到null値时会报错。如图示。
如下修改函数代码(红色代码)可以避免报错,也可以完成保存。但不知道出错的根本原因,还请老师指教!
此主题相关图片如下:截屏图片 (3).jpg
自定义行数修改:
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 = "INS ERT INTO " & kName & " ("
'生成语句前导
For Each str1 As String In _表格_字段对应表.Values
sql11 += str1 & ","
Next
sql11 = sql11.Trim(",") & ") VA LUES ("
For Each dr As System.Data.DataRow In ChangeTable.Rows
'遍历设置列值
sql = sql11
For Each str1 As String In _表格_字段对应表.Keys
str = "'" & dr(str1) & "',"
Sel ect 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 Sel ect
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)
Sel ect Case _表格_字段类型表(tbid)
Case "文本"
strkey = "'" & dr(tbid) & "'"
Case "日期"
If lx = 1 Then
strkey = "#" & dr(tbid) & "#"
Else
strkey = "'" & dr(tbid) & "'"
End If
End Select
'遍历设置列值
sql = "UP DATE " & kName & " Set "
For Each str1 As String In _表格_字段对应表.Keys
str = "'" & dr(str1) & "'"
Sel ect Case _表格_字段类型表(str1)
Case "数值"
'str = dr(str1) '数值列会报错
str = dr(str1) & "," '会产生2 个逗号,在最后替换成1个
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.replace(",,",",").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)
Sel ect Case _表格_字段类型表(tbid)
Case "文本"
strkey = "'" & strkey & "'"
Case "日期"
If lx = 1 Then
strkey = "#" & strkey & "#"
Else
strkey = "'" & strkey & "'"
End If
End Sel ect
sql = "DEL ETE FR OM " & kName & " WHERE " & kid & "=" & strkey
strlist.Add(sql)
Next
End If
'返回集合
Return strlist