Visual Basic Database Tutorial - Part 3
Let me explain how I created the Customer Browser. Well, it all started when I bought a computer and installed Windows 98. Then I purchased Visual Basic and played around a bit. Then I read a few books, sat a few courses and watched a few videos. Then I did some other things.
And then I created the Customer Browser. How?
First off, I added a Data control and changed the DatabaseName property to point to my Nwind.mdb database. I also changed the RecordSource property to the Customers table.
Then I added individual text boxes and "bound them" direct to the Data control. I did this by changing the DataSource property to the name of the above Data control, then changing the DataField property to the table field I wanted to 'connect' this text box with.
Next, I added six boring (really boring!) command buttons and changed their Caption properties. I'll add code behind these later.
So far, I've created everything I need to browse the entire Customers table. But what about the orders?
First, I added another Data control to the project. The main control above will handle the customers, whilst this one will retrieve the orders.
Next, I added the DBGrid control to my Toolbox by clicking Project, Components and selecting 'Microsoft Data Bound Grid Control 5.0'.
Then I added an instance of the control to my form, as above - changing its DataSource property to the name of my second Data control.
Now the design is over and it's time to code, geek-to-geek. Here's the code you'll find underneath each command button:
datCustomers.Recordset.AddNew ' Go on, add a new customer! ' Just fill in the customer fields ' and move to another record. Then ' try using the Search button to ' find the customer again!
Due to the way Microsoft have setup the Nwind.mdb database, you can't delete customers that have orders. If you do, you'll receive an error. You must first delete all the orders before deleting a customer. This type of database integrity is maintained using "relationships", which we'll cover later in this tutorial.
On Error Resume Next datCustomers.Recordset.Delete ' Delete the record datCustomers.Recordset.Requery ' Then 'requery' the Recordset - ' which will 'refresh' it. ' It should show everything it did ' before, minus the deleted record
Dim CompName as String CompName = _ InputBox("Which company would you like to search for?") ' Get name from user, via InputBox datCustomers.Recordsource = "select * from Customers " & _ "where CompanyName = '" & CompName & "'" ' Merge that name with an SQL string datCustomers.Refresh ' Refresh the customer Data control
You'll notice that for the majority of buttons, we're simply using a method of the Data control's Recordset object, such as datCustomers.Recordset.Delete
The Recordset object in datCustomers is the bit that actually holds all the records. As its name implies, a Recordset is simply a "set of records". So when you want to delete a record, you simply fire that method of the Recordset.
Top Tip: You know how you can declare and work with strings in Visual Basic? Well, you can also declare and work direct with Recordset objects in code, something we'll be covering later in this tutorial
Of course, you can always implement error handling and all that jazz into your command button code, but I haven't bothered in other to keep things simple. And because I'm a complete sloth.
Now things may seem a little sticky when it comes down to the Search button code, but it's really pretty straight-forward. The code is doing this:
- Grabs the company name from the user (InputBox stuff)
- Changes the RecordSource property to an SQL string, with the company name inserted in the middle
- Refreshes the Data control to display the new bunch of records in the Recordset
There, that wasn't too bad, was it?
Page 8 of 10