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

[flem1]

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…

[flem1]

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

[alexey.noskov]

Hi
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.

[flem1]

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…

[alexey.noskov]

Hi
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.