Automating MailMerge via C# problems

[Tony]

Hi All,
I was wondering if you could help me out. I am calling mailmerge through a
sql call in c#; however, some of the fields do not display when the document
is displayed. Any suggestion on what I should be looking at.... I used the
same query while manually creating a mail merge and that worked...therefore,
I concluded that it couldnt be on the db end....

thanks

 

[Peter Jamieson]

If the data source is SQL Server, the "rogue fields" have Unicode data
types, you are using Word 2002 or later, and you're using OpenDataSource,
then the chances are that
a. when you connect manually, you are using OLE DB
b. when you connect via c#, you are inadvertently using ODBC

That's 4 "if"s - if you can post the strings you are passing to
OpenDataSource for the name, connection and sqlstatement parameters it might
be a bit easier to consider what might be going wrong. Also useful to know
which version of Word and what the data source is.

 

[Tony]

Hi,
I am using Word 2003.
Here is the connection string I use:
"DSN=csasearch;DATABASE=csasearch;uid=sa;pwd=cobovubu;", str,
"C:\Contract.doc"

the sql statement is

select * from vw1

----------------------
the code is:
dsName = Nothing
dsFormat = System.Type.Missing
dsConfirmConversion = System.Type.Missing
dsReadOnly = System.Type.Missing
dsLinkToSource = System.Type.Missing
dsAddToRecentFiles = System.Type.Missing
dsPasswordDocument = System.Type.Missing
dsPasswordTemplate = System.Type.Missing
dsRevert = False
dsWritePasswordDocument = System.Type.Missing
dsWritePasswordTemplate = System.Type.Missing

dsConnection = connection
dsSQLStatement = sql

dsSQLStatement1 = System.Type.Missing
dsOpenExclusive = System.Type.Missing
dsSubType = Word.WdMergeSubType.wdMergeSubTypeOLEDBText

wordDoc.MailMerge.OpenDataSource("", dsFormat, dsConfirmConversion,
dsReadOnly, dsLinkToSource, dsAddToRecentFiles, dsPasswordDocument,
dsPasswordTemplate, dsRevert, dsWritePasswordDocument, dsPasswordTemplate,
dsConnection, dsSQLStatement, dsSQLStatement1, dsOpenExclusive, dsSubType)

Thanks for your response

 

[Peter Jamieson]

As soon as you specify a DSN Word will use ODBC and you will lose Unicode
data. Even if Word happens to use the OLE Db provider for ODBC data sources,
I think you will lose the Unicode data (if that is what the problem is). I
am slightly cautious because you say it works when you connect manually
(i.e. I think you would have to use a .udl or .odc, not a DSN, for that to
work, but perhaps I am wrong).

The only way to use OLE DB (which should retain Unicode data) is to specify
a .udl or .odc file in the Name parameter. You can either use a valid .udl
or .odc that specifies the necessary connection information, or you can use
an empty .odc or .udl (e.g. create a Notepad file and put no text in it) and
specify all the connection information in the Connection parameter. But
AFAIK there is no way to avoid having that .udl or .odc. If you are
concerned about having to distribute that file, you can consider putting it
on a network drive that ican't locate the .udl or .odc at a URL such as
http:// or ftp://

Anyway, if you go the "blank .udl/.odc" route, what you probably need is a
connection string like:

Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=cobovubu;Initial Catalog=csasearch;Data Source=<put the name
of the server machine here>

You may need Persist Security Info=True

That uses the old SQL Server OLE DB provider. For the new one ("SQL Native
Client"), try using

Provider=SQLNCLI.1

(as far as I know most of the other parameters are the same). however,
whenever I have tried to use Word with the new "Native Client" and with a
logon and password rather than Windows Integrated Security, I have failed. I
don't think this is just down to the additional security configuration stuff
in newer versions of SQL Server, but maybe you will not have to face that
problem.)

Finally,

dsSubType = Word.WdMergeSubType.wdMergeSubTypeOLEDBText

is not the right subtype - you will probably be better off using
System.Type.Missing

I think this OLEDBText subtype is intended to specify that you want to open
a delimited text file using OLE DB rather than an internal converter or
ODBC, but I've never been able to verify that (The "Subtype" parameter isn't
exactly well-documented :-( )