×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Accessing A Crystal Report Using The Same Stored Procedure In Different Databases.

Accessing A Crystal Report Using The Same Stored Procedure In Different Databases.

Accessing A Crystal Report Using The Same Stored Procedure In Different Databases.

(OP)

I'm using C# in Visual Studio 2010 to create an interface that displays Crystal Reports already generated in 8.5 and 9.0. The reports are all based upon result sets returned from stored procedures in SQL Server 2008. Most of the stored procedures accept parameters, but I chose an example without parameters to try to simplifiy this issue. We have multiple client databases across multiple servers, but the structure of the data and the stored procedure names are consistant across the client databases. Through our legacy front end (written in Visual FoxPro) we simply change the server name, database name, user name and password and the reports run correctly across the appropriate client's data. I'm having a problem getting C# to work in that same way.

This particular example is to run a sepcific report for a specific client database. You'll see in the code that I have two different reports that I have tested. When I use the RPT that runs across a table, it works correctly, but when I run the RPT that runs across a SP, I get "The table 'MyStoredProcedure' could not be found...". Both have been tested directly through CR and work fine. Any Suggestions?

CODE --> C#

:
:

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;

:
:

private void button1_Click(object sender, EventArgs e)
{

    ReportDocument cryRpt = new ReportDocument();
    TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
    TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
    ConnectionInfo crConnectionInfo = new ConnectionInfo();
    Tables CrTables ;

//// Table - Local_Clients
    cryRpt.Load("C:\\CRTestWithTable.rpt");
//// Stored Procedure - Database Integrity
//  cryRpt.Load("C:\\CRTestWithSP.rpt");

    crConnectionInfo.ServerName = "MYSERVERNAME";
    crConnectionInfo.DatabaseName = "MYDBNAME";
    crConnectionInfo.UserID = "MYUSERNAME";
    crConnectionInfo.Password = "MYPASSWORD";

    CrTables = cryRpt.Database.Tables;
    foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
    {
        crtableLogoninfo = CrTable.LogOnInfo;
        crtableLogoninfo.ConnectionInfo = crConnectionInfo;
        CrTable.ApplyLogOnInfo(crtableLogoninfo);
    }

    crystalReportViewer1.ReportSource = cryRpt;
    crystalReportViewer1.Refresh();
}

:
: 
Thanks in advance.

RE: Accessing A Crystal Report Using The Same Stored Procedure In Different Databases.

(OP)
It took some effort, but I figured it out on my own. I'm putting the answer here for others who may have the same question. I'm a newbie to C# coding, so this may not be the best solution, but it works. I'm using a console application and pushing the report out to a PDF.

CODE --> C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main()
        {
            ReportDocument cryRpt = new ReportDocument();
            TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
            TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
            ConnectionInfo crConnectionInfo = new ConnectionInfo();

            cryRpt.Load("C:\\MyReportFolder\\MyReportName.rpt");
            crConnectionInfo.ServerName = "MyServerName";
            crConnectionInfo.DatabaseName = "MyDatabaseName";
            crConnectionInfo.UserID = "MyUserID";
            crConnectionInfo.Password = "MyPassword";
/*
 * Our Crystal RPT files were built in Crystal Reports 8.5.  We used stored procedures as the data source.
 * They defaulted to MyDatabase.dbo.Proc(MyStoredProcedure), but we needed the reports to run on a variety of 
 * databases all having the same structure, so we stripped off the "MyDatabase.".  The end result was that
 * our report location contained something that looked like "dbo.Proc(MyStoredProcedure)".  The syntax that 
 * worked for this purpose was "MyStoredProcedure;1", so the .Replace function is used to take the original
 * value of the data location and convert that to the new format when the change of database and reconnection
 * is done in this routine.
*/

            foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in cryRpt.Database.Tables)
            {
                crtableLogoninfo = CrTable.LogOnInfo;
                crtableLogoninfo.ConnectionInfo = crConnectionInfo;
                CrTable.ApplyLogOnInfo(crtableLogoninfo);
                string crStoredProcedureName = CrTable.Location.ToUpper();
                crStoredProcedureName = crStoredProcedureName.Replace("DBO.", "");
                crStoredProcedureName = crStoredProcedureName.Replace("PROC(", "");
                crStoredProcedureName = crStoredProcedureName.Replace(")", ";1");
                CrTable.Location = crStoredProcedureName;
            }

            ExportOptions CrExportOptions;
            DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
            PdfRtfWordFormatOptions CrFormatTypeOptionsPDF = new PdfRtfWordFormatOptions();
            CrDiskFileDestinationOptions.DiskFileName = "C:\\MyOutputFolder\\MyReportName.PDF";
            CrExportOptions = cryRpt.ExportOptions;
            {
                CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
                CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
                CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
                CrExportOptions.FormatOptions = CrFormatTypeOptionsPDF;
            }
            try
/*
 * The stored procedures that am calling have only one parameter named MyWildcard and I'm passing a value of
 * "ABC%" to that parameter.  More parameters can be added and passed, but this is just a sample.
*/
            {
                cryRpt.SetParameterValue("@MyWildcard", "ABC%");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            try
            {
                cryRpt.Export();
            }
            catch (Exception ex)
            {
                string MainString = "";
                MainString = ex.ToString();
                Console.WriteLine(ex.ToString());
            }
        }
    }
    // Create the derived exception class.
    class SecondLevelException : Exception
    {
        const int SecondLevelHResult = unchecked((int)0x81234567);

        // Set HResult for this exception, and include it in 
        // the exception message.
        public SecondLevelException(string message, Exception inner) :
            base(string.Format("(HRESULT:0x{1:X8}) {0}",
              message, SecondLevelHResult), inner)
        {
            HResult = SecondLevelHResult;
        }
    }

} 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close