View Single Post
  #1 (permalink)  
Old August 3rd, 2004, 11:51 PM
jakvike jakvike is offline
Registered User
 
Join Date: Aug 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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!

Reply With Quote