Vbscript Database Query Student PC Lab

VBScript Database Query Student PC Lab

Objective

In this lab, students will complete the following objectives:

• Create a connection to a Access database.

• Create various SQL Queries to extract information from a Database

• Format extracted data with Column headers

Scenario

The IT department has an Access database that is used to inventory the computers in the various rooms. Fields in the database include: Computer Type, Hostname, Room Number, CPU Type, Number of Bits, Speed, Number of Processors, Operating System, Memory and Hard Drive Size. We need to query the database to determine upgrades and replacements for existing computers.

Database Contents

Task 1: Doc Sharing Downloads and Lab Preparation

·  Go to Doc Sharing in eCollege and download the file VBS_Database.zip. This zip file contains two files: ComputerDatabase.vbs and Computers.accdb. ComputerDatabase.vbs is a VBScript program that makes a connection to the Microsoft Access database file Computers.accdb. Unzip these files and copy them to the C:\Scripts directory.

·  If you do not have Microsoft Access 2007 or Microsoft Access 2010 installed on your computer, you will need to download and install the Microsoft Access 2007 database driver: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734
Follow the default instructions to download and install the AccessDatabaseEngine.exe driver on your Windows computer.

·  Open the ComputerDatabase.vbs program in NotePad++. Press the <F6 function key and type wscript ComputerDatabase.vbs in the Execute box. Click the OK button to RUN the VBScript program. Verify the program properly displays Column headers and database records as shown on page 1.

Task 2: Understanding the ADODB.Connection and ADODB.Recordset objects

·  In NotePad++, look at the following code lines

Line 11 contains the SQL Query String named sqlStr. This is the line you will have to modify to properly query the Computer database.

Line 12 & 13 uses a string named dataSource to specify the Microsoft Driver and the name and location of the local database: Computers.accdb. This driver was installed when you ran the AccessDatabaseEngine.exe program.

Lines 15 Creates the “ADODB.Connection” object while line 16 opens the connection to the database.

Lines 17 Creates the “ADODB.Recordset” object while line 18 provides access to the records using the SQL Query String and the Connection object. Line 19 moves the objRecordSet pointer to the first record.

Task 3: Displaying the Record Headers and Database Records

·  In NotePad++, look at the following lines of the ComputerDatabase.vbs program.

sqlStr = "SELECT * FROM ComputeLines 21 - 25 display the Database fieldnames as column headers. Note the use to concatenate (add) string values together and _ which is the VBScript line continuation character.

Lines 26 – 38 are a Do Until loop that sequences through the database looking for records

that match the SQL Query String. The objRecordSet.EOF method checks to see if we have reached the last record in the database. This required because reading past the end of a database will cause an error. recordStr is a string variable initially set to “”. recordStr is used to create a multi-line string that contains the column headers and records that match the SQL query. The WScript.Echo recordStr statement in Line 42 displays the column headers and records to the console or desktop windows depending on whether cscript or wscript is used to run the program.

Lines 39 & 40 closes the database connections made by the ADODB.Connection and ADODB.Recordset objects.

The function pad(byVal strText, ByVal len) in Lines 44-46 are used to format the field values with added spaces so the tab positions will line up correctly.

Task 4: Write and Run Database Query Program 1

In this scenario, we need to query the Computer database to determine which computers need to be replaced. Our decision will be based on the CPU speed, Number of Processors and the size of the Hard Drive.

·  Open the ComputerDatabase.vbs program in NotePad++ and Save As the program with the name ComputerReplace.vbs.

·  Modify the SQL Query String (sqlStr) in line 11 to extract the following information from the database;
Fields Displayed from Computers Table:

Computer

Room_Num

Speed

Num_CPUs

OS_Type

HDD_Size
Replacement Criteria

Any computer with a single CPU

Any computer with a CPU speed less than 2.1 GHz

Any Computer with a Hard Disk Drive size less than 300 GBytes

Sort Criteria

Sort the extracted records by the “Room_Num” field.

·  Modify lines 21 – 25 to display the correct field headers for the fields being displayed

·  Modify the Do Until loop body to include only the fields being displayed. Use the pad( ) function as needed to make the header and field values line up.

·  Press the <F6> function key and type wscript ComputerReplace.vbs in the Execute box. Click the OK button to RUN the VBScript program. Verify correct formatting and query results.

·  In the space provided in your Lab Report document, paste your modified VBScript program and the RUN.

·  Answer the questions about the Replace SQL Query in the Lab Report document.

Task 5: Write and Run Database Query Program 2

In this scenario, we need to upgrade our company computers based on the Operating System and the amount of memory. We want to ensure that all Fedora 10 machines are upgraded to Fedora 14 and all Windows XP machines are upgraded to Windows 7. If we find any computers with only 2 GB of memory, we will upgrade the memory to 4 GB.

·  Open the ComputerDatabase.vbs program in NotePad++ and Save As the program with the name ComputerUpgrade.vbs.

·  Modify the SQL Query String (sqlStr) in line 11 extract the following information from the database;
Fields Displayed from Computers Table:

Computer

HostName

Room_Num

OS_Type

Memory
Replacement Criteria

Any computer with the Fedora 10 Operating System (‘Fedora 10’)

Any computer with the Windows XP Operating System (‘Windows XP’)

Any Computer with 2 GB of Memory

Sort Criteria

Sort the extracted records by the “OS_Type” field.

·  Modify lines 21 – 25 to display the correct field headers for the fields being displayed

·  Modify the Do Until loop to include only the fields being displayed. Use the pad( ) function as needed to make the header and field values line up.

·  Press the <F6> function key and type wscript ComputerUpgrade.vbs in the Execute box. Click the OK button to RUN the VBScript program. Verify correct formatting and query results.

·  In the space provided in your Lab Report document, paste your modified VBScript program and the RUN.

·  Answer the questions about the Upgrade SQL Query in the Lab Report document.

4

COMP230_W7_Database_sLab.docx Revision Date: 1207