Re: VB6 Newbie - Progress Bar

"jdoggz" <brownsugareve2003@xxxxxxxxx> wrote in
news:1158846370.114146.36580@xxxxxxxxxxxxxxxxxxxxxxxxxxx:

(Top posted for brevity...)

I'm not really sure how much effort you want to put into this progress
bar.

With your code below, you are requesting data, then looping through it
and doing some file manipulations. When you execute the sql statements,
you have absolutely no way of knowing the timing of it, until the
recordset is returned completely. At that point, you run through the
recordset with a for/next loop. At that for/next loop, you do know the
timing and can adjust the pBar value properly.

It looks like outside the sql, you have 3 for/next loops handling file
I/O. It is during these that you would have to calculate the progress.

Actually, this looks kind of tough to provide an accurate pBar. It could
take a bit to get it to be fairly accurate.

When you get done with each for/next, you know that another 1/3 of the
progress is done.

If you were using the pBar I linked, when you call the first sql execute,
you can set the caption on the pBar to 'Retreiving Data', or whatever,
then when you get to the first for/next, change the caption pBar caption
to 'Processing Data' and increment the pBar value in the loop. At the end
of the first for/next the pBar should be at 33%. Change the caption back
to 'Retreiving Data', execute the next sql. When you get to the second
for/next, once again change the caption to 'Processing Data', run the
loop incrementing the pBar, etc.

I'm not sure on how this will be perceived by the user though. I would
guess the time it takes to get the data back form the sql will be much
greater than stuffing it in a file, so the bar would sit at 0 for a
while, quickly go up to 33%, sit there until the next sql is returned,
quickly go up to 66%, then once again sit there until the rest of the sql
runs, then once again quickly go up to 99/100%.

You really aren't going to be sure how it looks to the user until you
test it out.

Regards,

DanS



Here is the code of the procedure that I would like the user to see
the progress of....tell me if it's possible.

Private Sub cmdRfmt2Day_Click()
Dim Db As Connection
Dim tempRecSet As Recordset
Dim strSQL As String
Dim TextLine As String
Dim strFile1In As String ' DAY1 FILE
Dim strFile2In As String ' DAY2 FILE
Dim strFile3In As String ' DAY1 DATE FOR HEADER
Dim cmd1 As Command
Dim cmd2 As Command
Dim nCounter1 As Long
Dim nCounter2 As Long



' IMPORT DAY1 FILE TO SQL TABLE "DAY1RECORDS"

Set Db = New Connection

Db.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=ItemProcessing;Data Source=UF2KOBDSSQL1"

Set cmd2 = New Command
cmd2.ActiveConnection = Db
cmd2.CommandType = adCmdStoredProc
cmd2.CommandText = "usp_CreateHolidayTables"
cmd2.Execute
Db.Close

Set tempRecSet = New Recordset

Db.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=ItemProcessing;Data Source=UF2KOBDSSQL1"
strSQL = "Delete from Day1Records"
tempRecSet.Open strSQL, Db, adOpenStatic, adLockOptimistic
Db.Close


Set tempRecSet = New Recordset


Db.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=ItemProcessing;Data Source=UF2KOBDSSQL1"
strSQL = "Select * from Day1Records Where RecordType is not null"
tempRecSet.Open strSQL, Db, adOpenStatic, adLockOptimistic

strFile1In = "L:\xfer\SKY_XFER\DWFD\Testing\DAY1.txt"

Open strFile1In For Input As #1

nCounter1 = 0
Do While Not EOF(1)
Line Input #1, strdata
tempRecSet.AddNew
nCounter1 = nCounter1 + 1
tempRecSet("RecNum") = nCounter1
tempRecSet("RecordType") = Mid(strdata, 1, 1)
tempRecSet("Account") = Mid(strdata, 2, 15)
tempRecSet("Amount") = Mid(strdata, 17, 11)
tempRecSet("Finish") = Mid(strdata, 28, 54)
tempRecSet.Update

Loop

Close #1
Db.Close


' IMPORT DAY2 FILE TO SQL TABLE "DAY2RECORDS"


Set Db = New Connection
Set tempRecSet = New Recordset

Db.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=ItemProcessing;Data Source=UF2KOBDSSQL1"
strSQL = "Delete from Day2Records"
tempRecSet.Open strSQL, Db, adOpenStatic, adLockOptimistic
Db.Close

Set tempRecSet = New Recordset


Db.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=ItemProcessing;Data Source=UF2KOBDSSQL1"
strSQL = "Select * from Day2Records Where RecordType is not Null"
tempRecSet.Open strSQL, Db, adOpenStatic, adLockOptimistic

strFile2In = "L:\xfer\SKY_XFER\DWFD\Testing\DAY2.txt"


Open strFile2In For Input As #1
nCounter2 = 0
Do While Not EOF(1)
Line Input #1, strdata
tempRecSet.AddNew
nCounter2 = nCounter2 + 1
tempRecSet("RecNum") = nCounter2
tempRecSet("RecordType") = Mid(strdata, 1, 1)
tempRecSet("Account") = Mid(strdata, 2, 15)
tempRecSet("Amount") = Mid(strdata, 17, 11)
tempRecSet("Finish") = Mid(strdata, 28, 54)
tempRecSet.Update


Loop

Close #1
Db.Close


Db.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=ItemProcessing;Data Source=UF2KOBDSSQL1"
strSQL = "Select * from Day2Records Where RecordType is not Null"
tempRecSet.Open strSQL, Db, adOpenStatic, adLockOptimistic


Set cmd1 = New Command
cmd1.ActiveConnection = Db
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "usp_HolidayFile"
cmd1.Execute
Db.Close


Set tempRecSet = New Recordset
Db.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=ItemProcessing;Data Source=UF2KOBDSSQL1"
strSQL = "select RecordType + Record As Records from holidayfile"
tempRecSet.Open strSQL, Db, adOpenStatic, adLockOptimistic



Open "L:\xfer\SKY_XFER\DWFD\Testing\Combined2.TXT" For Output As
#1 Do While Not tempRecSet.EOF ' Loop until end of file.
Print #1, tempRecSet.Fields(0)
tempRecSet.MoveNext

Loop
Close #1
Db.Close


MsgBox "Done"

End Sub

Again, I would like to show the overall progress of this entire
code...if possible.

Thanks for everyone's reply!!


DanS wrote:
"jdoggz" <brownsugareve2003@xxxxxxxxx> wrote in
news:1158790841.492898.140740@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:

Hi,

I'm new to VB6, and I would like to add a progress bar for an
entire procedure. The procedure has several database connections
which serve sql queries. It also imports and exports text files.
I would like a progress bar to incriment through the entire process
of the procedure.
From the time that the cmd button is pressed to the point of "end
sub",
I'd like to show the progress. Is there any way for this to
happen? Thanks in advance!!!


Yes you can add a progress bar. Getting it to work correctly will
take some doing, as you need to base the display value somehow
against your function.

If you are looping through something x times, it is easy to make the
bar display the proper progress. You run into a problem when you have
now idea of the processing time. If you are planning to show the
progress bar for sql server queries for instance, where you send a
request and get the data back, you'd have to get some kind of
feedback to be able to update the progress bars value fairly
accurately.

I myself try to use as little ActiveX controls in my projects and do
much of the UI stuff manually, but I have used this progress bar OCX
for a long time because it kills the standard VB pBar. Gradient, text
on the bar, automatically doing percentages, smooth appearance, etc.

http://home.gwi.net/~bergerons/yahoofiles/MBPrgBar.zip



.