以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  [求助] 外部数据没有输入  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=179904)

--  作者:建筑人生
--  发布时间:2022/9/16 17:05:00
--  [求助] 外部数据没有输入

Dim sts() As String = {"收入", "支出", "银行", "现金", "中转"}

Dim cmd1 As New S   QLCommand
cmd1.C
\'Dim dt1 As DataTable
Dim bm As String
Dim bm2, bm3, bm4, bm5 As Integer

Dim cmd As New S   QLCommand
cmd.C
cmd.CommandText = "S   elect DISTINCT 一级科目,二级科目,三级科目,四级科目,五级科目,收支类别 From {科目表} " \'生成空表
dt = cmd.ExecuteReader() \'记得将参数设置为True

If dt.DataRows.Count > 0 Then
    For i As Integer = 0 To sts.Length - 1 
        Dim cmd0 As New S   QLCommand
        cmd0.C
        Dim dt0 As DataTable
        cmd0.CommandText = "S   elect DISTINCT 一级科目,二级科目,三级科目,四级科目,五级科目,收支类别 From {科目表} Where 收支类别=\'" & sts(i) & "\'" \'生成表 
        dt0 = cmd0.ExecuteReader() \'记得将参数设置为True        
        
        Dim s2s As List(Of String) = dt0.GetValues("二级科目", "二级科目 is not null") 
        For Each s2 As String In s2s 
             bm2 = bm2 + 1
            Dim bm22 As String = Format(bm2, "000") 
            bm = i + 1 & bm22 
            cmd1.CommandText = "U  PDATE {科目表2} SET 科目编码 =\'" & bm & "\' ,科目名称=\'" & s2 & "\'"

            
            Dim s3s As List(Of String) = dt0.GetValues("三级科目", "三级科目 is not null") 
            For Each s3 As String In s3s 
                bm3 = bm3 + 1
                Dim bm32 As String = Format(bm3, "000")\'                   
                bm = i + 1 & bm22 & bm32
                cmd1.CommandText = "U  PDATE {科目表2} SET 科目编码 =\'" & bm & "\' ,科目名称=\'" & s3 & "\'" 
     
                
                Dim s4s As List(Of String) = dt0.GetValues("四级科目", "四级科目 is not null") 
                For Each s4 As String In s4s 
                   bm4 = bm4 + 1
                    Dim bm42 As String = Format(bm4, "000")\'                      
                    bm = i + 1 & bm22 & bm32 & bm42
                    cmd1.CommandText = "U  PDATE {科目表2} SET 科目编码 =\'" & bm & "\' ,科目名称=\'" & s4 & "\'"
                    

                    Dim s5s As List(Of String) = dt0.GetValues("五级科目", "五级科目 is not null") 
                    For Each s5 As String In s5s 
                         bm5 = bm5 + 1
                        Dim bm52 As String = Format(bm5, "000")\'   
                        bm = i + 1 & bm22 & bm32 & bm42 & bm52
                        cmd1.CommandText = "U  PDATE {科目表2} SET 科目编码 =\'" & bm & "\' ,科目名称=\'" & s5 & "\'"
\'                        
                    Next
                    bm5 = 0
                Next
                bm4 = 0
            Next
           bm3 = 0
        Next
        bm2 = 0
    Next 
End If
 MessageBox.Show("数据输入完成")
代码执行完,科目表2没有数据输入

--  作者:有点蓝
--  发布时间:2022/9/16 17:10:00
--  
都没有执行sql:http://www.foxtable.com/webhelp/topics/0696.htm

cmd1.CommandText = "U  PDATE {科目表2} SET 科目编码 =\'" & bm & "\' ,科目名称=\'" & s2 & "\'"
cmd1.ExecuteNonQuery
            
            Dim s3s As List(Of String) = dt0.GetValues("三级科目", "三级科目 is not null") 
            For Each s3 As String In s3s 
                bm3 = bm3 + 1
                Dim bm32 As String = Format(bm3, "000")\'                   
                bm = i + 1 & bm22 & bm32
                cmd1.CommandText = "U  PDATE {科目表2} SET 科目编码 =\'" & bm & "\' ,科目名称=\'" & s3 & "\'" 
cmd1.ExecuteNonQuery
……

--  作者:建筑人生
--  发布时间:2022/9/16 18:15:00
--  
Dim cmd1 As New S   QLCommand
cmd1.C
\'Dim dt1 As DataTable
Dim bm As String
Dim bm2, bm3, bm4, bm5 As Integer

Dim cmd As New SQLCommand
Dim dt As DataTable
cmd.C
cmd.CommandText = "S   elect DISTINCT 一级科目,二级科目,三级科目,四级科目,五级科目,收支类别 From {科目表} " \'生成空表
dt = cmd.ExecuteReader() \'记得将参数设置为True

Dim sts() As String = {"收入", "支出", "银行", "现金", "中转"}


If dt.DataRows.Count > 0 Then
    For i As Integer = 0 To sts.Length - 1
        
        Dim cmd0 As New SQLCommand
        cmd0.C
        Dim dt0 As DataTable
        cmd0.CommandText = "S  elect DISTINCT 一级科目,二级科目,三级科目,四级科目,五级科目,收支类别 From {科目表} Where 收支类别=\'" & sts(i) & "\'" \'生成表 
        dt0 = cmd0.ExecuteReader() \'记得将参数设置为True        
   
        Dim s2s As List(Of String) = dt0.GetValues("二级科目", "二级科目 is not null")
       
        For Each s2 As String In s2s 
            bm2 = bm2 + 1 
            Dim bm22 As String = Format(bm2, "000") 
            bm = i + 1 & bm22 
            cmd1.CommandText = "U  PDATE {科目表2} SET 科目编码 =\'" & bm & "\',科目名称=\'" & s2 & "\'"
          cmd1.ExecuteNonQuery
    
            Dim s3s As List(Of String) = dt0.GetValues("三级科目", "三级科目 is not null and 二级科目=\'" & s2 & "\'") 
            For Each s3 As String In s3s 
                bm3 = bm3 + 1
                Dim bm32 As String = Format(bm3, "000")\'                   
                bm = i + 1 & bm22 & bm32
                cmd1.CommandText = "U  PDATE {科目表2} SET 科目编码 =\'" & bm & "\',科目名称=\'" & s3 & "\'" 
             cmd1.ExecuteNonQuery()
                
                Dim s4s As List(Of String) = dt0.GetValues("四级科目", "四级科目 is not null and 二级科目=\'" & s2 & "\'and 三级科目=\'" & s3 & "\'") 
                For Each s4 As String In s4s 
                    bm4 = bm4 + 1
                    Dim bm42 As String = Format(bm4, "000")\'                      
                    bm = i + 1 & bm22 & bm32 & bm42
                    cmd1.CommandText = "U  PDATE {科目表2} SET 科目编码 =\'" & bm & "\',科目名称=\'" & s4 & "\'"
                 cmd1.ExecuteNonQuery() 
                    
                    Dim s5s As List(Of String) = dt0.GetValues("五级科目", "五级科目 is not null and 二级科目=\'" & s2 & "\'and 三级科目=\'" & s3 & "\'and 四级科目=\'" & s4 & "\'") 
                    For Each s5 As String In s5s 
                        bm5 = bm5 + 1
                        Dim bm52 As String = Format(bm5, "000")\'   
                        bm = i + 1 & bm22 & bm32 & bm42 & bm52
                        cmd1.CommandText = "U  PDATE {科目表2} SET 科目编码 =\'" & bm & "\',科目名称=\'" & s5 & "\'"
                      cmd1.ExecuteNonQuery() 
                    Next
                    bm5 = 0
                Next
                bm4 = 0
            Next
            bm3 = 0
        Next
        bm2 = 0
    Next 
End If

 MessageBox.Show("数据输入完成")
我改了,还是没有输入 为什么,原科目表2为空表,科目表有数据,这样就不能科目表的数据添加到科目表2吗,只能修改吗
[此贴子已经被作者于2022/9/16 18:34:49编辑过]

--  作者:有点蓝
--  发布时间:2022/9/17 8:55:00
--  
sql使用的是UPDATE,当然只能修改。新增数据要使用insert:http://www.foxtable.com/webhelp/topics/0691.htm
--  作者:建筑人生
--  发布时间:2022/9/17 11:47:00
--  
以下是引用有点蓝在2022/9/17 8:55:00的发言:
sql使用的是UPDATE,当然只能修改。新增数据要使用insert:http://www.foxtable.com/webhelp/topics/0691.htm

Dim cmd1 As New S   QLCommand
cmd1.C
\'Dim dt1 As DataTable
Dim bm As String
Dim bm2, bm3, bm4, bm5 As Integer


Dim cmd As New S   QLCommand
Dim dt As DataTable
cmd.C
cmd.CommandText = "S   elect DISTINCT [_Identify],一级科目,二级科目,三级科目,四级科目,五级科目,收支类别 From {科目表} " \'生成空表
dt = cmd.ExecuteReader() \'记得将参数设置为True

Dim sts() As String = {"收入", "支出", "银行", "现金", "中转"}


If dt.DataRows.Count > 0 Then
    For i As Integer = 0 To sts.Length - 1
       
        Dim cmd0 As New S   QLCommand
        cmd0.C
        Dim dt0 As DataTable
        cmd0.CommandText = "S   elect DISTINCT 二级科目,三级科目,四级科目,五级科目,收支类别 From {科目表} Where 收支类别=\'" & sts(i) & "\'" \'生成表
        dt0 = cmd0.ExecuteReader() \'记得将参数设置为True       
       
        Dim s2s As List(Of String) = dt0.GetValues("二级科目", "二级科目 is not null")
       
        \'        MessageBox.Show(s2s.Count)
       
        For Each s2 As String In s2s
            bm2 = bm2 + 1
            Dim bm22 As String = Format(bm2, "000")
            bm = CStr(i + 1 & bm22)
\'            MessageBox.Show(bm)
            \'            cmd1.CommandText = "U   PDATE {科目表2} S  ET 科目编码 =\'" & bm & "\',科目名称=\'" & s2 & "\'"  \'只能修改****
             MessageBox.Show(1)
                  MessageBox.Show(bm & "-" & s2)
            cmd1.CommandText = "Insert Into 科目表2 (科目编码,科目名称) Values(\'" & bm & "\',\'" & s2 & "\')"
       MessageBox.Show(2)
           cmd1.ExecuteNonQuery  ‘执行到这里提示’Insert Into 语法错误
            MessageBox.Show(3)
            Return


--  作者:有点蓝
--  发布时间:2022/9/17 11:56:00
--  
msgbox("Insert Into 科目表2 (科目编码,科目名称) Values(\'" & bm & "\',\'" & s2 & "\')")
--  作者:建筑人生
--  发布时间:2022/9/17 11:56:00
--  
以下是引用建筑人生在2022/9/17 11:47:00的发言:

Dim cmd1 As New S   QLCommand
cmd1.C
\'Dim dt1 As DataTable
Dim bm As String
Dim bm2, bm3, bm4, bm5 As Integer


Dim cmd As New S   QLCommand
Dim dt As DataTable
cmd.C
cmd.CommandText = "S   elect DISTINCT [_Identify],一级科目,二级科目,三级科目,四级科目,五级科目,收支类别 From {科目表} " \'生成空表
dt = cmd.ExecuteReader() \'记得将参数设置为True

Dim sts() As String = {"收入", "支出", "银行", "现金", "中转"}


If dt.DataRows.Count > 0 Then
    For i As Integer = 0 To sts.Length - 1
       
        Dim cmd0 As New S   QLCommand
        cmd0.C
        Dim dt0 As DataTable
        cmd0.CommandText = "S   elect DISTINCT 二级科目,三级科目,四级科目,五级科目,收支类别 From {科目表} Where 收支类别=\'" & sts(i) & "\'" \'生成表
        dt0 = cmd0.ExecuteReader() \'记得将参数设置为True       
       
        Dim s2s As List(Of String) = dt0.GetValues("二级科目", "二级科目 is not null")
       
        \'        MessageBox.Show(s2s.Count)
       
        For Each s2 As String In s2s
            bm2 = bm2 + 1
            Dim bm22 As String = Format(bm2, "000")
            bm = CStr(i + 1 & bm22)
\'            MessageBox.Show(bm)
            \'            cmd1.CommandText = "U   PDATE {科目表2} S  ET 科目编码 =\'" & bm & "\',科目名称=\'" & s2 & "\'"  \'只能修改****
             MessageBox.Show(1)
                  MessageBox.Show(bm & "-" & s2)
            cmd1.CommandText = "Insert Into 科目表2 (科目编码,科目名称) Values(\'" & bm & "\',\'" & s2 & "\')"
       MessageBox.Show(2)
           cmd1.ExecuteNonQuery  ‘执行到这里提示’Insert Into 语法错误
            MessageBox.Show(3)
            Return

 

 cmd1.CommandText = "Insert Into 科目表2()科目编码,科目名称) Values(\'" & bm & "\',\'" & s2 & "\')"  搞定了,是表名后不能有空格,帮助没写清楚