Midrange Programmer

In the article "Sprinkle GUI on Your Green Screen Salad," I pumped some nutritional concepts at you, like ODBC, SQL, and ADO. There wasn't really too much in the calorie department, but there were essentials you needed to have some understanding of before today's lesson. Today, we get into the dessert, the fun stuff, as I discuss how to use the ListView control in Visual Basic to display records and how to manipulate our records on the AS/400.

My first objective is to introduce the ListView control in Visual Basic and show you how it works to display AS/400 records. Figure 1 shows you what the Visual Basic form containing the control looks like.

The second objective is to create a form, or a pop-up window, in VB, where we can add, change or delete our existing database. Figure 2 shows you the screen that we will use to modify our records. The concepts introduced in the first article regarding SQL and ADO will be used throughout today's presentation. Mom, can I have a spoon? It's time to eat some pudding.

You are probably thinking that you've never seen the ListView control. If you've ever used the program Windows Explorer, then you have been exposed to it. In Windows Explorer, the left window is the TreeView control and the right window is a ListView control. The ListView control has four different views: large icons, small icons, list, and report. We will be using the report view, so we have column headings to help describe our data. The ListView control is contained in the ActiveX object named COMCTL32.OCX and can be added to your VB project by selecting Project, then Components, from VB's menu, and choosing Microsoft Windows Common Controls 5.0. Several controls--including Slider, Progress Bar, Tab Strip, TreeView, and StatusBar controls--are also included when you select this component. Let's see how the ListView control plays a role in our application.

There are a few things I need to point out. First, all of the code for this article is available for download. Second, you need a reference, in your Visual Basic project, to ActiveX Data Object. You can make this reference by selecting References from the VB IDE Project menu and selecting Microsoft ActiveX Data Objects (ADO) 2.5 Library. This is the MSADO15.DLL file and will allow us to use the ADO objects. In the sample program included in the download file for this article, you will find two forms, or windows. The first form is named frmMain, which gets filled with AS/400 data and allows us to select an individual record to change. The form frmDetail is displayed when we select a record from frmMain. Let's start reviewing the code that makes this all work.

The main piece of code is in the form frmMain in a procedure called FillView. Its purpose is to fill the ListView control with data from the AS/400.

The statement Listview1.listitems.clear clears all of the data that exists. This will be helpful because, if we have added, updated, or deleted a record, we want that reflected within the ListView control.

Next, we build our mRS object, which is an ODBC RecordSet object, and connect it to the mConn object (which is an ODBC Connection object). The mConn gets created when we open the program and is destroyed when we close the program. It is tied into the DSN name we created in the previous article, under the ODBC section. If you have not yet read and completed the tasks in that last article, you should do so before continuing here.

The sqlstg variable holds our SQL string to retrieve all of the data from the AS400. When we use sqlstg in the mRS.Open command, it returns all of the records into mRS object. It is important to know that the "order by" is loading the mRS object with records sorted by the FLAST field (last name). This field is one of the named fields in the physical file on the AS/400.

I have a loop where I will read each record found. I have four columns in my ListView control that were already set up. To see how they were set up, check out the properties of the ListView control. They were set up as follows:

The set mylist statement lets me add new entries into the Listview1.Listitems collection. This information is being displayed on your screen in the columns defined above. I'll try to read another record, and if one is found, I'll go through the same process of putting another entry into the collection.

After I have read all of my records, the mRS object gets closed and destroyed. I do this because I don't want any unnecessary calories in my diet or any objects left open in my application.

The other part of the code, found within frmMain, is selecting an entry on our ListView control. By double-clicking any record in the first column, the following happens within the Listview1_DblClick subroutine.

First, the information in the ID column is found, so I know what record I will be dealing with. This information also gets stored in the currentID variable, so I can refer to it when I am updating or deleting a record in the frmDetail form.

Then I fill the frmDetail form with the last name, first name, city, state and phone-number fields found, and display the form. Figure 2 shows the record for Vanessa Baker that was selected and is now displayed.

The purpose of the frmDetail form is to allow us a gateway to change our database. Let's take a peek at the code embedded within it. The two main pieces of code are found in the Delete_Click and Update_Click routines. The Delete_Click subroutine deletes the records, while the Update_Click subroutine updates the database. Let's chat about the Delete_Click subroutine first.

This subroutine runs when the Delete Record button is clicked. It first asks if you really want to delete the record. If you choose yes, it retrieves the currentID field from the frmMain form and inserts it into an SQL statement to delete the record. It then runs the FillView routine from the frmMain form, to make the changes visible, and then unloads the frmDetail form. Pretty easy stuff so far?

The biggest thing to note is that I am adding a new record or updating an existing record. This is determined by what value is found within the mymode field. If mymode equals 0, then I am in update mode; if not, I am in add mode. Let's review the update path first.

First thing, I am building a SQL string to use within the mRS recordset to choose the correct record, because I know the FID field is stored in currentID in frmMain. I open the recordset with the mRS.Open statement.

I then take the information in the text fields on the screen and put the values into the fields that I wish to update on the AS/400. After I've done this, I issue the mRS.Update, and the record has been updated.

Then I run the FillView routine in the frmMain form to update the ListView control and close down the frmDetail window.

To add a record, follow the same logic as is in the Update_Click subroutine, except that the mymode field value is 1. The other difference is that, if we are in add mode, we only see one button on our screen that says Add Record. In update mode, we would see two buttons: Update Record and Delete Record.

Adding records is similar to updating records, except for a few details. Adding the new record starts with the Addnew method. After that, I fill the fields with the necessary values and then issue the Update method, which adds the record. The one exception is that the FID field gets created using the current date (MMDDYY) format and time (HHMMSS) format. When you go to edit this record in the future, you have seen that this field is invaluable to the whole process working correctly.

Well, that wraps up our dessert-tasting session. You have seen how critical it is to have an understanding of ADO and SQL, because they are used throughout this article. You have also seen that is pretty simple to add a GUI front-end to your back-end database on the AS/400. Who said you can't have your cake and eat it, too?

Bob Butcher is an AS/400 consultant with over 15 years' experience in the AS/400 arena. He specializes in client/server applications that deal with the AS/400 and Active Server Pages or Visual Basic. Bob can be reached at butcherb@samicsoft.com.