| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 1893 人关注过本帖
标题:VB程序运行时,sql server 2000出现audit logout
只看楼主 加入收藏
wine_wu
Rank: 1
等 级:新手上路
帖 子:3
专家分:0
注 册:2007-8-30
收藏
 问题点数:0 回复次数:5 
VB程序运行时,sql server 2000出现audit logout

环境: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

搜索更多相关主题的帖子: logout audit sql server 数据库 
2007-08-30 14:52
wine_wu
Rank: 1
等 级:新手上路
帖 子:3
专家分:0
注 册:2007-8-30
收藏
得分:0 

程式在自己机器上运行正常,只是放到服务器上去才出现问题。
由于服务器上sql server 还有很多其它用户,估计是连接池连接数目
大多引起的。请各位指点一下。

2007-08-30 15:09
wine_wu
Rank: 1
等 级:新手上路
帖 子:3
专家分:0
注 册:2007-8-30
收藏
得分:0 
怎么没人回复呀?
2007-08-31 07:57
multiple19O2
Rank: 1
等 级:新手上路
帖 子:326
专家分:0
注 册:2007-8-29
收藏
得分:0 
http://bbs.bc-cn.net/viewthread.php?tid=166636

首先你应该有这样一个概念:没有人有义务一定要帮助你。决不要自以为够资格得到答案,你没这种资格。毕竟你没有为这种服务支付任何报酬。你要自己去“挣”回一个答案,而不仅仅是被动的从他人处索要知识。
没有回应不代表你被忽视,或许只是看过你的问题的人不知道答案,也或许别人正在帮着你想答案。总之,不要气馁,说不定明天再来看帖,你会发现问题已经有人作出了回答。

[此贴子已经被作者于2007-8-31 8:00:49编辑过]

2007-08-31 07:58
lisypro
Rank: 4
等 级:业余侠客
威 望:3
帖 子:695
专家分:216
注 册:2005-9-25
收藏
得分:0 

友情帮顶


长期承接管理系统
代做各种vb/ / vc小程序
QQ:82341763
手机:13623290828
群号 11619730
2007-08-31 10:48
simpson
Rank: 3Rank: 3
等 级:论坛游民
威 望:7
帖 子:863
专家分:17
注 册:2006-11-16
收藏
得分:0 

叙述下问题的现象嘛!!!


全国最大的 Java专业电子书免费分享[url]http:///in.asp?id=xrmao[/url]
2007-08-31 11:59
快速回复:VB程序运行时,sql server 2000出现audit logout
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.048112 second(s), 7 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved