COMP 230 - iLab Report - Week - 7;VBScript Database Query Lab Report

VBScript Database Query Lab Report

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.

In the space provided in your lab-report document, paste your modified VBScript program and the

RUN.

In the table cell below, paste your ComputerReplace.vbs Program.

'==========================================================================

' NAME: ComputerReplace.vbs

' ' AUTHOR:

' DATE :

'

' COMMENT: Use 32 bit ODBC Microsoft Access Driver

'

'==========================================================================

recordsStr = ""

sqlStr = "SELECT Computer,Room_Num,Speed,Num_CPUs,OS_Type,HDD_Size FROM Computers where Num_CPUs = 1 OR Speed < 2.1 OR HDD_Size <300 order by Room_Num" dataSource = "provider=Microsoft.ACE.OLEDB.12.0;" _ & "data source=Computers.accdb"

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open dataSource

Set objRecordSet = CreateObject("ADODB.Recordset")

objRecordSet.Open sqlStr , objConnection

objRecordSet.MoveFirst

' Display Headers

recordsStr = "Computer      Room_Num    " & _ "Speed    Num_CPUs    OS_Type      " & _

" HDD_Size" & vbCrLf & _ "============================================================" & _ "================================" & vbCrLf

Do Until objRecordSet.EOF

recordsStr = recordsStr & objRecordSet.Fields.Item("Computer") & _

vbTab & pad(objRecordSet.Fields.Item("Room_Num"),8) & _

vbTab & objRecordSet.Fields.Item("Speed") & _

vbTab & objRecordSet.Fields.Item("Num_CPUs") & _        vbTab

& pad(objRecordSet.Fields.Item("OS_Type"),12) & _

vbTab & objRecordSet.Fields.Item("HDD_Size") & vbCrLf

objRecordSet.MoveNext

Loop objRecordSet.Close

objConnection.Close

WScript.Echo recordsStr

function pad(ByVal strText, ByVal len) pad = Left(strText & Space(len), len) end Function