Hey, Scripting Guy! The Return of the Database Script

The Microsoft Scripting Guys

Download the code for this article: HeyScriptingGuy2008_04.exe (151KB)

You know, if the Scripting Guys have a weakness (come on, we said if), it's this: we worry too much about the possible consequences of the actions we take, or don't take. Rather than act in our own best interests, we often hold back and, instead, do what we think will be in the best interests of everyone else. And we do that regardless of what those actions might mean for us.

Take articles about database scripting, for example. To be perfectly honest, articles about database scripting are difficult for the Scripting Guys to write. That's not because database scripting is hard; it's actually pretty easy. Instead, the problem is that the Scripting Guys don't have many chances to work with databases; because of that, any time we write about databases we have to stop and think about what we're doing. As you probably figured out a long time ago, thinking is definitely not a requirement for becoming a Scripting Guy.

As a matter of fact, if any of us had really thought about it in the first place, there probably wouldn't even be any Scripting Guys.

So if articles about database scripting are so hard for the Scripting Guys to write, why do we continue to write them? The answer to that is simple: we're concerned about what might happen to the rest of the world if we stop writing these articles. When Sir Arthur Conan Doyle decided to kill off Sherlock Holmes (by having him fall off a cliff in the story "The Final Problem"), he thought he was simply getting rid of a character he no longer wanted to write about. Instead, a huge outcry arose around the world. According to some reports, people in London actually wore mourning cloths on their hats and sleeves when they heard the news. "The Final Problem" was published in The Strand Magazine in 1893; in response, 20,000 people canceled their subscriptions.

Ouch.

The Scripting Guys are concerned that killing off our articles on database scripting would have a similar effect worldwide. We don't want to be the cause of so much heartache and despair, and we certainly don't want 20,000 of you to cancel your subscription to TechNet Magazine. Therefore, we'd like to announce that the Scripting Guys will continue to write articles about database scripting. When? Well, how about right now?

Adding Records to a Database

What we thought we'd do this month is show you a few nifty little tricks for working with databases. And yes, these are in addition to the tricks we showed you in our September 2007 column (technetmagazine.com/issues/2007/09/HeyScriptingGuy); even the Scripting Guys wouldn't stoop so low as to run the exact same article twice.

Well, not unless we thought we could get away with it.

Let's start out by showing you a simple way to add a record to a database. Assume we have a database named C:\Scripts\Inventory.mdb, and that this database includes a table named Computers. The Computers table includes the following fields:

• ComputerName
• SerialNumber
• UserName
• Department

Now, how do we add a new computer to that database? Take a look at the code in Figure 1.

Figure 1 Adding a record to the Computers table

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "INSERT INTO Computers (ComputerName, SerialNumber, UserName, Department)" &  _
    "VALUES ('atl-ws-07', '11234', 'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, adLockOptimistic

We're not going to talk about the first lines of this script in any detail; if you need that sort of background information you might want to take a look at some of the resources we have in the Script Center at microsoft.com/technet/scriptcenter. Suffice it to say that we use the constants adOpenStatic and adLockOptimistic to control cursor type and record-locking for our recordset. (Yes, that sounds impressive, but it's really pretty simple stuff.) After we're done creating instances of the ADODB.Connection and ADODB.Recordset objects, we then use this command to open our database:

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"

By the way, that command opens a Microsoft® Access® 2003 database. To open an Access 2007 database, use this command instead:

objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = C:\Scripts\Inventory.accdb"

And, although we're beginning to stray a bit from the main topic, you might use a command similar to this to open a SQL Server® database:

objConnection.Open _
"Provider=SQLOLEDB;" & _
"Data Source=atl-sql-01;" & _
"Trusted_Connection=Yes;" & _ "InitialCatalog=Inventory;" & _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"

Up to this point, everything we've shown you is boilerplate code; as you'll soon see, pretty much all your database scripts will start off with these same lines of code. The part we want to emphasize is actually this line:

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName, Department) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

Here we're using an INSERT INTO query to, well, insert a new record into the Computers table. Notice that, following the table name, we include the names of all the fields for which we have values, separating those field names with commas and enclosing all the names in parentheses.

You might be asking: what do the Scripting Guys mean when they say "all the fields for which we have values"? Good question. Well, let's suppose this new computer has yet to be assigned a Department. Assuming the database allows us to put a Null value in the Department field, we can simply leave Department off the list and not assign a value to it. In other words, we could write a query like this:

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

Speaking of assigning values, look what comes after the field names: the VALUES keyword followed by the values we want to assign to each field (which are also enclosed in parentheses). Keep in mind that when we list the field names, they can be in any order; for example, we put ComputerName first even though it might not be the first field in the database. That's fine for field names, but not so fine for values. Values must be listed in the exact same order as the fields. If our first listed field is ComputerName, then our first value must be the name of the computer. If it isn't, we're going to run into problems. (For example, we're going to end up assigning the user name or serial number to the ComputerName field.)

As you can see, assigning values isn't very complicated; just make sure that you format the values according to data type: dates and string values should be enclosed in single quote marks; numeric and Boolean values should not be enclosed in single quotes.

Oh, and if you have a value that actually has it's very own single quote mark (like the name O'Brien), then you need to "escape" that single quote by doubling it:

'O''Brien'

Crazy, but true.

Deleting Records from a Database

That was actually pretty cool: we can add a record to a database just by executing a single SQL query. Could there be anything better than that? Not that we know of.

Well, except maybe being able to delete multiple records from a database using a single query.

Suppose your company decides to eliminate its Human Resources department. (No, no jokes; we're usually in enough trouble with the Microsoft HR department as it is.) How can you delete all the Human Resources computers from your Inventory database? Figure 2 shows one way to do that.

Figure 2 Deleting multiple records

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "DELETE * FROM Computers Where Department='Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

As you can see, this particular script starts out exactly the same way as our script for adding a new record: by defining some constants, creating a couple of objects, then connecting to the Inventory.mdb database. After that we simply use a DELETE query to delete all the records (*) from the Computers table, or at least all the records where the Department field is equal to Human Resources:

objRecordSet.Open _
  "DELETE * FROM Computers " & _
   "Where Department='Human Resources'", _
    objConnection, adOpenStatic, _
    adLockOptimistic

It's as easy as falling off a cliff.

Um, no offense, Sherlock Holmes.

Updating Records in a Database

No doubt everyone agrees how much fun it would be to delete all traces of your Human Resources department. (Note to Microsoft HR department: we mean fun for other people and their HR department. Not us. We wouldn't enjoy it at all.) Nevertheless, deleting all the HR computers from the Inventory database might not be the best thing you could do. Why not? Well, unless your HR employees absconded with all their hardware (which sounds like something they would—well, never mind; we said no jokes) those computers still belong to your company; that means those computers should still be listed in the database. With that in mind, let's do this. Instead of deleting those records from the database, let's simply update each record; we'll just change the department name for each of these computers from Human Resources to None. In other words, we'll run the script shown in Figure 3.

Figure 3 Updating records

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "UPDATE Computers Set Department = 'None' " & _
    "Where Department = 'Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

Again, the only thing different about this script is the SQL query. In this case we're using an UPDATE query to update records in the Computers table. In addition to the UPDATE keyword, we need to add two parameters that tell the query how to update the records and which records to update:

• Set Department = 'None'. This simply specifies the new value for the Department field. You can actually set more than one field value at a time by using syntax like this: Set Department = 'None', UserName = 'None'. As you can see, that will set both the Department and UserName fields to None.
• Where Department = 'Human Resources'. This is a standard Where clause that indicates which records should be updated; in this case, that's all the records that have a Department field equal to Human Resources. We put this in for one simple reason: we don't want to modify all the records in the database, only the records (computers) that formerly belonged to the Human Resources department.

Here's a cool thing you can do with Update queries. Let's say your company has decided to give everyone a 10 percent cost-of-living raise. (The Scripting Guys are obviously like Sir Arthur Conan Doyle in at least one respect: in our writings we both deal with fictitious worlds.) Here's a script that opens a table named Employees and modifies the Salary field for each employee. And what does it set the Salary field to? The query sets this field to the employee's current salary times 1.1; in other words, it gives each employee a 10 percent raise. (If only it were that easy!) Here's the query:

objRecordSet.Open _
  "Update Employees " & _
  "Set Salary = (Salary * 1.1)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

The cool thing? As you can see, you can do calculations in your Update queries. Have you decided to extend the contract for all your temporary employees another 60 days? In that case, a query similar to the following might come in handy:

objRecordSet.Open _
  "Update TempEmployees " & _
  "Set ContractExpirationDate = " & _
  "(ContractExpirationDate + 60)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Cool Ways to Retrieve Data

Speaking of queries that might come in handy, let's wrap things up by looking at some cool ways to retrieve data from a database. For example, here's a simple yet very useful script. Suppose our Inventory database includes a field named Price, a field that—logically enough—represents the price of the computer. Want to know the five most expensive computers in your organization? The sample script in Figure 4 will tell you that.

Figure 4 Sorting the recordset

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Test.mdb" 

objRecordSet.Open "SELECT Top 5 * FROM Computers Order By Price", _
     objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("ComputerName")
    objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close

As you can see, in this SQL query we did two things: we sorted the recordset by Price (Order By Price), and we asked for only the top five (that is, the five most expensive) computers; that's what the SELECT Top 5 does. If we wanted a list of the 10 most expensive computers we'd use this query:

objRecordSet.Open _
  "SELECT Top 10 * FROM Computers " & _
  "Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Note. We also added some code that loops through the recordset and displays the name of each computer. We didn't need this code in our previous scripts because those scripts didn't return and display data.

Alternatively, we can get the top 10 percent by using this query:

objRecordSet.Open _
  "SELECT Top 10 PERCENT * " & _
  "FROM Computers Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Now, what if we wanted to get the cheapest computers? No problem; we simply use the same approach, except this time we sort the recordset in descending order (that is, from lowest price to highest price). In other words, we use this query, with DESC indicating that the recordset should be sorted in descending order:

objRecordSet.Open _
  "SELECT Top 5 * FROM Computers " & _
  "Order By Price DESC", _
    objConnection, adOpenStatic, _
    adLockOptimistic

See? We told you these were useful.

Here are two more you might find interesting. Let's suppose your database has a field named Budgeted that tracks the amount of money originally budgeted for the computer. Want to compare the actual cost to the budgeted amount? This query returns a list of computers that cost more than the budgeted amount:

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "Where (Budgeted < Price)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Meanwhile, this query calculates the average price of all the computers (SELECT AVG(Price) FROM Computers), then returns a list of all the computers that cost less than the average price:

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "WHERE Price < " & _
  "(SELECT AVG(Price) FROM Computers)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

And yes, your right, we did use a SELECT query in our Where clause. How does that work? Well, that's another story for yet another day. But, as you can see, it opens the door to retrieving all sorts of cool information with your SQL queries.

The Moral of the Story

As we noted at the beginning of this month's column, we weren't sure we even wanted to write another article on database scripting. We're glad we did, however, partly because we think you'll find some of these queries useful, but also because of this: if we didn't write this column now we'd end up having to write it sooner or later. After all, even Sir Arthur Conan Doyle was forced to bring Sherlock Holmes back from the dead. Sir Arthur took the first tentative steps in that direction by penning a new Sherlock Holmes story that allegedly took place before the detective's death. After that, though, he simply gave in to the pressure from his readers, concocting a somewhat dubious tale explaining that Sherlock Holmes had faked his own death. Turns out that everyone, and therefore everything, was perfectly fine.

Interestingly enough, when that latest Sherlock Holmes story appeared, 30,000 new subscribers signed up for The Strand Magazine. Sir Arthur's grateful publishers in both Britain and the U.S. responded by making him the highest-paid writer in the world.

Hmmm, Sir Arthur Conan Doyle brought back a much-beloved character and became the highest-paid writer in the world; now the Scripting Guys have brought back a much-beloved topic—and without having to fake any deaths. Wonder if the good folks at TechNet Magazine have noticed?

Dr. Scripto's Scripting Perplexer

The monthly challenge that tests not only your puzzle-solving skills, but also your scripting skills.

April 2008: Pick a Letter

In this month's puzzle, you need to insert the letters A through O in the blue spaces to reveal the name of a VBScript function. Each letter (A through O) should be used only once, and the letters do not appear in alphabetical order. The inserted letter could be the beginning of the function name, the end of the function name, or anywhere in the middle of a function name. For example, in the following line you would insert the letter D to complete the function name IsDate:

  (Click the image for a larger view)

Now you try it; insert the letters A through O to reveal VBScript function names in this grid:

(Click the image for a larger view)

ANSWER:

Dr. Scripto's Scripting Perplexer

Answer: Pick a Letter, April 2008

  (Click the image for a larger view)

The Microsoft Scripting Guys work for—well, are employed by—Microsoft. When not playing/coaching/watching baseball (and various other activities) they run the TechNet Script Center. Check it out at www.scriptingguys.com.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.