Creating the List Window
The list window uses a DataGrid control bound directly to the Access database table. In this particular form, three controls are used to bind the database to the DataGrid—an OleDbConnection object, an OleDbDataAdapter object, and a DataSet object. The form also includes a Timer control and a Menu control. In this section, you'll add code and set properties for all these controls.
This form is called frmStockMonitor and you should set the following properties of the form before getting started with the controls:
| Property | Value |
|---|---|
| (Name) | frmStockMonitor |
| Text | Stock Monitor |
The next control to add is the DataGrid, which is shown on the Toolbox's Windows Forms tab. Add the control to your form and set the following properties:
| Property | Value |
|---|---|
| (Name) | grdStocks |
| AlternatingBackColor | 224,224,224 |
| BackColor | White |
| CaptionVisible | False |
| ReadOnly | True |
| RowHeadersVisible | False |
As you add the database binding controls, other properties will be set for you. Place the control so that you have a margin on the top and left of the control. In the Resize event handler, you'll write code to resize this control to fill the form's area, and the margin will be based on the position of the upper-left corner of the control.
Periodically, this window will update the prices of the stocks being monitored. To do this, a Timer control can be set with an interval in milliseconds to refresh every 10 minutes, which would be 600000 milliseconds. This value goes into the Interval property. Add a Timer control and set the following properties:
| Property | Value |
|---|---|
| (Name) | tmrInterval |
| Enabled | True |
| Interval | 600000 |
If you want, you can change the interval to be a shorter length of time for testing purposes.
The last non-data control you need to add is the Menu control. Unlike previous versions of Visual Basic, there is no separate menu editor. After you add the Menu control to your form, you can edit the menu in place. The following are the menu choices and options you want to create for the menu:
| Text | Name | Shortcut |
|---|---|---|
| &Functions | mnuFunc | |
| &Add Stock... | Ctrl+A | mnuFuncAdd |
| &Edit Stock... | Ctrl+E | mnuFuncEdit |
| &Delete Stock | mnuFuncDel | |
| - | mnuFuncSeparator | |
| E&xit | mnuFuncExit |
Now that all the non-data controls are created, you can add your data controls to bind the grid. The first control you want is the OleDbConnection control, which is on the Data tab of the Toolbox. The properties you need to set on this control are as follows:
| Property | Value |
|---|---|
| (Name) | dbStocks |
| ConnectionString | * use wizard to create connection string |
When you select the ConnectionString property, you'll have the option to create a new connection string to point to your Access database.
The next step is to create an OleDbDataAdapter control to handle changes to the database that you will be making by way of the DataSet object. Create the OleDbDataAdapter control, name it daStocks, and then right-click it to select Configure Data Adapter from the pop-up menu. Using a wizard, the Data Adapter will generate the SQL statements to insert, delete, and update records based on the SQL SELECT statement you write. Use the following SQL statement or a variation:
SELECT StockID, CurrentPrice, HighPriceAlert, LowPriceAlert, LastPriceUpdate FROM tblStocks ORDER BY StockID
After you enter this statement and continue through the configuration wizard, the insert, update, and delete statements will be generated automatically for you.
The final step is to generate a DataSet object. If you right-click the configured data adapter object, you can select Generate DataSet from the pop-up menu. In the dialog that appears, you can choose to create a new DataSet object and to link it to the tblStocks table the data adapter is providing. You can rename the DataSet to dsStocks, which is what is used in the example code.
The final step in binding is to tie the DataSet to the DataGrid control. This can be done in several ways. You can bind the DataSet at design time by changing the DataSource property of the DataGrid to point to the DataSet you created. The other way you can do it is through code, which you'll see in the code in the next section.