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()
{
try
{
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"];
#endregion
newBankList.Update();
CContext.Load(newBankList);
CContext.ExecuteQuery();
}
CContext.Load(branchList);
CContext.ExecuteQuery();
Console.WriteLine("New Bank and Branch list inserted/updated successfully");
Console.ReadLine();
}
}
catch (Exception e)
{
Console.WriteLine(DateTime.Now + e.Message);
Console.ReadLine();
}
}
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);
connection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
connection.Close();
da.Dispose();
return dataTable;
}
}
}