Lets Learn

Opinion Matters

Inserting Items in/Connecting to SharePoint List using OLEDB (ACE) Provider

Posted by Ankush on February 18, 2011


Recently I worked on a case where customer was trying to insert a record using OLE DB provider in SharePoint list.  Well that’s easy then where is the problem???? Problem came when list has a date filed and for some reason OLEDB provider was not able to update the date field.

So here is some more detail about it:

Problem: Consider you have created a VB.Net which uses the OLEDB provider to connect to a SharePoint list and insert a new record. Now the list contains some date field which should also be modified.

Environment: SharePoint 2007, VB.Net project (or as a matter of fact you can do it anywhere. You just need to refer to the provider. I also did it in using VBA)

Complete Solution:

Assumptions: You have created a list name Test which contains two columns:  Title and Start (which is of date type)

<< Solution which didn’t work>>

Step 1: Create the connection string and declare the appropriate variables. Please note that I have used the GUID of the list which you can easily get.

Dim conSharePoint As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConnection As String
Dim intIMEX As Integer
Dim strDatabase As String
Dim strList As String
Dim strTable As String

intIMEX = 2                                         ‘IMEX=1 for Read only, IMEX=2 for Read/Write
strDatabase = “http://abha21597318:9354
strList = “{46218811-6D9A-4DBD-AEE4-9F122692E0AE}”  ‘LIST=GUID to specific list or view
strTable = “Test”                                   ‘TABLE=Display name of list
strConnection = “Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=” & intIMEX & “;RetrieveIds=Yes;” & “DATABASE=” & strDatabase & “;” & “LIST=” & strList & “;VIEW=;RetrieveIds=Yes;TABLE=” & strTable

Step 2: Open the connection and recordset

conSharePoint = New ADODB.Connection()
conSharePoint.Open(strConnection)
rst = New ADODB.Recordset()
rst.Open(“Test”, conSharePoint, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

Step 3: Add a new record

With rst
.AddNew()
.Fields(“Title”).Value = “Test”
.Fields(“Start”).Value = “2011-04-15 00:00:00” ‘ THIS IS WHERE IT FAILS
.Update()
End With
Now I tried a lots of date formats but nothing seems to worked for me. If you happen to know a workaround/date format which works, please leave a comment here.

<< Solution which DID work>>

I started thinking about other workarounds and I used the popular workaround SOAP toolkit. I downloaded the SOAP toolkit and used the following code to do it. Fiddler tool really helped me in building the SOAP queries.

Sub InsertUsingSOAP()

Dim oSOAPClnt As SoapClient30
Dim sht As String
Dim wbkPath As String
Dim sts() As Object
Dim sId As String
Dim dom As IXMLDOMSelection
Dim ndListView As IXMLDOMNode
Set oSOAPClnt = New SoapClient30
Dim myXMLNodeList As MSXML2.IXMLDOMNodeList
Dim root As MSXML2.IXMLDOMElement
Dim xmlDoc As New MSXML2.DOMDocument30
Dim strListID As String
Dim xmlText As String

oSOAPClnt.MSSoapInit (“http://abha21597318:9354/_vti_bin/Lists.asmx?WSDL“)
‘ Test -> List Name
Set dom = oSOAPClnt.GetListAndView(“Test”, “”)
Set ndListView = dom.item(0)
‘ Make sure to Change the Attributes index
strListID = ndListView.ChildNodes(0).Attributes(7).NodeValue
xmlDoc.async = False

xmlText = “<root>” + _
“<Batch OnError=’Continue’ ListVersion=’24’ PreCalc=’TRUE’ xmlns=”>” + _
“<Method ID=’1′ Cmd=’New’>” + _
“<Field Name=’Title’>Test Insert from VBA</Field>” + _
“<Field Name=’Start’>2011-04-15 00:00:00</Field>” + _
“</Method>” + _
“</Batch>” + _
“</root>”
xmlDoc.LoadXML (xmlText)
If xmlDoc.parseError.ErrorCode <> 0 Then
Dim myErr
Set myErr = xmlDoc.parseError
MsgBox (myErr.reason)
Else
MsgBox xmlDoc.XML
End If
‘ Set up IXMLDOMNodeList object

Set root = xmlDoc.DocumentElement
Set myXMLNodeList = root.ChildNodes
oSOAPClnt.UpdateListItems strListID, myXMLNodeList

End Sub

Note : The same code which doesnt work in SP 2007 works in SP 2010. So you can use OLEDB provider to insert a record in list in SP 2010.

There is also one interesting scenerio comes when you want to use user name and password in SOAP tool kit. So this is what you can do to do that.

Sample Code
=============================
‘provide authentication info in case of basic auth. For windows ‘ authe. you don’t need to pass this info. Our connector asks windows security ‘ layer for this

SoapClient3.ConnectorProperty(“AuthUser”) = “username”
SoapClient3.ConnectorProperty(“AuthPassword”)=”password”
=============================
Basically If you don’t pass authentication information then the SoapToolkit’s Connector will  automatically query the windows security layer for the integrated authentication information for the current user and will use these credentials.If you want to supply the authentication information then you can use the ConnectorProperty as demonstrated above to pass the username and password
information.

Happy Reading!!!!!

Update : I thought about some other interestingoptions and here is the list:

1. Have an access database , linked table and then update records from there
2. Create a .net application which can consume web services and make it COM visible. Use this assembly in VBA
3. Use XMLHttp and directly invoke webservice. It will be a bit  tedious job as it requires one to manipulate and construct the XML.
http://msdn.microsoft.com/en-us/library/dd582945(v=office.11).aspx

 

Advertisements

4 Responses to “Inserting Items in/Connecting to SharePoint List using OLEDB (ACE) Provider”

  1. Ken said

    I have tried the Ace provider within VBA and get a ‘Could not find installable ISAM’ error. I thought the provider was part of the Office install but manually installed it and still no luck. Any idea why this is happening? Do I need to reference something in my references?

    Ken

    • Ankush said

      This error will appear even if the connection string is not correct.

      If possiable, can you please paste your code here?

      Thanks
      Ankush

  2. Brendon said

    .Fields(“Start”).Value = “2011-04-15T00:00:00Z″

  3. Ania said

    Thank you Ankush – it explains a lot to me. However I have SP 2010 and OLEDB with date type fields still doesn’t work properly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: