http://www.developer.com/

Back to article

Visual Basic Database Tutorial - Part 4


November 19, 2002

Good morning and welcome to yet another fantabulous instalment in this Visual Basic Database Tutorial!

Im your surprisingly groovy host Karl Moore, and if youve missed the previous three parts, whereve you been? Check them out:

Don't forget that I'd love to hear what you think of this series - if you consider it terrible, tell us. I won't be offended. Much. Just use the feedback form below!

"So what Visual Basic delights will we encounter this week?", I hear the ecstatic surfers cry. Well...

  • Create a database application completely in code!
  • Do some other stuff in code
  • Figure out the BOF and EOF acronyms
  • Then well do a bit more stuff in code
  • And finally, well take a look at a few interesting database properties and methods

As you can see, its all awfully exciting stuff. So come on chaps, lets crack that whip, scream "Tally Ho! Barf Barf!" and set sail for some real geeky VBing...

Right, fellow Visual Basic geeks - this is the section which separates the boys from the men, the girls from the women, and that dodgy unknown down the street from his even dodgier looking brother. Yes, were about to access a database completely in code! Muhahahaha!!

[Ed: Mr Producer, are you SURE we cant cut his contract early?]

The annoying customer services department is on the phone. Again. Theyd like a program into which they can tap a customer ID and have it display the total number of orders for that customer, along with the last order date. Why? To calculate the amount of discount to give, apparently.

So lets start by designing our wizzy Visual Basic form. Heres something I prepared a little earlier:

Quickly knock together the above in your copy of Visual Basic. Ive not set any special properties, just added a few labels and text boxes.

Ive named my three text boxes -

  • txtCustID - the one that holds the customer ID
  • txtTotalNumber - will display the total number of orders
  • txtLastDate - will display the last order date

If you stick with these names, itll make code writing much easier later in this tutorial.

So thats the design out of the way... now we need to think about code.

Previously, weve accessed databases solely through the Data control. But this time were doing it in code, using objects.

Just as you "Dim XYZ as String", were going to do the same with a collection of special objects that allow us to play with our database. For example, you might write "Dim XYZ as Recordset" - which is an object that holds a set of records - then later say "XYZ.Delete", to delete one particular record.

I remember back in the golden olden days of my programming life, when I first saw database access code. Argh! I mean, whod want to write all that code when you have the Data controls?

After a bit of use, it becomes clear that those Data controls arent particularly cool. After all, you often want just one little bit of information from the database - and then youre finished with it. Without code, youll have a clunky Data control constantly loitering on one of your forms. With code, you can simply tell Visual Basic, "access the database, get me that information, close the database"... the end.

In brief, accessing databases via code is amazingly flexible. Oh, and its something else to brag about at the next Visual Basic group meeting.

Before we start coding with the database objects I mentioned earlier, we need to tell Visual Basic we're going to use them. To get those objects into our project, we need to set a "reference" to them - by clicking Project, References. You should be presented with a list longer than Bill Clinton's "To Do" pad.

Scroll down to the ones starting 'Microsoft DAO'.

Top Tip: DAO stands for Data Access Objects, though its considered better taste to use the confusing three-letter acronym instead

Youll notice quite a few there. DAO 2.5, 3.0, 3.51, 3.6 - Zzzzz......

Every now and then, Microsoft likes to unveil new versions to access the latest database formats. For example, you should open Access 97 databases using DAO 3.51. And if youre wanting to plug into an Access 2000 database, youll want to choose DAO 3.6.

Were going to access the Nwind.mdb database again, which is an Access 97 database. So click the checkbox next to Microsoft DAO 3.51 Object Library and click OK.

Brilliant! In the next section, well get down to the real groovy stuff, something even more exciting than the Bumper Book of Italian Marriage Laws... actual database code!

[Sarcastic Ed: Gee, Im getting SO excited already... ]

Hold onto your frilly knickers folks, this is where we dive straight in at the deep end, with a tonne of real-world code.

You should find the below code fairly easy to walk through, with comments every step of the way. Add this behind your 'Find It!' command button and give it a test run!

Private Sub cmdFind_Click()Dim db As Database' This is the object that will hold the connection' to our databaseDim rs As Recordset' This is the object that will hold a set of' records coming back from the databaseDim SQLString As String' This is just to temporarily hold the SQL stringSet db = OpenDatabase("c:\Microsoft Visual Studio\Nwind.mdb")' This activates the database object, telling it' to link to the Nwind.mdb database. Note that' you may have to change this path depending on' where Visual Basic has been installed on your PC.SQLString = "SELECT Orders.CustomerID, " & _    "Count(Orders.OrderID) " & _    "AS NoOfOrders From Orders GROUP " & _    "BY Orders.CustomerID " & _    "HAVING (((Orders.CustomerID)='" & _    txtCustID.Text & "'))"' This SQL statement was created in Access. It simply' returns the number of orders for a particular customer' using the 'Count' feature on the 'Total' line. If you'd' like to use Count, but are a little unsure about it -' search Access help -' it's very simple!Set rs = db.OpenRecordset(SQLString)' This ties the recordset object with the database' object. You're telling it to set the recordset object to' whatever the "db.OpenRecordset" function returns. And' that function will return a set of records according to' the SQL statement you pass it.txtTotalNumber.Text = rs.Fields("NoOfOrders")' Simply throws the value in the 'NoOfOrders' field' from the Recordset, direct into the txtTotalNumber' text boxSQLString = "SELECT Orders.CustomerID, " & _     "Last(Orders.OrderDate) " & _     "AS LastOrderDate From Orders GROUP " & _     "BY Orders.CustomerID " & _     "HAVING (((Orders.CustomerID)='" &      txtCustID.Text & "'))"' We've already figured out the number of orders - so' this is the SQL statement that finds out the last order' dateSet rs = db.OpenRecordset(SQLString)' This is the second time we've seen this statement. Here,' it says the Recordset object to hold the records' from our new SQLString statementtxtLastDate.Text = rs.Fields("LastOrderDate")' Here, we're taking the information from the 'LastOrderDate'' field and placing it in the txtLastDate text boxtxtLastDate.Text = Format(txtLastDate.Text, "Long Date")' Now we're just formatting to make it look prettyrs.Close' Close the Recordsetdb.Close' Close the DatabaseEnd Sub

Wow, groovy or what! Try looking up the details for QUEDE, GALED, MAGAA or perhaps even ROMEY.

So your end users are merrily tapping away at the call centre, easily pulling up customer details with your supercool program. Suddenly, a terrifying scream echoes through the corridors. Move over Poirot, this aint no murder... its much more serious.

Enter stage - RUNTIME ERROR!

Try entering an incorrect customer ID into your program, such as KARLOS, and hitting the Find button. You should get this awful message:

Hit the End button and let's discuss this like the adults we're not.

Runtime errors occur when youve told Visual Basic to do something it really cant handle. In this case, its screaming: "No current record". But why?

In Visual Basic, each set of records has two special markers in it - known as the Beginning Of File (BOF) and the End Of File (EOF).

The BOF sits just before the first record and the EOF, just after the last record. In graphical terms, it looks a little like this:

OK, so Im no Picasso - but you get the idea. And at least Ive still got both my arms.

[Ed: Ears, Karl. Picasso cut off his EAR!]

If you remember, we set the Recordset object to hold the results of our SQL statement. Most of the time, this will contain details such as the number of orders. But when the customer ID cant be found in the orders table - meaning the customer is either non-existent or hasnt yet placed an order - an empty set of records is returned to the Recordset object.

In graphical terms, that empty set of records looks like this:

So when we say txtTotalNumber.Text = rs.Fields("NoOfOrders") - Visual Basic replies with "Whoah, hold on a minute. There's no current record for me to grab your 'NoOfOrders' field from!"

We can check to see whether there is a current record by examining the BOF and EOF properties of the Recordset. When youre sitting on an actual record, both the BOF and EOF properties are false. If we are at the beginning and end of a Recordset after we execute an SQL string (BOF=True and EOF=True), we know it didnt return any records.

So let's edit our Visual Basic code to reflect that. Add the bolded code behind your command button:

Private Sub cmdFind_Click()Dim db As Database' This is the object that will hold the connection' to our databaseDim rs As Recordset' This is the object that will hold a set of' records coming back from the databaseDim SQLString As String' This is just to temporarily hold the SQL stringSet db = OpenDatabase("c:\Microsoft Visual Studio\Nwind.mdb")' This activates the database object, telling it' to link to the Nwind.mdb database.  Note that' you may have to change this path depending on' where Visual Basic has been installed on your PC.SQLString = "SELECT Orders.CustomerID, " & _    "Count(Orders.OrderID) " & _    "AS NoOfOrders From Orders " & _    "GROUP BY Orders.CustomerID " & _    "HAVING (((Orders.CustomerID)='" &     txtCustID.Text & "'))"' This SQL statement was created in Access. It simply returns' the number of orders for a particular customer using the' 'Count' feature on the 'Total' line. If you'd like to use' Count, but are a little unsure about it - search Access help -' it's very simple!Set rs = db.OpenRecordset(SQLString)' This ties the recordset object with the database object.' You're telling it to set the recordset object to whatever' the "db.OpenRecordset" function returns. And that function' will return a set of records according to the SQL statement' you pass it.If rs.BOF = True And rs.EOF = True Then    ' Obviously the customer cannot be found in the    ' orders table, so let's tell the user - and close    ' the recordset/database connections    MsgBox ("Cannot find customer - " & _         "txtCustID.Text & " - " & _         "in the Orders table!")    rs.Close    db.Close    Exit SubEnd IftxtTotalNumber.Text = rs.Fields("NoOfOrders")' Simply throws the value in the 'NoOfOrders' field' from the Recordset, direct into the txtTotalNumber' text boxSQLString = "SELECT Orders.CustomerID, " & _    "Last(Orders.OrderDate) " & _    "AS LastOrderDate From Orders " & _    "GROUP BY Orders.CustomerID " & _    "HAVING (((Orders.CustomerID)='" &     txtCustID.Text & "'))"' We've already figured out the number of orders - so' this is the SQL statement that finds out the last order' dateSet rs = db.OpenRecordset(SQLString)' This is the second time we've seen this statement. Here,' it says the Recordset object to hold the records' from our new SQLString statementtxtLastDate.Text = rs.Fields("LastOrderDate")' Here, we're taking the information from the 'LastOrderDate'' field and placing it in the txtLastDate text boxtxtLastDate.Text = Format(txtLastDate.Text, "Long Date")' Now we're just formatting to make it look prettyrs.Close' Close the Recordsetdb.Close' Close the DatabaseEnd Sub

When it comes down to databases, you can do virtually everything in code. Here are a few of the top properties and methods of the Recordset object for you to play around with:

  • MoveNext - Moves to the next record in the Recordset
  • MovePrevious - Moves to the previous record
  • MoveFirst - Moves to the first record
  • MoveLast - Moves to the last record
  • Edit - Enters edit mode, for changing fields
  • Update - Saves any edits
  • AddNew - Adds a new, empty record
  • Delete - Deletes the current record
  • RecordCount - (Number) Returns the number of records currently accessed (move to the last record to get total number of records)
  • BOF - (Boolean) Hit when you step before the first record in a Recordset
  • EOF - (Boolean) Hit when you pass the last record in a Recordset
  • Indeed, using these simple properties and methods you could very simply replicate all the functionality of the Data control.

    Go on, have a go! Try to build a simple Customer browsing application, similar to the one we created in the third tutorial.

    This week, we've covered loads of stuff. We built a mini-application completely in code, added some BOF/EOF checking and got wise to the numerous helpful properties and methods of the Recordset object.

    Next week, we'll be covering... erm, other stuff. I'll be droning on about database theory and the correct way to do, erm, more stuff in databases. Then Ill talk about joins and stuff, followed by even more database stuff. Ahh, at least thats clear then.

    But until next week, this is your host, Karl Moore signing off for tonight. Goodnight!

    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.

    Sitemap | Contact Us

    Thanks for your registration, follow us on our social networks to keep up-to-date