Student Name ____________________________ GB# _____ Date



VBScript Database Query LabObjective In this lab, students will complete the following objectives: ? create a connection to an Access database; ? create various SQL queries to extract information from a database; and ? format extracted data with column headers. Scenario The I.T. department has an Access database that is used to inventory the computers in the various rooms. The 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 Contents43180077470Task 1: Doc Sharing Downloads and Lab PreparationGo to Doc Sharing in eCollege and download the file VBS_Database.zip. This zip file contains three files: VBS_ComputerDatabase.vbs, Computers.accdb, and AccessDatabaseEngine.exe. VBS_ComputerDatabase.vbs is a VBScript program that makes a connection to the Microsoft Access database file Computers.accdb. AccessDatabaseEngine.exe is the Microsoft database driver that supports the newer (.accdb) format.Unzip the three files into your C:\VBScripts folder. In Windows Explorer, double click on the AccessDatabaseEngine.exe program, and follow the instructions to install the Microsoft Access database driver.Open the VBS_ComputerDatabase.vbs program in NotePad++. Press the <F5> function key, and type C:\VBScripts\ComputerDatabase.vbs in the Program to Run box. Click the Run button to RUN the VBScript program. Verify that the program properly displays column headers and database records, as shown on page 1.Task 2: Understanding the ADODB.Connection and ADODB.Recordset Objects462915283845In NotePad++, look at the following code lines.Line 11 contains the SQL Query String sqlStr. This is the line you will have to modify to properly query the Computer database. Line 12 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, whereas line 16 opens the connection to the database.Lines 17 creates the ADODB.Recordset object, whereas 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 the Database Records In NotePad++, look at the following lines of the VBS_ComputerDatabase.vbs program.431800126365sqlStr = "SELECT * FROM ComputeLines 21-25 display the database fieldnames as column headers. Note the use of & to concatenate (add) string values together and the use of _, which is the VBScript line-continuation character.Lines 26-38 show a Do-Until loop that sequences through the database looking for recordsthat match the SQL Query String. The objRecordSet.EOF method checks to see if we have reached the last record in the database. This is required because reading past the end of a database causes an error. recordStr is a string variable initially set to “”. recordStr is used to create a multiline 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 and 40 close the database connections made by the ADODB.Connection and ADODB.Recordset objects. The function pad(byVal strText, ByVal len) in Lines 44-46 is used to format the field values with added spaces so that 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, the number of processors, and the size of the hard drive.Open the ComputerDatabase.vbs program in NotePad++ and click Save As to save the program with the name ComputerReplace.vbs. Modify the SQL Query String (sqlStr) in Line 11, extracting the following information from the database.Fields Displayed from the Computers Table ComputerRoom_NumSpeedNum_CPUsOS_TypeHDD_SizeReplacement Criteriaany computer with a single CPUany computer with a CPU speed less than 2.1 GHzany computer with a hard-disk drive size less than 300 GBytesSort CriteriaSort 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 to include only the fields being displayed. Use the pad( ) function as needed to make the header and field values line up.Press the <F5> function key and type C:\VBScripts\ComputerReplace.vbs in the Program to Run box. RUN the program and verify the 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 2In 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 that 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 click Save As to save the program with the name ComputerUpgrade.vbs. Modify the SQL Query String (sqlStr) in Line 11, extracting the following information from the database.Fields Displayed from the Computers Table ComputerHostNameRoom_NumOS_TypeMemoryReplacement Criteriaany computer with the Fedora 10 operating systemany computer with the Windows XP operating systemany computer with 2 GB of memorySort CriteriaSort 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 <F5> function key and type C:\VBScripts\ComputerUpgrade.vbs in the Program to Run box. RUN your program and verify the 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. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download