Nooblet scripting coder here - database search question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello! And yes I know, I'm behind the game here, should have learned this
stuff years ago! But I'm working with a couple of scripts that I'd like to
get working if at all possible. The idea is logical - I'm using basic
scripting to connect to a SQL database (got that to work) and to get a few
pieces of pertinent information on the local computer that the script runs on
(computer name, serial number, OS) and populate the database with that. That
part works great! Its just what we need, a simple and customizable way to
inventory machines. Here's where my problem comes in. I need to be able to
use the script to select the local computer's name (whatever machine the
script is running on at the time), search the database, update the record if
found, and add a new one if not. My problem is, in all the documentation I
track down, the search is always based on a constant, not a variable string!
So help a nooblet out - how the heck do I do a search based on a variable??
 
I'm trying to do something very similar to you at the moment in our
login script (also in vbscript). Paste in the line of the script that's
giving you grief so we can see it, or better yet all the surrounding
code if you don't mind doing that to make it easier to suss out.

I'm not really sure what you mean by a constant vs. a variable string,
the select statement just gets concatenated into a single string
variable in the script I use, and then I execute on that.
 
OK here's my current code that adds a line to the table of the SQL database
The first part adds a DSN to the local machine the script runs on (thanks
Scripting guy for that one too)

Const HKEY_LOCAL_MACHINE = &H80000002

strComputer = "."

Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
strValueName = "DSNNAME"
strValue = "SQL Server"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Inventory"

objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Inventory"

strValueName = "Database"
strValue = "DATABASENAME"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

strValueName = "Driver"
strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

strValueName = "Server"
strValue = "SERVERNAME"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

strValueName = "Trusted_Connection"
strValue = "Yes"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue




Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=DSNNAME;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
adOpenStatic, adLockOptimistic
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer& "\root\cimv2")
Set colSettings = objWMIService.ExecQuery _
("SELECT * FROM Win32_ComputerSystem")
For Each objComputer in colsettings
objRecordset.AddNew
objRecordset("CompName") = objComputer.Name
objRecordset.Update
Next

Set colSettings = objWMIService.ExecQuery _
("SELECT * FROM Win32_OperatingSystem")
Set colSettings = objWMIService.ExecQuery _
("SELECT * FROM Win32_OperatingSystem")
For Each objOperatingSystem in colSettings
objRecordset("OS") = objOperatingSystem.Caption
objRecordset.Update
Next

Set colsettings = objWMIService.ExecQuery _
("SELECT * FROM Win32_BIOS")
For Each ObjBIOS in colsettings
objRecordset("CompType") = objBios.Manufacturer
objRecordset("Serial") = objBios.SerialNumber
objRecordset.Update

Next

objRecordset.Close
objConnection.Close





OK what I want is this - for the script to run on a computer, and query the
database to see if its already in there (based on computer name which is of
course unique on the network). There's probably an easy way but like I said
I'm such a nooblet :-) Need to hook to it, search the CompName section, add
if not there, update info if it is....does this make sense?
 
Hi Melanie
Maybe it is a case of the blind leading the blind.
Your Variable strComputer, Why do you declare it twice, once should be enough.
How much did you read up in the Scripting center. Maybe you should ask the
scripting Guys to have a look at it for you. I am also a novice at scripting.
Ta
 
Oh...oops - ok as I said I'm a nooblet - that probably came from my earlier
script where I was just trying to get the info to echo back out to me *blush*
 
Back
Top