Foxtable(狐表)用户栏目专家坐堂 → 保存问题


  共有2882人关注过本帖树形打印复制链接

主题:保存问题

帅哥哟,离线,有人找我吗?
有点甜
  21楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2017/10/30 14:39:00 [只看该作者]

以下是引用刘林在2017/10/30 13:27:00的发言:

同样的sql提取函数,作天可以,今天提示","附近有语法错误了呢

 

 

弹出每一句生成的sql语句,看哪一句有问题。


 回到顶部
帅哥哟,离线,有人找我吗?
天一生水
  22楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:五尾狐 帖子:1137 积分:11218 威望:0 精华:0 注册:2017/9/26 16:30:00
  发帖心情 Post By:2020/7/4 19:31:00 [只看该作者]

遇到了楼主同样的问题,参照蓝老师提供的方法,弹出每一句合成的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


 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  23楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:105495 积分:536466 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2020/7/5 20:16:00 [只看该作者]

应该是单元格为空值,

可以判断一下:

Case "数值"
if dr(str1) is system.dbbull.value then
str = "null"
else
str =  dr(str1)
end if

 回到顶部
总数 23 上一页 1 2 3