?
Solved

Problem passing parameters/SQL Connection from C# .Net application to embedded Crystal Report

Posted on 2008-10-08
7
Medium Priority
?
3,539 Views
Last Modified: 2013-12-17
I am having trouble passing the database connection information and possibly parameters into an embedded crystal report in a C# application.  Here is the info:

I have a Crystal Report that I developed in Crystal Reports 10.  It gets its data from a stored procedure in SQL Server 2005 using two parameters.  It also contains a subreport that uses the same two parameters, but a different stored procedure.  It works with each of our 3 SQL Servers inside Crystal: Development, Test and Production.

Using Visual Studio 2005, I created a C# .NET Windows application where a user can select the entries for the two parameters, and then click submit to generate the report.  The above report is embedded in the application (I added an existing item to the windows application to embed my report).  The application is supposed to pass the parameters to the embedded CR report, then read a configuration file for the correct SQL Server, database, user ID and password, and then use that information for the connection to the database.

The application works in our development environment.  It passes the parameters and reads the database using the entered parameters and creates the report and sub report. But, then I changed the configuration file to point to the Test SQL server, and it does not work properly.  It does not throw an error, but it instead continues to use both the old parameters and SQL connection that were used when developing the report and testing it inside of Crystal.  I was hoping that by fixing the database connection problem it would also fix the parameter problem, but maybe they are two different problems.

Any help you can give would be appreciated.

//
        // this routine will get the database connections from the app.config file
        // and pass it to Crystal
        //
        internal static void SetConnection(ReportDocument report)
        {
            try
            {
                CrystalDecisions.Shared.TableLogOnInfo MyLogonInfo;
                foreach (CrystalDecisions.CrystalReports.Engine.Table MyTable in report.Database.Tables)
                {
                    MyLogonInfo = MyTable.LogOnInfo;
 
                    string sServerName = ConfigurationManager.AppSettings["ServerName"];
                    string sDatabaseName = ConfigurationManager.AppSettings["DatabaseName"];
                    string sUserID = ConfigurationManager.AppSettings["UserID"];
                    string sPassword = ConfigurationManager.AppSettings["Password"];
 
                    MyLogonInfo.ConnectionInfo.ServerName = sServerName;
                    MyLogonInfo.ConnectionInfo.DatabaseName = sDatabaseName;
                    MyLogonInfo.ConnectionInfo.UserID = sUserID;
                    MyLogonInfo.ConnectionInfo.Password = sPassword;
 
                    //MyTable.Location = MyTable.Location.Substring(MyTable.Location.LastIndexOf(".") + 1);
                    MyTable.ApplyLogOnInfo(MyLogonInfo);
 
                }
            }
            catch (Exception logonInfoException)
            {
                throw logonInfoException;
            }
        }
 
        //
        // the main routine for crystal reports!!
        // this will call the routine to set up the parameters to add to the crystal reports
        // and bind the crystal viewer to the report
        //
        private bool ConfigureCrystalReports()
        {
            bool bSuccess = false;
            try
            {
                // here put in the requested date
                string sDate = this.calDatePicker.SelectionStart.ToString();
 
                int nShowAll = 0;
                if (this.chkShowAll.Checked)
                {
                    nShowAll = 1;
                }
 
                rOnlineRequestsReport = new OnlineRequests();
                SetCurrentValuesForParameterFields(rOnlineRequestsReport, sDate, nShowAll);
                SetConnection(rOnlineRequestsReport);
                rCrystalReportViewer.ReportSource = rOnlineRequestsReport;
                bSuccess = true;
 
            }
            catch (Exception ex)
            {
                bSuccess = false;
                MessageBox.Show(this, ex.Message.ToString(), "Error", MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
            }
            return bSuccess;
        }
 
        //
        // this routine adds the parameters to the crystal report
        //
        private void SetCurrentValuesForParameterFields(ReportDocument reportDocument, string psDate, int pnShowAll)
        {
            ParameterValues currentParameterValues = new ParameterValues();
            ParameterDiscreteValue parameterDiscreteValue1 = new ParameterDiscreteValue();
            parameterDiscreteValue1.Value = psDate;
            currentParameterValues.Add(parameterDiscreteValue1);
            ParameterFieldDefinitions parameterFieldDefinitions = reportDocument.DataDefinition.ParameterFields;
            ParameterFieldDefinition parameterFieldDefinition1 = parameterFieldDefinitions[PARAMETER_FIELD_NAME_1];
            parameterFieldDefinition1.ApplyCurrentValues(currentParameterValues);
 
            ParameterDiscreteValue parameterDiscreteValue2 = new ParameterDiscreteValue();
            parameterDiscreteValue2.Value = pnShowAll.ToString();
            currentParameterValues.Add(parameterDiscreteValue2);
            ParameterFieldDefinition parameterFieldDefinition2 = parameterFieldDefinitions[PARAMETER_FIELD_NAME_2];
            parameterFieldDefinition2.ApplyCurrentValues(currentParameterValues);
 
        }

Open in new window

0
Comment
Question by:pamh_cfcausa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 22674137
You also have to set the tables for the subreport.

mlmcc
0
 

Author Comment

by:pamh_cfcausa
ID: 22677983
I'm not sure what you mean.  The main report is not accessing the test database either.  The subreport is linked inside of the main report making it part of the report, so I did not add it to the C# program.  
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22678314
It may be linked but you still have to open each subreport as a report and set the tables in the same manner.  crystal treats it as a separate report and doesn't link the tables.

Check this document starting on page 141
http://www.businessobjects.com/global/pdf/dev_zone/VS2005_Walkthroughs.pdf

mlmcc
0
Stack Overflow Podcast - Frustrating Miracles

In this podcast, Stack Overflow interviewed Linux Academy CEO/Founder, Anthony James, and got his developer story!

"Follow your passion, be prepared to work hard and sacrifice, and, above all, don't let anyone limit your dreams."  - Donovan Bailey

 

Author Comment

by:pamh_cfcausa
ID: 22687419
Thank you for the document. It was a long document, and it was helpful, but it is still not fixing my problem.  
I removed the subreport from the report, and I still cannot connect to another server, even when running with only a report.
I changed it from an embedded report to a non-embedded report, and I still cannot connect to another server.
I debugged the code, going line by line, and looked at the CR Table right after the ApplyLogOnInfo method executed, and the default server (from when I was developing the report in Crystal) was still there.  It also defaults to the parameters that were there when developing the report.
I am wondering if Visual Studio 2005 has some kind of bug in it?  It just doesn't seem to want to change to another server.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22688285
Is the date parameter a date or a string?

mlmcc
0
 

Author Comment

by:pamh_cfcausa
ID: 22703174
the data parameter is passed as a string.  As I mentioned before, it does work in my development environment as long as the non-embedded report has a datasource that is pointing to the same server/database as the config file.  
The more I try different things, the more I am convinced there is a bug In Visual Studio 2005 where it does not handle changing the datasource on non-embedded reports.  It seemed to work okay in Visual Studio 2003, but that version does not have the new crystal viewer component in it.
0
 

Accepted Solution

by:
pamh_cfcausa earned 0 total points
ID: 22730224
We have decided to leave it as a non-embedded report, and then to manually point the datasource to the correct server inside of the report on each server.  Since we only have 3 servers (development, test and production) and this is a temporary report anyway, we decided to stop wasting time trying to figure out why Visual Studio is not handling the ApplyLogOnInfo command correctly.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question