c/s程序中.采用随用随连的方式连接数据库,可是企业管理器中的进程号越来越多,如何解决呀?
-----------------
关闭了连接对象,可是企业管理器中的进程号依然存在,以前整个程序只用一个连接不会出现这个问题,但听说要采用即用即连的方式才好,所以改成即用即连,但却出现了这个问题,如何解决呀?我反复测试,发现,如果只打开一个连接进程号,conn.close后,企业管理器中的进程号就没有了,但是只要同时打开了一个以上的连接进程号,就无法再关闭了.[前提是我定义的是窗体级的连接对象,如果是一个按钮中的就会即时释放.]
以下我把类代码写出来,我定义一个窗体级的对象时出现这个问题的.我已经在窗体的unload事件中set nothing了.
类代码如下:
Option ExplicitPublic dbconn As adodb.Connection '连接对象
Public rs As adodb.Recordset '记录集
'-------------------------------
'连接服务器函数
'-------------------------------
Public Function ConnectToServer() As Boolean
On Error GoTo errhandler:
If Not dbconn Is Nothing Then
If dbconn.State = adStateOpen Then
dbconn.Close
End If
Else
GoTo errhandler
End If
'connstr是一个全局变量,存贮连接串
With dbconn
.ConnectionString = connstr
.ConnectionTimeout = 6
.Open '连接
End With
If dbconn.Errors.Count = 0 Then
'MsgBox "连接时无错"
ConnectToServer = True
Else
MsgBox "conn时出错"
ConnectToServer = False
End If
Exit Function
errhandler:
MsgBox "打开数据库错误!", vbCritical
ConnectToServer = False
End Function
'-------------------------------
'执行一个sql语句,得到一个记录集
'-------------------------------
Public Function GetRecordSet(StrSQL As String) As adodb.Recordset On Error GoTo errhandler
If Trim(StrSQL) = "" Then
Exit Function
End If
If Not rs Is Nothing Then
If rs.State = adStateOpen Then
rs.Close
End If
End If
Set rs = Nothing
Set rs = New adodb.Recordset If ConnectToServer = True Then
'必须要用客户端游标
rs.CursorLocation = adUseClient
rs.Open StrSQL, dbconn
Set GetRecordSet = rs
Else
MsgBox "与服务器连接失败!", 32, "提示"
End If
Exit Function
errhandler:
MsgBox Err.DescriptionEnd Function'关闭连接
Private Function CloseConnect() As Boolean On Error GoTo errhandler
If Not dbconn Is Nothing Then
If dbconn.State = adStateOpen Then
dbconn.Close
End If
End If
Set dbconn = Nothing
CloseConnect = True
Exit Function
errhandler:
MsgBox Err.Description
End Function'关闭记录集
Private Function CloseRecordSet() As Boolean
On Error GoTo errhandler
If rs.State = adStateOpen Then
rs.Close
End If
Set rs = Nothing
Exit Function
errhandler:
MsgBox Err.Description
End Function'类初始化
Private Sub Class_Initialize()
On Error GoTo errhandler
Set dbconn = New adodb.Connection
Exit Sub
errhandler:
MsgBox Err.Description
End Sub'释放类
Private Sub Class_Terminate()
' MsgBox "释放类"
On Error GoTo errhandler
Call CloseRecordSet
Call CloseConnect '关闭连接
Exit Sub
errhandler:
MsgBox Err.Description
End Sub
-----------------
关闭了连接对象,可是企业管理器中的进程号依然存在,以前整个程序只用一个连接不会出现这个问题,但听说要采用即用即连的方式才好,所以改成即用即连,但却出现了这个问题,如何解决呀?我反复测试,发现,如果只打开一个连接进程号,conn.close后,企业管理器中的进程号就没有了,但是只要同时打开了一个以上的连接进程号,就无法再关闭了.[前提是我定义的是窗体级的连接对象,如果是一个按钮中的就会即时释放.]
以下我把类代码写出来,我定义一个窗体级的对象时出现这个问题的.我已经在窗体的unload事件中set nothing了.
类代码如下:
Option ExplicitPublic dbconn As adodb.Connection '连接对象
Public rs As adodb.Recordset '记录集
'-------------------------------
'连接服务器函数
'-------------------------------
Public Function ConnectToServer() As Boolean
On Error GoTo errhandler:
If Not dbconn Is Nothing Then
If dbconn.State = adStateOpen Then
dbconn.Close
End If
Else
GoTo errhandler
End If
'connstr是一个全局变量,存贮连接串
With dbconn
.ConnectionString = connstr
.ConnectionTimeout = 6
.Open '连接
End With
If dbconn.Errors.Count = 0 Then
'MsgBox "连接时无错"
ConnectToServer = True
Else
MsgBox "conn时出错"
ConnectToServer = False
End If
Exit Function
errhandler:
MsgBox "打开数据库错误!", vbCritical
ConnectToServer = False
End Function
'-------------------------------
'执行一个sql语句,得到一个记录集
'-------------------------------
Public Function GetRecordSet(StrSQL As String) As adodb.Recordset On Error GoTo errhandler
If Trim(StrSQL) = "" Then
Exit Function
End If
If Not rs Is Nothing Then
If rs.State = adStateOpen Then
rs.Close
End If
End If
Set rs = Nothing
Set rs = New adodb.Recordset If ConnectToServer = True Then
'必须要用客户端游标
rs.CursorLocation = adUseClient
rs.Open StrSQL, dbconn
Set GetRecordSet = rs
Else
MsgBox "与服务器连接失败!", 32, "提示"
End If
Exit Function
errhandler:
MsgBox Err.DescriptionEnd Function'关闭连接
Private Function CloseConnect() As Boolean On Error GoTo errhandler
If Not dbconn Is Nothing Then
If dbconn.State = adStateOpen Then
dbconn.Close
End If
End If
Set dbconn = Nothing
CloseConnect = True
Exit Function
errhandler:
MsgBox Err.Description
End Function'关闭记录集
Private Function CloseRecordSet() As Boolean
On Error GoTo errhandler
If rs.State = adStateOpen Then
rs.Close
End If
Set rs = Nothing
Exit Function
errhandler:
MsgBox Err.Description
End Function'类初始化
Private Sub Class_Initialize()
On Error GoTo errhandler
Set dbconn = New adodb.Connection
Exit Sub
errhandler:
MsgBox Err.Description
End Sub'释放类
Private Sub Class_Terminate()
' MsgBox "释放类"
On Error GoTo errhandler
Call CloseRecordSet
Call CloseConnect '关闭连接
Exit Sub
errhandler:
MsgBox Err.Description
End Sub
解决方案 »
- vb6向crystal reports 传递参数
- 新干线!又是日本新干线!!
- 请将这段VB写的防止SQL注入改为JS写的
- OLE中插入excel编辑表格后怎么保存?
- 急!!!VB编程,客户端用ACCESS,想用个按钮或直接把记录转换成txt格式的文件,把txt文件传到服务器上(SQL Server)直接转换格式存入SQ
- VB6.0中DataGrid如何实现分页
- 请教高手:怎样写VBS才能让已经设计好的excel自动导入到outlook2000的日历中,另外怎样才能修改节假日在日历中的颜色
- 紧急求助:如何在运行程序中运行注册控件的命令语句
- 急!!!!!那位大哥知道怎么通过VB程序调用关机????非常急!!知道后马上给分!!
- 哪位大虾帮我看看?!(我是穷鬼)
- 如何学习api
- 全局快捷键与多个剪贴板的问题
b/s模式一般随用随连,c/s还是保持连接比较好。
也就是定义一个全局连接,整个程序结束时才close
-------------------
我确定销了,先close,然后 set nothing
其实在类的代码中已经可以看出来了.
当第二次的时候
你的对象还没有销毁就又NEW出来一个
不在编程环境,感觉看的不是很清楚只在类初始化的时候NEW一个
在销毁的时候释放就好了
中间任何地方都不要NEW
不用的时候关闭 不要释放
还原数据库时要求没有任何连接,但即使用把所有ADO的CONNECTION和RECORDSET对像都SET NOTHING了都还有连接存在。没有想到更好的办法。因为一个庞大的程序,不可能只使用一个连接,有可能你需要一个CONNECTION进行QUERY,而另一个用来进行事务处理。
最后我想到的解决办法是,向SQL发送KILL命令。但对于楼子好像不太好用,因为你不知道何时需要KILL哪个连接。
好了,我还是把代码贴出来,看对楼子是否有用:'还原数据库
Private Sub cmdRestore_Click()
On Error GoTo msg
Dim tmpCnStr$
Dim SqlStr$
Dim objCn As New ADODB.Connection, objRs As New ADODB.Recordset
Dim objRs2 As New ADODB.Recordset
Dim LastOne%
VB.Screen.MousePointer = 11
If Listbak.Text = "" Then Error 51040 If optnVrf(0) Then
tmpCnStr$ = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=" & comSrvList.Text
Else '必须在此之前从REG取得数据库密码
tmpCnStr$ = "Provider=SQLOLEDB.1;Password=" & txtPassword & " ;User ID=" & txtUsername & ";Initial Catalog=master;Data Source=" & comSrvList.Text
End If
objCn.Open tmpCnStr$ If MsgBox("确定要从 " & Listbak.Text & " 还原数据库?" & vbCrLf & vbCrLf & _
"注意:覆盖后以前数据库中的所有数据将丢失!", vbQuestion + vbYesNo + vbDefaultButton2) = vbNo Then
GoTo msg
End If
'KILL所有使用U8DATABASE的进程
On Error Resume Next
ReStop: SqlStr$ = "USE [master]"
objCn.Execute SqlStr$
'得到进程列表
SqlStr$ = "declare @id int" & vbCrLf & "exec sp_MSset_current_activity @id" & vbCrLf & "set noexec off set parseonly off " '
Set objRs = objCn.Execute(SqlStr$)
SqlStr$ = "exec sp_MSget_current_activity " & objRs(0) & ",1" & vbCrLf & "set noexec off set parseonly off "
Set objRs = objCn.Execute(SqlStr$)
While Not objRs.EOF
If VBA.LCase(objRs("Database")) = VBA.LCase(DB_NAME) Then
'KILL进程
SqlStr$ = "dbcc inputbuffer(" & objRs(0) & ")"
Set objRs2 = objCn.Execute(SqlStr$)
If MsgBox("进程 [" & objRs(0) & "] 正在使用当前数据库,其最后一批T-SQL是:" & vbCrLf & _
vbCrLf & objRs2("eventinfo") & vbCrLf & vbCrLf & vbCrLf & "中止它吗?", vbQuestion + vbYesNo + vbDefaultButton1) = vbNo Then
err = 0
GoTo msg
End If
SqlStr$ = "set noexec off set parseonly off"
objCn.Execute SqlStr$
SqlStr$ = "KILL " & objRs(0)
objCn.Execute SqlStr$
End If
objRs.MoveNext
Wend
'还原数据库
'得到一个Device中有多少个备份
LastOne% = 0
SqlStr$ = "Declare @U8Dir AS VARCHAR(200) " & vbCrLf & _
" Declare @BackFile AS VARCHAR(200) " & vbCrLf & _
" SELECT @U8Dir = cAcc_Path FROM UFSystem..UA_Account WHERE cAcc_Id = '" & Grid1.TextMatrix(Grid1.Row, 0) & "' " & vbCrLf & _
" SET @BackFile = @U8Dir + '" & Listbak.Text & "' " & vbCrLf & _
" RESTORE HEADERONLY FROM DISK = @BackFile WITH NOUNLOAD "
Set objRs = objCn.Execute(SqlStr$)
While Not objRs.EOF
If objRs("Position") > LastOne% Then LastOne% = objRs("Position")
objRs.MoveNext
Wend
If LastOne% > 1 Then MsgBox "备份文件中所包含的实际备份内容为 " & LastOne% & " 个,现在将恢复最近一次备份的数据.", vbInformation
SqlStr$ = "Declare @U8Dir AS VARCHAR(200) " & vbCrLf & _
" Declare @COSTDB AS VARCHAR(200) " & vbCrLf & _
" Declare @COSTLog AS VARCHAR(200) " & vbCrLf & _
" Declare @BackFile AS VARCHAR(200) " & vbCrLf & _
" SELECT @U8Dir = cAcc_Path FROM UFSystem..UA_Account WHERE cAcc_Id = '" & Grid1.TextMatrix(Grid1.Row, 0) & "' " & vbCrLf & _
" SET @COSTDB = @U8Dir + '" & DB_NAME & ".mdf' " & vbCrLf & _
" SET @COSTLog = @U8Dir + '" & DB_NAME & "_log.ldf' " & vbCrLf & _
" SET @BackFile = @U8Dir + '" & Listbak.Text & "' " & vbCrLf & _
" RESTORE DATABASE [" & DB_NAME & "] " & vbCrLf & _
" From DISK = @BackFile " & vbCrLf & _
" WITH FILE = " & LastOne% & ", NOUNLOAD , STATS = 10, RECOVERY, " & vbCrLf & _
" Move '" & DB_NAME & "' TO @COSTDB, " & vbCrLf & _
" Move '" & DB_NAME & "_log' TO @COSTLog"
err = 0
objCn.Execute SqlStr$ If err Then GoTo ReStop
MsgBox "还原U8-COST数据库完成。", vbInformation, "Created database"
Call TestCostDB '显示数据库信息
Call frmMain.Form_Load
VB.Screen.MousePointer = 0
Exit Sub
msg:
VB.Screen.MousePointer = 0
If ShowErr(err, Me.name & "->cmdRestore_Click", True) Then Resume
End Sub