Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ADODB.RecordSet For updating a record in MSMSQL DB Error Object Does Not Support Updating

Posted on 2014-03-27
2
393 Views
Last Modified: 2014-03-27
I have two VBS scripts that are used to write to a MSSQL DB.

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

Open in new window


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

Open in new window

0
Comment
Question by:yo_bee
2 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 39959883
You need normally to call objRS.Edit first, before changing a value.

But: The GROUP BY clause makes the recordset not updateable.

So you need to filter for the latest user row, e.g.

SQL = _
  "SELECT * FROM Table1_1 " & _
  "WHERE computername ='{0}' AND username = '{1}' " & _
  "AND ID = ( SELECT MAX(ID) FROM Table1_1 WHERE username = '{1}' ); "
SQL = Replace(SQL, "{0}", strComputerName)
SQL = Replace(SQL, "{1}", strUser(1))
Msgbox SQL

Open in new window

0
 
LVL 22

Author Closing Comment

by:yo_bee
ID: 39959982
Thank you for the help.

That was perfect.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
SQL Quer 4 21
Getting invalid Syntax SQL. 3 17
Inserting a datasheet query into the body of an email using VBA? 4 16
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question