Natchiket
asked on
ADODB recordset FetchComplete event never fires
Hi, I'm new to this, so I hope someone can help.
I'm trying to write an analysis package which takes some hefty SQL server (version 7) views and then uses the data to generate graphs etc.
The problem is that I'm trying to use an asyncronous recorcordset and then have the fetchcomplete event fire once the recordset is loaded. This is so I can gave a nice little spinning globe while SQL server does it's stuff.
I've tested the view on SQL Query Analyser and it takes about 15 seconds to load, however in my application the fetchcomplete event never seems to fire.
I'm using Windows 2000, MDAC 2.7, VB 6 with Sp 6 loaded
Here's some of the code ... (it's running in form hence a class module)
Declarations...
Option Explicit
Dim mblnTimer As Boolean
Dim mtrv As TreeView
Dim mnd As Node
Dim mintSS As Integer
Dim WithEvents mrst As ADODB.Recordset
Dim mblnGotData As Boolean
..code that starts the recordset...
strSQL = "SELECT * FROM " & strView
cnn.CommandTimeout = 200
Set mrst = New ADODB.Recordset
With mrst
.CursorLocation = adUseClient
.Properties("Initial Fetch Size") = 0
.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adAsyncExecute
End With
.. code that services the fetchcomplete...
Private Sub mrst_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal precordset As ADODB.Recordset)
ClearFetch
If adStatus <> adStatusOK Then
MsgBox "Record retrieval Failed"
Else
MsgBox "Got the data"
End If
End Sub
Any ideas anyone ?
Thanks in advance
I'm trying to write an analysis package which takes some hefty SQL server (version 7) views and then uses the data to generate graphs etc.
The problem is that I'm trying to use an asyncronous recorcordset and then have the fetchcomplete event fire once the recordset is loaded. This is so I can gave a nice little spinning globe while SQL server does it's stuff.
I've tested the view on SQL Query Analyser and it takes about 15 seconds to load, however in my application the fetchcomplete event never seems to fire.
I'm using Windows 2000, MDAC 2.7, VB 6 with Sp 6 loaded
Here's some of the code ... (it's running in form hence a class module)
Declarations...
Option Explicit
Dim mblnTimer As Boolean
Dim mtrv As TreeView
Dim mnd As Node
Dim mintSS As Integer
Dim WithEvents mrst As ADODB.Recordset
Dim mblnGotData As Boolean
..code that starts the recordset...
strSQL = "SELECT * FROM " & strView
cnn.CommandTimeout = 200
Set mrst = New ADODB.Recordset
With mrst
.CursorLocation = adUseClient
.Properties("Initial Fetch Size") = 0
.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adAsyncExecute
End With
.. code that services the fetchcomplete...
Private Sub mrst_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal precordset As ADODB.Recordset)
ClearFetch
If adStatus <> adStatusOK Then
MsgBox "Record retrieval Failed"
Else
MsgBox "Got the data"
End If
End Sub
Any ideas anyone ?
Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Have also tested with the FetchProgress event and that's not firing at all
did you put a do event in your waiting loop?
ASKER
Erm... don't actually have a waiting loop... was assuming that the timer event would act as my loop.
Do you mean I should go into a loop after firing the recordset fetch ?
Do you mean I should go into a loop after firing the recordset fetch ?
to be honest ,i only ue asynchronous code in VB for ftp transfer and in this case i loop after having perform some tasks and wait for a time out with a do events loop, i exit this loop on error or time out or if the transfer was well done.
without the do events the
Private Sub Inet1_StateChanged(ByVal State As Integer) subroutine was never triggered!
this subroutine is triggered at each step of the ftp transfer
without the do events the
Private Sub Inet1_StateChanged(ByVal State As Integer) subroutine was never triggered!
this subroutine is triggered at each step of the ftp transfer
Hi all,
Iam having similar issues. Please check following code.
Please note that FetchComplete is not getting triggered. When I ran in debug mode, I could see fetchcomplete was getting called once.
Iam having similar issues. Please check following code.
Please note that FetchComplete is not getting triggered. When I ran in debug mode, I could see fetchcomplete was getting called once.
Option Explicit
Dim WithEvents Conn As ADODB.Connection
Dim WithEvents RS As ADODB.Recordset
Dim Counter As Long
Const DBConnection = "Driver={Microsoft ODBC for Oracle};Server=cruz1;UID=augustine;PWD=augustine;"
Private Sub Command1_Click()
Dim iCount As Long
iCount = 0
If mAcctId = 0 Then
mAcctId = 100000000
End If
Do While iCount < 10
mAcctId = mAcctId + 1
ExecuteQuery DBConnection, mAcctId
iCount = iCount + 1
Loop
End Sub
Private Sub Form_Load()
mAcctId = 0
Counter = 0
End Sub
Private Sub ExecuteQuery(ByVal DBConnString As String, AcctId As Long)
Dim SQL As String
SQL = "Select orderid from order where orderactid=999910605"
If DBConnString <> "" Then
Counter = Counter + 1
Set RS = New ADODB.Recordset
With RS
.CursorLocation = adUseClient
.Properties("Initial Fetch Size") = 0
.Properties("Background Fetch Size") = 1
'RS.ActiveConnection = GetConnection(DBConnString)
RS.Open SQL, GetConnection(DBConnString), , , adAsyncFetch
End With
End If
End Sub
Private Function GetConnection(DBConnString As String) As ADODB.Connection
'If Conn Is Nothing Then
Set Conn = New ADODB.Connection
Conn.Open DBConnString
'End If
Set GetConnection = Conn
End Function
Private Sub RS_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, _
ByVal pRecordset As ADODB.Recordset)
Dim myConn As ADODB.Connection
Counter = Counter - 1
Set myConn = pRecordset.ActiveConnection
If Not myConn Is Nothing Then
myConn.Close
Set myConn = Nothing
End If
If Not pRecordset Is Nothing Then
If pRecordset.State <> adStateClosed Then
pRecordset.Close
End If
End If
End Sub
ASKER