How to update a multi-choice field of a sharepoint list with ADO

%3CLINGO-SUB%20id%3D%22lingo-sub-151388%22%20slang%3D%22en-US%22%3EHow%20to%20update%20a%20multi-choice%20field%20of%20a%20sharepoint%20list%20with%20ADO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-151388%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Community%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20managed%20to%20create%20a%20VBscript%20to%20access%20a%20Sharepoint-List%2C%20get%20a%20recordset%2C%20add%20a%20line%20and%20fields%20etc.%20with%20ADO.%20One%20thing%20that%20I%20can't%20get%20to%20work%20is%20how%20to%20set%20or%20update%20a%20multiple%20choice%20field.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20I%20get%20a%20recordset%20from%20an%20existing%20line%20the%20value%20is%20a%20string%20with%20the%20choices%20separated%20with%20%3B%23.%20However%20when%20I%20try%20to%20set%20the%20same%20line%20to%20a%20new%20record%20it%20fails.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20the%20proper%20way%20to%20do%20that%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20some%20sample%20code%2C%20the%20constants%20need%20to%20set%20to%20the%20actual%20sharepoint%20site%2Flist%2Fid.%20I%20created%20a%20test%20sharepoint%20list%20with%20the%20default%20Title%20column%20and%20one%20'Options'%20column%20with%20a%20choice%20%2F%20Checkboxes%20with%20the%20choices%3A%3CBR%20%2F%3EEvent%3CBR%20%2F%3EProblem%3CBR%20%2F%3EChange%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20the%20below%20code%20a%20new%20record%20is%20created%20with%20the%20Title%20but%20with%20an%20empty%20Options%20field.%3C%2FP%3E%0A%3CP%3EWho%20can%20help%20me%20do%20that%3F%20Thanks%20ahead%20for%20your%20help!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23003300%22%3EPublic%20Const%20strSPCustomerBatchListName%20%3D%26nbsp%3B%22MyListName%22%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3EPublic%20Const%20strSPSite%20%3D%20%22https%3A%2F%2FMySharepointSite%22%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3EPublic%20Const%20strSPCustomerBatchListID%20%3D%20%22the%20connection%20ID%22%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23003300%22%3EDim%20cnt%20As%20ADODB.Connection%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20Dim%20rst%20As%20ADODB.Recordset%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20Dim%20mySQL%20As%20String%3C%2FFONT%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20'set%20connection%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20Set%20cnt%20%3D%20New%20ADODB.Connection%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20Set%20rst%20%3D%20New%20ADODB.Recordset%3C%2FFONT%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20'sql%20select%20statement%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20mySQL%20%3D%20%22SELECT%20*%20FROM%20%5B%22%20%26amp%3B%20strSPCustomerBatchListName%20%26amp%3B%20%22%5D%20WHERE%20%5BID%5D%20%3D%201%3B%22%3C%2FFONT%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20'open%20connection%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20With%20cnt%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20.ConnectionString%20%3D%20_%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20%22Provider%3DMicrosoft.ACE.OLEDB.12.0%3BWSS%3BIMEX%3D0%3BRetrieveIds%3DYes%3BDATABASE%3D%22%20%26amp%3B%20strSPSite%20%26amp%3B%20%22%3BLIST%3D%7B%22%20%26amp%3B%20strSPCustomerBatchListID%20%26amp%3B%20%22%7D%3B%22%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20.Open%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20End%20With%3C%2FFONT%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20'open%20table%20%2F%20recordset%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3Erst.Open%20mySQL%2C%20cnt%2C%20adOpenDynamic%2C%20adLockOptimistic%3C%2FFONT%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3Erst.AddNew%3C%2FFONT%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20rst.Fields(%22Title%22).Value%20%3D%20%22Test3%22%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20rst.Fields(%22Options%22).Value%20%3D%20%22%3B%23Event%3B%23Change%3CSPAN%3E%3B%23%3C%2FSPAN%3E%22%26nbsp%3B%3C%2FFONT%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20rst.Update%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23003300%22%3E'close%20recordset%2Fconnection%20and%20clean%20memory%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20If%20CBool(rst.State%20And%20adStateOpen)%20%3D%20True%20Then%20rst.Close%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20Set%20rst%20%3D%20Nothing%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20If%20CBool(cnt.State%20And%20adStateOpen)%20%3D%20True%20Then%20cnt.Close%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23003300%22%3E%20Set%20cnt%20%3D%20Nothing%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-335171%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20update%20a%20multi-choice%20field%20of%20a%20sharepoint%20list%20with%20ADO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-335171%22%20slang%3D%22en-US%22%3EAbsolutely.%20Thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334972%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20update%20a%20multi-choice%20field%20of%20a%20sharepoint%20list%20with%20ADO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334972%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282207%22%20target%3D%22_blank%22%3E%40johnweathington%3C%2FA%3E%26nbsp%3BJohn%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20short%2C%20no.%20Funny%20is%20that%20I%20went%20for%20other%20solutions%20in%20the%20mean%20time%20and%20am%20just%20picking%20up%20yesterday%20to%20use%20sharepoint%20lists%20again.%20Main%20reason%20is%20that%20SharePoint%20is%20available%20to%20me%20and%20a%20proper%20(SQL)%20database%20is%20not%20%3A-).%20For%20now%20I%20just%20avoid%20multi-choice%20fields%20and%20use%20text%20fields%20in%20stead%20and%20let%20the%20script%20do%20the%20logic.%20I%20forgot%20about%20this%20post%20to%20be%20honest%20but%20will%20update%20when%20I%20find%20a%20solution%2C%20will%20you%20too%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3EMartijn.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334886%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20update%20a%20multi-choice%20field%20of%20a%20sharepoint%20list%20with%20ADO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334886%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Martijn%2C%20did%20you%20ever%20find%20a%20solution%20to%20this%3F%20I%20am%20facing%20the%20exact%20same%20problem.%20Any%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-151416%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20update%20a%20multi-choice%20field%20of%20a%20sharepoint%20list%20with%20ADO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-151416%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Juan%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20use%20vbscript%20a%20lot%20at%20Excel%2C%20Word%2C%20Outlook%20etc.%20I%20have%20some%20large%20script%20in%20excel%20to%20produce%20reports.%20I'd%20like%20to%20have%20the%20reports%20settings%20on%20sharepoint.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-151410%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20update%20a%20multi-choice%20field%20of%20a%20sharepoint%20list%20with%20ADO%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-151410%22%20slang%3D%22en-US%22%3EWhy%20are%20you%20using%20VBScript%20to%20access%20the%20SharePoint%20list%20data%3F%3C%2FLINGO-BODY%3E
New Contributor

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

5 Replies
Why are you using VBScript to access the SharePoint list data?

Hi Juan,

 

I use vbscript a lot at Excel, Word, Outlook etc. I have some large script in excel to produce reports. I'd like to have the reports settings on sharepoint. 

 

 

Hi Martijn, did you ever find a solution to this? I am facing the exact same problem. Any help would be greatly appreciated.

Hi @johnweathington John,

 

In short, no. Funny is that I went for other solutions in the mean time and am just picking up yesterday to use sharepoint lists again. Main reason is that SharePoint is available to me and a proper (SQL) database is not :-). For now I just avoid multi-choice fields and use text fields in stead and let the script do the logic. I forgot about this post to be honest but will update when I find a solution, will you too?

 

Kind regards,

Martijn.