Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Other Programming > VBScript
VBScript For questions and discussions related to VBScript.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VBScript section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 3rd, 2004, 11:51 PM
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:
    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
            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))
            exceldoc.Worksheets(1).cells(2).rows(row).value = 0
        End if
             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

             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
             exceldoc.Worksheets(1).cells(4).rows(row).value = Portvalue
        end if
        end If


        call InsertWin_IntoDB(cardvalue,name,instvalue,portvalu e)


        End if
            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
wscript.echo("Instrument query ID is done")
Function InsertPDA_IntoDB(strID,strLocation)
    strSearchsql = "Insert into Instr_ID (ID,Location) VALUES ('" & strID & "','" & strLocation & "')"
end function

Function InsertWin_IntoDB(Strcard,strlocation,strID,strport )
    strsql = "Insert into Instr_ID (card,Location,ID,Port) VALUES ('" & cardvalue & "','" & name & "','" & instvalue & "','" & portvalue & "')"
end function

Function updateSql(strcard,strlocation,strid,strport)
    strsql = "update Instr_ID set (card = ' "& cardvalue &" ', location = ' "& name &" ', ID = ' "& instvalue &" ', port = ' "& portvalue &" ')"
End Function

Function SelectName(Name)
    strsql = "select Location from Instr_ID where (location = '"& name &"')"
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
  #2 (permalink)  
Old August 4th, 2004, 06:32 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post

Not sure what is that you are facing problem with this as there is no mention about that. But I could see that you don't use WHERE clause in UPDATE statement, that would result in updating all the rows with the given data.
Function updateSql(strcard,strlocation,strid,strport)
    strsql = "update Instr_ID set (card = ' "& cardvalue &" ', location = ' "& name &" ', ID = ' "& instvalue &" ', port = ' "& portvalue &" ') WHERE PC_Name_Column = PC_Name_Value"
End Function
Hope that helps.

- Vijay G
Strive for Perfection
Reply With Quote

Similar Threads
Thread Thread Starter Forum Replies Last Post
Primary key changed - How to migrat database.? nmmure Crystal Reports 0 June 26th, 2006 03:47 PM
update values of changed cells into database using changusee2k PHP Databases 0 March 9th, 2006 01:05 PM
Database options changed on Audit Login ocliff SQL Server 2000 1 January 12th, 2006 03:07 PM
Allow SQL to update an Access database levinho Classic ASP Databases 7 November 11th, 2003 08:32 AM
sql datatype change be changed mateenmohd SQL Server 2000 3 July 18th, 2003 03:00 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.