打开了两个数据连接,一个是外部文件连接的 cn_db ,一个是SQL的数据连接 cn_sql‘总库’是SQL中的一个表,‘分类’是cn_db连接文件的一个表要执行
cn_sql.Execute("insert into 总库 select * from 分类")
or
cn_db.Execute("insert into 总库 select * from 分类")
怎么办?
cn_sql.Execute("insert into 总库 select * from 分类")
or
cn_db.Execute("insert into 总库 select * from 分类")
怎么办?
insert into 数据库1.dbo.总库(f1,f2,f3....) select f1,f2,f3... from 数据库2.dbo.分类")
用ODBC的系统DSN连接的from [d:\数据库2;pwd=123].分类
也不行。。
比如:
cn1 连接总库,cn2 连接分类set rs=cn2.execute("select f1,f2... from 分类")
while not rs.eof
cn1.execute "insert into 总库(f1,f2...) values ('"& rs!f1 &"',"& rs!f2 &"...)"
rs.movenext
wend
set rs=nothing
set cn2=nothing
set cn1=nothing
你的状况只能用记录集,数据中有特殊字符,可以用command参数化方式执行insert语句
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim sql As String '连接分类数据库
Set cn2 = New ADODB.Connection
cn2.Open "连接分类字符串"
'返回分类记录集
set rs=cn2.execute("select f1,f2,f3 from 分类 where ....") '插入总库
Set cn1 = New ADODB.Connection
cn1.Open "连接总库字符串"
while not rs.eof
sql="insert into 总库(f1,f2,f3) values(@1,@2,@3)"
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn1
cmd.CommandText = sql
cmd.CommandType = adCmdText
Set param = cmd.CreateParameter("@1", advarChar, adParamInput, 20, rs!f1)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@2", adInteger, adParamInput, 4, rs!f2)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@3", adDate, adParamInput, 8, rs!f3)
cmd.Parameters.Append param
cmd.Execute
Set cmd = Nothing
rs.movenext
wend
Set rs = Nothing
Set cn2 = Nothing
Set cmd = Nothing
Set cn1 = Nothing
Dim cn1 As ADODB.Connection,cn2 As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim sql As String '连接分类数据库
Set cn2 = New ADODB.Connection
cn2.Open "连接分类字符串"
'返回分类记录集
set rs=cn2.execute("select f1,f2,f3 from 分类 where ....") '插入总库
Set cn1 = New ADODB.Connection
cn1.Open "连接总库MSSQL字符串"
while not rs.eof
sql="insert into 总库(f1,f2,f3) values(?,?,?)"
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn1
cmd.CommandText = sql
cmd.CommandType = adCmdText
Set param = cmd.CreateParameter("f1", advarChar, adParamInput, 20, rs!f1)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("f2", adInteger, adParamInput, 4, rs!f2)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("f3", adDate, adParamInput, 8, rs!f3)
cmd.Parameters.Append param
cmd.Execute
Set cmd = Nothing
rs.movenext
wend
Set rs = Nothing
Set cn2 = Nothing
Set cmd = Nothing
Set cn1 = Nothing
cn_db是*.db文件的连接接口,
cn_sql是SQL的连接接口Do While Not rs_db.EOF
Set rs_db3 = cn_db.Execute("select 描述 from 分类 where 编号=" & rs_db(3))
If Not rs_db3.EOF Then
sql = "insert into 总库(城市,区号,手机,处理情况,日期) values (?,?,?,?,?)"
'cn_sql.Execute sql
cmd.ActiveConnection = cn_sql
cmd.CommandText = sql
cmd.CommandType = adCmdText Set param = cmd.CreateParameter("城市", adVarChar, adParamInput, 50, rs_db(0))
Set param = cmd.CreateParameter("区号", adVarChar, adParamInput, 50, rs_db(1))
Set param = cmd.CreateParameter("手机", adVarChar, adParamInput, 50, rs_db(2))
Set param = cmd.CreateParameter("处理情况", adVarChar, adParamInput, 100, rs_db3(0))
Set param = cmd.CreateParameter("日期", adDate, adParamInput, 8, rs_db(4))
cmd.Parameters.Append param
cmd.Execute
Set cmd = Nothing End If
Set rs_db3 = Nothing
rs_db.MoveNext
Loop
Do While Not rs_db.EOF
Set rs_db3 = cn_db.Execute("select 描述 from 分类 where 编号=" & rs_db(3))
If Not rs_db3.EOF Then
sql = "insert into 总库(城市,区号,手机,处理情况,日期) values (?,?,?,?,?)"
set cmd=new adodb.command
cmd.ActiveConnection = cn_sql
cmd.CommandText = sql
cmd.CommandType = adCmdText
Set param = cmd.CreateParameter("城市", adVarChar, adParamInput, 50, rs_db(0))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("区号", adVarChar, adParamInput, 50, rs_db(1))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("手机", adVarChar, adParamInput, 50, rs_db(2))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("处理情况", adVarChar, adParamInput, 100, rs_db3(0))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("日期", adDate, adParamInput, 8, rs_db(4))
cmd.Parameters.Append param
cmd.Execute
Set cmd = Nothing
End If
Set rs_db3 = Nothing
rs_db.MoveNext
Loop
一,这里可以这样表示? ..values (?,?,?,?,?)二,cmd.CreateParameter("城市", 这里双引号不是写SQL存储过程名称!?怎么会写到字段了
“没有执行可选特性”这个,你可以按F8单步调试,看看在那一行,可能是赋值错误,比如日期格式有问题....