我在一个模块中定义了一个通用的ADO Recordset通用函数openrs,现在发现同一个问题: 在其他函数/过程调用openrs时,如果该函数/过程只调用openrs一次的话,可以得到正确的数据,如果多次调用openrs的话,无论多少次都只能得到第一次调用的结果;是否ADO Recordset的recordset不支持这样的连续调用?set rs = nothing ,rs.close那些都试过了,还是一样;具体为:函数Count_GW,两次调用openrs,第一次是count_f,第二次是count_t ,但Count_GW得到的数据只是两个count_f的相加,count_t得不到数据,就是第二次调用recordset时,数据不能进行更新传递; 如果将count_f 和count_t分为两个函数进行计算,就能得到正确的结果,测试了好久不知道原因,请各们高手指点一二,谢谢!代码如下: **********Openrs 相关:Public Function DBconnect() cnn.ConnectionString = "Provider = microsoft.jet.oledb.4.0;persist security info = false;data source = " & PJPath & "\TestDB.mdb;jet oledb:DataBase password = " cnn.Open End FunctionPublic Function DBDisconnect() cnn.Close Set cnn = Nothing End FunctionPublic Function OpenRS(ByVal sqlstmt As String) As ADODB.Recordset Dim rst As New ADODB.Recordset DBconnect rst.ActiveConnection = cnn rst.Open sqlstmt Set OpenRS = rst End Function**********调用openrs的程序 Public Function Count_GW() 'count gross weight On Error Resume Next Dim Rs_Count As New ADODB.Recordset Dim Count_f, Count_t As Single Count_f = 0 Count_t = 0 sql_f = "select SUM([GW(kgs)]) from [fabric details]" sql_t = "select SUM([GW(kgs)]) from [packing list]"
Set Rs_Count = OpenRS(sql_f) If Rs_Count(0).Value > 0 Then Count_f = Rs_Count(0).Value End If Set Rs_Count = OpenRS(sql_t) If Rs_Count(0).Value > 0 Then Count_t = Rs_Count(0).Value End If
Count_GW = Count_f + Count_t Rs_Count.Close Set Rs_Count = Nothing DBDisconnect End Function
NextRecordset可以取出下一个记录集.
你的问题出在连接上,第一次连接打开正常,第二次查询记录的时候,又要打开连接,但是此时连接是open的,所以出错,出错后,你没有对错误处理,结果是第二次数据查询没有执行!Dim cnn As ADODB.Connection'在模块中添加链接的定义Public Function OpenRS(ByVal sqlstmt As String) As ADODB.Recordset Dim rst As New ADODB.Recordset '加入红色部分进行判断,别的可以不要改 If cnn Is Nothing Then Set cnn = New ADODB.Connection DBconnect '打开之后,如果没有关闭不能再打开,否则会出错,跳过本操作 End If rst.ActiveConnection = cnn rst.Open sqlstmt Set OpenRS = rst End Function
注意: Dim cnn As ADODB.Connection 不能定义为: Dim cnn As new ADODB.Connection原因,你懂的
在其他函数/过程调用openrs时,如果该函数/过程只调用openrs一次的话,可以得到正确的数据,如果多次调用openrs的话,无论多少次都只能得到第一次调用的结果;是否ADO Recordset的recordset不支持这样的连续调用?set rs = nothing ,rs.close那些都试过了,还是一样;具体为:函数Count_GW,两次调用openrs,第一次是count_f,第二次是count_t ,但Count_GW得到的数据只是两个count_f的相加,count_t得不到数据,就是第二次调用recordset时,数据不能进行更新传递;
如果将count_f 和count_t分为两个函数进行计算,就能得到正确的结果,测试了好久不知道原因,请各们高手指点一二,谢谢!代码如下:
**********Openrs 相关:Public Function DBconnect()
cnn.ConnectionString = "Provider = microsoft.jet.oledb.4.0;persist security info = false;data source = " & PJPath & "\TestDB.mdb;jet oledb:DataBase password = "
cnn.Open
End FunctionPublic Function DBDisconnect()
cnn.Close
Set cnn = Nothing
End FunctionPublic Function OpenRS(ByVal sqlstmt As String) As ADODB.Recordset
Dim rst As New ADODB.Recordset
DBconnect
rst.ActiveConnection = cnn
rst.Open sqlstmt
Set OpenRS = rst
End Function**********调用openrs的程序
Public Function Count_GW() 'count gross weight
On Error Resume Next
Dim Rs_Count As New ADODB.Recordset
Dim Count_f, Count_t As Single
Count_f = 0
Count_t = 0
sql_f = "select SUM([GW(kgs)]) from [fabric details]"
sql_t = "select SUM([GW(kgs)]) from [packing list]"
Set Rs_Count = OpenRS(sql_f)
If Rs_Count(0).Value > 0 Then
Count_f = Rs_Count(0).Value
End If
Set Rs_Count = OpenRS(sql_t)
If Rs_Count(0).Value > 0 Then
Count_t = Rs_Count(0).Value
End If
Count_GW = Count_f + Count_t
Rs_Count.Close
Set Rs_Count = Nothing
DBDisconnect
End Function
你的问题出在连接上,第一次连接打开正常,第二次查询记录的时候,又要打开连接,但是此时连接是open的,所以出错,出错后,你没有对错误处理,结果是第二次数据查询没有执行!Dim cnn As ADODB.Connection'在模块中添加链接的定义Public Function OpenRS(ByVal sqlstmt As String) As ADODB.Recordset
Dim rst As New ADODB.Recordset
'加入红色部分进行判断,别的可以不要改
If cnn Is Nothing Then
Set cnn = New ADODB.Connection
DBconnect '打开之后,如果没有关闭不能再打开,否则会出错,跳过本操作
End If
rst.ActiveConnection = cnn
rst.Open sqlstmt
Set OpenRS = rst
End Function
Dim cnn As ADODB.Connection
不能定义为:
Dim cnn As new ADODB.Connection原因,你懂的