环境：VB6.0 sql server 2000
程式中由于有大量的运算，用了很多 execute语句更新数据库和adodc控件，
execute语句结束后都用close关闭连接，
程序运行一段时间，就出现数据库死锁。
请各位大虾指点一下。
以下是部份代码段，
========
Select Case Number
Case 1
db.Execute "update a set a.sj_madenum=(b.jh_xstotal-a.store),a.kit_kcnum=(isnull(b.kit_kcnum,0)+isnull(b.bcp_kcnum,0)+isnull(a.kit_lnum,0)-a.jmadenum-a.bcp_kcnum),a.cp_kcnum=(b.cp_kcnum+a.jmadenum-a.jh_xstotal) from clas_file a,clas_file b where a.classname=b.classname and convert(int,a.years)-1=convert(int,b.years) and convert(int,a.months)+11=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=b.jh_xstotal,a.nb_kcnum = (a.cp_kcnum + a.kit_kcnum + a.bcp_kcnum) from clas_file a,clas_file b where a.classname=b.classname and convert(int,a.years)-1=convert(int,b.years) and convert(int,a.months)+11=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.sj_madenum=(a.sj_madenum+b.store) from clas_file a,clas_file b where a.years=b.years and a.classname=b.classname and convert(int,a.months)+1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=(a.jh_cgnum+b.store) from clas_file a,clas_file b where a.years=b.years and a.classname=b.classname and convert(int,a.months)+3=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=(a.jh_cgnum-b.store) from clas_file a,clas_file b where a.years=b.years and a.classname=b.classname and convert(int,a.months)+2=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
MsgBox "计算完成"
Text1.Text = ""
Text2.Text = ""
Case 2 To 9
db.Execute "update a set a.sj_madenum=(b.jh_xstotal-a.store),a.kit_kcnum=(isnull(b.kit_kcnum,0)+isnull(b.bcp_kcnum,0)+isnull(a.kit_lnum,0)-a.jmadenum-a.bcp_kcnum),a.cp_kcnum=(b.cp_kcnum+a.jmadenum-a.jh_xstotal) from clas_file a,clas_file b where a.classname=b.classname and a.years=b.years and convert(int,a.months)-1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=b.jh_xstotal,a.nb_kcnum = (a.cp_kcnum + a.kit_kcnum + a.bcp_kcnum) from clas_file a,clas_file b where a.classname=b.classname and a.years=b.years and convert(int,a.months)-1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.sj_madenum=(a.sj_madenum+b.store) from clas_file a,clas_file b where a.years=b.years and a.classname=b.classname and convert(int,a.months)+1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=(a.jh_cgnum+b.store) from clas_file a,clas_file b where a.years=b.years and a.classname=b.classname and convert(int,a.months)+3=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=(a.jh_cgnum-b.store) from clas_file a,clas_file b where a.years=b.years and a.classname=b.classname and convert(int,a.months)+2=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
MsgBox "计算完成"
Text1.Text = ""
Text2.Text = ""
Case 10
db.Execute "update a set a.sj_madenum=(b.jh_xstotal-a.store),a.kit_kcnum=(isnull(b.kit_kcnum,0)+isnull(b.bcp_kcnum,0)+isnull(a.kit_lnum,0)-a.jmadenum-a.bcp_kcnum),a.cp_kcnum=(b.cp_kcnum+a.jmadenum-a.jh_xstotal) from clas_file a,clas_file b where a.classname=b.classname and a.years=b.years and convert(int,a.months)-1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=b.jh_xstotal,a.nb_kcnum = (a.cp_kcnum + a.kit_kcnum + a.bcp_kcnum) from clas_file a,clas_file b where a.classname=b.classname and a.years=b.years and convert(int,a.months)-1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.sj_madenum=(a.sj_madenum+b.store) from clas_file a,clas_file b where a.years=b.years and a.classname=b.classname and convert(int,a.months)+1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=(a.jh_cgnum+b.store) from clas_file a,clas_file b where convert(int,a.years)+1=convert(int,b.years) and a.classname=b.classname and convert(int,a.months)-9=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=(a.jh_cgnum-b.store) from clas_file a,clas_file b where a.years=b.years and a.classname=b.classname and convert(int,a.months)+2=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
MsgBox "计算完成"
Text1.Text = ""
Text2.Text = ""
Case 11
db.Execute "update a set a.sj_madenum=(b.jh_xstotal-a.store),a.kit_kcnum=(isnull(b.kit_kcnum,0)+isnull(b.bcp_kcnum,0)+isnull(a.kit_lnum,0)-a.jmadenum-a.bcp_kcnum),a.cp_kcnum=(b.cp_kcnum+a.jmadenum-a.jh_xstotal) from clas_file a,clas_file b where a.classname=b.classname and a.years=b.years and convert(int,a.months)-1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=b.jh_xstotal,a.nb_kcnum = (a.cp_kcnum + a.kit_kcnum + a.bcp_kcnum) from clas_file a,clas_file b where a.classname=b.classname and a.years=b.years and convert(int,a.months)-1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.sj_madenum=(a.sj_madenum+b.store) from clas_file a,clas_file b where a.years=b.years and a.classname=b.classname and convert(int,a.months)+1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=(a.jh_cgnum+b.store) from clas_file a,clas_file b where convert(int,a.years)+1=convert(int,b.years) and a.classname=b.classname and convert(int,a.months)-9=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=(a.jh_cgnum-b.store) from clas_file a,clas_file b where convert(int,a.years)+1=convert(int,b.years) and a.classname=b.classname and convert(int,a.months)-10=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
MsgBox "计算完成"
Text1.Text = ""
Text2.Text = ""
Case 12
db.Execute "update a set a.sj_madenum=(b.jh_xstotal-a.store),a.kit_kcnum=(isnull(b.kit_kcnum,0)+isnull(b.bcp_kcnum,0)+isnull(a.kit_lnum,0)-a.jmadenum-a.bcp_kcnum),a.cp_kcnum=(b.cp_kcnum+a.jmadenum-a.jh_xstotal) from clas_file a,clas_file b where a.classname=b.classname and a.years=b.years and convert(int,a.months)-1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=b.jh_xstotal,a.nb_kcnum = (a.cp_kcnum + a.kit_kcnum + a.bcp_kcnum) from clas_file a,clas_file b where a.classname=b.classname and a.years=b.years and convert(int,a.months)-1=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.sj_madenum=(a.sj_madenum+b.store) from clas_file a,clas_file b where convert(int,a.years)+1=convert(int,b.years) and a.classname=b.classname and convert(int,a.months)-11=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=(a.jh_cgnum+b.store) from clas_file a,clas_file b where convert(int,a.years)+1=convert(int,b.years) and a.classname=b.classname and convert(int,a.months)-9=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
db.Execute "update a set a.jh_cgnum=(a.jh_cgnum-b.store) from clas_file a,clas_file b where convert(int,a.years)+1=convert(int,b.years) and a.classname=b.classname and convert(int,a.months)-10=convert(int,b.months) and a.years='" & s1 & "' and a.months='" & s2 & "' ;"
MsgBox "计算完成"
Text1.Text = ""
Text2.Text = ""
Case Else
MsgBox ("月份不在 1 和 12之间")
Text1.Text = ""
Text2.Text = ""
End Select
db.Close
End If
=====================================================================
Private Sub Command7_Click()
'取store值
Dim strCnn As String
Dim db As ADODB.Connection
strCnn = "Provider=SQLOLEDB.1;Password=datapassword;Persist Security Info=True;User ID=sa;Initial Catalog=yamatodata1;Data Source=it3"
Set db = New ADODB.Connection
db.Open strCnn
db.Execute "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[templocal]') and OBJECTPROPERTY(id, N'IsUserTable') =1) drop table [dbo].[templocal] ;"
db.Execute "select classname,years,months,sum(kbshu) total into templocal from mxbl_file group by classname,years,months ; "
db.Execute "update a set a.store=b.total from clas_file a,templocal b where a.classname=b.classname and a.years=b.years and a.months=b.months ; "
db.Execute "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempusa]') and OBJECTPROPERTY(id, N'IsUserTable') =1) drop table [dbo].[tempusa] ;"
db.Execute "select classname,years,months,sum(kbshu) total into tempusa from usa_sales group by classname,years,months ; "
db.Execute "update a set a.store=a.store+b.total from clas_file a,tempusa b where a.classname=b.classname and a.years=b.years and a.months=b.months ; "
db.Execute "update a set a.kbshu=b.total from inter_total a ,tempusa b where a.classname=b.classname and a.exp_country='美国' and a.years=b.years and a.months=b.months ; "
db.Execute "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempsin]') and OBJECTPROPERTY(id, N'IsUserTable') =1) drop table [dbo].[tempsin] ;"
db.Execute "select classname,years,months,sum(kbshu) total into tempsin from sin_sales group by classname,years,months ; "
db.Execute "update a set a.store=a.store+b.total from clas_file a,tempsin b where a.classname=b.classname and a.years=b.years and a.months=b.months ; "
db.Execute "update a set a.kbshu=b.total from inter_total a ,tempsin b where a.classname=b.classname and a.exp_country='新加坡' and a.years=b.years and a.months=b.months ; "
db.Execute "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempham]') and OBJECTPROPERTY(id, N'IsUserTable') =1) drop table [dbo].[tempham] ;"
db.Execute "select classname,years,months,sum(kbshu) total into tempham from ham_sales group by classname,years,months ; "
db.Execute "update a set a.store=a.store+b.total from clas_file a,tempham b where a.classname=b.classname and a.years=b.years and a.months=b.months ; "
db.Execute "update a set a.kbshu=b.total from inter_total a ,tempham b where a.classname=b.classname and a.exp_country='汉堡' and a.years=b.years and a.months=b.months ; "
db.Execute "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempcasa]') and OBJECTPROPERTY(id, N'IsUserTable') =1) drop table [dbo].[tempcasa] ;"
db.Execute "select classname,years,months,sum(kbshu) total into tempcasa from casa_sales group by classname,years,months ; "
db.Execute "update a set a.store=a.store+b.total from clas_file a,tempcasa b where a.classname=b.classname and a.years=b.years and a.months=b.months ; "
db.Execute "update a set a.kbshu=b.total from inter_total a ,tempcasa b where a.classname=b.classname and a.exp_country='墨西哥' and a.years=b.years and a.months=b.months ; "
db.Execute "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempother]') and OBJECTPROPERTY(id, N'IsUserTable') =1) drop table [dbo].[tempother] ;"
db.Execute "select classname,years,months,sum(kbshu) total into tempother from other_sales group by classname,years,months ; "
db.Execute "update a set a.store=a.store+b.total from clas_file a,tempother b where a.classname=b.classname and a.years=b.years and a.months=b.months; "
db.Execute "update a set a.kbshu=b.total from inter_total a ,tempother b where a.classname=b.classname and a.exp_country='其它' and a.years=b.years and a.months=b.months ; "
db.Close
End Sub
===========================================
Dim strCnn As String
Dim db As ADODB.Connection
Dim sql As String
strCnn = "Provider=SQLOLEDB.1;Password=datapassword;Persist Security Info=True;User ID=sa;Initial Catalog=yamatodata1;Data Source=it3"
Set db = New ADODB.Connection
db.Open strCnn
'取美国销售合计数（按类）
db.Execute "update a " _
+ "Set a.total = b.shu , a.sy_total=b.sy_plan " _
+ "From casa_sales a, inter_total b " _
+ "Where a.classname = b.classname And a.years = b.years And a.months = b.months and b.exp_country='墨西哥' ; "
db.Execute "update a " _
+ "set a.shu=(b.perc*a.total/100) ,a.sy_plan=((b.perc*a.sy_total)/100) " _
+ "from casa_sales a,casa_mxbl b " _
+ "Where a.productno = b.productno ; "
db.Execute "update casa_sales set kbshu=bs*shu ;"
db.Close
Adodc1.RecordSource = "select b.productno [机种],b.classname [类名],b.total [合计],b.zx [次数], " _
+ "b.ave [月平均数],b.perc [比例],a.years [年度],a.months [月份],a.bs [倍数],a.sy_plan [事业计划], " _
+ "a.shu [计划销售数],a.kbshu [安全库存],store [实际库存],kbshu-store [库存差异] " _
+ "from casa_sales a,casa_mxbl b " _
+ "Where a.productno = b.productno And a.classname = b.classname "
Adodc1.Refresh
