Search and update SQL database if value is changed
Ok, maybe im being a bonehead and cannot think this out. but what im trying to accomplish:
1. Search LDAP computers, enumerate them. (I have this done)
2. Search Sql Database for that PC name.
a. if PC name is found, then update data in rest of columns ie ID, Card, and port in the same row.(which i am reading the data from the win.ini file)" I have the code written to read the win.ini file. i only want to update if card, port, or ID are different from what is in the database. if ID returns zero then go to next computer.
b. if not found then insert into sql database. (have this done too.)
Mainly i need the middle part to search the database and if the data is different then update.
Here is the code i have:
<Code:
For Each Computer in Container
row = row + 1
name = replace(computer.name,"CN=","")
exceldoc.Worksheets(1).cells(1).rows(row).value = name
call OpenRecordSet(rsfilesnum, instr_ID)
Set rsfilenum.activeconnection = Nothing
objconn.close
rsfilesnum.MoveFirst
Do While Not rsfilesnum.EOF
If rsfilesnum("location") = name then
'objconn.execute("insert into instr_ID (location) values = name")
ICMPPing = CInt(objIPNetwork.Ping(name,,,300))
if icmpping = 0 Then
set filesys = CreateObject("Scripting.FileSystemObject")
Set ws = CreateObject("WScript.Network")
ws.MapNetworkDrive "X:", "\\" & name & "\admin$",false,jkapp,P3nnstate
'ws.mapnetworkdrive "z:", "\\" & name & "\c$", false,jkapp,P3nnState
if filesys.fileexists("X:\win.ini") then
set textstream = filesys.opentextfile("x:\win.ini", 1)
Instrument = split(textstream.readall, vbnewline)
for each line in instrument
if left(line,11) = "Instrument=" then
subArray2 = split(line, "=")
instvalue = subarray2(ubound(subarray2))
else
exceldoc.Worksheets(1).cells(2).rows(row).value = 0
End if
else
exceldoc.Worksheets(1).cells(2).rows(row).value = instvalue
end If
end If
if left(line,5) = "Card=" then
subArray1 = split(line, "=")
cardvalue = subarray1(ubound(subarray1))
if cardvalue = "0" then
exceldoc.Worksheets(1).cells(3).rows(row).value = 0
Else
exceldoc.Worksheets(1).cells(3).rows(row).value = cardvalue
end if
end if
if left(line,5) = "Port=" then
subArray = split(line, "=")
PortValue = subarray(ubound(subarray))
rsFilesNum("port") = request("Port")
If Not rsFilesNum("port") = portvalue then
exceldoc.Worksheets(1).cells(4).rows(row).value = PortValue
rsFilesNum.update
else
exceldoc.Worksheets(1).cells(4).rows(row).value = Portvalue
rsfilesnum.CancelUpdate
end if
end If
Next
call InsertWin_IntoDB(cardvalue,name,instvalue,portvalu e)
textstream.Close
End if
else
exceldoc.Worksheets(1).cells(2).rows(row).value = "Cannot connect to computer"
end If
Set instvalue = nothing
Set cardvalue = nothing
Set name = nothing
Set portvalue = nothing
ws.RemoveNetworkDrive "X:",true
'ws.RemoveNetworkDrive "Z:",true
next
exceldoc.save
exceldoc.close
wscript.echo("Instrument query ID is done")
wscript.quit
Function InsertPDA_IntoDB(strID,strLocation)
strSearchsql = "Insert into Instr_ID (ID,Location) VALUES ('" & strID & "','" & strLocation & "')"
objconn.execute(strSearchsql)
end function
Function InsertWin_IntoDB(Strcard,strlocation,strID,strport )
strsql = "Insert into Instr_ID (card,Location,ID,Port) VALUES ('" & cardvalue & "','" & name & "','" & instvalue & "','" & portvalue & "')"
objconn.execute(strsql)
end function
Function updateSql(strcard,strlocation,strid,strport)
strsql = "update Instr_ID set (card = ' "& cardvalue &" ', location = ' "& name &" ', ID = ' "& instvalue &" ', port = ' "& portvalue &" ')"
objconn.Execute(strsql)
End Function
Function SelectName(Name)
strsql = "select Location from Instr_ID where (location = '"& name &"')"
objconn.Execute(strsql)
End Function
function OpenRecordSet(rsfilesnum, instr_ID)
rsfilesnum.Open Instr_ID, objConn
End Function
End Code:>
Your help is greatly appreciated. Thanks!
|
