Dear Community,
I managed to create a VBscript to access a Sharepoint-List, get a recordset, add a line and fields etc. with ADO. One thing that I can't get to work is how to set or update a multiple choice field.
When I get a recordset from an existing line the value is a string with the choices separated with ;#. However when I try to set the same line to a new record it fails.
What is the proper way to do that?
Here some sample code, the constants need to set to the actual sharepoint site/list/id. I created a test sharepoint list with the default Title column and one 'Options' column with a choice / Checkboxes with the choices:
Event
Problem
Change
With the below code a new record is created with the Title but with an empty Options field.
Who can help me do that? Thanks ahead for your help!
Public Const strSPCustomerBatchListName = "MyListName"
Public Const strSPSite = "https://MySharepointSite"
Public Const strSPCustomerBatchListID = "the connection ID"
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim mySQL As String
'set connection
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
'sql select statement
mySQL = "SELECT * FROM [" & strSPCustomerBatchListName & "] WHERE [ID] = 1;"
'open connection
With cnt
.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=" & strSPSite & ";LIST={" & strSPCustomerBatchListID & "};"
.Open
End With
'open table / recordset
rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic
rst.AddNew
rst.Fields("Title").Value = "Test3"
rst.Fields("Options").Value = ";#Event;#Change;#"
rst.Update
'close recordset/connection and clean memory
If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
If CBool(cnt.State And adStateOpen) = True Then cnt.Close
Set cnt = Nothing
