none
Using signature From SQL server in Content control returns System.NullReferenceException: 'Object reference not set to an instance of an object.' RRS feed

  • Question

  • I have been battling with this for sometime now. And i have not made anything that looks like a Headway. 

    Now i have added some content controls to an MS word document and i wanted to fetch data from SQL server and put on the content control. How ever it shows true on the webservice and should i check on the MS Word document,  I see nothing there just this exception System.NullReferenceException: 'Object reference not set to an instance of an object.'

    My code looks somewhat like this.

    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.IO;
    using System.Linq;
    using System.Web;
    using System.Web.Services;
    using DocumentFormat.OpenXml.Drawing;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Wordprocessing;
    using Text = DocumentFormat.OpenXml.Wordprocessing.Text;
    
    namespace eMemoSignatureService
    {
        /// <summary>
        /// Summary description for signaturewebservice
        /// </summary>
        [WebService(Namespace = "http://tempuri.org/")]
        [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
        [System.ComponentModel.ToolboxItem(false)]
        // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
        // [System.Web.Script.Services.ScriptService]
        public class signaturewebservice : System.Web.Services.WebService
        {
            string fullname;
            string designation;
    
            [WebMethod]
            public string HelloWorld()
            {
                return "Hello World";
            }
            
            [WebMethod]
            public bool PlaceSignatureonMsWord(string WEMAstaffID)
            {
                string constring = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constring))
                {
                    con.Open();
                    string sqlq = "select * from [e-SignatureDB].[dbo].[signature_table] where staffID = @staffID";
                    using (SqlCommand cmd = new SqlCommand(sqlq, con))
                    {
                        cmd.Parameters.AddWithValue("@staffID", WEMAstaffID);
                        SqlDataReader rd = cmd.ExecuteReader();
                        if (rd.Read())
                        {
                            fullname = (rd["fullname"].ToString());
                            designation = (rd["designation"].ToString());
                            byte[] bytes = Convert.FromBase64String(rd["approval_signature"].ToString());
                            Image img;
                            using (MemoryStream ms = new MemoryStream(bytes))
                            {
                                img = Image.FromStream(ms);
    
                                string folderPath = Server.MapPath("~/Signature_image/");
                                string fileName =  fullname + ".jpg";
                                string imagePath = folderPath + fileName;
                                img.Save(imagePath, System.Drawing.Imaging.ImageFormat.Jpeg);
    
                                using (WordprocessingDocument document = WordprocessingDocument.Open(@"C:\Users\emi\Desktop\e-Memo.docx", true))
                                {
                                    MainDocumentPart mainPart = document.MainDocumentPart;
                                    DocumentFormat.OpenXml.Wordprocessing.Text text = null;
                                    DocumentFormat.OpenXml.Wordprocessing.Text text2 = null;
                                    SdtContentBlock designationBlk = null;
                                    SdtContentBlock fullnameblk = null;
    
                                    List<SdtBlock> sdtList = mainPart.Document.Descendants<SdtBlock>().ToList();
    
                                    foreach (SdtBlock sdt in sdtList)
                                    {
                                        Console.WriteLine(sdt.SdtProperties.GetFirstChild<Tag>().Val.Value);
                                    }
    
                                    if (designation == "Manager")
                                    {
                                       // SdtElement s1 = mainPart.Document.Body.Descendants<SdtElement>().Where(r => r.SdtProperties.GetFirstChild<Tag>().Val == "ManName1").Single();
                                       // SdtElement s2 = mainPart.Document.Body.Descendants<SdtElement>().Where(r => r.SdtProperties.GetFirstChild<Tag>().Val == "ManDesg1").Single();
    
                                        SdtBlock s1 = mainPart.Document.Body.Descendants<SdtBlock>().Where(r => r.SdtProperties.GetFirstChild<Tag>().Val == "ManName1").Single();
                                        SdtBlock s2 = mainPart.Document.Body.Descendants<SdtBlock>().Where(r => r.SdtProperties.GetFirstChild<Tag>().Val == "ManDesg1").Single();
    
                                        SdtElement s3 = mainPart.Document.Body.Descendants<SdtElement>().FirstOrDefault(r =>
                                        {
                                            SdtElement p = r.Elements<SdtElement>().FirstOrDefault();
                                            if (p != null)
                                            {
                                                Console.WriteLine("P is not null");
                                                // Is it a picture content control?
                                                SdtContentPicture pict =
                                                    p.Elements<SdtContentPicture>().FirstOrDefault();
                                                if (pict != null) Console.WriteLine("Pict is not null");
                                                // Get the alias.
                                                SdtAlias a = p.Elements<SdtAlias>().FirstOrDefault();
                                                if (pict != null && a.Val == "Approval1")
                                                    return true;
                                            }
                                            return false;
                                        });
                                        if (s1 != null)
                                        {
                                            fullnameblk = s1.Descendants<SdtContentBlock>().FirstOrDefault();
                                            text = fullnameblk.Descendants<Text>().FirstOrDefault();
                                            text.Text = fullname;
                                            Console.WriteLine(text.Text);
                                        }
    
                                        if (s2 != null)
                                        {
                                            designationBlk = s2.Descendants<SdtContentBlock>().FirstOrDefault();
                                            text2 = designationBlk.Descendants<Text>().FirstOrDefault();
                                            
                                            text2.Text = designation;
                                            Console.WriteLine(text2.Text);
                                        }
    
                                        string embed = null;
                                        if (s3 != null)
                                        {
                                            Drawing dr = s3.Descendants<Drawing>().FirstOrDefault();
                                            if (dr != null)
                                            {
                                                Blip blip = dr.Descendants<Blip>().FirstOrDefault();
                                                if (blip != null)
                                                    embed = blip.Embed;
                                            }
                                        }
    
                                        if (embed != null)
                                        {
                                            IdPartPair idpp = document.MainDocumentPart.Parts
                                                .Where(pa => pa.RelationshipId == embed).FirstOrDefault();
                                            if (idpp != null)
                                            {
                                                ImagePart ip = (ImagePart)idpp.OpenXmlPart;
                                                using (FileStream fileStream =
                                                    File.Open(imagePath, FileMode.Open))
                                                    ip.FeedData(fileStream);
                                            }
    
                                            mainPart.Document.Save();
                                            document.Close();
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                return true;
            }
        }
    }


    Right now I followed several examples from here https://forums.asp.net/t/1941290.aspx?Populate+a+Content+Control+in+Word+with+SQL+data+using+Ling+to+SQL+and+OpenXML  and here https://social.msdn.microsoft.com/Forums/Lync/en-US/f9aa3c1a-3004-4582-a884-f39902f7dc64/insert-a-picture-into-the-pictureimage-content-control-using-open-xml-sdk-20?forum=oxmlsdk unfortunately , nothing helps

    I get the error on this line : 

                                        if (s1 != null)
                                        {
                                            fullnameblk = s1.Descendants<SdtContentBlock>().FirstOrDefault();
                                            text = fullnameblk.Descendants<Text>().FirstOrDefault();
                                            text.Text = fullname; <------------------- Exception Error here 
                                            Console.WriteLine(text.Text);
                                        }

    • Edited by oderis Monday, October 28, 2019 4:10 PM
    Monday, October 28, 2019 4:07 PM

Answers

  • Hi,

    This forum handles requests related to Open Specifications documentation issues. The Open Specifications can be found at: http://msdn2.microsoft.com/en-us/library/cc203350.aspx.
    Your question does not appear to be related to the Open Specifications documentation set.
    I suggest you try the following avenue to get assistance: 
    https://social.msdn.microsoft.com/Forums/en-US/home?forum=oxmlsdk
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    Thanks,

    Edgar

    Monday, October 28, 2019 4:38 PM
    Moderator