How to do a SQL statement to a mailmerge .doc??

N

nuno_fbo

Hi,

PS: Look into this close situation:
http://www.experts-exchange.com/Programming/Misc/Q_21767403.html

I was looking for information to resolve a little problem that I'm having
and I saw this topic hear and I hope if you can help me

I have a event im my page page.aspx.cs>


I tried to run the code that I writed and I have an Error in here:

docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
The message error is: {"Command failed"}

If I comment this line I don't have any problem with my process.. It merge a
new .doc, but with all data from the table intervinientes... and I want to
get specic data...

I hope to get any information about this question...

Anyone who did something like this before... Did a process in C# and merge a
document with specific data from a SQL Server data source...

How can I do a SQL command only to merge specific .doc?
That is all my event code:

void testeWord()
{
String data = DateTime.Today.ToShortDateString();
string utilizador = HttpContext.Current.User.Identity.Name.ToString();

object objMiss = Type.Missing;
object ficheiroOrigem = "C:\\Documents and Settings\\Nuno
Oliveira\\My Documents\\Nuno Oliveira\\CV\\mailmerge.doc";
object ficheiroDest = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\Nuno Oliveira\\CV\\MailMerge" + utilizador + "." + data + ".doc";
object objTrue = true;
object objFalse = false;
Microsoft.Office.Interop.Word.Document docWord;

//open Microsoft Word

Microsoft.Office.Interop.Word.ApplicationClass appWord = new
Microsoft.Office.Interop.Word.ApplicationClass();
appWord.Visible = false;


//open Template file
docWord = appWord.Documents.Open(ref ficheiroOrigem,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss);
docWord.Select();

//open datasource
object format =
Microsoft.Office.Interop.Word.WdOpenFormat.wdOpenFormatText;
string source = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\My Data Sources\\localhost mba_testes intervenientes.odc";
docWord.MailMerge.OpenDataSource(source, ref format, ref objFalse,
ref objMiss, ref objTrue,
ref objFalse, ref objMiss, ref objMiss, ref objMiss, ref
objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);


docWord.MailMerge.Destination =
Microsoft.Office.Interop.Word.WdMailMergeDestination.wdSendToNewDocument;
docWord.MailMerge.SuppressBlankLines = true;
docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
docWord.MailMerge.DataSource.FirstRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord;
docWord.MailMerge.DataSource.LastRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultLastRecord;
docWord.MailMerge.Execute(ref objFalse);


appWord.ActiveDocument.SaveAs(ref ficheiroDest,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);

docWord.Close(ref objFalse, ref objMiss, ref objMiss);

appWord.Visible = true;
appWord.ShowMe();
//appWord.Quit(null, null, null);

//Server.Transfer(@"\\localhost\C:\Documents and Settings\Nuno
Oliveira\My Documents\Nuno Oliveira\CV\rESULTADOmAILmERGE.DOC");
}

Can you help me

Thank you for your attencion...

Regards,
Nuno Oliveira
 
P

Peter Jamieson

docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
Click to expand...

First, try using a table alias in your query, e.g.

docWord.MailMerge.DataSource.QueryString = "SELECT i.[intnome] FROM
[intervenientes] i WHERE i.[intid] = 2";

The SQL /should be/ OK without this alias, but for some reason Word seems to
require it.

--
Peter Jamieson
http://tips.pjmsn.me.uk

nuno_fbo said:
Hi,

PS: Look into this close situation:
http://www.experts-exchange.com/Programming/Misc/Q_21767403.html

I was looking for information to resolve a little problem that I'm having
and I saw this topic hear and I hope if you can help me

I have a event im my page page.aspx.cs>


I tried to run the code that I writed and I have an Error in here:

docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
The message error is: {"Command failed"}

If I comment this line I don't have any problem with my process.. It merge
a
new .doc, but with all data from the table intervinientes... and I want to
get specic data...

I hope to get any information about this question...

Anyone who did something like this before... Did a process in C# and merge
a
document with specific data from a SQL Server data source...

How can I do a SQL command only to merge specific .doc?
That is all my event code:

void testeWord()
{
String data = DateTime.Today.ToShortDateString();
string utilizador = HttpContext.Current.User.Identity.Name.ToString();

object objMiss = Type.Missing;
object ficheiroOrigem = "C:\\Documents and Settings\\Nuno
Oliveira\\My Documents\\Nuno Oliveira\\CV\\mailmerge.doc";
object ficheiroDest = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\Nuno Oliveira\\CV\\MailMerge" + utilizador + "." + data +
".doc";
object objTrue = true;
object objFalse = false;
Microsoft.Office.Interop.Word.Document docWord;

//open Microsoft Word

Microsoft.Office.Interop.Word.ApplicationClass appWord = new
Microsoft.Office.Interop.Word.ApplicationClass();
appWord.Visible = false;


//open Template file
docWord = appWord.Documents.Open(ref ficheiroOrigem,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss);
docWord.Select();

//open datasource
object format =
Microsoft.Office.Interop.Word.WdOpenFormat.wdOpenFormatText;
string source = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\My Data Sources\\localhost mba_testes intervenientes.odc";
docWord.MailMerge.OpenDataSource(source, ref format, ref objFalse,
ref objMiss, ref objTrue,
ref objFalse, ref objMiss, ref objMiss, ref objMiss, ref
objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);


docWord.MailMerge.Destination =
Microsoft.Office.Interop.Word.WdMailMergeDestination.wdSendToNewDocument;
docWord.MailMerge.SuppressBlankLines = true;
docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
docWord.MailMerge.DataSource.FirstRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord;
docWord.MailMerge.DataSource.LastRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultLastRecord;
docWord.MailMerge.Execute(ref objFalse);


appWord.ActiveDocument.SaveAs(ref ficheiroDest,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref
objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);

docWord.Close(ref objFalse, ref objMiss, ref objMiss);

appWord.Visible = true;
appWord.ShowMe();
//appWord.Quit(null, null, null);

//Server.Transfer(@"\\localhost\C:\Documents and Settings\Nuno
Oliveira\My Documents\Nuno Oliveira\CV\rESULTADOmAILmERGE.DOC");
}

Can you help me

Thank you for your attencion...

Regards,
Nuno Oliveira
Click to expand...
 
N

nuno_fbo

Hi...

First of all thank you for your answer...
With alias doesn't work too... I thing that I have to find another way to do
this situations...

In the microsoft word I'm open a Data Sorce with a file like .odc. But only
for a table. Is possible do join tables and get information for 2 or more
tables?

I think that first in my aspx.cs code I have to create a method to catch the
information that I need and then create a data source, no?

I'm devoloping a financial web aplication for my University project and I
need to find a way to merge document throw my .doc templates with proposal
information...

I need help because I look so hard to know how to do this... and I'm having
dificult...

Is the code that I have the best and the right way to do this?

My aplication is able to do mail merge documents... but not to a specific
SQL Statement... and I don't know if I can get information throw .odc from 2
or more tables from my SQL Server Data Source...

I'm waiting for your feedback,

Thank you very much

Regards,
Nuno Oliveira

Peter Jamieson said:
docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
Click to expand...

First, try using a table alias in your query, e.g.

docWord.MailMerge.DataSource.QueryString = "SELECT i.[intnome] FROM
[intervenientes] i WHERE i.[intid] = 2";

The SQL /should be/ OK without this alias, but for some reason Word seems to
require it.

--
Peter Jamieson
http://tips.pjmsn.me.uk

nuno_fbo said:
Hi,

PS: Look into this close situation:
http://www.experts-exchange.com/Programming/Misc/Q_21767403.html

I was looking for information to resolve a little problem that I'm having
and I saw this topic hear and I hope if you can help me

I have a event im my page page.aspx.cs>


I tried to run the code that I writed and I have an Error in here:

docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
The message error is: {"Command failed"}

If I comment this line I don't have any problem with my process.. It merge
a
new .doc, but with all data from the table intervinientes... and I want to
get specic data...

I hope to get any information about this question...

Anyone who did something like this before... Did a process in C# and merge
a
document with specific data from a SQL Server data source...

How can I do a SQL command only to merge specific .doc?
That is all my event code:

void testeWord()
{
String data = DateTime.Today.ToShortDateString();
string utilizador = HttpContext.Current.User.Identity.Name.ToString();

object objMiss = Type.Missing;
object ficheiroOrigem = "C:\\Documents and Settings\\Nuno
Oliveira\\My Documents\\Nuno Oliveira\\CV\\mailmerge.doc";
object ficheiroDest = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\Nuno Oliveira\\CV\\MailMerge" + utilizador + "." + data +
".doc";
object objTrue = true;
object objFalse = false;
Microsoft.Office.Interop.Word.Document docWord;

//open Microsoft Word

Microsoft.Office.Interop.Word.ApplicationClass appWord = new
Microsoft.Office.Interop.Word.ApplicationClass();
appWord.Visible = false;


//open Template file
docWord = appWord.Documents.Open(ref ficheiroOrigem,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss);
docWord.Select();

//open datasource
object format =
Microsoft.Office.Interop.Word.WdOpenFormat.wdOpenFormatText;
string source = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\My Data Sources\\localhost mba_testes intervenientes.odc";
docWord.MailMerge.OpenDataSource(source, ref format, ref objFalse,
ref objMiss, ref objTrue,
ref objFalse, ref objMiss, ref objMiss, ref objMiss, ref
objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);


docWord.MailMerge.Destination =
Microsoft.Office.Interop.Word.WdMailMergeDestination.wdSendToNewDocument;
docWord.MailMerge.SuppressBlankLines = true;
docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
docWord.MailMerge.DataSource.FirstRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord;
docWord.MailMerge.DataSource.LastRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultLastRecord;
docWord.MailMerge.Execute(ref objFalse);


appWord.ActiveDocument.SaveAs(ref ficheiroDest,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref
objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);

docWord.Close(ref objFalse, ref objMiss, ref objMiss);

appWord.Visible = true;
appWord.ShowMe();
//appWord.Quit(null, null, null);

//Server.Transfer(@"\\localhost\C:\Documents and Settings\Nuno
Oliveira\My Documents\Nuno Oliveira\CV\rESULTADOmAILmERGE.DOC");
}

Can you help me

Thank you for your attencion...

Regards,
Nuno Oliveira
Click to expand...
Click to expand...
 
P

Peter Jamieson

I just checked again here and
a. it works OK here
b. I definitely need the table alias.

However, at the moment I am assuming that
a. your OpenDataSource is working OK. Can you verify that? The problem is
that the number of parameters varies depending on the version of Word, and I
do have not checked your statement in detail. Incidentally, if the .odc was
created using Word's user interface, you should be able to provide /just/
the .odc file pathname to OpenDataSource. The table opened will be the one
specified in the .odc. (NB, you should also be able to open your .odc in
Internet Explorer and see the table data that is specified in there).
b. the tables/views you are trying to open can be found on the connection
that you specified in the .odc. If, for example, the table is on a different
Server (or instance) from the one specified in the .odc, changing
QueryString will never work. If the table is on the same server but in a
different database, you can qualify the table name in the usual way (e.g.
use
c. you are using Windows Integrated Security (i.e. you do not need a
separate login/password to access this table). if this is not the case, in
my experience you may not be able to retrieve SQL Server data at all with
the new SQL Server 2005 native client, and you will have to specify security
info. in your OpenDataSource or .odc to get them to work even with the older
SQL Server provider.

[databasename].[dbo].[tablename]

<<
In the microsoft word I'm open a Data Sorce with a file like .odc. But
only
for a table. Is possible do join tables and get information for 2 or more
tables?
Click to expand...

Not in the .odc, as far as I know. Since it is possible to specify SQL text
in a .odc file (for example I think .odc files created in Excel let you do
that) this is probably a shortcoming of Word.

You /should/ be able to do this by setting QueryString to the query you
want.

You can also override the table in the .odc by specifying an SQL query in
the OpenDataSource. There are two parameters -
SQLStatement
and
SQLStatement1

Together, they make up the complete SQL Statement. i.e. Word builds the
statement from

SQLStatement + SQLStatement1

Each parameter is limited to 255 characters, which suggests that the overall
statement can be up to 510 characters, but you may find that there is an
additional limitation.

There are alternatives to using a .odc with connection info, e.g.
a. you can try uing a .udl file (the type of file created by the old
DataLink editor). .udl files do not let you specify a table so you will have
to provide a query in OpenDataSource
b. you can use a completely empty .odc file (i.e. and empty text file). In
this case you have to provide all the connection info. in the Connection
parameter of the OpenDataSource and a query in SQLStatement/SQLStatement1


--
Peter Jamieson
http://tips.pjmsn.me.uk

nuno_fbo said:
Hi...

First of all thank you for your answer...
With alias doesn't work too... I thing that I have to find another way to
do
this situations...

In the microsoft word I'm open a Data Sorce with a file like .odc. But
only
for a table. Is possible do join tables and get information for 2 or more
tables?

I think that first in my aspx.cs code I have to create a method to catch
the
information that I need and then create a data source, no?

I'm devoloping a financial web aplication for my University project and I
need to find a way to merge document throw my .doc templates with proposal
information...

I need help because I look so hard to know how to do this... and I'm
having
dificult...

Is the code that I have the best and the right way to do this?

My aplication is able to do mail merge documents... but not to a specific
SQL Statement... and I don't know if I can get information throw .odc from
2
or more tables from my SQL Server Data Source...

I'm waiting for your feedback,

Thank you very much

Regards,
Nuno Oliveira

Peter Jamieson said:
docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
Click to expand...

First, try using a table alias in your query, e.g.

docWord.MailMerge.DataSource.QueryString = "SELECT i.[intnome] FROM
[intervenientes] i WHERE i.[intid] = 2";

The SQL /should be/ OK without this alias, but for some reason Word seems
to
require it.

--
Peter Jamieson
http://tips.pjmsn.me.uk

nuno_fbo said:
Hi,

PS: Look into this close situation:
http://www.experts-exchange.com/Programming/Misc/Q_21767403.html

I was looking for information to resolve a little problem that I'm
having
and I saw this topic hear and I hope if you can help me

I have a event im my page page.aspx.cs>


I tried to run the code that I writed and I have an Error in here:

docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
The message error is: {"Command failed"}

If I comment this line I don't have any problem with my process.. It
merge
a
new .doc, but with all data from the table intervinientes... and I want
to
get specic data...

I hope to get any information about this question...

Anyone who did something like this before... Did a process in C# and
merge
a
document with specific data from a SQL Server data source...

How can I do a SQL command only to merge specific .doc?
That is all my event code:

void testeWord()
{
String data = DateTime.Today.ToShortDateString();
string utilizador = HttpContext.Current.User.Identity.Name.ToString();

object objMiss = Type.Missing;
object ficheiroOrigem = "C:\\Documents and Settings\\Nuno
Oliveira\\My Documents\\Nuno Oliveira\\CV\\mailmerge.doc";
object ficheiroDest = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\Nuno Oliveira\\CV\\MailMerge" + utilizador + "." + data +
".doc";
object objTrue = true;
object objFalse = false;
Microsoft.Office.Interop.Word.Document docWord;

//open Microsoft Word

Microsoft.Office.Interop.Word.ApplicationClass appWord = new
Microsoft.Office.Interop.Word.ApplicationClass();
appWord.Visible = false;


//open Template file
docWord = appWord.Documents.Open(ref ficheiroOrigem,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss);
docWord.Select();

//open datasource
object format =
Microsoft.Office.Interop.Word.WdOpenFormat.wdOpenFormatText;
string source = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\My Data Sources\\localhost mba_testes intervenientes.odc";
docWord.MailMerge.OpenDataSource(source, ref format, ref objFalse,
ref objMiss, ref objTrue,
ref objFalse, ref objMiss, ref objMiss, ref objMiss, ref
objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);


docWord.MailMerge.Destination =
Microsoft.Office.Interop.Word.WdMailMergeDestination.wdSendToNewDocument;
docWord.MailMerge.SuppressBlankLines = true;
docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
docWord.MailMerge.DataSource.FirstRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord;
docWord.MailMerge.DataSource.LastRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultLastRecord;
docWord.MailMerge.Execute(ref objFalse);


appWord.ActiveDocument.SaveAs(ref ficheiroDest,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref
objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);

docWord.Close(ref objFalse, ref objMiss, ref objMiss);

appWord.Visible = true;
appWord.ShowMe();
//appWord.Quit(null, null, null);

//Server.Transfer(@"\\localhost\C:\Documents and Settings\Nuno
Oliveira\My Documents\Nuno Oliveira\CV\rESULTADOmAILmERGE.DOC");
}

Can you help me

Thank you for your attencion...

Regards,
Nuno Oliveira
Click to expand...
Click to expand...
Click to expand...
 
N

nuno_fbo

Hi...

I resoved my problem... I made a SQL Statement and put the string in the
right paramanter of docWord.MailMerge.OpenData...

Such a big fight for a little problem... know it's working good...

Do you know how can I get a .doc file in aspx.cs and show in a browser... It's
possible to do without ActiveX?

I want to make it possible to open the .doc on the client side...

After the aplication do the mail merge and generate the .doc file I want to
show the result in the browser... know I only save the new file and I can
open the .doc file... but I want to show in the client side...

I'm trying to do some tests... know I don't having sucess...

Give a look... I'm triyng a lot of things and this information don't make
sence, can you help me with you experience... If you can I appreciate...

//abrir .doc no browser
//string url = "http://localhost/";
//WebClient browser = new WebClient();
//UTF8Encoding enc = new UTF8Encoding();


//appWord.ActiveDocument.

docWord.Close(ref objFalse, ref objMiss, ref objMiss);

string browser = "http://localhost\\C:\\Documents and Settings\\Nuno
Oliveira\\My Documents\\Nuno Oliveira\\CV\\MailMerge" + utilizador + "." +
data + ".doc";

Page.ClientScript.RegisterStartupScript(GetType(), "test",
"window.open('" + browser +
"','_blank','Height=400,Width=700,menubar=No,toolbar=no,scrollbars=yes');");

// appWord.Visible = true;
//appWord.ShowMe();
//appWord.Quit(null, null, null);

//Server.Transfer("http://localhost\\C:\\Documents and
Settings\\Nuno Oliveira\\My Documents\\Nuno Oliveira\\CV\\MailMerge" +
utilizador + "." + data + ".doc");

//http://localhost\\C:\\Documents and Settings


Thanks a lot,
Regards,
Nuno Oliveira


Peter Jamieson said:
I just checked again here and
a. it works OK here
b. I definitely need the table alias.

However, at the moment I am assuming that
a. your OpenDataSource is working OK. Can you verify that? The problem is
that the number of parameters varies depending on the version of Word, and I
do have not checked your statement in detail. Incidentally, if the .odc was
created using Word's user interface, you should be able to provide /just/
the .odc file pathname to OpenDataSource. The table opened will be the one
specified in the .odc. (NB, you should also be able to open your .odc in
Internet Explorer and see the table data that is specified in there).
b. the tables/views you are trying to open can be found on the connection
that you specified in the .odc. If, for example, the table is on a different
Server (or instance) from the one specified in the .odc, changing
QueryString will never work. If the table is on the same server but in a
different database, you can qualify the table name in the usual way (e.g.
use
c. you are using Windows Integrated Security (i.e. you do not need a
separate login/password to access this table). if this is not the case, in
my experience you may not be able to retrieve SQL Server data at all with
the new SQL Server 2005 native client, and you will have to specify security
info. in your OpenDataSource or .odc to get them to work even with the older
SQL Server provider.

[databasename].[dbo].[tablename]

<<
In the microsoft word I'm open a Data Sorce with a file like .odc. But
only
for a table. Is possible do join tables and get information for 2 or more
tables?
Click to expand...

Not in the .odc, as far as I know. Since it is possible to specify SQL text
in a .odc file (for example I think .odc files created in Excel let you do
that) this is probably a shortcoming of Word.

You /should/ be able to do this by setting QueryString to the query you
want.

You can also override the table in the .odc by specifying an SQL query in
the OpenDataSource. There are two parameters -
SQLStatement
and
SQLStatement1

Together, they make up the complete SQL Statement. i.e. Word builds the
statement from

SQLStatement + SQLStatement1

Each parameter is limited to 255 characters, which suggests that the overall
statement can be up to 510 characters, but you may find that there is an
additional limitation.

There are alternatives to using a .odc with connection info, e.g.
a. you can try uing a .udl file (the type of file created by the old
DataLink editor). .udl files do not let you specify a table so you will have
to provide a query in OpenDataSource
b. you can use a completely empty .odc file (i.e. and empty text file). In
this case you have to provide all the connection info. in the Connection
parameter of the OpenDataSource and a query in SQLStatement/SQLStatement1


--
Peter Jamieson
http://tips.pjmsn.me.uk

nuno_fbo said:
Hi...

First of all thank you for your answer...
With alias doesn't work too... I thing that I have to find another way to
do
this situations...

In the microsoft word I'm open a Data Sorce with a file like .odc. But
only
for a table. Is possible do join tables and get information for 2 or more
tables?

I think that first in my aspx.cs code I have to create a method to catch
the
information that I need and then create a data source, no?

I'm devoloping a financial web aplication for my University project and I
need to find a way to merge document throw my .doc templates with proposal
information...

I need help because I look so hard to know how to do this... and I'm
having
dificult...

Is the code that I have the best and the right way to do this?

My aplication is able to do mail merge documents... but not to a specific
SQL Statement... and I don't know if I can get information throw .odc from
2
or more tables from my SQL Server Data Source...

I'm waiting for your feedback,

Thank you very much

Regards,
Nuno Oliveira

Peter Jamieson said:
docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";

First, try using a table alias in your query, e.g.

docWord.MailMerge.DataSource.QueryString = "SELECT i.[intnome] FROM
[intervenientes] i WHERE i.[intid] = 2";

The SQL /should be/ OK without this alias, but for some reason Word seems
to
require it.

--
Peter Jamieson
http://tips.pjmsn.me.uk

Hi,

PS: Look into this close situation:
http://www.experts-exchange.com/Programming/Misc/Q_21767403.html

I was looking for information to resolve a little problem that I'm
having
and I saw this topic hear and I hope if you can help me

I have a event im my page page.aspx.cs>


I tried to run the code that I writed and I have an Error in here:

docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
The message error is: {"Command failed"}

If I comment this line I don't have any problem with my process.. It
merge
a
new .doc, but with all data from the table intervinientes... and I want
to
get specic data...

I hope to get any information about this question...

Anyone who did something like this before... Did a process in C# and
merge
a
document with specific data from a SQL Server data source...

How can I do a SQL command only to merge specific .doc?
That is all my event code:

void testeWord()
{
String data = DateTime.Today.ToShortDateString();
string utilizador = HttpContext.Current.User.Identity.Name.ToString();

object objMiss = Type.Missing;
object ficheiroOrigem = "C:\\Documents and Settings\\Nuno
Oliveira\\My Documents\\Nuno Oliveira\\CV\\mailmerge.doc";
object ficheiroDest = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\Nuno Oliveira\\CV\\MailMerge" + utilizador + "." + data +
".doc";
object objTrue = true;
object objFalse = false;
Microsoft.Office.Interop.Word.Document docWord;

//open Microsoft Word

Microsoft.Office.Interop.Word.ApplicationClass appWord = new
Microsoft.Office.Interop.Word.ApplicationClass();
appWord.Visible = false;


//open Template file
docWord = appWord.Documents.Open(ref ficheiroOrigem,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss);
docWord.Select();

//open datasource
object format =
Microsoft.Office.Interop.Word.WdOpenFormat.wdOpenFormatText;
string source = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\My Data Sources\\localhost mba_testes intervenientes.odc";
docWord.MailMerge.OpenDataSource(source, ref format, ref objFalse,
ref objMiss, ref objTrue,
ref objFalse, ref objMiss, ref objMiss, ref objMiss, ref
objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);


docWord.MailMerge.Destination =
Microsoft.Office.Interop.Word.WdMailMergeDestination.wdSendToNewDocument;
docWord.MailMerge.SuppressBlankLines = true;
docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
docWord.MailMerge.DataSource.FirstRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord;
docWord.MailMerge.DataSource.LastRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultLastRecord;
docWord.MailMerge.Execute(ref objFalse);


appWord.ActiveDocument.SaveAs(ref ficheiroDest,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref
objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);

docWord.Close(ref objFalse, ref objMiss, ref objMiss);

appWord.Visible = true;
appWord.ShowMe();
//appWord.Quit(null, null, null);

//Server.Transfer(@"\\localhost\C:\Documents and Settings\Nuno
Oliveira\My Documents\Nuno Oliveira\CV\rESULTADOmAILmERGE.DOC");
}

Can you help me

Thank you for your attencion...

Regards,
Nuno Oliveira
Click to expand...
Click to expand...
Click to expand...
 
P

Peter Jamieson

<<
Do you know how can I get a .doc file in aspx.cs and show in a browser...
It's
possible to do without ActiveX?

I want to make it possible to open the .doc on the client side...
No, I don't know how to do that, sorry.

You may be better off asking this question in (say) an ASP or IIS group.

--
Peter Jamieson
http://tips.pjmsn.me.uk

nuno_fbo said:
Hi...

I resoved my problem... I made a SQL Statement and put the string in the
right paramanter of docWord.MailMerge.OpenData...

Such a big fight for a little problem... know it's working good...

Do you know how can I get a .doc file in aspx.cs and show in a browser...
It's
possible to do without ActiveX?

I want to make it possible to open the .doc on the client side...

After the aplication do the mail merge and generate the .doc file I want
to
show the result in the browser... know I only save the new file and I can
open the .doc file... but I want to show in the client side...

I'm trying to do some tests... know I don't having sucess...

Give a look... I'm triyng a lot of things and this information don't make
sence, can you help me with you experience... If you can I appreciate...

//abrir .doc no browser
//string url = "http://localhost/";
//WebClient browser = new WebClient();
//UTF8Encoding enc = new UTF8Encoding();


//appWord.ActiveDocument.

docWord.Close(ref objFalse, ref objMiss, ref objMiss);

string browser = "http://localhost\\C:\\Documents and
Settings\\Nuno
Oliveira\\My Documents\\Nuno Oliveira\\CV\\MailMerge" + utilizador + "." +
data + ".doc";

Page.ClientScript.RegisterStartupScript(GetType(), "test",
"window.open('" + browser +
"','_blank','Height=400,Width=700,menubar=No,toolbar=no,scrollbars=yes');");

// appWord.Visible = true;
//appWord.ShowMe();
//appWord.Quit(null, null, null);

//Server.Transfer("http://localhost\\C:\\Documents and
Settings\\Nuno Oliveira\\My Documents\\Nuno Oliveira\\CV\\MailMerge" +
utilizador + "." + data + ".doc");

//http://localhost\\C:\\Documents and Settings


Thanks a lot,
Regards,
Nuno Oliveira


Peter Jamieson said:
I just checked again here and
a. it works OK here
b. I definitely need the table alias.

However, at the moment I am assuming that
a. your OpenDataSource is working OK. Can you verify that? The problem
is
that the number of parameters varies depending on the version of Word,
and I
do have not checked your statement in detail. Incidentally, if the .odc
was
created using Word's user interface, you should be able to provide /just/
the .odc file pathname to OpenDataSource. The table opened will be the
one
specified in the .odc. (NB, you should also be able to open your .odc in
Internet Explorer and see the table data that is specified in there).
b. the tables/views you are trying to open can be found on the
connection
that you specified in the .odc. If, for example, the table is on a
different
Server (or instance) from the one specified in the .odc, changing
QueryString will never work. If the table is on the same server but in a
different database, you can qualify the table name in the usual way (e.g.
use
c. you are using Windows Integrated Security (i.e. you do not need a
separate login/password to access this table). if this is not the case,
in
my experience you may not be able to retrieve SQL Server data at all with
the new SQL Server 2005 native client, and you will have to specify
security
info. in your OpenDataSource or .odc to get them to work even with the
older
SQL Server provider.

[databasename].[dbo].[tablename]

<<
In the microsoft word I'm open a Data Sorce with a file like .odc. But
only
for a table. Is possible do join tables and get information for 2 or
more
tables?
Click to expand...

Not in the .odc, as far as I know. Since it is possible to specify SQL
text
in a .odc file (for example I think .odc files created in Excel let you
do
that) this is probably a shortcoming of Word.

You /should/ be able to do this by setting QueryString to the query you
want.

You can also override the table in the .odc by specifying an SQL query in
the OpenDataSource. There are two parameters -
SQLStatement
and
SQLStatement1

Together, they make up the complete SQL Statement. i.e. Word builds the
statement from

SQLStatement + SQLStatement1

Each parameter is limited to 255 characters, which suggests that the
overall
statement can be up to 510 characters, but you may find that there is an
additional limitation.

There are alternatives to using a .odc with connection info, e.g.
a. you can try uing a .udl file (the type of file created by the old
DataLink editor). .udl files do not let you specify a table so you will
have
to provide a query in OpenDataSource
b. you can use a completely empty .odc file (i.e. and empty text file).
In
this case you have to provide all the connection info. in the Connection
parameter of the OpenDataSource and a query in SQLStatement/SQLStatement1


--
Peter Jamieson
http://tips.pjmsn.me.uk

nuno_fbo said:
Hi...

First of all thank you for your answer...
With alias doesn't work too... I thing that I have to find another way
to
do
this situations...

In the microsoft word I'm open a Data Sorce with a file like .odc. But
only
for a table. Is possible do join tables and get information for 2 or
more
tables?

I think that first in my aspx.cs code I have to create a method to
catch
the
information that I need and then create a data source, no?

I'm devoloping a financial web aplication for my University project
and I
need to find a way to merge document throw my .doc templates with
proposal
information...

I need help because I look so hard to know how to do this... and I'm
having
dificult...

Is the code that I have the best and the right way to do this?

My aplication is able to do mail merge documents... but not to a
specific
SQL Statement... and I don't know if I can get information throw .odc
from
2
or more tables from my SQL Server Data Source...

I'm waiting for your feedback,

Thank you very much

Regards,
Nuno Oliveira

:

docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";

First, try using a table alias in your query, e.g.

docWord.MailMerge.DataSource.QueryString = "SELECT i.[intnome] FROM
[intervenientes] i WHERE i.[intid] = 2";

The SQL /should be/ OK without this alias, but for some reason Word
seems
to
require it.

--
Peter Jamieson
http://tips.pjmsn.me.uk

Hi,

PS: Look into this close situation:
http://www.experts-exchange.com/Programming/Misc/Q_21767403.html

I was looking for information to resolve a little problem that I'm
having
and I saw this topic hear and I hope if you can help me

I have a event im my page page.aspx.cs>


I tried to run the code that I writed and I have an Error in here:

docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
The message error is: {"Command failed"}

If I comment this line I don't have any problem with my process.. It
merge
a
new .doc, but with all data from the table intervinientes... and I
want
to
get specic data...

I hope to get any information about this question...

Anyone who did something like this before... Did a process in C# and
merge
a
document with specific data from a SQL Server data source...

How can I do a SQL command only to merge specific .doc?
That is all my event code:

void testeWord()
{
String data = DateTime.Today.ToShortDateString();
string utilizador =
HttpContext.Current.User.Identity.Name.ToString();

object objMiss = Type.Missing;
object ficheiroOrigem = "C:\\Documents and Settings\\Nuno
Oliveira\\My Documents\\Nuno Oliveira\\CV\\mailmerge.doc";
object ficheiroDest = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\Nuno Oliveira\\CV\\MailMerge" + utilizador + "." + data +
".doc";
object objTrue = true;
object objFalse = false;
Microsoft.Office.Interop.Word.Document docWord;

//open Microsoft Word

Microsoft.Office.Interop.Word.ApplicationClass appWord = new
Microsoft.Office.Interop.Word.ApplicationClass();
appWord.Visible = false;


//open Template file
docWord = appWord.Documents.Open(ref ficheiroOrigem,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss,
ref objMiss);
docWord.Select();

//open datasource
object format =
Microsoft.Office.Interop.Word.WdOpenFormat.wdOpenFormatText;
string source = "C:\\Documents and Settings\\Nuno Oliveira\\My
Documents\\My Data Sources\\localhost mba_testes
intervenientes.odc";
docWord.MailMerge.OpenDataSource(source, ref format, ref objFalse,
ref objMiss, ref objTrue,
ref objFalse, ref objMiss, ref objMiss, ref objMiss, ref
objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);


docWord.MailMerge.Destination =
Microsoft.Office.Interop.Word.WdMailMergeDestination.wdSendToNewDocument;
docWord.MailMerge.SuppressBlankLines = true;
docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM
[intervenientes] WHERE [intid] = 2";
docWord.MailMerge.DataSource.FirstRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord;
docWord.MailMerge.DataSource.LastRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.wdDefaultLastRecord;
docWord.MailMerge.Execute(ref objFalse);


appWord.ActiveDocument.SaveAs(ref ficheiroDest,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref
objMiss,
ref objMiss, ref objMiss, ref objMiss, ref objMiss);

docWord.Close(ref objFalse, ref objMiss, ref objMiss);

appWord.Visible = true;
appWord.ShowMe();
//appWord.Quit(null, null, null);

//Server.Transfer(@"\\localhost\C:\Documents and Settings\Nuno
Oliveira\My Documents\Nuno Oliveira\CV\rESULTADOmAILmERGE.DOC");
}

Can you help me

Thank you for your attencion...

Regards,
Nuno Oliveira
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to get specific data from a datasorce to a mailmerge doc? SQL 1
Mailmerge hangs at OpenDataSource 3
Microsoft.Office.Interop.Word stops working after inserting a pict 1
How to dismiass Microsoft Office Word "Do you want to save the cha 2
How to determine if a .doc file is already open? 2
Details I have learned writing a Word Add-in 0
MailMerge Issues - doc 'forgets' source data, other issues 2
Protect Word Doc 1

Top