Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Other Programming > VBScript
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 3rd, 2004, 11:51 PM
Registered User
 
Join Date: Aug 2004
Location: Omaha, 12824 Deauville dr. #301, USA.
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
  #2 (permalink)  
Old August 4th, 2004, 06:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
Code:
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"
    objconn.Execute(strsql)
End Function
Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 11:42 PM.


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