Visual Basic Database Tutorial - Part 6
Guten Morgen and welcome to yet another instalment in this wizzy Visual Basic Database tutorial.
As ever, I'm your stunningly geeky host Karl Moore and if you've missed any previous slots, check out:
This week, we'll be taking a sneak geek peek at how you can program your Visual Basic application around last week's groovy database design. We'll then take a little look at validation before moving on to create a simple VB-integrated report.
But be warned, those with older versions of Visual Basic may feel about as comfortable as the passengers of infamous Flight 402, with the plane designed by La Augustus of Tower of Pisa fame, technically authenticated by Crazy Joe of Slackers Associated, and piloted by Kamikaze brothers Tun Twick Tang and Tun Twick Tang Junior.
Unfortunately we're dealing with all the latest and greatest features, which only work with VB 6.0 yeah, I know, I'm sorry. Please, stop blubbing.
Oh, and don't forget to tell us how we're doing. If you love the series, tell us using the below feedback form. If you hate the series, tell us using the feedback form below. If you're after a date, e-mail me - Karl@karlmoore.com ;-))
Anyway, let's get on with the show, kiddo...
Binding, binding, binding. Sounds a bit boring doesn't it?
I'd much prefer the word "bonding". Even better, "bondage". But unfortunately that just wouldn't go. Sure, we all know this is a family website, but the Editor's on holiday in France this week so why not? ...
Do you remember all the bondage we encountered back in the distant realms of tutorial one? How we tied that text box direct to the database! We just added a data control, threw a few text boxes on a form, set a couple o' properties... and hey presto, we're up-and-running.
But with the wizzy database design you created last week, that just isn't possible anymore. For instance, if you wanted your users to enter new pets into the Pets table, your database requires a PetName, Breed... and an OwnerID.
If you remember, the OwnerID is a Foreign Key. In other words, it's a number that links this particular record to a parent record in the Owners table.
But asking your end user to input an OwnerID isn't very user friendly. And when you start creating databases that are absolutely jam-packed full of Foreign Keys, it'll become more confusing then the Arabic translation of Stephen Hawking's "Bumper Book of the Galaxy".
So you need to make it simple, by allowing them to select an owner from a list... but you should still insert the OwnerID number into the field.
How do you do this? By use of a few little-known control properties...
In previous tutorials, we've connected to databases using the bog standard Data control.
Now this seemingly innocent control accesses your Access database via a technology known as DAO, or Data Access Objects.
Since those halcyon days of prehistoric data access, Microsoft has released another two ways of getting at databases Remote Data Objects (RDO) and ActiveX Data Objects (ADO).
Remember we programmed in database code a few instalments back? 'Dim db as Database' and all that? Well, we were using Data Access Objects to do that.
But RDO and ADO offer us new ways of accessing such information. How come? Well, we'll skip over RDO for the moment. But ADO supposedly allows us to access all sorts of information, above and beyond your typical Access database.
In essence, you should be able to simply connect with any data source from SQL Server to Access to Oracle to an e-mail application to old Mainframe information all using the same wad of ADO code.
And that code is a little different to the stuff we wrote earlier. Not too much, but there are unique distinctions.
Why am I telling you all this? Because those special control properties I was talking about on the previous page the ones that'll help you design a great VB application around an ever better database design - are only available when you access databases through ADO.
So first off, start a new project and let's add the ADO equivalent of the Data control to our project:
- Select Project, Components
- Scroll down the list and check 'Microsoft ADO Data Control 6.0'
- Click OK
You'll notice a new control appear in your toolbox -
- Double click on the ADODC control to add it to your form
- Change its Name property to 'datPets'
We now need to tell this control we want it to connect with the Vetinary database we created last week. This is done slightly differently using ADO via 'connection strings':
- Click on the ellipsis beside the ConnectionString property
- Click the Build option
- On the screen that appears, select 'Microsoft Jet 3.51 OLE DB Provider' if you created the database in Access 97 or 4.0 if you created it in Access 2000
- Click Next
- Type in the path to your database (eg, c:\surgery.mdb)
- Click OK
- Click OK on the Property Page form
You will notice the builder inserted something similar to the following in the ConnectionString property:
- Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Surgery.mdb;Persist Security Info=False
This string tells ADO what type of database it is looking at and where it can be found. Now, as with the DAO control, we need to tell it what information to retrieve:
- Click the ellipsis next to the RecordSource property
- Select Command Type '2 adCmdTable'
- Select the 'Pets' table from the available list
- Click OK
Now we're going to add another control to our program:
- Click Project, Components
- Scroll down, select 'Microsoft DataList Controls 6.0'
- Click OK
You should see two extra widgets appear in your toolbox -
Add the one titled 'DataCombo' to your project and change its Name property to 'dbcOwner' and its Style to '2 dbcDropdownList'. This is the special control that will allow the user to select an owner name, but still put the OwnerID number into the database.
But before we can use that DataCombo control, we need to add another ADODC control.
- Add another control to your project
- Change the Name property to 'datOwners'
- Set the Visible property to False
- Use the same ConnectionString in this control as you did the previous datPets
- Change its RecordSource to the Owners table
So that control is now setup to retrieve information direct from the Owners table. Now we plan to send those details to the DataCombo control and tell it to display the owner name but when inserting into a database, use the OwnerID field.
We do this using a few supercool properties:
- RowSource the data control from which the combo box gets its initial wad of information
- ListField the field that will be listed in the combo box
- BoundColumn the underlying value or 'Key' field (eg, OwnerID)
So let's change our Data Combo properties to:
- RowSource datOwners
- ListField OwnerName
- BoundColumn OwnerID
Now add a couple of other text boxes to store the pet name and pet breed values.
Link all three of your controls up with the datPets ADODC control by changing the DataSource and DataField properties.
My completed application looks a little something like this:
If yours doesn't look as good as mine, well... it takes years of practice.
If it looks better than mine... well, I don't want to discuss it. I'll start getting grumpy and upset. I may even go for a sulk somewhere in the corner of cyberspace.
Anyway, hit F5 to run your application. Does it work?
Err, as this is a kinda one-way thing... I'll presume you answered with a resounding "yes!"
You should be able to scroll backwards and forwards through your Recordset using the ADODC control. You should also be able to change the owner of a pet very easily using the Data Combo box whilst at the same time keeping physical numbers in the database, not text values.
So there we have it a basic application built on a solid database design with absolutely no code.
Why not try creating another table to hold all the pet breeds and linking into those with another key? Go on, I dare you!
Cliff Richard once had a number one hit that began, "Congratulations (strum, strum) and Validations (strum, strum) lah lah lah dadada lah lah dada la lahhh!".
Actually, no, he didn't. In fact, those fictional lyrics weren't even remotely plausible. But nevertheless, it introduced the next section validation which is itself rather abstract.
You see, it doesn't really fit anywhere within my grand plan to boost your database knowledge. And that's why I'm going to include it here.
[Ed: I see... clear as mud]
Now, many of the common controls have a 'CausesValidation' property, which is set to True by default. This means before a user leaves the Text Box or Combo Box or whatever, the Validation event is fired.
And it's there you can check what they entered. For example, I placed the following code behind my txtBreed button from the project on the last page:
Private Sub txtBreed_Validate(Cancel As Boolean) If Trim(txtBreed.Text) = "Unknown" Then MsgBox "Can't accept that. Are you sure " & _ "it's not an iguana?" Cancel = True End IfEnd Sub
If a user enters 'Unknown' into the Breed Text Box, my code tells the Text Box the value provided is unacceptable by setting the passed Cancel value to True. And that means the focus remains on the Text Box in which the problem occurred.
If I performed no checking or set Cancel to False, the user would've been able to continue in their operations.
So the Validate event is neat for verifying information a user enters; whether you want to ensure they've typed a sensible number or you need to perform a little syntax checking, it's a pretty cool event to have around. In fact, it's cooler than Mr Cool the Cola Bear, winner of last year's I'm-Sooo-Cool competition.
[Ed: Hmm, no]
[Karl: <Tears> ]
Some really tricky users don't just want to throw information into a system, they also want a little output in the form of reports. Sure, we know they're just being picky... but you'd better oblige in the faint hope of a pay rise sometime within the next millennia.
There are a few ways you can create reports via Visual Basic. The most popular are:
- Transfer query results direct to Excel for processing
- Produce reports in Access and print out occasionally
- Use Crystal Reports (usually the cut-down version on VB CD)
- Utilise the built-in Data Report widget
In this section, we're going to check out the latter Data Report object. This allows you to create a report in Visual Basic very similar to those found in Microsoft Access and it can be viewed, printed or exported direct through your application.
So let's have a stab at creating our first Data Report in the 'Groovy Pet Adder' program we created earlier:
- Click Project, More ActiveX Designers, Data Environment
Every Data Report requires a source of information. In Visual Basic, this comes from a Data Environment.
Now stick a big hat on my head, call me Professor and dance around ten times chanting "He's Einstein, Shakespeare and Angela Lansbury rolled into one"... but I personally understand a Data Environment to simply be an environment. For data.
Anyway, let us continue fiddling with it:
- Click on the node titled 'DataEnvironment1'
- Change its Name property to 'Vets'
- Change the Name property of Connection1 to Surgery
- Now click on the 'Add Command' button at the top of your screen
- Change the Name property of Command1 to Owners
The Finished Product
All this 'naming' ensures we keep track of the information these objects will hold.
- Right-click Surgery and select Properties
- Choose Jet 3.51 if your Surgery database was created in Access 97, or Jet 4.0 if you used Access 2000
- Hit Next
- Type in the database path and click OK
That's told the Surgery object all things below it (ie, Owners and Pets objects) will get their information from the Surgery database.
- Right-click Owners and select Properties
- Click the 'SQL Statement' radio button and enter the following SQL:
Select * from Owners
This statement simply tells the Data Environment's Owners object to retrieve all fields from the Owners table.
- Click OK
- Tap the little + next to the Owners object
You should see a list of fields your query returns OwnerID, OwnerName and Address. But in this report, I want to display each owner followed by every pet name and breed owned by that character. So we need to tell our Data Environment about the Pets table:
- Right-click Owners and select 'Add Child Command'
- Rename the command you just created, 'Pets'
- As with the previous command object, enter an SQL statement of:
Select * from Pets
So far we've told the Data Environment about the existence of both tables, but not how they're related to each other. So:
- Right-click Pets and select Properties
- Click the Relation tab this is where you define the link between the parent object Owners and the child object Pets
- In Parent Fields combo box, select OwnerID and likewise in Child Fields. Then click the 'Add' button. This links the two lots of information together
- Click OK
Top Tip: You can change the SQL statement behind your 'command' in code with -
YourDataEnvironmentName.Commands("Owner").CommandText = _"select * from owners"
This allows you to dynamically change the database information your reportwill be based on. So instead of selecting ALL the owners, you could perhapspick out just one.
So we've sorted out the information we want to get from the database... now we just need to throw it into a report:
- Click Project, Add Data Report
This is the report window in which you may design your report. But before telling it what to display, you need to tell it to link to your Surgery Data Environment:
- Click on the 'DataReport1' title to display relevant information in the Properties window
- Change the DataSource property to Vets and the DataMember to Owners
- Now right-click anywhere on the Data Report and select Retrieve Structure clicking Yes when prompted
Now select Window, Tile Vertically. This should enable you to see both the Data Environment and Data Report screens at once.
First, drag the OwnerName field from the DataEnvironment direct over to the 'Group Header (Owners_Header)' band. Do the same with the Address field.
Next, drag and drop the PetName and Breed fields over from the Data Environment into the 'Detail (Pets_Detail)' band.
Now try formatting the report. Perhaps you'd like to bold or rename a few of the field labels, shrink the Details section or add a title using the toolbox controls.
This is what my report looked like after a little fiddling:
Return to the main 'Groovy Pet Adder' screen and add a command button with a caption of 'View List of Pets'. Behind that button, add the code:
where 'MyReport' is, err, the name of your report.
Then run your program and hit the command button. Your report should look something like this:
Note the buttons in the top left-hand corner. Although you can remove these by setting a few properties, they allow your user to easily print the report even export it to various file formats, including a HTML document
Cool, eh? In fact, it's cooler than Mr Cool the Cola Bear, winner of... oh darn, I've used that one.
For more information on creating a Data Report, search help for "Creating a Simple Data Report".
This week, we covered how to build your Visual Basic application around last week's supercool design. Then we dabbled with a little abstract validation before skipping on to create a simple report.
Next week, we'll be taking a peek at the built-in Visual Database Tools that ship with Visual Basic 6.0. And if you're using SQL Server or Oracle databases, you're in for a treat.
Until then, I'm your wizzy host, Karl Moore... saying goodnight for tonight. Goodnight!
[Note: You can download the full VB 6 project used in this week's tutorial here]
Karl's Database Tutorial Index
Visual Basic Database Tutorial - Part 1
What do VB-World.net and databases have in common? Well for a start, the letters "b", "e" and "t". But more relevantly, over the next couple of months, it'll be hosting a weekly Visual Basic database tutorial with head geek, Karl Moore.
Visual Basic Database Tutorial - Part 2
Wondering how to use databases in Visual Basic? Ponder no more. This week, head geek Karl Moore returns with the second part in his Visual Basic Database tutorial. That's right, he's back... and he's geekier than ever.
Visual Basic Database Tutorial - Part 3
The Head Geek is back with the third part in his Visual Basic Database tutorial. Join Karl Moore as he takes you on a wobbly ride through the wacky world of Access, queries, grids and controls. And that's just this week!
Visual Basic Database Tutorial - Part 4
Head geek Karl Moore returns with another extra helping of juicy database info. This week, Karl explains how to access databases completely in code and build a complete working mini db application!
Visual Basic Database Tutorial - Part 5
Database diva Karl Moore continues his epic series by taking a look at good db design, primary keys, relationships and more.
Visual Basic Database Tutorial - Part 6
Karl Moore continues his epic database tutorial by taking a sneak geek peek at good database design and validation, before moving on to create a simple VB-integrated report.
Visual Basic Database Tutorial - Part 7
VB-World database dude Karl Moore concludes his epic tutorial series by uncovering the mysteries of Oracle, SQL Server and the oh-so-groovy VB database tools.