Looping

[Appok]

How can i loop this instruction

sSoftware = "Insert into Software_List ([Name], [Version]) values ('" & objSoftware.Name & "','" & objSoftware.Version & "')"
mConnection.Execute (sSoftware)

Because at the moment it executes only once and it lists only 1 program in my table, i have been told that i would need some sort of looping instruction for it to do all the programs.

i have been looking over some coding for DO until instruction but i just know how to start it with this line

 

[SkipVought]

hi,

You must have some sort of recordset object from your table access, so what is you code so far regarding the table access?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Appok]

'
'Found most parts of this script available at

http://www.tek-tips.com/viewthread.cfm?qid=798632

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)

'SQL Database Connection
Dim mConnection
Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open = "Provider=SQLOLEDB.1;Persist Security Info=True;Trusted_Connection=True;Initial Catalog=Test;Data Source=WIC-314\WALTERS; UID=adam; PWD=qazw"

'"Provider=SQLOLEDB.1;Server=WIC-314\WALTERS;Database=Test;Uid=Adam; Pwd=qazw;"
'"Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Inventory;Data Source=ADAM-SERVER\SQLEXPRESS; UID=Adam; PWD=qazw"

Set oShell = CreateObject("wscript.Shell")
Set env = oShell.environment("Process")
strComputer = env.Item("Computername")
Const HKEY_LOCAL_MACHINE = &H80000002
Const UnInstPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
".\root\default:StdRegProv")

'Get OS Information
report = strComputer & " Computer Inventory" & vbCrLf & "******************************************" & vbCrLf & vbCrLf
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
For Each objOS in colItems
'report = report & "Caption: " & objItem.Caption & vbCrLf
exit For
Next

'Get Domain Information
Set objWMISvc = GetObject( "winmgmts:\\.\root\cimv2" )
Set colItems = objWMISvc.ExecQuery( "Select * from Win32_ComputerSystem", , 48 )
For Each objItem in colItems
strComputerDomain = objItem.Domain
If objItem.PartOfDomain Then
'report = report & "Computer Domain: " & strComputerDomain &
Else
End If
Exit for
Next

'Get Hardware Information
Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")
for Each objComputer in colSettings
'report = report & "System Name: " & objComputer.Name &
exit for
next

'Get BIOS Information
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colBIOS = objWMIService.ExecQuery _
("Select * from Win32_BIOS",,48)
For each objBIOS in colBIOS
'report = report & "BIOS Version: " & objBIOS.SMBIOSBIOSVersion &
exit for
Next

'Get Video Card Information
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery ("Select * from Win32_VideoController")
For Each objVC in colItems
' report = report & "Video Card: " & objItem.Description &
exit for
Next

'Get Service Tag Information
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSMBIOS = objWMIService.ExecQuery ("Select * from Win32_SystemEnclosure")
For Each objSMBIOS in colSMBIOS
'report = report & "Service Tag: " & objSMBIOS.SerialNumber &
exit for
Next

'Processor Information
Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_Processor")
report = report
For Each objProcessor in colSettings
'report = report & "Processor Information: " & objProcessor.Name &
exit for

'Get Total RAM Information
Next
Set colSettings = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")
For Each objComputer in colSettings
'report = report & objComputer.TotalPhysicalMemory /1024\1024+1 & "MB Total memory" &
exit for
Next

'RAM Speed Information
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_PhysicalMemory",,48)
For Each objItem in colItems
'report = report & "Speed: " & objItem.Speed &
exit for
Next

'Get Hard Drive information
Set objWMIService = GetObject("winmgmts:")
Set objLogicalDisk = objWMIService.Get("Win32_LogicalDisk.DeviceID='c:'")
'report = report & objLogicalDisk.FreeSpace /1024\1024+1 & "MB Free Disk Space" &

'Get Installed Software
'Set objWMIService = GetObject("winmgmts:\\ " & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
'Set colSoftware = objWMIService.ExecQuery ("Select * from Win32_Product")
'For Each objSoftware in colSoftware
'software = software & "Version: " & objSoftware.Version & vbCrLf
'software = software & "RegOwner: " & objSoftware.RegOwner & vbCrLf
'software = software & "Name: " & objSoftware.Name & vbCrLf
'Exit for
'Next

'Get Network IP/MAC Information
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled=True")
For Each objIP in colItems
'report = report & "Mac Address: " & objItem.MACAddress &
' If Not IsNull(objAdapter.IPAddress) Then
' For i = 0 To UBound(objAdapter.IPAddress)
' Next
'End If
exit for
Next

'My Documents Location
Const MY_DOCUMENTS = &H5&
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_DOCUMENTS)
Set objFolderItem = objFolder.Self

sInsert = "Insert into Inventory ([User], [System Name], [System Manufacturer], [System Model],[Operating System], [OS Serial Number], [Domain], [Service Tag], [BIOS Version], [BIOS Manufacturer], [Video Card], [Video Card Driver Version], [HDD Free Space (GB)], [Processor], [IP Address], [MAC], [Total RAM], [RAM SPEED], [My Documents Location])" &_
" VALUES ('"& objComputer.UserName & "','" & objComputer.Name & "','" & objComputer.Manufacturer & "','" & objComputer.Model & "','" & objOS.Caption & "','" & objOS.SerialNumber & "','" & strComputerDomain & "','" & objSMBIOS.SerialNumber & "','" & objBIOS.SMBIOSBIOSVersion & "','" & objBIOS.Manufacturer & "','" & objVC.Description & "','" & objVC.DriverVersion & "','" & objLogicalDisk.FreeSpace /1073741824 & "','" & objProcessor.Name & "','" & objIP.IPAddress(i) & "','" & objIP.MACAddress & "','" & objComputer.TotalPhysicalMemory /1024\1024+1 & "','" & objItem.Speed & "','" & objFolderItem.Path & "')"
mConnection.Execute (sInsert)

'sSoftware = "Insert into Software_List ([Name], [Version]) values ('" & objSoftware.Name & "','" & objSoftware.Version & "')"
'mConnection.Execute (sSoftware)

 

[guitarzan]

You have the loop already, it's just commented out. It would go something like this:

'Get Installed Software
Set objWMIService = GetObject("winmgmts:\\ " & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSoftware = objWMIService.ExecQuery ("Select * from Win32_Product")
For Each objSoftware in colSoftware

   [COLOR=green]'Insert statement goes here...[/color]

Next

However, your INSERT into the Software_List table needs to also include the key field from the Inventory table, otherwise you will not be able to join the data later.

 

[Appok]

what do you mean by key field?

 

[guitarzan]

The key field is the field (or set of fields) in a table that make each row unique. There should be a "primary key" field in the Inventory table, making every row unique (maybe the system name is unique, maybe you have an auto-increment id field that is unique, etc.). This field should also be included in the Software_List table (called a "foreign key")... this is how you can determine which computer each "Software_List" record originated from.

 

[Appok]

Yes my inventory table does have a primary key auto increment field. Ok i'll make one also now for software list

 

[Appok]

create table Software
(
[Name] varchar (255) not null,
[Version] varchar (255) not null,
[F_ID] int not null,
)

Created a table then i altered the table
ALTER TABLE Software
ADD FOREIGN KEY (F_ID) REFERENCES Inventory(P_ID);

nothing gets inputted when i run the script, but when i allow nulls for the F_ID field i generate a whole list of installed programs like i wanted but the F_ID column all say NULL.

weird

 

[guitarzan]

When the Inventory record is created, retrieve the P_ID value, store it in a variable, and include that value in each INSERT for the Software table.

sSoftware = "Insert into Software ([F_ID], [Name], [Version]) values " & _
   (" & [b][COLOR=blue]intPID[/color][/b] & ", '" & objSoftware.Name & "','" & objSoftware.Version & "')"
mConnection.Execute (sSoftware)

 

[DTracy]

You are jumping out of your for loops after only one pass. Remove those exit for instructions or at least put a condition on them.

Dave.