Nwosu Yahgozie

Nwosu Yahgozie

  • 2.1k
  • 41
  • 336

Mapping data stored in sql server to online sharepoint lookup fields.

Jul 23 2021 2:23 AM

I want to insert data from my sql server database into my sharepoint online list titled the "bank branch"  which contains a lookup column called "bank". Please somebody should assist me on how i can go about doing this to work properly, without it displaying the data is a read only. Below is my source code.

using Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security;
using System.Text;
using System.Threading.Tasks;

namespace SharepointBankProject
    public class Bankbranch
        public void insert()
                string WebUrl = ConfigurationManager.AppSettings["SPOUrl"];
                string Id = ConfigurationManager.AppSettings["SPOUserName"];
                string password = ConfigurationManager.AppSettings["SPOPassword"];

                var securePassword = new SecureString();
                foreach (char c in password)
                { securePassword.AppendChar(c); }
                var onlineCredentials = new SharePointOnlineCredentials(Id, securePassword);

                using (ClientContext CContext = new ClientContext(WebUrl))
                    CContext.Credentials = onlineCredentials;
                    Web web = CContext.Web;
                    List branchList = web.Lists.GetByTitle("Bank Branch");
                    List bankList = web.Lists.GetByTitle("Bank");
                    DataTable dt = new DataTable();
                    dt = GetDatafromSQL();

                    foreach (DataRow dr in dt.Rows)
                        var branchInfo = new ListItemCreationInformation();
                        ListItem newBranchList = branchList.AddItem(branchInfo);

                        var newBankItem = new ListItemCreationInformation();
                        var newBankList = bankList.AddItem(newBankItem);

                        #region Inserting/Updating the branchlist
                        newBankList["Title"] = dr["Bank_Name"];//This is the lookup column list and my source of error having a lookup list
                        newBranchList["Title"] = dr["Branch_Name"];
                        newBranchList["BranchPartyId"] = dr["Branch_Party_Id"];
                        newBranchList["Branch_x0020_Party_x0020_Number"] = dr["Branch_Party_Number"];
                        newBranchList["BIC_x002f_SWIFTCode"] = dr["BIC_SWIFT_Code"];
                        newBranchList["SortCode"] = dr["Sort_Code"];
                    Console.WriteLine("New Bank and Branch list inserted/updated successfully");

            catch (Exception e)
                Console.WriteLine(DateTime.Now + e.Message);



        private static DataTable GetDatafromSQL()
            DataTable dataTable = new DataTable();
            string connString = ConfigurationManager.AppSettings["Connection"];
            string query = ConfigurationManager.AppSettings["Query"];

            SqlConnection connection = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(query, connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);


            return dataTable;

Answers (1)