locked
Fetching Data from Sql server and prints on MS Word returns error (Object reference = null) RRS feed

  • Question

  • User-849204934 posted

    I have a Worry. I am trying to get the Fullname , Designaiton and signature from a table in my SQL server and place the signature on an MS Word file.  Since it would be using designation from the Database.

    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();
    
                                        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();
                                            
                                            text.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;
            }
        }
    }
    

    Now on this line 

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

    It tells me Object reference is null where text.Text = null 

    Why is this so? I am getting data from my Sql server.

    Monday, October 28, 2019 1:38 PM

All replies