以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  [求助]这段代码怎么改才行  (http://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("成功")


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