Click here to Skip to main content
14,326,246 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am getting some Exception errors using OpenXML to populate data from MSSQL server on a content control. I want to do a simple walk around to have the data populated from a table in the Database and print on an MS Word Document, and I am using the OpenXML SDK.

Having compiled the program and run to see what happens, I am getting this exception error : system.invalidoperationexception Sequence contains no elements

My code looks somewhat like this

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using DocumentFormat.OpenXml.Drawing;
using System.Data.SqlClient;
using System.IO;

namespace DesktopSignaturetest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string constring = @"Data Source=DESKTOP-9CM4N5S\SQLEXPRESS;Initial Catalog=SignatureBox2;User ID=sa;Password=123456;";
            using (SqlConnection con = new SqlConnection(constring))
            {
                con.Open();
                string q = "select * from SignatureBox_DB where StaffID = @StaffID";
                using (SqlCommand cmd = new SqlCommand(q, con))
                {
                    cmd.Parameters.AddWithValue("@StaffID", textBox1.Text);
                    using (SqlDataReader rd = cmd.ExecuteReader())
                    {
                        try
                        {
                            if (rd.Read())
                            {
                                string fileName = @"C:\Users\emi\Desktop\test.jpg";
                                byte[] imageBytes = Convert.FromBase64String(rd["SignatureBase64"].ToString());
                                string fullname = rd["FullName"].ToString();
                                string designation = rd["Designation"].ToString();
                                MemoryStream ms = new MemoryStream(imageBytes, 0, imageBytes.Length);
                                ms.Write(imageBytes, 0, imageBytes.Length);
                                Image image = Image.FromStream(ms, true, true);
                                image.Save(fileName, System.Drawing.Imaging.ImageFormat.Jpeg);

                                using (WordprocessingDocument doc = WordprocessingDocument.Open(@"C:\Users\emi\Desktop\MSWordTest.docx", true))
                                {
                                    MainDocumentPart mainPart = doc.MainDocumentPart;
                                    SdtBlock block = mainPart.Document.Body.Descendants<SdtBlock>().Where
                                    (r => r.SdtProperties.GetFirstChild<Tag>().Val == "Name").Single();

                                    SdtBlock desg = mainPart.Document.Body.Descendants<SdtBlock>().Where
                                    (r => r.SdtProperties.GetFirstChild<Tag>().Val == "Designation").Single();


                                    SdtBlock cc = doc.MainDocumentPart.Document.Body.Descendants<SdtBlock>()
                                    .FirstOrDefault(c =>
                                    {
                                        SdtProperties p = c.Elements<SdtProperties>().FirstOrDefault();
                                        if (p != null)
                                        {
                                            // Is it a picture content control?
                                            SdtContentPicture pict =
                                                p.Elements<SdtContentPicture>().FirstOrDefault();
                                            // Get the alias.
                                            SdtAlias a = p.Elements<SdtAlias>().FirstOrDefault();
                                            if (pict != null && a.Val == "Signature")
                                                return true;
                                        }
                                        return false;
                                    });

                                    string embed = null;
                                    if (cc != null)
                                    {
                                        Drawing dr = cc.Descendants<Drawing>().FirstOrDefault();
                                        if (dr != null)
                                        {
                                            Blip blip = dr.Descendants<Blip>().FirstOrDefault();
                                            if (blip != null)
                                                embed = blip.Embed;
                                        }
                                    }
                                    if (embed != null)
                                    {
                                        IdPartPair idpp = doc.MainDocumentPart.Parts
                                            .Where(pa => pa.RelationshipId == embed).FirstOrDefault();
                                        if (idpp != null)
                                        {
                                            DocumentFormat.OpenXml.Drawing.Text name = block.Descendants<DocumentFormat.OpenXml.Drawing.Text>().Single();
                                            DocumentFormat.OpenXml.Drawing.Text desgx = desg.Descendants<DocumentFormat.OpenXml.Drawing.Text>().Single();
                                            name.Text = fullname;
                                            desgx.Text = designation;
                                            ImagePart ip = (ImagePart)idpp.OpenXmlPart;
                                            using (FileStream fileStream =
                                                File.Open(fileName, FileMode.Open))
                                                ip.FeedData(fileStream);
                                            MessageBox.Show("Done!");
                                        }
                                    }
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.ToString());
                        }
                        finally
                        {
                            con.Close();
                        }
                    }
                }
            }
        }
    }
}


What I have tried:

The code fires an Exception error here

SdtBlock block = mainPart.Document.Body.Descendants<SdtBlock>().Where
(r => r.SdtProperties.GetFirstChild<Tag>().Val == "Name").Single();


Please what am i getting wrong?
Posted
Updated 5-Oct-19 2:02am
Rate this:
Please Sign up or sign in to vote.

Solution 2

Single can throw this error if no element is present in the sequence.

Try using SingleorDefault which will return null if no value found.

Msdn link: Enumerable.Single Method (System.Linq) | Microsoft Docs[^]

A good explanation here

Linq First vs Single vs Last methods. | Code Martini[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

One of your queries on that line is returning no results. Split the line into separate queries so you can test them in the debugger. Something like

var a = mainPart.Document.Body.Descendants<SdtBlock>();
var b = a.Where(r => r.SdtProperties.GetFirstChild<Tag>().Val == "Name");
SdtBlock block = b.Single();


Once you've found out which bit is returning an empty result set try and work out why that is. Either the data is wrong, you are handling it wrong, or no results is a valid outcome and you need to change your code to accommodate it. We don't know your business rules so you will have to decide what the appropriate fix it.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100