Hey nonny nonny and welcome to the second part in this wizzy Visual Basic Database tutorial.
Just in case you’ve forgotten, I’m your shockingly handsome host Karl Moore – and if you missed part one, you can read it here.
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!
Anyway, enough small talk. This week we’ll be taking a geek-peek at:
- The incredibly nerdy Structured Query Language
- Creating our own live VB querying program
- Producing SQL statements… the easy way!
So without further ado, my fellow geeks and geekesses, let’s proceed onwards and upwards…
My psychologist always tells me to imagine smooth relaxing sands and blue rippling seas, as I unveil the maniacal details of my crazed programming life. And that’s just what we’ll be doing in this section – except we don’t have a couch. Erm, nor an official Institute of Psychology certificate.
But let’s just imagine you wanted to talk to your database – perhaps you want to ask it the telephone number of one of the contacts in your table.
Top Tip: Just in case your memory is as useful as the Pope’s wedding tackle, don’t forget that a field is a ‘box’ inside a table – numerous fields make up one table. One or more tables make up a database.
In a perfect world, we’d be able to say "What’s the telephone number for Johnny Briggs?" – and the computer would reply, "It’s 517-000-238, Karl, you stunningly wonderful person".
Welcome to an imperfect world.
In the world of databases, we talk in a language called SQL, or Structured Query Language.
Top Tip: Never call SQL "Structured Query Language", unless you’re writing a report – or are called Karl Moore. Phrases like "I’m just writing a lil’ bit of SQL" sound much more impressive and are guaranteed to improve your social status.
To pick a random example, a sentence such as "Show me all the contact names" in English, translates into something like "select ContactName from Contacts" in SQL.
In order to demonstrate the SQL language, boot up Visual Basic and follow these steps to create our own mini-querying tool:
1. Create a Standard Exe
2. Click Project, Components – in the list that appears, check the item entitled ‘Microsoft FlexGrid Control 6.0’ (or similar) and click OK. The FlexGrid control allows us to easily display the answers to our database questions
3. Next, add the following controls onto your form so your design roughly matches the below image. Don’t worry about naming conventions or whatever at the moment.
– Text Box
– Command Button
– Data control
4. In the Properties window for the FlexGrid control, click on the ‘DataSource’ property. A list of Data controls will appear – it should just contain the ‘Data1’ control you added above. Select it.
5. Change the DatabaseName property of the Data control to point to that Nwind.mdb database we talked about last week. Mine is located at “C:Program FilesMicrosoft Visual StudioVB98Nwind.mdb”
6. Now insert the following code behind your command button:
On Error GoTo OhDear Data1.RecordSource = Text1.Text Data1.Refresh Exit Sub OhDear: MsgBox "Euston, we have a problem!"
7. Err, that’s it!
Brilliant. Now let me briefly explain what we just did.
First off, we added the FlexGrid component to our project. This is just another control – like the CommandButton or CheckBox controls – that allows us to add a bit of specific functionality to our project.
The FlexGrid control allows us to very simply display all the records currently held by the Data control. In other words, if we were to tell the Data control to look directly at the Customers table (by changing its RecordSource property to Customers), the FlexGrid would display field in the Customer table.
Because we’re true database divas, we’re going to get all technical now. We’re going to ask the database a question in SQL and tell it to display the results in the FlexGrid. That’s where the bit of code steps in.
What does the code do? It simply sets the RecordSource property of the Data control to whatever is sitting in your text box. So if the text box held the text ‘Customers’, it would fetch everything from the Customers table. But if the text box contained an SQL question, likewise, it will ask the question and display the results.
The second line of control then simply refreshes the Data control – which means all linked items, such as the FlexGrid – are kicked and told to update themselves.
Phew! I’m glad that’s out of the way. Hit F5 to run the application!
Type ‘Customers’ in the text box and hit your command button. You should notice that your grid has suddenly filled with dozens of weird-sounding businesses.
That’s brilliant… but let’s pretend we only wanted to show your user the contact names for each company. Ahh. Problem.
Actually, it isn’t. We can do this using one of those database questions in Structured Query Language!
Top Tip: Don’t call database questions ‘database questions’. Sure I might do it, but that’s because this is my tutorial and I have permission. You should probably call them SQL statements
OK, tap the following into your text box and click the command button:
Select ContactName from Customers
Don’t worry about capitalisation and the like – just tap it in and hit the button. You should’ve got a list of all the contact names – don’t worry if the FlexGrid is cutting off the end of names, that’s just a sizing issue.
In English, this statement is saying to the database: "Give me everything from the ‘ContactName’ field, which lies inside the Customers table… OK?"
Here’s another example:
Select ContactName, ContactTitle from Customers
This one is saying "Show me everything in the ‘ContactName’ and ‘ContactTitle’ fields, which are hiding in the Customers table… now!"
But just before you start telling all your friends how simple SQL is… errm, don’t. It can get much more complicated… yah booh, sucks to you, SQL!
You pick up the telephone and the caller says, "Grassy ass! My name is Maurizio Moroni and I have a question, pleasssse!"
Hmm, you think. You’d really like to get a job with some cool company – so if this dodgy sounding Maurizio is the owner of a customer company, then you’ll probably want to sweet talk him. Of course, if he’s just a sales representative or some such, you’ll want to tell him to stop bothering you.
And wahay, SQL can step in here to help. Let’s take a peek at another SQL statement:
Select ContactTitle from Customers where ContactName="Maurizio Moroni"
This one is saying "Show me the contents of the ‘ContactTitle’ field where the ‘ContactName’ is Maurizio Moroni"
And what is he? Just a sales representative, so you’d better hang up that telephone. Here’s a challenge – tell me what position Marie Bertrand holds – and for which company!
So, that "where FieldName=Criteria" bit is amazingly useful yet surprisingly simple. Except, I bet you were wondering about those "quotes" weren’t you? Why do we have to enclose Maurizio’s name in quotation marks?
When programming in Visual Basic, you enclose strings in "quotes". And it’s snap-snap-snippety-snap in SQL. Similarly, numeric values are not enclosed by "quotes"… not in Visual Basic nor in SQL, for example:
Select UnitPrice from [Order Details] where OrderID=10254
It’s all about data types – if the database recognises a particular field as a text field, it expects "quotes". If it’s numeric, it doesn’t. If it’s a date, it’s completely different. Don’t worry about this for now – we’ll figure an easier way of creating SQL statements in the next section.
Crikey, I’m babbling aren’t I? Am I side-tracking? Is Karlos off on another tangent? Err, possibly, yes.
Well, I’ll shutup now. In the next section, we’ll be creating more of these statements… but in a really cool way that takes virtually no effort. Sounds good to me…
Do you want the bad news or the good news?
Hmm, bad news first. I thought, being a personal friend of mine, you ought to know that I’m actually listening to a Barry Manilow CD as I type. I know, I know… but some of his songs really aren’t that bad. Oh, and I don’t mind Chris Rea neither.
Oh boy, I’m never going to get that date I wanted back in the Beginning Visual Basic tutorial, am I?
Anyway, enough of that – here’s a bit of good news. Access has a built-in translator that allows you to design those SQL statements using a really groovy user interface – and yes, that means you never really have to learn about SQL, you idle person you.
Start Microsoft Access and open the Nwind.mdb database we used in the last section. Click on the Queries tab, then select Design View and click OK. When prompted to add a table, select Customers, click Add, then Close.
Your screen should look something like this:
Despite all those strange asterisk symbols and criteria boxes lying all over the place, you’ll soon get the hang of using this ‘query’ builder.
Top Tip: If you have a sore throat, try drinking warm honey mixed with fresh lemon juice.
Double click on the ContactName field in the ‘Customers’ table. Then double click the ContactTitle field. They should both appear on your screen, as so:
You’ve just designed your first query. You’re asking the database "Show me everything in the ‘ContactName’ and ‘ContactTitle’ fields in the Customers table" – just as we did in the last section.
Click on View, Datasheet View. Your screen should look something like this:
Now click on View, SQL View. You should see this:
SELECT Customers.ContactName, Customers.ContactTitle FROM Customers;
"What??", I hear you cry. Yes, with just a couple of clicks you have automatically generated the SQL statements we poured over in the last section.
Our original statement looked like this:
Select ContactName, ContactTitle from Customers
But both statements – the manual SQL and Access-generated SQL – do exactly the same thing. So when generating more complex queries, you might as well head straight for Access… and do it the easy way!
This week we covered a lot of ground; we’ve demystified the horrid world of Structured Query Language, and figured how to create simple SQL statements – the easy way. Trust me, that’s a lot of stuff in the database world.
What will we be learning next week? Oh, don’t ask me. It’s Saturday morning, I’m on my third cup of coffee and desperately trying to get over the night before… I really don’t plan that far ahead!
Though if you were to really push me, I’d expect we’ll probably delve into SQL just a leeeetle more, then sneak off into the crazy world of queries.
But until next week, whatever it may have in store, this is Karl Moore signing off – 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.