Click here to Skip to main content
15,888,816 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to export data from database to word document. But it breaks in the loop action. How can we loop multiple rows?

What I have tried:

I am trying to export data from database to word document. But it breaks in the loop action. How can we loop multiple rows? It works very well without loop. But when it loops, it gives error. It reads the first row in the database correctly. It gives an error while reading the next row.


C#
int iTotalFields = 0;
            Object oMissing = System.Reflection.Missing.Value;
            Object oTrue = true;
            Object oFalse = false;
            Word.Application oWord = new Word.Application();
            Word.Document oWordDoc = new Word.Document();

            oWord.Visible = true;
            Object oTemplatePath = System.Windows.Forms.Application.StartupPath + "\\GeziListesi.docx";
            oWordDoc = oWord.Documents.Add(ref oTemplatePath, ref oMissing, ref oMissing, ref oMissing);

            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=gezievrak2541.mdb; Mode=ReadWrite");

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM gezilistemiz25", conn))
            {
                using (OleDbDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        foreach (Word.Field myMergeField in oWordDoc.Fields)
                        {
                            iTotalFields++;

                            Word.Range rngFieldCode = myMergeField.Code;
                            String fieldText = rngFieldCode.Text;

                            if (fieldText.StartsWith(" MERGEFIELD"))
                            {
                                Int32 endMerge = fieldText.IndexOf("\\");
                                Int32 fieldNameLength = fieldText.Length - endMerge;
                                String fieldName = fieldText.Substring(11, endMerge - 11);

                                fieldName = fieldName.Trim();

                                if (fieldName == "tcno")
                                {
                                    myMergeField.Select();
                                    oWord.Selection.TypeText(dr["tcno"].ToString());
                                }
                                if (fieldName == "adsoyad")
                                {
                                    myMergeField.Select();
                                    oWord.Selection.TypeText(dr["adi"].ToString() + " " + dr["soyadi"].ToString());
                                }
                                if (fieldName != "sinifi")
                                {
                                    //myMergeField.Select();
                                    oWord.Selection.TypeText(" ");

                                }
                                if (fieldName == ("sinifi"))
                                {
                                    myMergeField.Select();
                                    oWord.Selection.TypeText(dr["sinifi"].ToString());
                                }

                                if (fieldName == "unvan")
                                {
                                    myMergeField.Select();
                                    oWord.Selection.TypeText(dr["unvani"].ToString());
                                }
                            }
                        }
                    }
                }
            }
        }
Posted
Updated 24-Jun-23 4:14am
v2
Comments
Pete O'Hanlon 18-Jun-23 14:28pm    
What error does it give? That's a rather important clue missing to what is going wrong.
Member 12505620 18-Jun-23 15:31pm    
There is no error. It reads only one row. I want it to read all of the rows.
Pete O'Hanlon 19-Jun-23 0:56am    
Then why does your question say that if errors in the loop?

The problem is that your code replaces the merge field with the record read from the database. That means one of two things is going to happen:
  • The text replaces the merge field itself, meaning only the data from the first record will be present; or
  • The text leaves the merge field in place, meaning only the data from the last record will be present.

You can't simply select the merge field and replace it with the text you want to use. Instead you need to use a combination of MailMerge.OpenDataSource[^], MailMerge.Execute[^], and possibly other methods on the MailMerge property.
 
Share this answer
 
Comments
Andre Oosthuizen 19-Jun-23 7:23am    
Thanks for comment Richard, my solution deleted for now, will post correct version. Happens when you just skim the code supplied, my bad!
So set a breakpoint on the while(dr.Read) statement and step through the code, executing it line-by-line, watching what it does and hover the mouse over the dr variable to inspect its properties.

This is going to be on you to solve. There's nothing in there that's going to show anyone looking at the code alone what's wrong. The problem is dependent on the data the code is using and nobody, but you can see that.
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900