請問為何使用ADO搭配SQL取得符合條件的RECORDSET並放入陣列中,
第一次執行OPEN時正常,但我將RECORDSET先CLOSE後再用另一個SQL語法
取得另外的符合條件資料並將其放入相同的RECORDSET變數時,當我要RECORDCOUNT值時會出現
＜Method 'RecordCount' of object '_RecordSet' failed＞
的錯誤
Dim LSTR_DSN As String
Dim LSTR_UserId As String
Dim LSTR_UserPwd As String
Dim Lcon_adoConn As New ADODB.Connection
Dim Lrst_adoRsc As New ADODB.Recordset
Dim Lint_RstCount As Integer
Dim Lvar_Fields() As Variant
Dim Lstr_SqlSent As String
Dim Lint_TmpI As Integer
Dim Lint_TmpJ As Integer
Dim Lint_Row As Integer
Dim Lint_Row_1 As Integer
Dim Lbln_Found As Boolean
Lcon_adoConn.Errors.Clear
'取得FILEDSN,USERID,USERPWD
Call sbGetUserProfile(LSTR_DSN, LSTR_UserId, LSTR_UserPwd)
Lcon_adoConn.Open LSTR_DSN, LSTR_UserId, LSTR_UserPwd
Lrst_adoRsc.ActiveConnection = Lcon_adoConn
Lrst_adoRsc.CursorType = adOpenStatic
Lrst_adoRsc.LockType = adLockReadOnly
Lrst_adoRsc.CursorLocation = adUseClient
Lstr_SqlSent = " SELECT " & vbCrLf & _
" DF023.CLNTID, " & vbCrLf & _
" SUM(NVL(DF023.SETADVSBAL,0))," & vbCrLf & _
" '0'," & vbCrLf & _
" MAX(DF023.SETINVSDT) " & vbCrLf & _
" FROM " & vbCrLf & _
" DF023 " & vbCrLf & _
" WHERE " & vbCrLf & _
" DF023.CLNTID BETWEEN '" & Trim(Lstr_代號開始) & "' AND '" & Trim(Lstr_代號結束) & "'" & vbCrLf & _
" GROUP BY DF023.CLNTID " & vbCrLf & _
" ORDER BY DF023.CLNTID "
Lrst_adoRsc.Open Lstr_SqlSent, , , , adCmdText 'Command16
Lint_RstCount = Lrst_adoRsc.RecordCount
Select Case Lint_RstCount
Case 0
ReDim Lvar_暫存陣列(0, 0)
If Not Lcon_adoConn Is Nothing Then Set Lcon_adoConn = Nothing
If Not Lrst_adoRsc Is Nothing Then Set Lrst_adoRsc = Nothing
'GetObjectContext.SetComplete
Exit Function
Case Else
ReDim Lvar_Fields(Lrst_adoRsc.Fields.Count - 1)
For Lint_TmpI = 0 To Lrst_adoRsc.Fields.Count - 1
Lvar_Fields(Lint_TmpI) = Lrst_adoRsc.Fields(Lint_TmpI).Name
Next Lint_TmpI
Lvar_暫存陣列() = Lrst_adoRsc.GetRows(Lint_RstCount, 1, Lvar_Fields)
End Select
Lrst_adoRsc.Close
Lstr_SqlSent = " SELECT " & vbCrLf & _
" DF023.INSTLT," & vbCrLf & _
" DF023.INST01," & vbCrLf & _
" DF023.INST02," & vbCrLf & _
" DF023.INST03," & vbCrLf & _
" DF023.INST04," & vbCrLf & _
" DF023.INST05," & vbCrLf & _
" DF023.INST06," & vbCrLf & _
" DF023.INST07," & vbCrLf & _
" DF023.INST08," & vbCrLf & _
" DF023.INST09," & vbCrLf & _
" DF023.INST10," & vbCrLf & _
" DF023.INST11," & vbCrLf & _
" DF023.INST12," & vbCrLf & _
" DF023.CLNTID," & vbCrLf & _
" DF023.SETADVSBAL," & vbCrLf & _
" DF023.SETINVSDT, " & vbCrLf & _
" DF023.YYY " & vbCrLf & _
" FROM DF023 " & vbCrLf & _
" WHERE " & vbCrLf & _
" DF023.CLNTID='" & Trim(Lvar_代號) & "'" & vbCrLf & _
" AND DF023.SETINVSDT ＞= '" & Trim(Lstr_計算終止日) & "'" & vbCrLf & _
" ORDER BY DF023.YYY ASC"
Lrst_adoRsc.Open Lstr_SqlSent, , , , adCmdText
Lint_RstCount = Lrst_adoRsc.RecordCount
ps. Lrst_adoRsc.RecordCount為錯誤發生處
我的環境為Win95,VB6中文,VB6SP3
有誰遇過這問題,請回答我...我急需解決這問題....
非常感謝,感謝....