Read value from Infopath form and save in sharepoint list

Question

Hi,

I have a Sharepoint list called "Job" where I am uploading the infopath form, with in infopath form I have one repreating table, I want to loop into it and save all data in datatable, then save this datatable value to another list called "time", I am writing the whole code in "ItemAdded".

Please can some one help me to find out what is the problem in my code?, if you feel there is some better way, please suggest me a well.

public override void ItemAdded(SPItemEventProperties properties)
       {
           DataTable dataTable = new DataTable();

           dataTable.Columns.Add("JobNumber", typeof(string));
           dataTable.Columns.Add("TmDate", typeof(string));
           dataTable.Columns.Add("TmTrvStart", typeof(double));
           dataTable.Columns.Add("TmSiteStart", typeof(double));
           dataTable.Columns.Add("TmSiteFinish", typeof(double));
           dataTable.Columns.Add("TmTrvFinish", typeof(double));
           dataTable.Columns.Add("TmHrsOnSite", typeof(double));
           dataTable.Columns.Add("TmChg01", typeof(double));
           dataTable.Columns.Add("TmHrsTrv", typeof(double));
           dataTable.Columns.Add("TmChg02", typeof(double));
           dataTable.Columns.Add("TmMileageFrom", typeof(double));
           dataTable.Columns.Add("TmMileageTo", typeof(double));


           //Get the name of item that is newely added
           string xmlFileName;// =;//Convert.ToString(properties.AfterProperties["Title"]);
           xmlFileName = properties.ListItem.Title;

           //xmlFileName = "Form4.xml";

           // create an XPathDocument object
           XPathDocument MainDataSource = new XPathDocument(xmlFileName);

           XPathNavigator domNav = MainDataSource.CreateNavigator();



           XPathNodeIterator JobNumber = domNav.Select("/my:Ext_Field_Srv_Rep");
           string JobNumberValue = JobNumber.Current.SelectSingleNode("my:JobNumber").Value;


           XPathNodeIterator rows = domNav.Select("/my:Ext_Field_Srv_Rep/my:TimeTbl/my:TimeLine");

           string TmDate = string.Empty;
           double TmTrvStart;
           double TmSiteStart;
           double TmSiteFinish;
           double TmTrvFinish;
           double TmHrsOnSite;
           double TmChg01;
           double TmHrsTrv;
           double TmChg02;
           double TmMileageFrom;
           double TmMileageTo;

           while (rows.MoveNext())
           {
               //Delete later
               TmDate = rows.Current.SelectSingleNode("my:TmDate").Value;
               TmTrvStart = Convert.ToDouble(rows.Current.SelectSingleNode("my:TmTrvStart").Value);
               TmSiteStart = Convert.ToDouble(rows.Current.SelectSingleNode("my:TmSiteStart").Value);
               TmSiteFinish = Convert.ToDouble(rows.Current.SelectSingleNode("my:TmSiteFinish").Value);
               TmTrvFinish = Convert.ToDouble(rows.Current.SelectSingleNode("my:TmTrvFinish").Value);
               TmHrsOnSite = Convert.ToDouble(rows.Current.SelectSingleNode("my:TmHrsOnSite").Value);
               TmChg01 = Convert.ToDouble(rows.Current.SelectSingleNode("my:TmChg01").Value);
               TmHrsTrv = Convert.ToDouble(rows.Current.SelectSingleNode("my:TmHrsTrv").Value);
               TmChg02 = Convert.ToDouble(rows.Current.SelectSingleNode("my:TmChg02").Value);
               TmMileageFrom = Convert.ToDouble(rows.Current.SelectSingleNode("my:TmMileageFrom").Value);
               TmMileageTo = Convert.ToDouble(rows.Current.SelectSingleNode("my:TmMileageTo").Value);


               DataRow datarow;
               datarow = dataTable.NewRow();

               datarow["JobNumber"] = JobNumberValue;
               datarow["TmDate"] = TmDate;
               datarow["TmTrvStart"] = TmTrvStart;
               datarow["TmSiteStart"] = TmSiteStart;
               datarow["TmSiteFinish"] = TmSiteFinish;
               datarow["TmTrvFinish"] = TmTrvFinish;
               datarow["TmHrsOnSite"] = TmHrsOnSite;
               datarow["TmChg01"] = TmChg01;
               datarow["TmHrsTrv"] = TmHrsTrv;
               datarow["TmChg02"] = TmChg02;
               datarow["TmMileageFrom"] = TmMileageFrom;
               datarow["TmMileageTo"] = TmMileageTo;

               dataTable.Rows.Add(datarow);

           }

           //Adding rows to Time list
           
               using (SPWeb web = properties.OpenWeb())
               {
                   SPList list = web.Lists["Time"];
                   for (int count = 0; count < dataTable.Rows.Count; count++)
                   {
                       SPListItem Item = list.Items.Add();
                       Item["JobNumber"] = dataTable.Rows[count]["JobNumber"];
                       Item["TmDate"] = dataTable.Rows[count]["TmDate"];
                       Item["TmTrvStart"] = dataTable.Rows[count]["TmTrvStart"];
                       Item["TmSiteStart"] = dataTable.Rows[count]["TmSiteStart"];
                       Item["TmSiteFinish"] = dataTable.Rows[count]["TmSiteFinish"];
                       Item["TmTrvFinish"] = dataTable.Rows[count]["TmTrvFinish"];
                       Item["TmHrsOnSite"] = dataTable.Rows[count]["TmHrsOnSite"];
                       Item["TmHrsTrv"] = dataTable.Rows[count]["TmHrsTrv"];
                       Item["TmChg02"] = dataTable.Rows[count]["TmChg02"];
                       Item["TmMileageFrom"] = dataTable.Rows[count]["TmMileageFrom"];
                       Item["TmMileageTo"] = dataTable.Rows[count]["TmMileageTo"];

                       Item.Update();

                   }


               }

           
           base.ItemAdded(properties);
       }

---

ARUN

Answer 1

It seems you have a problem in the list block of you code when you are trying to add the item to the List Item ("Adding rows to Time List" block) , did you debug the code and checked if there is an exception firing in Item.Update() ? From what I see , maybe you want to try disabling event firing before Item.Update() and then enable it , as you adding new list items inside the event . Is this event firing only on the InfoPath Form list instance or for the template  ? 

Although I don't recommend this way for your purpose ( in event receivers) , as you can write code in the InfoPath form itself and add the items to the Time List as you can access the Form data easily . Here is an example of adding items to another list using the InfoPath Code behind 

SharePoint: Using the SharePoint Object Model with InfoPath Form Code Behind

Thanks,
Saed 

Answer 2

Hi,

Yes, I debug the code- i am getting the problem in this line-

XPathDocumentMainDataSource = newXPathDocument(xmlFileName);

Is looking file into Could not find file 'c:\windows\system32\inetsrv\jhjjhhjh.xml'.

I am not sure, why is looking into this folder "'c:\windows\system32\inetsrv\"

Thanks

---

ARUN

Answer 3

You should open the binary stream of the current SPFile object instead of using new XPathDocument with just the filename.

MemoryStream myInStream = new MemoryStream(properties.ListItem.File.OpenBinary());
XmlDocument doc = new XmlDocument();
doc.Load(myInStream);

https://www.nothingbutsharepoint.com/sites/devwiki/SP2007Dev/Pages/Reading%20the%20XML%20from%20an%20InfoPath%20Form%20instance%20in%20a%20Form%20Library.aspx

---

jliu - http://johnliu.net - http://sharepointgurus.net

Answer 4

Hi,

Thanks, at least move a bit got different error in the same method-

error on this line-  xml.Load(myInStream);

[System.Xml.XmlException] = {"Root element is missing."}

using(MemoryStreammyInStream = newMemoryStream(properties.ListItem.File.OpenBinary()))

                        {                  

                            xml.Load(myInStream);

                        }

---

ARUN

Answer 5

Hi,

Here is full code, might help someone-

  public override void ItemAdded(SPItemEventProperties properties)
        {
            Thread.Sleep(5000);
            try
            {
                DataTable dataTable = new DataTable();

                dataTable.Columns.Add("JobNumber", typeof(string));
                dataTable.Columns.Add("TmDate", typeof(DateTime));
                dataTable.Columns.Add("TravelstartTime", typeof(DateTime));
                dataTable.Columns.Add("TravelFinishTime", typeof(DateTime));
                dataTable.Columns.Add("StartTimeOnSite", typeof(DateTime));
                dataTable.Columns.Add("FinishTimeOnSite", typeof(DateTime));
                dataTable.Columns.Add("ClockNumber/EngineerName", typeof(string));
                dataTable.Columns.Add("TmMileageFrom", typeof(string));
                dataTable.Columns.Add("TmMileageTo", typeof(string));

                SPListItem currentListItem = properties.ListItem;
                using (SPWeb myWeb = properties.OpenWeb())
                {
                    SPList formsLib = myWeb.Lists["Jobs"];

                    if (formsLib != null)
                    {
                        XmlDocument xml = null;
                        using (MemoryStream myInStream = new MemoryStream(currentListItem.File.OpenBinary()))
                        {
                            xml = new XmlDocument();
                            xml.Load(myInStream);
                        }
                        
                        XmlNamespaceManager xmlNSManager = new XmlNamespaceManager(xml.NameTable);
                        xmlNSManager.AddNamespace("my", "http://schemas.microsoft.com/office/infopath/2003/myXSD/2012-03-06T14:56:43");
                        XmlNodeList nodes = xml.GetElementsByTagName("my:JobNumber");
                        string JobNumberValue = string.Empty;
                        foreach (XmlNode pathNode in nodes)
                        {
                            JobNumberValue = pathNode.InnerText;
                        }
 
                        //Reading the repeating table value
                        XmlNodeList nodes2 = xml.SelectNodes("/my:Ext_Field_Srv_Rep/my:TimeTbl/my:TimeLine", xmlNSManager);

                        //Loop into the all node with in the repeating table
                        foreach (XmlNode pathNode in nodes2)
                        {
                            try
                            {
                                XmlNode TmDate = pathNode.SelectSingleNode("./my:TmDate", xmlNSManager);
                                if (TmDate.InnerText != "")
                                {
                                    DataRow datarow;
                                    datarow = dataTable.NewRow();

                                    datarow["JobNumber"] = JobNumberValue;


                                    if (TmDate != null)
                                    {
                                        datarow["TmDate"] = Convert.ToDateTime(TmDate.InnerText).ToShortDateString();
                                    }

                                    DateTime tmDate = Convert.ToDateTime(TmDate.InnerText);

                                    XmlNode TmTrvStar = pathNode.SelectSingleNode("./my:TmTrvStart", xmlNSManager);

                                    if (TmTrvStar != null)
                                    {
                                        DateTime finaldate = returnDate(TmTrvStar.InnerText, tmDate);
                                        datarow["TravelstartTime"] = finaldate;
                                    }


                                    XmlNode TmSiteStart = pathNode.SelectSingleNode("./my:TmSiteStart", xmlNSManager);
                                    if (TmSiteStart != null)
                                    {
                                        DateTime finaldate = returnDate(TmSiteStart.InnerText, tmDate);
                                        datarow["StartTimeOnSite"] = finaldate;
                                    }


                                    XmlNode TmSiteFinish = pathNode.SelectSingleNode("./my:TmSiteFinish", xmlNSManager);
                                    if (TmSiteFinish != null)
                                    {
                                        DateTime finaldate = returnDate(TmSiteFinish.InnerText, tmDate);
                                        datarow["FinishTimeOnSite"] = finaldate;
                                    }


                                    XmlNode TmTrvFinish = pathNode.SelectSingleNode("./my:TmTrvFinish", xmlNSManager);
                                    if (TmTrvFinish != null)
                                    {
                                        DateTime finaldate = returnDate(TmTrvFinish.InnerText, tmDate);
                                        datarow["TravelFinishTime"] = finaldate;
                                    }

                                    XmlNode TmMileageFrom = pathNode.SelectSingleNode("./my:TmMileageFrom", xmlNSManager);
                                    if (TmMileageFrom != null)
                                    {
                                        datarow["TmMileageFrom"] = Convert.ToString(TmMileageFrom.InnerText);
                                    }


                                    XmlNode TmMileageTo = pathNode.SelectSingleNode("./my:TmMileageTo", xmlNSManager);
                                    if (TmMileageTo != null)
                                    {
                                        datarow["TmMileageTo"] = Convert.ToString(TmMileageTo.InnerText);

                                    }

                                    dataTable.Rows.Add(datarow);
                                }
                            }
                            catch (Exception ex)
                            {
                            }

                        }
                    }


                    //Adding rows to Time list

                    try
                    {
                        using (SPWeb Web = properties.OpenWeb())
                        {
                            SPList list = myWeb.Lists["TimeT"];
                            for (int count = 0; count < dataTable.Rows.Count; count++)
                            {
                                SPListItem Item = list.Items.Add();

                                Item["JobNumber"] = dataTable.Rows[count]["JobNumber"];
                                Item["TimeDate"] = dataTable.Rows[count]["TmDate"];
                                Item["TravelstartTime"] = dataTable.Rows[count]["TravelstartTime"];
                                Item["TravelFinishTime"] = dataTable.Rows[count]["TravelFinishTime"];
                                Item["StartTimeOnSite"] = dataTable.Rows[count]["StartTimeOnSite"];
                                Item["FinishTimeOnSite"] = dataTable.Rows[count]["FinishTimeOnSite"];
                                Item["ClockNumber/EngineerName"] = "oooo";
                                Item["MileageFrom"] = dataTable.Rows[count]["TmMileageFrom"];
                                Item["MileageTo"] = dataTable.Rows[count]["TmMileageTo"];

                                Item.Update();
                            }


                        }
                    }
                    catch (Exception ex)
                    {
                    }

                    base.ItemAdded(properties);
                }
            }

            catch (Exception ex)
            {
            }
        }

---

ARUN