WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
A progress bar is used to inform the user about (lengthy) processing. It shows the user the status of processing/computations, and depicts that the application has not gone into a "not responding" state. A large number of applications, such as setups, database-driven applications, and file transfer tools, swear by progress bars. Under Visual Basic, the progress bar can be included on forms through the Microsoft Windows Common Controls component. After spending a month on the Internet, searching for an (useful) implementation of a progress bar in a Visual Basic application (which eventually failed), I thought of implementing one on my own.
What Will We Be Doing?
We will search a table (in a database named Fpnwnd.mdb) for tuples that meet a specified search criteria. More specifically, we will search a table for Company Names in a particular country. As we find a tuple in the table with the specified country, we will add the corresponding Company Name to a list box. While the table is being searched from beginning to end, we will try to keep the user informed about the search progress through the use of a progress bar. This progress bar will start at 0% and go to 100%, which means that the entire table has been searched.
A Little Homework
For those who haven't used a progress bar ever in Visual Basic, it is a control provided with the Microsoft Windows Common Controls component. It has various exposed properties and methods, of which we will be interested in only three properties: Min, Max, and Value.
Min, as the name indicates, is used to denote the lowest value a progress bar can take. This is the initial starting position of the progress bar. Similarly, Max is used to depict the maximum value that can be assigned to the progress bar. A progress bar can't take a value greater than the one specified in the Max property. Finally, the Value property can be used to assign (or retrieve) a value (which must, of course, be between Min and Max (both inclusive)) to the progress bar, so that the bar in the progress bar can increase appropriately.
I have used ADO (ActiveX Data Objects) and ODBC to connect to the database. Once I am connected to the database and have fired the query, I am provided with the recordset objects containing all the records (satisfying the specified SQL command, 'select * from Customers', in my case).
Steps to Follow
I have used Microsoft Office XP's default database Fpnwind.mdb for my example. However, if you don't have it on your machine, you can use any other database. (You will, in that case, need to change the SQL statement appropriately.)
- First of all, create a DSN for the database (you may name it 'fpnwnd').
- Open Microsoft Visual Basic and create a 'Standard EXE'.
- Go to Project > References and select 'Microsoft ActiveX Data Objects x.x Library'. Here, x.x is the version of ADO your system has. Click OK.
- Now, go to Project > Components and select 'Microsoft Windows Common Controls x.x'. Again, x.x is the version of common controls on your machine. Click OK.
- Finally, from the toolbox, add the appropriate controls to the default form. Don't forget to rename the commands accordingly.
When you are done with these steps, place the following source code in the code window and press <Ctrl> + <F5>.
Dim MyConn As New ADODB.Connection Dim Sql As String Dim MyRs As New Recordset Private Sub AdjustListBoxSize() ' Some math, to show up and hide the percent and other label ' The listbox ovelaps the labels If lstResults.Height = 3375 Then lstResults.Height = 2790 lstResults.Top = 1800 Else lstResults.Height = 3375 lstResults.Top = 1200 End If End Sub Private Sub cmdSearch_Click() ' Specify the SQL to be run on recordset - selecting all records MyRs.Source = "Select * from Customers" ' Open the recordset (obtained from SQL) MyRs.Open Call AdjustListBoxSize ' Set up the progress bar's properties ProgressBar1.Min = 0 ProgressBar1.Max = MyRs.RecordCount ProgressBar1.Value = 0 ' Set up the labels showing percentage of completion lblPercent.Visible = True Label2.Visible = True ' Clear the list box lstResults.Clear While Not MyRs.EOF If MyRs("Country") = txtCountry.Text Then lstResults.AddItem MyRs("CompanyName") End If lstResults.Refresh For I = 0 To 400000 ' Do nothing, but wait ' To show up the progress bar proceeding Next I ' Update the progress bar and percent label accordingly ProgressBar1.Value = ProgressBar1.Value + 1 lblPercent.Caption = Int(ProgressBar1.Value * 100 / ProgressBar1.Max) lblPercent.Refresh MyRs.MoveNext Wend ' Hide the percent and other labels lblPercent.Visible = False Label2.Visible = False Call AdjustListBoxSize ' Close the recordset object, so that the next query can be fired MyRs.Close End Sub Private Sub Form_Load() MyConn.Open "DSN=fpnwnd" MyRs.ActiveConnection = MyConn MyRs.CursorLocation = adUseClient End Sub
The trick for the implementation of the progress bar in this example is that the recordset object provides a RecordCount method that returns the number of rows affected (as a result of the query fire on the specified table). All I have done is set up the Min property of the progress bar to 0 and Max property to MyRs.RecordCount. This will allow me to update the progress bar (by Value property) in a single-increment fashion.
When the recordset is created in memory, I parse it from beginning to end (hence MyRs.EOF). As soon as the record with the matching criterion is found (I am searching for 'Country Name' here), the Company Name attribute is read from the table and is appended to the list box control; then I move on to the next record (through MyRs.MoveNext). On the other hand, if the record doesn't match the criteria, I simply move on the next record (without adding the Company Name to the list box). Prior to moving on to the next record (in both cases), I increment the value of progress bar by 1. This process continues until I reach the EOF (End of File), where I must terminate the processing (the progress bar must reach 100%).
The progress bar calculates the number of bars required to fill itself entirely, by means of the Min and Max properties. Hence, when we increment or decrement its Value, it automatically fills up the bars in the area apprpriately. I have also used a label control to depict the percentage completed (which is plain mathematics, isn't it?). The rest of the code does formatting (shifting, enlarging, contracting, enabling and disabling, and so forth appropriately).
I hope the code example shown here will give you an insight into the Progress Bar Control. I believe that (keeping this example in mind) you will be better able to use this control in your applications in an effective and productive manner.
What You Might Say
Some of the brilliant, seasoned SQL programmers might argue that I wasted my time searching through the entire table when I could have specified select * from Customer where Country=" & txtCountry.Text, instead of select * from Customer. My answer to all those gurus is that I am emphasising the usage of a Progress Bar in a Visual Basic application and not on writing effective queries. If you use the former version of the query, the progress bar will fill up very fast and you probably will not be able to view the practicality of the progress bar.
You may use a Timer control in place of the blank loop (implemented for the waiting purpose) in the demo application. This will tune up your application for the optimization. This technique is useful because, as soon as a blank loop is encountered, the CPU goes into a dark room, doing nothing but waiting for the loop to finish execution. Thus, a significant number of CPU cycles is wasted. However, if you use the Timer control (after setting its Interval property to a reasonable integer value) the CPU will be able to do other useful jobs (such as implementing the OS' driven scheduling) while the Timer is computing the ticks.
The Timer will notify the CPU from time to time instead of making it engaged all the time. Even if the Timer will be computing with the help of the CPU, the aliter approach will be better. The code will require a little modification all over. This modification will not be trivial at all. Despite of this, if you find the updating clumsy, either remain stuck to the original code or do let me know about it (and we will work around it).
As I already said, I have used Microsoft Office's default database, Fpnwind.mdb, for this example (340 KB). If you don't have this database at your machine but still want to test it, you can send me an e-mail and I will arrange the same for you. View my profile from the top of this page to send me e-mails.