I have a simple application that queries an Oracle datasource using ODBC and ADODB. The table that I'm hitting is immense and can at times take 10-15 minutes to respond to simple queries. I would like to have a simple graphical progress bar in my code to show the status of the query as it proceeds, however I thought that with ADODB using ODBC for a connection, it is impossible to have the database communicate back to the application the progress of the query until the recordset is returned? If thats the case then is it possible to just have a sort of "while you wait" progess bar (sort of like the progress bar you see when Windows XP boots up) that doesn't really correlate to the query of the recordset, just really lets the user know that the application is still searching for the records? Hers is my code for the connection:
HTML Code:
Option Explicit
Public rsmain As ADODB.Recordset
Public cn As ADODB.Connection
'connection variables
Public gstrDSN As String
Public gstrUser As String
Public gstrPassword As String
Public Function OpenDatabaseConnection() As Boolean
On Error GoTo errhandler
Dim connectionstring As String
Set cn = New ADODB.Connection
cn.Provider = "MSDAORA.1"
cn.connectionstring = "Password=" & gstrPassword & ";Persist Security Info=True;User ID=" & gstrUser & ";Data Source=" & gstrDSN
cn.CursorLocation = adUseClient
cn.Open
OpenDatabaseConnection = True
Exit Function
errhandler:
MsgBox "Connection failed, Enter Login Parameters Again", vbCritical
OpenDatabaseConnection = False
Exit Function
End Function
...and here is my code for the actual query:
HTML Code:
Private Sub cmdEnter_Click()
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
Dim wasNull As Boolean
If txtSKU.Text = "" Then
MsgBox "SKU cannot be empty, please enter a valid SKU...", vbCritical
txtSKU.SetFocus
Exit Sub
End If
strsql = " select trn_dt, ins_dt_time, sku_num, store_cd, other_store_cd, trn_tp, qty, ext_cst, ship_num, keyrec_num, processed_flag" & _
" from pipe_inv_trn where sku_num = '" & txtSKU.Text & "'" & _
" and store_cd = '" & cboFromStoreNo.Text & "'" & _
" and other_store_cd = '" & cboToStoreNo.Text & "'"
Set rsmain = New ADODB.Recordset
rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly
If rsmain.EOF Then
MsgBox "SKU data data does not exist for this location combination...", vbCritical
txtSKU.Text = ""
txtSKU.SetFocus
Exit Sub
Else
blnflag = False
For inti = 1 To MSFItem.Rows - 1
If Left(MSFItem.TextMatrix(inti, 0), 9) = txtSKU.Text Then
blnflag = True
Exit For
End If
Next
If blnflag = False Then
Do While Not rsmain.EOF()
If MSFItem.TextMatrix(1, 0) = "" Then
inti = 1
Else
inti = MSFItem.Rows
MSFItem.Rows = MSFItem.Rows + 1
End If
wasNull = False
MSFItem.TextMatrix(inti, 0) = rsmain("trn_dt")
MSFItem.TextMatrix(inti, 1) = rsmain("ins_dt_time")
MSFItem.TextMatrix(inti, 2) = rsmain("sku_num")
MSFItem.TextMatrix(inti, 3) = rsmain("store_cd")
MSFItem.TextMatrix(inti, 4) = rsmain("other_store_cd")
MSFItem.TextMatrix(inti, 5) = rsmain("trn_tp")
MSFItem.TextMatrix(inti, 6) = rsmain("qty")
MSFItem.TextMatrix(inti, 7) = rsmain("ext_cst")
MSFItem.TextMatrix(inti, 8) = rsmain("ship_num")
MSFItem.TextMatrix(inti, 9) = rsmain("keyrec_num")
MSFItem.TextMatrix(inti, 10) = rsmain("processed_flag")
rsmain.MoveNext
Loop
Else
MsgBox " This Item is already entered", vbInformation
End If
txtSKU.Text = ""
txtSKU.SetFocus
Exit Sub
End If
Exit Sub
errhandler:
MsgBox "Errors occured while retreiving the item information" & vbCrLf & "Please Click on Reset Button and Redo the process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
Exit Sub
End Sub
...how should I go about adding some type of graphical reference to let the user know that the application is still running?
Most programs that need to provide a visual component for the user to stare at while something loads and to make sure that they don't think the program crashed but don't know how long it will be displayed for will either show an animated picture of some sort, or a looping progress bar (such as the one on the Windows XP splash screen).
You could set the Max to the number of rows, and advance the value for each row. This example works quickly, but take notice of the DoEvents staement. Without it, the form would never show completely until the loop was finished.
OK now I'm completely confused.... Lets say that all I want to do is have a new form (frmProgress) that pops up when the user chooses "Enter" and has an animation (AVI) file play while the query is taking place. Then when the application returns a recordset "frmProgress" will dissappear. On "frmProgress" I'll have a message that says something like, "Searching database, please wait...." and then have the animation in the loop. Does anybody have a good example of this, I'm stumped? To make the new form appear I'd do something like:
HTML Code:
Private Sub cmdEnter_Click()
frmProgress.Show
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
'Dim wasNull As Boolean
If txtSKU.Text = "" Then
MsgBox "SKU cannot be empty, please enter a valid SKU...", vbCritical
txtSKU.SetFocus
Exit Sub
End If
strsql = " select trn_dt, ins_dt_time, sku_num, store_cd, other_store_cd, trn_tp, qty, ext_cst, ship_num, keyrec_num, processed_flag" & _
" from pipe_inv_trn where sku_num = '" & txtSKU.Text & "'" & _
" and store_cd = '" & cboFromStoreNo.Text & "'" & _
" and other_store_cd = '" & cboToStoreNo.Text & "'"
Set rsmain = New ADODB.Recordset
rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly
frmProgress.Hide
If rsmain.EOF Then
MsgBox "SKU data data does not exist for this location combination...", vbCritical
txtSKU.Text = ""
txtSKU.SetFocus
Exit Sub
Else
blnflag = False
For inti = 1 To MSFItem.Rows - 1
If Left(MSFItem.TextMatrix(inti, 0), 9) = txtSKU.Text Then
blnflag = True
Exit For
End If
Next
If blnflag = False Then
Do While Not rsmain.EOF()
If MSFItem.TextMatrix(1, 0) = "" Then
inti = 1
Else
inti = MSFItem.Rows
MSFItem.Rows = MSFItem.Rows + 1
End If
wasNull = False
MSFItem.TextMatrix(inti, 0) = rsmain("trn_dt")
MSFItem.TextMatrix(inti, 1) = rsmain("ins_dt_time")
MSFItem.TextMatrix(inti, 2) = rsmain("sku_num")
MSFItem.TextMatrix(inti, 3) = rsmain("store_cd")
MSFItem.TextMatrix(inti, 4) = rsmain("other_store_cd")
MSFItem.TextMatrix(inti, 5) = rsmain("trn_tp")
MSFItem.TextMatrix(inti, 6) = rsmain("qty")
MSFItem.TextMatrix(inti, 7) = rsmain("ext_cst")
MSFItem.TextMatrix(inti, 8) = rsmain("ship_num")
MSFItem.TextMatrix(inti, 9) = rsmain("keyrec_num")
MSFItem.TextMatrix(inti, 10) = rsmain("processed_flag")
rsmain.MoveNext
Loop
Else
MsgBox " This Item is already entered", vbInformation
End If
txtSKU.Text = ""
txtSKU.SetFocus
Exit Sub
End If
Exit Sub
errhandler:
MsgBox "Errors occured while retreiving the item information" & vbCrLf & "Please Click on Reset Button and Redo the process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
Exit Sub
End Sub
Got it working with Animation Control, however when I call the form containing the AVI it just blanks out while the query is taking place?
That's because VB is busy running the query and it wont update the AVI control until it's done. In other words it's pretty useless. Why can't you use the events as I suggested earlier? You could atleast put a DoEvents in that event so the Animation control can refresh itself. Or you could update a progressbar with the information passed as arguments to the event.
I would, unfortunately I dont understand exactly how that would work with my code? Are you saying that ADODB is returning information about the progress of the queried recordsets, then update the progress bar accordingly? I was pretty sure ADODB didn't return anything to the application other than the final records retreived?
What I mean is that a ADODB Recordset raises events just as any control you could add on a Form. You only need to declare your recordset using the WithEvents keyword.
VB Code:
Private WithEvents rsmain As Recordset
If you've done that you can select rsmain in the Object dropdown list of the code editor and in the events dropdown select the FetchProgress event VB will then create the following event sub in your code:
VB Code:
Private Sub rsmain_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, _
adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
End Sub
As you can see this event has a Progress and a MaxProgress argument that will be filled with the current Progress of the maximum MaxProgress. So the percentage of the progress will be (MaxProgress / Progress) * 100 (%).
Correct, I understood that part, where I'm confused I guess, is where does that fit into my code? I understand that portion, but I don't get how that relates to the actual query retrieving the recordsets? You see when I send the query "strsql" here:
HTML Code:
Private Sub cmdEnter_Click()
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
If txtSKU.Text = "" Then
MsgBox "SKU cannot be empty, please enter a valid SKU...", vbCritical
txtSKU.SetFocus
Exit Sub
End If
strsql = " select trn_dt, ins_dt_time, sku_num, store_cd, other_store_cd, trn_tp, qty, ext_cst, ship_num, keyrec_num, processed_flag" & _
" from pipe_inv_trn where sku_num = '" & txtSKU.Text & "'" & _
" and store_cd = '" & cboFromStoreNo.Text & "'" & _
" and other_store_cd = '" & cboToStoreNo.Text & "'"
Set rsmain = New ADODB.Recordset
rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly
...does ADODB return the progress of that query, I thought that it did not? I was under the impression that ADODB just returned the actual recordset and let the application finish? My query takes 10-15 minutes to complete, but actually only usually returns 3 or 4 records (its hitting a table with well over 550 million records). I'm a little confused on how the actual progress bar interacts with this?
You create a recordset using the New keyword, right? You then run a query using that recordset object. During the time the query is running the recordset object will raise the FetchProgress event.
So you can just add a progress bar control to a form and reference the FetchProgress event how? Sorry to be a pain, but I'm jst having a hard time understanding the control.
OK, that makes more sense. I dropped a progress bar control onto my form and now my codes looks like:
HTML Code:
Private Sub rsmain_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, _
adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
With frmProgress.ProgressBar1
.Max = MaxProgress
.value = Progress
.Refresh
End With
End Sub
...my question is, now how do you attach the status of the query via ADO to the progress bar, to see the bar update as the query progresses? Here is the final code for the query (I had to make some corrections for NULL objects):
HTML Code:
Private Sub cmdEnter_Click()
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
Dim wasNull As Boolean
If txtSKU.Text = "" Then
MsgBox "SKU cannot be empty, please enter a valid SKU...", vbCritical
txtSKU.SetFocus
Exit Sub
End If
strsql = " select trn_dt, ins_dt_time, sku_num, store_cd, other_store_cd, trn_tp, qty, ext_cst, ship_num, keyrec_num, processed_flag" & _
" from pipe_inv_trn where sku_num = '" & txtSKU.Text & "'" & _
" and store_cd = '" & cboFromStoreNo.Text & "'" & _
" and other_store_cd = '" & cboToStoreNo.Text & "'"
Set rsmain = New ADODB.Recordset
rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly
If rsmain.EOF Then
MsgBox "SKU data data does not exist for this location combination...", vbCritical
txtSKU.Text = ""
txtSKU.SetFocus
Exit Sub
Else
blnflag = False
For inti = 1 To MSFItem.Rows - 1
If Left(MSFItem.TextMatrix(inti, 0), 11) = txtSKU.Text Then
blnflag = True
Exit For
End If
Next
If blnflag = False Then
Do While Not rsmain.EOF()
If MSFItem.TextMatrix(1, 0) = "" Then
inti = 1
Else
inti = MSFItem.Rows
MSFItem.Rows = MSFItem.Rows + 1
End If
wasNull = False
MSFItem.TextMatrix(inti, 0) = IfNull(rsmain("trn_dt"), wasNull, "")
MSFItem.TextMatrix(inti, 1) = IfNull(rsmain("ins_dt_time"), wasNull, "")
MSFItem.TextMatrix(inti, 2) = rsmain("sku_num")
MSFItem.TextMatrix(inti, 3) = rsmain("store_cd")
MSFItem.TextMatrix(inti, 4) = rsmain("other_store_cd")
MSFItem.TextMatrix(inti, 5) = IfNull(rsmain("trn_tp"), wasNull, "")
MSFItem.TextMatrix(inti, 6) = IfNull(rsmain("qty"), wasNull, "")
MSFItem.TextMatrix(inti, 7) = IfNull(rsmain("ext_cst"), wasNull, "")
MSFItem.TextMatrix(inti, 8) = IfNull(rsmain("ship_num"), wasNull, "")
MSFItem.TextMatrix(inti, 9) = IfNull(rsmain("keyrec_num"), wasNull, "")
MSFItem.TextMatrix(inti, 10) = IfNull(rsmain("processed_flag"), wasNull, "")
rsmain.MoveNext
Loop
Else
MsgBox " This SKU is already entered", vbInformation
End If
txtSKU.Text = ""
txtSKU.SetFocus
Exit Sub
End If
Exit Sub
errhandler:
MsgBox "Errors occured while retreiving the item information" & vbCrLf & "Please Click on Reset Button and Redo the process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
Exit Sub
End Sub
OK, that makes more sense. I dropped a progress bar control onto my form and now my codes looks like:
HTML Code:
Private Sub rsmain_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, _
adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
With frmProgress.ProgressBar1
.Max = MaxProgress
.value = Progress
.Refresh
End With
End Sub
...my question is, now how do you attach the status of the query via ADO to the progress bar, to see the bar update as the query progresses? Here is the final code for the query (I had to make some corrections for NULL objects):
HTML Code:
Private Sub cmdEnter_Click()
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
Dim wasNull As Boolean
If txtSKU.Text = "" Then
MsgBox "SKU cannot be empty, please enter a valid SKU...", vbCritical
txtSKU.SetFocus
Exit Sub
End If
strsql = " select trn_dt, ins_dt_time, sku_num, store_cd, other_store_cd, trn_tp, qty, ext_cst, ship_num, keyrec_num, processed_flag" & _
" from pipe_inv_trn where sku_num = '" & txtSKU.Text & "'" & _
" and store_cd = '" & cboFromStoreNo.Text & "'" & _
" and other_store_cd = '" & cboToStoreNo.Text & "'"
Set rsmain = New ADODB.Recordset
rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly
If rsmain.EOF Then
MsgBox "SKU data data does not exist for this location combination...", vbCritical
txtSKU.Text = ""
txtSKU.SetFocus
Exit Sub
Else
blnflag = False
For inti = 1 To MSFItem.Rows - 1
If Left(MSFItem.TextMatrix(inti, 0), 11) = txtSKU.Text Then
blnflag = True
Exit For
End If
Next
If blnflag = False Then
Do While Not rsmain.EOF()
If MSFItem.TextMatrix(1, 0) = "" Then
inti = 1
Else
inti = MSFItem.Rows
MSFItem.Rows = MSFItem.Rows + 1
End If
wasNull = False
MSFItem.TextMatrix(inti, 0) = IfNull(rsmain("trn_dt"), wasNull, "")
MSFItem.TextMatrix(inti, 1) = IfNull(rsmain("ins_dt_time"), wasNull, "")
MSFItem.TextMatrix(inti, 2) = rsmain("sku_num")
MSFItem.TextMatrix(inti, 3) = rsmain("store_cd")
MSFItem.TextMatrix(inti, 4) = rsmain("other_store_cd")
MSFItem.TextMatrix(inti, 5) = IfNull(rsmain("trn_tp"), wasNull, "")
MSFItem.TextMatrix(inti, 6) = IfNull(rsmain("qty"), wasNull, "")
MSFItem.TextMatrix(inti, 7) = IfNull(rsmain("ext_cst"), wasNull, "")
MSFItem.TextMatrix(inti, 8) = IfNull(rsmain("ship_num"), wasNull, "")
MSFItem.TextMatrix(inti, 9) = IfNull(rsmain("keyrec_num"), wasNull, "")
MSFItem.TextMatrix(inti, 10) = IfNull(rsmain("processed_flag"), wasNull, "")
rsmain.MoveNext
Loop
Else
MsgBox " This SKU is already entered", vbInformation
End If
txtSKU.Text = ""
txtSKU.SetFocus
Exit Sub
End If
Exit Sub
errhandler:
MsgBox "Errors occured while retreiving the item information" & vbCrLf & "Please Click on Reset Button and Redo the process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
Exit Sub
End Sub
...again, thanks for all your help.
I also do have same issue, FetchProgress does not trigger at all.. Have you resolved your issue or any success?
help in this regard is most appreciated.
I also do have same issue, FetchProgress does not trigger at all.. Have you resolved your issue or any success?
help in this regard is most appreciated.
Most of the progress events on ADODB.Recordset are raised for client-side cursors only, although using these to display progress is a lost cause as most of the time is usually spent by RDBMS executing the query (think of JOINs and filtering in WHERE clause and grouping with GROUP BY clause and sorting with ORDER BY) which takes most of the time.
The actual network transport, which is what FetchProgess indicates, usually takes significantly less time compared to the query plan execution beforehand.
Also, next time, instead of resurrecting an old thread where it's likely most if not all people invilved in it are long gone and not around anymore (I think resurrecting a 17-yo thread takes top prize for oldest revival) ... instead, create a new thread, link to the old one, and then give a description of hte problem.