专家坐堂-[求助] 外部数据没有输入Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  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 & "\')" 搞定了，是表名后不能有空格，帮助没写清楚