yo_bee
asked on
ADODB.RecordSet For updating a record in MSMSQL DB Error Object Does Not Support Updating
I have two VBS scripts that are used to write to a MSSQL DB.
Logon Script: (this one works perfect)
Logoff Script (not working)
Used to update a record based on the Select statement.
When Recordset.open SQL, Conn,3,3 I have the result output to a msgbox and I am gets what I need it do, but when I added Recordset("Field") = Now or even a String like "TEST" It returns an error
"Current RecordSet does not support Updating. This may be a limitation of the provider, or the selected locktype.
Logon Script: (this one works perfect)
on error resume next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objSWbemDateTime = CreateObject("WbemScripting.SWbemDateTime")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
Dim StrComputerName, StrUser, strManufacturer, StrModel, StrSN
'************************************************************************************
'Data Collection *
'************************************************************************************
set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\." _
&"\root\cimv2")
Set colSystems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")
For Each objSystem In colSystems
strComputerName = objSystem.name
strModel = objsystem.Model
strManufacturer = objsystem.Manufacturer
If Not (ISNULL(objsystem.UserName)) then
strUser = Split(objsystem.UserName,"\")
strUser(1) = UCase(Left(strUser(1),1))_
& Trim(Mid(strUser(1),2,20))
else
Struser = split("\boscam","\")
strUser(1) = UCase(Left(strUser(1),1))_
& Trim(Mid(strUser(1),2,20))
End If
next
Set colSMBIOS = objWMIService.ExecQuery _
("Select * from Win32_bios")
For Each objSMBIOS in colSMBIOS
strSN = objSMBIOS.SerialNumber
Next
Set ColDM =objWMIService.ExecQuery("Select * from Win32_DesktopMonitor")
For Each objDM in ColDM
strScreenHeight = objDM.ScreenHeight
StrScreenWidth = objDM.ScreenWidth
next
Set ColLogon = objwmiservice.execquery("Select * from Win32_LogonSession") ' Where LogonType = 2")
For Each objLogon in ColLogon
strDate = WMIDateStringToDate(objlogon.StartTime)
strTime = WMIDateStringTotime(objlogon.StartTime)
objSWbemDateTime.value = Objlogon.starttime
vtdLogonTime = objSWbemDateTime.GetVarDate(true)
StrDateDiff = DATEDIFF("s", vtdLogonTime ,now)
Next
'msgbox vtlogonTime
StrID= year(vtdLogonTime) & month(vtdLogonTime) & day(vtdLogonTime) & Hour(vtdLogonTime) & Minute(vtdLogonTime) & Second(vtdLogonTime)
'Msgbox StrID
'********************************************
'********************************************
'Connection to the Datadase *
'********************************************
strConnect = "Provider = SQLOLEDB.1;Data Source=SANDBOXHOST;Initial Catalog=Inventory;User ID='sa';Password='********'"
objConnection.Open strConnect
If lcase(strComputerName) <> "*********" then
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Table1_1" , objConnection, _
adOpenStatic, adLockOptimistic
objRecordset.AddNew
objRecordset("ID") = strID
objRecordset("ComputerName")= StrComputerName
objRecordset("UserName")= StrUser(1)
objRecordset("ComputerManufacturer")= strManufacturer
objRecordset("ComputerModel")= strModel
objRecordset("ComputerSN")= strSN
objrecordset("Recorddate") = now
objrecordset("RecordTime") = Now
objRecordset.Update
else
wscript.quit
end if
objRecordset.Close
objConnection.Close
'************************************************************************************
'Function *
'************************************************************************************
Function WMIDateStringToDate(dtmInstallDate)
WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & _
Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4))
End Function
Function WMIDateStringTotime(dtmInstallDate)
WMIDateStringTotime = CDate(Mid(dtmInstallDate, 9, 2) & ":" & _
Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate,13, 2))
End Function
Logoff Script (not working)
Used to update a record based on the Select statement.
When Recordset.open SQL, Conn,3,3 I have the result output to a msgbox and I am gets what I need it do, but when I added Recordset("Field") = Now or even a String like "TEST" It returns an error
"Current RecordSet does not support Updating. This may be a limitation of the provider, or the selected locktype.
'************************************************************************************
'Data Collection *
'************************************************************************************
Set objSWbemDateTime = CreateObject("WbemScripting.SWbemDateTime")
Dim StrComputerName, StrModel, strManufacturer, strUser
set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\." _
&"\root\cimv2")
Set colSystems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")
For Each objSystem In colSystems
strComputerName = objSystem.name
strModel = objsystem.Model
strManufacturer = objsystem.Manufacturer
If Not (ISNULL(objsystem.UserName)) then
strUser = Split(objsystem.UserName,"\")
strUser(1) = UCase(Left(strUser(1),1))_
& Trim(Mid(strUser(1),2,20))
else
Struser = split("\boscam","\")
strUser(1) = UCase(Left(strUser(1),1))_
& Trim(Mid(strUser(1),2,20))
End If
'strUser = objsystem.UserName
next
'************************************************************************************
'Connect to DB *
'************************************************************************************
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
strConnect = "Provider = SQLOLEDB.1;Data Source=SANDBOXHOST;Initial Catalog=Inventory;User ID='sa';Password='5QLsa@745'"
objConn.Open strConnect
SQL = "Select Computername,username,Logoffdate, Max(Recorddate) as Lastdate" & _
" From Table1_1 where computername ='" & strComputerName & "' " & _
" and username = '" & StrUser(1) & "' and logoffdate is null Group by ComputerName,UserName,logoffdate"
Msgbox SQL
objRS.open SQL, ObjConn,adOpenKeyset, adLockOptimistic, adCmdText
'MSGBOX objRS("ComputerName").value & VBcrLF & objRS("username").value & vbCRLF & objRS("LASTDATE").value
objrs("LogoffDate") = "TEST"
objRS.Update
'objRS.Close
objConn.Close
Set objConnection = Nothing
set objRecordset = Nothing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was perfect.