none
Loop on mergeField and fill it with database RRS feed

  • Question

  • I am working on .net platform. I have created a word template(.dotx) and added mergefields in it. i am replacing merge field from database column values. Now  i want to loop on merge field.
    for example :- i have merge fields named <<DepartmentID>> and <<DepartmentName>> . And  i am getting data from database's table named "tblDepartment" which have two columns "DepID " and "DepName". this table have 5 rows. i want to loop on these mergefields.

    i want to show file like this.
    before execute the docx file is :-
                                <<DepartmentID>>           <<DepartmentName>>

    after code execute:-
                                     DepID          DepName

                                       1                 DotNet

                                       2                 Java

                                       3                 PHP

    Please help me . Thanks in advance.


    Bharat Bhushan


    • Edited by Bharat7387 Wednesday, March 13, 2013 5:38 AM
    Wednesday, March 13, 2013 5:37 AM

Answers

All replies

  • From your description, it seems like you need to use «Next Record» fields, thus:
    DepID          DepName
    «DepartmentID»          «DepartmentName»«Next Record»
    «DepartmentID»          «DepartmentName»«Next Record»
    «DepartmentID»          «DepartmentName»«Next Record»
    «DepartmentID»          «DepartmentName»


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, March 13, 2013 6:40 AM
  • Thanks for replying Paul .
    Please tell me, What is <<Next Record>> here?
    will it work for dynamic records, because i don't know how many records  database return?
    I am sending a snapshot of my requirement,please check.

    Here is my code sample:-

               foreach (Word.Field myMergeField in wordDoc.Fields)
                {
                    Word.Range rngFieldCode = myMergeField.Code;
                    String fieldText = rngFieldCode.Text;
                    // ONLY GETTING THE MAILMERGE FIELDS
                    if (fieldText.StartsWith(" MERGEFIELD"))
                    {
                        Int32 endMerge = fieldText.IndexOf("\\");
                        Int32 fieldNameLength = fieldText.Length - endMerge;
                        String fieldName = fieldText.Substring(11, endMerge - 11);
                        fieldName = fieldName.Trim().Replace("\"", "");
                        if (fieldName == "DepartmentID")
                        {
                            myMergeField.Select();
                            wordApp.Selection.TypeText("DepartmentID");
                        }
                        if (fieldName == "DepartmentName")
                        {
                            EAccreditationEntities dbModel = new EAccreditationEntities();
                            var q = (from p in dbModel.Departments select p).ToList();
                            //here q has all data from database , have columns (ID and Name of Department)
                             myMergeField.Select();
                            wordApp.Selection.TypeText("DEPNAME");
                        }
                    }

    How can i find here both merge fields and replace them with DepartmentID and Name.


    Bharat Bhushan


    • Edited by Bharat7387 Wednesday, March 13, 2013 7:53 AM
    Wednesday, March 13, 2013 7:50 AM
  • Hi Bharat,

    Your initial post didn't indicate how you were doing this, so I assumed you were using Word's mailmerge tools. The nextrecord field is one of those.

    It now seems you're actually trying to do a many-to-one merge, for which there are already some good addins available. See, for example, the Many-to-One Mail Merge add-ins, from:
    Graham Mayor at http://www.gmayor.com/ManyToOne.htm; and
    Doug Robbins at https://skydrive.live.com/?cid=5aedcb43615e886b#!/?cid=5AEDCB43615E886B!cid=5AEDCB43615E886B&id=5AEDCB43615E886B%21566


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, March 13, 2013 8:27 AM
  • Thanks a lot Paul,

    sky drive  is related to word 2013, but i am working on 2007.
    The whole process is as follows:-

    I created a word template and added  merge fields(DepartmentID and Name) in template. Now i am getting data from SQL table named department,which have two columns (ID,Name). Now i want to show all data in word document from database. The format is shown in earlier post.

    And the problem is that , my document have five pages , merge fields  on 5th page and sql query returns 4 rows. when i am executing my code ,it shows first record on 5th page, then 6,7,8,9 pages create with 1,2,3,4 pages content and on 10th page shows 2nd record and so on.... Whole process creates 25 pages document file.

    But i want to show all data on 5th page only. and not want to create extra pages.
    Is Process  clear now?


    Bharat Bhushan


    • Edited by Bharat7387 Wednesday, March 13, 2013 10:13 AM
    Wednesday, March 13, 2013 10:09 AM
  • It is quite clear, and was before you made your latest post. That's why I provided the links. If you look at the addins, I think you'll find the work has already been done for you.

    Alternatively, if you'd like to do this without any code, you can use Word's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
    http://windowssecrets.com/forums/showthread.php/111149-Mailmerge-Tutorial-Create-Sorted-Listings-(2000-2010)
    or
    http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

    The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

    For some worked examples, see the attachments to the posts at:
    http://www.msofficeforums.com/mail-merge/9180-mail-merge-duplicate-names-but-different-dollar.html#post23345
    http://www.msofficeforums.com/mail-merge/11436-access-word-creating-list-multiple-records.html#post30327


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, March 13, 2013 10:15 AM
  • Thanks for replying too fast Paul  :)

    Bharat Bhushan

    Wednesday, March 13, 2013 10:26 AM
  • Hi Paul,
    these all links about Excel to Word , but i am working on sql and C#. So i am not able to get any data using these queries.

    Bharat Bhushan

    Thursday, March 14, 2013 8:09 AM
  • these all links about Excel to Word , but i am working on sql and C#.

    The mailmerge fields in my tutorial work with any mailmerge datasource, for the most part without requiring any VBA/C code.

    Alternatively, if you're populating a document directly via code, you don't need to use mergefields. Instead, you should define the output range (eg via a bookmark or a table) and simply populate that, adding lines/rows as needed.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Thursday, March 14, 2013 8:46 AM