dynamic sqlserver query on user editable mail merge document

[mad4ever]

Hello !

I'm developing an application where users can create companies as new items.
When a company is created, a new folder named as the company is created too.

In that folder, a word document is copied from a shared folder. This
document is a mail merge master document.

That folder has a subfolder where the data source for the mail merge is
copied from the shared folder too. This document is a .odc file.

The folders tree is as follows:
Company Folder -> Documents (.doc) -> DataSources (.odc)

I need to modify the query located into the .odc file, so when the user
opens the .doc file, it should display filtered results.

The user can modify as well the master document.

I've tried to acomplish this situation using word automation, but when the
user edits the document and then tries to save the changes, word shows an
error related to the database.

Here is the code that i've used:
-----------------------------------------------------------------

/// <summary>
/// this method uses word automation to open a mail merge document
that shows certificates text.
/// The data source is filtered to display only the certificate
whose id is received in the last method parameter.
/// </summary>
/// <param name="wordFilename">Path to the word mail merge
document.</param>
/// <param name="dsFilename">Path to the datasource ( .odc
file)</param>
/// <param name="idCert">Certificate's id.</param>
private static void OpenDocWord(object wordFilename, string
dsFilename, int idCert)
{
object oTrue = true;
object oFalse = false;
object objMiss = System.Reflection.Missing.Value;
object oPass = "........";
object oConnStr = ".......";
object oQueryStr = String.Format("SELECT * FROM
\"ImprimirCertificado\" WHERE \"IDCertificado\" = {0}", idCert);
object oQueryStr2 = String.Format("SELECT * FROM
\"ImprimirCertificado\"");
object oWhereStr = String.Format("\"IDCertificado\" = {0}",
idCert);
object oDSFilename = dsFilename;
object oHeaderRecord =
"IDCert;ActionNum;Name;Address;Company;Year;Month";

Word.Application oWord = new
Microsoft.Office.Interop.Word.Application();
Word.Document wordDoc;

wordDoc = oWord.Documents.Open(ref wordFilename, ref objMiss,
ref oFalse, ref objMiss, ref oPass, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss);
wordDoc.Select();

wordDoc.MailMerge.CreateDataSource(ref oDSFilename, ref objMiss,
ref objMiss, ref oHeaderRecord, ref objMiss, ref oQueryStr2, ref oWhereStr,
ref oConnStr, ref oTrue);

wordDoc.MailMerge.OpenDataSource(dsFilename, ref objMiss, ref
objMiss, ref oFalse, ref oTrue, ref objMiss, ref oPass, ref oPass, ref
objMiss, ref objMiss, ref objMiss, ref oConnStr, ref oQueryStr, ref objMiss,
ref objMiss, ref objMiss);


wordDoc.MailMerge.ViewMailMergeFieldCodes =
Convert.ToInt32(false);

oWord.Visible = true;

}

 

[Peter Jamieson]

Reply posted in the mailmerge/fields group.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv