We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Need help in using Sql stored procedure as a data source in mail merge

Hi, I am new to aspose and have read the mail merge documentation of aspose. Problem is, I'm not so sure how to use a stored procedure as a data source in mail merge.

This is the stored procedure code btw:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Wilfredo A. Estrada III

-- Create date: 5/7/2008

-- Description: Mail merge

-- =============================================

ALTER PROCEDURE CBI_ParticipantMailMerge

-- Add the parameters for the stored procedure here

@ActivityID nvarchar (8)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT dbo.Activity.Description,

dbo.Title.Description AS Title,

dbo.Person.FirstName,

dbo.Person.LastName,

dbo.Person.Initials,

dbo.Organization.Name AS CompanyName,

dbo.Employee.JobDescription,

dbo.Country.Description AS Country

FROM dbo.Organization

INNER JOIN dbo.Participant

INNER JOIN dbo.Employee

INNER JOIN dbo.Person

ON dbo.Employee.PersonID = dbo.Person.PersonID

AND dbo.Employee.Status = dbo.Person.Status

ON dbo.Participant.ObjODPID = dbo.Person.PersonID

AND dbo.Participant.Status = dbo.Person.Status

ON dbo.Organization.OrganizationID = dbo.Employee.ObjID

AND dbo.Organization.ObjType = dbo.Employee.ObjType

AND dbo.Organization.Status = dbo.Employee.Status

INNER JOIN dbo.Activity

ON dbo.Participant.ObjPrActID = dbo.Activity.ActivityID

AND dbo.Participant.ObjPrActType = dbo.Activity.ObjType

AND dbo.Participant.Status = dbo.Activity.Status

INNER JOIN dbo.Title

ON dbo.Person.TitleID = dbo.Title.TitleID

INNER JOIN dbo.Country

ON dbo.Person.CountryID = dbo.Country.CountryId

WHERE dbo.Activity.ActivityID = @ActivityID

AND (dbo.Participant.ObjODPType = N'4')

AND dbo.Activity.Status = 'A'

AND dbo.Person.Status = 'A'

END

GO

Any help would be greatly appreciated, Thank you..

Oh, I am using ASP.Net C# in this project btw…

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thanks for your request. Stored procedure returns table that you can use to perform mail merge. Here is example for you.

Here is my stored procedure.

ALTER PROCEDURE [dbo].[usp_GetClients]

--No parameters

--Procedure will return all rows from table

--You can use procedure with parameters

AS

BEGIN

--Note that field names in the returned table should be same

--as names of mergefields in the document

SELECT

[FirstName] AS FirstName

, [LastName] AS LastName

, [Company] AS Company

FROM [Clients]

END

Here is my code:

//Specify connection string

string connectionString = "server=Web1;database=myDB;uid=sa;pwd=mypwd;";

//Create connction

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);

//Create SQL command

string commandString = "usp_GetClients";

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(commandString, conn);

//Specify that command is stored procedure

command.CommandType = CommandType.StoredProcedure;

//You can also specify parameters.

//command.Parameters.AddWithValue("myParameter", "Value of my parameter");

//Create dataset where will be stored returned data

DataSet ds = new DataSet();

//Create data adapter that will be used to fill dataset

System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(command);

//Open connection

conn.Open();

//Read dataset

adapter.Fill(ds);

conn.Close();

//We should specify name of Table that will be used to find region in the document

//If you use simple mail merge then you don't need to specify name

DataTable table = new DataTable();

if (ds.Tables.Count > 0)

{

table = ds.Tables[0];

table.TableName = "Clients";

}

//Open template document

Document doc = new Document(@"Test192\in.doc");

//Execute mail merge with regions.

doc.MailMerge.ExecuteWithRegions(table);

//Save document

doc.Save(@"Test192\out.doc");

Also see attached documents (template and output).

Hope this helps.

Best regards.

Thanks for the reply because I think it worked.. but now i'm getting this error message:

"Access to the path 'C:\Documents and Settings\waestrada\My DocumentsSaved.doc' is denied."

Please help again.. Thank you..

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thanks for your request. If you are developing ASP.NET application then most probably you don’t have permissions to read files outside the root folder of your application. Please try copying your template to the root folder of your site and use the following code to open it.

Document doc = new Document(Server.MapPath("myTemplate.doc"));

Also please make sure that template is not opened by another program.

Best regards.