专家坐堂-[求助]这段代码怎么改才行Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  [求助]这段代码怎么改才行  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=22870)

--  作者：jianjingmaoyi
--  发布时间：2012/8/25 23:06:00
--  [求助]这段代码怎么改才行

我想把数据源1内的数据赚到数据源2 执行了,但是数据没有复制过去,应该咋改? 表是一模一样的结构.

Dim cmd As New SQLCommand
cmd.C o n n e c t i o n N a m e = "数据源1"
Dim cmd2 As New SQLCommand
cmd2.C o n n e c t i o n N a m e = "数据源2"
Dim dt As DataTable
Dim lst As List(Of String)
lst = Connections("数据源1").GetTableNames
For Each nm As String In lst
cmd.CommandText = "SELECT * From {" & nm & " }"
dt = cmd.ExecuteReader()
For Each dr As DataRow In dt.DataRows
For Each dc As DataCol In dt.DataCols
cmd2.CommandText = "Insert Into " & nm & " (" & dc.Name & ") Values (" & dr(dc.Name) & ")"
cmd2.ExecuteReader()
Next
Next
Next
MessageBox.Show("成功")

[此贴子已经被作者于2012-8-25 23:07:52编辑过]

--  作者：jianjingmaoyi
--  发布时间：2012/8/26 0:41:00
--

Dim d1 As Date = Date.Now
Dim cmd As New SQLCommand
cmd.C
Dim cmd2 As New SQLCommand
cmd2.C
Dim str1 As String, str2 As String
Dim dt As DataTable
Dim lst As List(Of String)
lst = Connections("数据源1").GetTableNames
For Each nm As String In lst
cmd.CommandText = "SELECT * From {" & nm & " }"
dt = cmd.ExecuteReader()
For Each dr As DataRow In dt.DataRows
str1= ""
str2= ""
For Each dc As DataCol In dt.DataCols
If dc.IsString Then
str1 = str1 & dc.name & ","
str2 = str2 & "\'" & dr(dc.name) & "\',"
ElseIf dc.IsNumeric Then
str1 = str1 & dc.name & ","
str2 = str2 & "" & dr(dc.name) & ","
\'ElseIf dc.IsDate Then

\' str2 = str2 & "#" & dr(dc.name) & "#,"
End If
Next
str1 = str1.TrimEnd(",")
str2 = str2.trimEnd(",")
cmd2.CommandText = "Insert Into " & nm & " (" & str1 & ") Values (" & str2 & ")"
cmd2.ExecuteReader()
Next
Next
Dim d2 As Date = Date.Now
MessageBox.Show((d2-d1).Totalseconds)
MessageBox.Show("成功")

在群里弄了半天数值和时间还有逻辑怎么转?字符型搞定了.

--  作者：woiz
--  发布时间：2012/8/26 0:55:00
--
insert 的 {}怎么没有？

--  作者：jspta
--  发布时间：2012/8/26 8:40:00
--

以下是引用woiz在2012-8-26 0:55:00的发言：
insert 的 {}怎么没有？

可以不用的

--  作者：czy
--  发布时间：2012/8/26 10:06:00
--

……

            ElseIf dc.IsNumeric
                str2 = str2 & "" & dr(dc.name) & ","
            ElseIf dc.IsBoolean Then
                str2 = str2 & "," & dr(dc.name) & ","
            ElseIf dc.IsDate Then
                str2 = str2 & "#" & dr(dc.name) & "#"
            End If
        Next

……

这样就应该行了吧

--  作者：jspta
--  发布时间：2012/8/26 11:27:00
--

以下是引用czy在2012-8-26 10:06:00的发言：

……

            ElseIf dc.IsNumeric
                str2 = str2 & "" & dr(dc.name) & ","
            ElseIf dc.IsBoolean Then
                str2 = str2 & "," & dr(dc.name) & ","
            ElseIf dc.IsDate Then
                str2 = str2 & "#" & dr(dc.name) & "#"
            End If
        Next

……

这样就应该行了吧
            ElseIf dc.IsBoolean Then
                str2 = str2 & "" & dr(dc.name) & "," \'多了逗号
            ElseIf dc.IsDate Then
                str2 = str2 & "#" & dr(dc.name) & "#" ‘理论是这样，但是插入时间就报错，foxtable时间格式为 YYYY/MM/DD 星期X 这种格式 SQL似乎不支持啊

--  作者：czy
--  发布时间：2012/8/26 13:10:00
--

以下是引用jspta在2012-8-26 11:27:00的发言：
            ElseIf dc.IsBoolean Then
                str2 = str2 & "" & dr(dc.name) & "," \'多了逗号
            ElseIf dc.IsDate Then
                str2 = str2 & "#" & dr(dc.name) & "#" ‘理论是这样，但是插入时间就报错，foxtable时间格式为 YYYY/MM/DD 星期X 这种格式 SQL似乎不支持啊

不会多个逗号吧，我测试过的。

如果是SQL数据源就不能用#符号，用\'符号即可

--  作者：czy
--  发布时间：2012/8/26 13:12:00
--

这是合成的sql语句，没多余逗号的

Insert Into 订单 (产品,客户,雇员,单价,折扣,数量,日期,逻辑) Values (\'PD01\',\'CS03\',\'EP04\',18,0.15,80,#1999-01-04#,True)

--  作者：jspta
--  发布时间：2012/8/26 14:24:00
--

以下是引用czy在2012-8-26 13:12:00的发言：

这是合成的sql语句，没多余逗号的

Insert Into 订单 (产品,客户,雇员,单价,折扣,数量,日期,逻辑) Values (\'PD01\',\'CS03\',\'EP04\',18,0.15,80,#1999-01-04#,True)

str2 = str2 & "," & dr(dc.name) & ","    没说清楚，这句话多了逗号。。。。

#1999-01-04# 这个值没问题，但是 YYYY/MM/DD 星期X 这样格式的就有问题，必须吧星期去掉才行。挺麻烦的

--  作者：blackzhu
--  发布时间：2012/8/27 8:14:00
--

Dim d1 As Date = Date.Now
Dim cmd As New SQLCommand
cmd.C
Dim cmd2 As New SQLCommand
cmd2.C
Dim str1 As String, str2 As String
Dim dt As DataTable
Dim lst As List(Of String)
lst = Connections("数据源1").GetTableNames
For Each nm As String In lst
cmd.CommandText = "SELECT * From {" & nm & " }"
dt = cmd.ExecuteReader()
For Each dr As DataRow In dt.DataRows
str1= ""
str2= ""
For Each dc As DataCol In dt.DataCols
str1 = str1 & dc.name & ","
If dc.IsString Then
str2 = str2 & "\'" & dr(dc.name) & "\',"
ElseIf dc.IsNumeric
str2 = str2 & "" & dr(dc.name) & ","
ElseIf dc.IsBoolean Then
str2 = str2 & "," & dr(dc.name)& ","
ElseIf dc.IsDate Then
str2 = str2 & "#" & dr(dc.name) & "#"
End If
Next
str1 = str1.TrimEnd(",")
str2 = str2.trimEnd(",")
cmd2.CommandText = "Insert Into " & nm & " (" & str1 & ") Values (" & str2 & ")"
cmd2.ExecuteReader()
Next
Next
Dim d2 As Date = Date.Now
MessageBox.Show((d2-d1).Totalseconds)
MessageBox.Show("成功")

代码现在改成这样 ,但是执行了却没有导入成功.