Retreiving Sharepoint List Data using vb.net

Question

I want to retrieve SharePoint list data using VB.NET.

Below Code is for reference:-

Public Const roleGuid As String = "{8405ef03-40fl-4fan-8dl2-cf7kll1b8c1e}"
Public Const sharepointSite As String = "https://mysharepointsite.com/sites/resourceview.aspx"

Public Function getSharepointList()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConn As String
Dim sSql As String

sConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;" & _
"DATABASE=" & sharepointSite & ";" & _
"LIST=" & roleGuid & ";"

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
    .ConnectionString = sConn
    .Open
End With

sSql = "SELECT * FROM [Student list] as [Student List];"

rs.Open sSql, cn, adOpenStatic, adLockOptimistic

ThisWorkbook.Worksheets("Sheet1").Range("A2").CopyFromRecordset rs


End Function

Below is error screenshot that gets popup

Any method or suggestion would be helpful.

RBK · Accepted Answer · 2017-10-06 03:54:06Z

I have found out a way to retrieve data from SharePoint list using vb.net

Public Function retrieveData()
        'ADD FOLLOWING REFERENCES:-
        'Microsoft ActiveX Data Objects 2.8 Library

        'DECLARING CONNECTION AND RECORDSET OBJECTS, SQLQUERY STRING VARIABLE.
        Dim cnt As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim sqlQuery As String

        'SETTING UP CONNECTION AND RECORDSET OBJECTS.
        cnt = New ADODB.Connection
        rs = New ADODB.Recordset

    'HERE STUDENT LIST IS YOUR SHAREPOINT LIST NAME.
        sqlQuery = "Select * from [Student List];"

        'SETTING CONNECTION STRING TO CONNECTION OBJECT AND OPENING CONNECTION.
        With cnt
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=https://mysharepointlist.com/sites/;LIST={Your List GUID};"
            .Open()
        End With

        'OPENING RECORDSET.
        rs.Open(sqlQuery, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly)

     'FILLING DATATABLE WITH THE HELP OF DATA ADAPTER.      
     Dim myDa As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
        Dim myDs As DataTable = New DataTable
        myDa.Fill(myDs, rs)

    'FILLING DATAGRIDVIEW WITH DATATABLE AS DATASOURCE.
    DataGridView1.Datasource = myDs

        'CHECKS IF CONNECTION OBJECTS AND RECORDSET OBJECT IS IN OPEN STATE IF YES THEN IT WILL CLOSE AND DEREFERENCE THEM.
        If CBool(rs.State And ADODB.ObjectStateEnum.adStateOpen) = True Then rs.Close()
        rs = Nothing

        If CBool(cnt.State And ADODB.ObjectStateEnum.adStateOpen) = True Then cnt.Close()
        cnt = Nothing
    End Function

Collectives™ on Stack Overflow

Retreiving Sharepoint List Data using vb.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related