VB 写 SQL语句问题
我在VB中写了语句如下：
db = "Provider=SQLOLEDB.1;Password=aK1C8d6F;Persist Security Info=True;User ID=sa;Initial Catalog=TEST;Data Source=EF-SERVER "
conn.Open db
conn.Execute "drop table 产品需求计算 "
conn.Execute "drop table 产品需求计算1 "
sql1 = "select rtrim(品号1)+ '--- '+ '000 ' AS 标示号,品号2,采购未交量,品号1,品名1,规格1,未交量1,成品库存量 into 产品需求计算 from (select TD004 AS 品号2,SUM(TD008-TD015) AS 采购未交量 fROM PURTD WHERE TD008-TD015> 0 AND TD016= 'N ' AND TD018= 'Y ' group by TD004) t3 right join (select MC007 AS 成品库存量,tt.* from INVMC right JOIN (select TD004 AS 品号1,TD005 as 品名1,TD006 AS 规格1,sum(TD008-TD009) AS 未交量1 from COPTC JOIN COPTD ON RTRIM(COPTC.TC001)+RTRIM(COPTC.TC002)=RTRIM(COPTD.TD001)+RTRIM(COPTD.TD002) WHERE TD016= 'N ' AND TD021= 'Y ' AND TD008-TD009> 0 group by TD004,TD005,TD006) tt ON RTRIM(品号1)=RTRIM(MC001) WHERE MC002= '001 ' ) d1 on 品号1=品号2 "
conn.Execute (sql1)
sql2 = "select * into 产品需求计算1 from 产品需求计算 "
conn.Execute (sql2)
conn.Execute "update 产品需求计算 set 品号2=品号1 "
conn.Execute "update 产品需求计算 set 采购未交量=0 where 采购未交量 is null "
sql2 = "select * from 产品需求计算 order by 品号1 "
Set rs1 = conn.Execute(sql2)
Do While Not rs1.EOF
bq = Trim(rs1( "标示号 "))
ph2 = Trim(rs1( "品号2 "))
cgw = CDbl(rs1( "采购未交量 "))
ph = Trim(rs1( "品号1 "))
gg = Trim(rs1( "规格1 "))
wgl = CDbl(rs1( "未交量1 "))
I = 0
sql3 = "select MD001 AS 主件,MD002 AS 序号 ,MD003 AS 元件,MD006 组成用量 FROM BOMMD where MD001= ' " & ph & " ' "
Set rs2 = conn.Execute(sql3)
If rs2.EOF Then
Else
I = I + 1
Do While Not rs2.EOF
yjph = Trim(rs2( "元件 "))
zcyl = CDbl(rs2( "组成用量 "))
wgl1 = (wgl - cgw) * zcyl
bbb:
bq1 = bq & I
conn.Execute "INSERT INTO 产品需求计算1 VALUES ( ' " & bq1 & " ', ' ',0, ' " & yjph & " ', ' ', ' ', ' " & wgl1 & " ',0) "
sql4 = "select MD001 AS 主件,MD002 AS 序号 ,MD003 AS 元件,MD006 组成用量 FROM BOMMD where MD001= ' " & yjph & " ' "
Set rs3 = conn.Execute(sql4)
If rs3.EOF Then
I = 1
Else
yjph = Trim(rs3( "元件 "))
zcyl = CDbl(rs3( "组成用量 "))
wgl1 = (wgl - cgw) * zcyl
I = I + 1
GoTo bbb
End If
rs2.MoveNext
Loop
End If
rs1.MoveNext
Loop
运行3次基本有1次是报错，内容是服务器超时或拒绝连接
正常运行时需要10妙左右
[解决办法]
在声明conn变量的下面加下面两行
conn.ConnectionTimeout =0
conn.CommandTimeout=0
