Database Logon Fail
Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17194
Printed Date: 26 Apr 2024 at 12:29pm
Topic: Database Logon Fail
Posted By: rushelp
Subject: Database Logon Fail
Date Posted: 02 Aug 2012 at 6:34am
Hi,
I have a crystal report file which generates reports for each clients monthly. Each month the crystal report file should be refreshed. I could manually refresh the file, however I have many crystal reports, it's pain to refresh all of them manually on the first of each month.
After I did some research I found out, if I uncheck the option "save data with report" it should refresh the crystal file.
I have a program that converts crystal to pdf. If I uncheck that option and try to convert the file I get the following error: "Database logon failed".
Any help on how I can resolve this issue?
|
Replies:
Posted By: hilfy
Date Posted: 02 Aug 2012 at 9:00am
|
Your program would have to be updated to "refresh" the report prior to trying to export it. -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: rushelp
Date Posted: 02 Aug 2012 at 9:28am
Posted By: hilfy
Date Posted: 02 Aug 2012 at 9:40am
|
I assume you're already loading the report into a ReportDocument to export to PDF. You would have to walk through the ReportDocument.DataSourceConnections collection and set the user ID and password for each connection. If your report contains subreports, you'll also have to walk through the ReportDocument.Subreports collection, casting each subreport as a ReportDocument and setting the connections there as well. From there ExportToPDF should refresh the report prior to export. -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: rushelp
Date Posted: 02 Aug 2012 at 11:01am
So this is how I load my file. And yes, I did not had my connections to database. I added.
So this is what I have, however I get a dofferemt error.
string fileName = Path.GetFileName(reportPath);
ReportDocument report = new ReportDocument();
report.Load(reportPath);
//added this line
report.SetDatabaseLogon("User", "Pass", "Server", "Database");
"Missing parameter values".
|
Posted By: hilfy
Date Posted: 03 Aug 2012 at 7:07am
|
If your report has parameters, you'll have to set them in code as well. Use ReportDocument.Parameter fields to do this. -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: rushelp
Date Posted: 03 Aug 2012 at 7:13am
|
That's what I've been trying to figure out all day. I do not pass any parameters from C# to Crystsal.
|
Posted By: hilfy
Date Posted: 03 Aug 2012 at 7:34am
|
But if you had parameters when you were manually running the report to save the data, what were they? -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: rushelp
Date Posted: 03 Aug 2012 at 7:41am
Going back to my first posts in here...
I had one big report that contained report for hundred clients. I needed somehow to separate it. I wrote a formula in crystal file where in that big report I can specify the client by it's ID. So what I was doing is running big report each time and when I get the client I need I saved it as to get one crystal file for that client, and then went to different client. That's how I get the report for all my clients.
Now when I think, the reason it's asking for the parameter is that I was entering them in crystal reports when I was doing {ContactInfo.ClientID} = {My_Parameter}.
And manually choosing parameter from the list I had. It's working this way, but only when it comes to REFRESH reports I get this.
|
Posted By: hilfy
Date Posted: 03 Aug 2012 at 7:57am
|
You're getting this because you're no longer working with saved data. You will have to set this parameter in code. However, you could always load a DataSet that contains all of the client ID's you want to run the report for, walk through the data set and do a separate export for each client instead of running just one big report. -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: rushelp
Date Posted: 03 Aug 2012 at 8:04am
I do have one crystal report viewer which shows all the clients by their ID. Once I open the program, it asks which ID I want to show the report for in the viewer. I select all of them. However, the view and the files that getting generated into pdf are not tied together. I don't know what to do then.
What I'm doing with reports for each checkbox is getting the path.
private void save_Click(object sender, EventArgs e)
{
var reportPaths =
from ctrl in panel1.Controls.OfType<CheckBox>()
let reportPath = ctrl.Tag as string
where ctrl.Checked && !string.IsNullOrEmpty(reportPath)
select reportPath;
foreach (string reportPath in reportPaths)
{
ExportReport(reportPath);
}
}
Then this is how I export.
private void ExportReport(string reportPath)
{
//uploads crystal file
string fileName = Path.GetFileName(reportPath);
ReportDocument report = new ReportDocument();
report.Load(reportPath);
report.SetDatabaseLogon("user", "pass", "server", "database");
report.Refresh();
//file path where pdf's are stored
string destination = @"...\convertedpdf\{0}_DetailedCallReport_{1:yyyyMM}.pdf";
DirectoryInfo tempPathPdfDirName = Directory.CreateDirectory(Path.Combine(System.IO.Path.GetTempPath(),
Guid.NewGuid().ToString()));
string tempPdfFileName = Path.Combine(tempPathPdfDirName.FullName, destination);
try
{
ExportOptions options = report.ExportOptions;
options.ExportDestinationType = ExportDestinationType.DiskFile;
options.ExportFormatType = ExportFormatType.PortableDocFormat;
options.DestinationOptions = new DiskFileDestinationOptions()
{
DiskFileName = String.Format(
@destination,
System.IO.Path.GetFileNameWithoutExtension(fileName),
DateTime.Now.AddMonths(-1))
};
options.FormatOptions = new PdfRtfWordFormatOptions();
report.Export();
report.Close();
report.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
Any help, on how I can bypass that parameter?
|
Posted By: hilfy
Date Posted: 06 Aug 2012 at 3:12am
|
In the report, try setting the parameter as optional and giving it a default value. -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: awardhan
Date Posted: 09 Dec 2012 at 4:40pm
Hi,
I am actually having a similar problem.
We are using cr for vs.net2010 (14.0.2000.0) and SQLExpress 2008r2 database.
The database connection seems to work fine in the following environment:
1. Dev
2. Old production box
And also for a brand new report.(apparently I can't afford to rewrite all the reports)
However, this doesn't work in the new production environment. I have checked if sql native client is installed, and it has.
I tested the database connectivity using the following code:
ReportDocument rd = new ReportDocument();
rd.Load(reportPath);
rd.SetParameterValue("@invoiceID", invoiceID);
ConnectionInfo myConnectionInfo = new ConnectionInfo();
myConnectionInfo.ServerName = server;
myConnectionInfo.UserID = username;
myConnectionInfo.Password = password;
myConnectionInfo.DatabaseName = database;
rd.DataSourceConnections.Clear();
Tables tbls = rd.Database.Tables;
foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in tbls)
{
TableLogOnInfo info = tbl.LogOnInfo;
info.ConnectionInfo = myConnectionInfo;
tbl.ApplyLogOnInfo(info);
bool b = tbl.TestConnectivity();
if (!b)
{
throw new Exception("main connection failed");
}
}
And it always got into "main connection failed".
I am not sure if I miss anything...
Any help would be appreciated,
Thank you,
|
Posted By: awardhan
Date Posted: 09 Dec 2012 at 5:24pm
Please ignore my message.
I had to change from (local) to ".\sqlexpress".
It seems like I have to use ".\sqlexpress", for sql express it doesn't work for (local).
Thank you,
|
|