Dim sqltxt As String sqltxt = "delete t2" Set conn = New ADODB.Connection conn.ConnectionString = "dsn=zbgl;uid=sa;pwd=sa;" conn.Open conn.Execute sqltxtIf condstr = "" Then sqltxt = "insert into t2 select * from t1" Else sqltxt = "insert into t2 select * from t1 where " + condstr End Ifconn.Execute sqltxt conn.Close 执行到程序末二句的时候,出的上面的错误. 我想实现的是把满足条件的t1表中的记录全部拷贝到t2表中去,待打印.
你的T2表 关键字假如是 自动编号 插入的时候就不可以 指定这个关键字 比如表 CREATE T2( ID INT IDENTITY(1,1),NAME VATCHAR(10)) GO INSERT T2 VALUES("AAA") INSERT T2 VALUES("BBB")SELECT * FROM T2结果: id name 1 AAA 2 BBB
现在又出新问题了,我改成了如下语句 Dim sqltxt As String sqltxt = "delete tmpzbgl_kc" Set conn = New ADODB.Connection conn.ConnectionString = "dsn=zbgl;uid=sa;pwd=sa;" conn.Open conn.Execute sqltxtIf condstr = "" Then sqltxt = "insert into tmpzbgl_kc (yqmc,bh,yxrq,zt,sydw) select * from zbgl_kc" Else sqltxt = "insert into tmpzbgl_kc (yqmc,bh,yxrq,zt,sydw) select * from zbgl_kc where " + condstr End If conn.Execute sqltxt conn.Close 出错说[microsoft][odbc sql server driver][sql server]插入错误,列名或所提供值的数目与表定义不匹配。
insert into tmpzbgl_kc (yqmc,bh,yxrq,zt,sydw) select yqmc,bh,yxrq,zt,sydw from zbgl_kc後面的Select的*也要替換 要不然的話回提示數目不對
insert into tmpzbgl_kc (yqmc,bh,yxrq,zt,sydw) select yqmc,bh,yxrq,zt,sydw from zbgl_kc这样我也试了,还是不对.我的T1和T2表有十一列,对我只选择其中的五列填充,会不会是这个的问题?我必须把不用的六列也列出来吗?
insert into tmpzbgl_kc (yqmc,bh,yxrq,zt,sydw) values (select yqmc,bh,yxrq,zt,sydw from zbgl_kc) 要有values关键字!!! 不过不知道你后面的select yqmc,bh,yxrq,zt,sydw from zbgl_kc 这样子可不可以,没这样写过!
//....................
sqltxt = "delete t2"
Set conn = New ADODB.Connection
conn.ConnectionString = "dsn=zbgl;uid=sa;pwd=sa;"
conn.Open
conn.Execute sqltxtIf condstr = "" Then
sqltxt = "insert into t2 select * from t1"
Else
sqltxt = "insert into t2 select * from t1 where " + condstr
End Ifconn.Execute sqltxt
conn.Close
执行到程序末二句的时候,出的上面的错误.
我想实现的是把满足条件的t1表中的记录全部拷贝到t2表中去,待打印.
比如表
CREATE T2( ID INT IDENTITY(1,1),NAME VATCHAR(10))
GO
INSERT T2 VALUES("AAA")
INSERT T2 VALUES("BBB")SELECT * FROM T2结果:
id name
1 AAA
2 BBB
我当时T1表和T2表的设计是一模一样的,怕复制出错
實際 你設置IDENTITY_INSERT 為 on 然後 直接用Select * 拷貝也可以 然後 在設置為off
不過 你的t2表沒有必要加關鍵字
Dim sqltxt As String
sqltxt = "delete tmpzbgl_kc"
Set conn = New ADODB.Connection
conn.ConnectionString = "dsn=zbgl;uid=sa;pwd=sa;"
conn.Open
conn.Execute sqltxtIf condstr = "" Then
sqltxt = "insert into tmpzbgl_kc (yqmc,bh,yxrq,zt,sydw) select * from zbgl_kc"
Else
sqltxt = "insert into tmpzbgl_kc (yqmc,bh,yxrq,zt,sydw) select * from zbgl_kc where " + condstr
End If
conn.Execute sqltxt
conn.Close 出错说[microsoft][odbc sql server driver][sql server]插入错误,列名或所提供值的数目与表定义不匹配。
sql = "SET IDENTITY_INSERT dbo.表名 ON "
dconn.Execute sql
sql = "你的插入数据语句"
dconn.Execute sql
sql = "SET IDENTITY_INSERT dbo.表名 OFF "
dconn.Execute sql
要有values关键字!!!
不过不知道你后面的select yqmc,bh,yxrq,zt,sydw from zbgl_kc 这样子可不可以,没这样写过!