Share
Report
2,667Hi All,
I am reading excel data & trying to create record in crm using console but in service.create am getting error "Cannot find record to be updated ".on each column hv chked theres no error got.
Excel has total 148 records out of them 134 records r getting creating but 14 records are not there am getting above issue.
public static void CreateActions(IOrganizationService Service)
{
Application excelApp = new Application();
if (excelApp == null)
{
Console.WriteLine("Excel is not installed!!");
return;
}
Workbook excelBook = excelApp.Workbooks.Open(@"C:\_DO_NOT_BACKUP\Jharana\Data Migration_Amit\Actions.xlsx");
_Worksheet excelSheet = excelBook.Sheets[1];
Range excelRange = excelSheet.UsedRange;
int rows = excelRange.Rows.Count;
int cols = excelRange.Columns.Count;
Entity Action = new Entity("mdc_actions");
for (int i = 1; i <= rows; i++)
{
if (i >= 2)
{
for (int j = 1; j <= cols; j++)
{
try
{
if (j == 1)
{
string CaseNumber = Convert.ToString(excelRange.Cells[i, j].Value2);
if (CaseNumber != null && CaseNumber != string.Empty)
{
QueryExpression query = new QueryExpression("incident");
query.Criteria.AddCondition("ticketnumber", ConditionOperator.Equal, CaseNumber);
try
{
EntityCollection col = Service.RetrieveMultiple(query);
if (col.Entities.Count > 0)
{
foreach (Entity Eninci in col.Entities)
{
Action["mdc_case"] = new EntityReference("incident", Eninci.Id);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
}
else if (j == 2)
{
Action["mdc_actionnumber"] = string.Empty;
string ActionNumber = Convert.ToString(excelRange.Cells[i, j].Value2);
if (!string.IsNullOrEmpty(ActionNumber))
{
Action["mdc_actionnumber"] = ActionNumber;
}
}
else if (j == 3)
{
Action["mdc_actiontype"] = null;
string actionType = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();
if (actionType != null && actionType != string.Empty)
{
QueryExpression query = new QueryExpression("mdc_actiontype");
query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, actionType);
try
{
EntityCollection col = Service.RetrieveMultiple(query);
if (col.Entities.Count > 0)
{
foreach (Entity Eninci in col.Entities)
{
Action["mdc_actiontype"] = new EntityReference("mdc_actiontype", Eninci.Id);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
}
else if (j == 4)
{
Action["mdc_subtype"] = null;
string subType = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();
if (subType != null && subType != string.Empty)
{
QueryExpression query = new QueryExpression("mdc_actionsubtype");
query.Criteria.AddCondition("mdc_name", ConditionOperator.Equal, subType);
try
{
EntityCollection col = Service.RetrieveMultiple(query);
if (col.Entities.Count > 0)
{
foreach (Entity Eninci in col.Entities)
{
Action["mdc_subtype"] = new EntityReference("mdc_actionsubtype", Eninci.Id);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
}
else if (j == 7)
{
Action["mdc_email"] = string.Empty;
Action["mdc_actionowner"] = null;
string ExternalEmail = Convert.ToString(excelRange.Cells[i, j].Value2);
string InternalEmail = Convert.ToString(excelRange.Cells[i, 8].Value2);
string ActionOwner = Convert.ToString(excelRange.Cells[i, 5].Value2);
string ActionOwnerAd = Convert.ToString(excelRange.Cells[i, 6].Value2);
if (!string.IsNullOrEmpty(ExternalEmail) || !string.IsNullOrEmpty(ActionOwner))
{
if (!string.IsNullOrEmpty(ExternalEmail))
{
Action["mdc_email"] = ExternalEmail;
QueryExpression query = new QueryExpression("contact");
query.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, ExternalEmail);
try
{
EntityCollection col = Service.RetrieveMultiple(query);
if (col.Entities.Count > 0)
{
foreach (Entity Eninci in col.Entities)
{
Action["mdc_actionowner"] = new EntityReference("contact", Eninci.Id);
}
}
else if(!string.IsNullOrEmpty(ActionOwner))
{
QueryExpression querycon = new QueryExpression("contact");
query.Criteria.AddCondition("fullname", ConditionOperator.Equal, ActionOwner);
EntityCollection colCon = Service.RetrieveMultiple(querycon);
if (colCon.Entities.Count > 0)
{
foreach (Entity Eninci in colCon.Entities)
{
Action["mdc_actionowner"] = new EntityReference("contact", Eninci.Id);
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
}
else if (!string.IsNullOrEmpty(InternalEmail) || !string.IsNullOrEmpty(ActionOwnerAd))
{
if (!string.IsNullOrEmpty(InternalEmail))
{
Action["mdc_email"] = InternalEmail;
QueryExpression query = new QueryExpression("contact");
query.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, InternalEmail);
try
{
EntityCollection col = Service.RetrieveMultiple(query);
if (col.Entities.Count > 0)
{
foreach (Entity Eninci in col.Entities)
{
Action["mdc_actionowner"] = new EntityReference("contact", Eninci.Id);
}
}
else if (!string.IsNullOrEmpty(ActionOwnerAd))
{
QueryExpression querycon = new QueryExpression("contact");
query.Criteria.AddCondition("fullname", ConditionOperator.Equal, ActionOwnerAd);
EntityCollection colCon = Service.RetrieveMultiple(querycon);
if (colCon.Entities.Count > 0)
{
foreach (Entity Eninci in colCon.Entities)
{
Action["mdc_actionowner"] = new EntityReference("contact", Eninci.Id);
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
}
}
else if (j == 9)
{
Action["mdc_actiondescription"] = string.Empty;
string description = Convert.ToString(excelRange.Cells[i, j].Value2);
if (!string.IsNullOrEmpty(description))
{
Action["mdc_actiondescription"] = description;
}
}
else if (j == 10)
{
Action["mdc_completiondate"] = null;
string completiondate = Convert.ToString(excelRange.Cells[i, j].Value2);
if (completiondate != "Unclassified")
{
try
{
double date = double.Parse(completiondate);
var dateTime1 = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");
DateTime dt = Convert.ToDateTime(dateTime1);
String Converteddate = dt.ToShortDateString();
Action["mdc_completiondate"] = dt;
}
catch (Exception ex)
{
continue;
}
}
}
else if (j == 11)
{
Action["mdc_reviseddate"] = null;
string Reviseddate = Convert.ToString(excelRange.Cells[i, j].Value2);
if (Reviseddate !=null)
{
try
{
double date = double.Parse(Reviseddate);
var dateTime1 = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");
DateTime dt = Convert.ToDateTime(dateTime1);
String Converteddate = dt.ToShortDateString();
Action["mdc_reviseddate"] = dt;
}
catch (Exception ex)
{
continue;
}
}
}
else if (j == 12)
{
Action["mdc_targetdate"] = null;
string Targetdate = Convert.ToString(excelRange.Cells[i, j].Value2);
if (Targetdate != "Unclassified")
{
try
{
double date = double.Parse(Targetdate);
var dateTime1 = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");
DateTime dt = Convert.ToDateTime(dateTime1);
String Converteddate = dt.ToShortDateString();
Action["mdc_targetdate"] = dt;
}
catch (Exception ex)
{
continue;
}
}
}
else if (j == 13)
{
Action["mdc_status"] = null;
string Status = Convert.ToString(excelRange.Cells[i, j].Value2);
if (!string.IsNullOrEmpty(Status))
{
if (Status.ToLower() == "open")
{
Action["mdc_status"] = new OptionSetValue(755040000);
}
else if (Status.ToLower() == "closed")
{
Action["mdc_status"] = new OptionSetValue(755040005);
}
else if (Status.ToLower() == "cancelled")
{
Action["mdc_status"] = new OptionSetValue(755040006);
}
}
}
//14 Created On NA
else if (j == 15)
{
string ismdcuser = Convert.ToString(excelRange.Cells[i, j].Value2);
if (!string.IsNullOrEmpty(ismdcuser))
{
if (ismdcuser.ToLower() == "yes")
{
Action["mdc_recipientisnotanmdcuser"] = true;
}
else
{
Action["mdc_recipientisnotanmdcuser"] = false;
}
}
}
//Recipient of the Disciplinary Action & External
else if (j == 16)
{
Action["mdc_recipientofthedisciplinaryaction"] = null;
string RecipientDisciplinaryAction = Convert.ToString(excelRange.Cells[i, j].Value2);
string RecipientDisciplinaryActionExternal = Convert.ToString(excelRange.Cells[i, 17].Value2);
if (!string.IsNullOrEmpty(RecipientDisciplinaryAction))
{
QueryExpression query = new QueryExpression("contact");
query.Criteria.AddCondition("fullname", ConditionOperator.Equal, RecipientDisciplinaryAction);
try
{
EntityCollection col = Service.RetrieveMultiple(query);
if (col.Entities.Count > 0)
{
foreach (Entity Eninci in col.Entities)
{
Action["mdc_recipientofthedisciplinaryaction"] = new EntityReference("contact", Eninci.Id);
}
}
else
{
var newRN = RecipientDisciplinaryAction.Split(' ');
string firstName = newRN[0];
string lastName = RecipientDisciplinaryAction.Remove(0, RecipientDisciplinaryAction.IndexOf(' ') + 1);
// string lastName = exInterview[1];
Entity reporter = new Entity("contact");
reporter["firstname"] = firstName;
reporter["lastname"] = lastName;
Service.Create(reporter);
QueryExpression qe = new QueryExpression();
qe.EntityName = "contact";
qe.ColumnSet = new ColumnSet(true);
qe.Criteria = new FilterExpression();
if (!string.IsNullOrEmpty(RecipientDisciplinaryAction))
qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, RecipientDisciplinaryAction);
EntityCollection ec = Service.RetrieveMultiple(qe);
if (ec.Entities.Count > 0)
{
foreach (var con in ec.Entities)
{
// Action["customerid"] = new EntityReference("contact", con.Id);
Action["mdc_recipientofthedisciplinaryaction"] = new EntityReference("contact", con.Id);
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
else if (!string.IsNullOrEmpty(RecipientDisciplinaryActionExternal))
{
QueryExpression query = new QueryExpression("contact");
query.Criteria.AddCondition("fullname", ConditionOperator.Equal, RecipientDisciplinaryActionExternal);
try
{
EntityCollection col = Service.RetrieveMultiple(query);
if (col.Entities.Count > 0)
{
foreach (Entity Eninci in col.Entities)
{
Action["mdc_recipientofthedisciplinaryaction"] = new EntityReference("contact", Eninci.Id);
}
}
else {
var newRN = RecipientDisciplinaryActionExternal.Split(' ');
string firstName = newRN[0];
string lastName = RecipientDisciplinaryActionExternal.Remove(0, RecipientDisciplinaryActionExternal.IndexOf(' ') + 1);
// string lastName = exInterview[1];
Entity reporter = new Entity("contact");
reporter["firstname"] = firstName;
reporter["lastname"] = lastName;
Service.Create(reporter);
QueryExpression qe = new QueryExpression();
qe.EntityName = "contact";
qe.ColumnSet = new ColumnSet(true);
qe.Criteria = new FilterExpression();
if (!string.IsNullOrEmpty(RecipientDisciplinaryActionExternal))
qe.Criteria.AddCondition("fullname", ConditionOperator.Equal, RecipientDisciplinaryActionExternal);
EntityCollection ec = Service.RetrieveMultiple(qe);
if (ec.Entities.Count > 0)
{
foreach (var con in ec.Entities)
{
// Action["customerid"] = new EntityReference("contact", con.Id);
Action["mdc_recipientofthedisciplinaryaction"] = new EntityReference("contact", con.Id);
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
}
}
catch (Exception ex)
{
continue;
}
}
try
{
Service.Create(Action); // Here getting exception Cannot find record to be updated
}
catch (Exception ex)
{
continue;
}
}
}
excelBook.Close(true);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
Any idea on this to resolve this ?
Thanks,
Jharana
All responses (
Answers (
