Binding Dataset to Crystal Reports

realG

Hi,

I have a dataset created programatically because I have a complex select statement consisting of a complex set of if else to retrieve value from databse. I need to display the retrieved data in my reportviewer.

So I have the code like:
string query = "Select ...";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dAdapter.Fill(dSet);
ReportDocument myReport = new ReportDocument ();
myReport.SetDataSource(dSet);
CrystalReportViewer1.ReportSource = myReport;

Now when I run the page, I get the following error at the line: myReport.SetDataSource(dSet);

CrystalDecisions.CrystalReports.Engine.LoadSaveReportException was unhandled by user code
Message="Invalid report file path."
Source="CrystalDecisions.Shared"
StackTrace:
at CrystalDecisions.CrystalReports.Engine.EngineExceptionUtils.DoThrowException(String message, EngineExceptionErrorID id)
at CrystalDecisions.CrystalReports.Engine.ExceptionThrower.ThrowEngineException(String messageID, EngineExceptionErrorID id)
at CrystalDecisions.CrystalReports.Engine.ReportDocument.Load(String filename, OpenReportMethod openMethod, Int16 parentJob)
at CrystalDecisions.CrystalReports.Engine.ReportDocument.EnsureLoadReport()
at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetDataSourceInternal(Object val, Type type)
at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetDataSource(DataSet dataSet)
at Reports_ReportsBySelection.ButtonSearch_Click(Object sender, EventArgs e) in c:\...\Reports.aspx.cs:line 438
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)



Can anybody please help me with this.

Thanks.


edit: I have checked with debugger and all my dataset is filled, it is not empty.

Adam D. Turner

Is the report you created binded to the viewer

ADam

realG

I'm a noobie at crystal reports and I see what you are saying. The answer is no, I haven't made a rpt file.

Ok, now my problem will be how can I make a rpt file that binds itself to a dataset that I create programatically as above

Thanks.

Adam D. Turner

Ok your first step is to create a report file and look at the properties of the report file. You'll see how to bind the viewer.

Let me know if I can help further.

Adam

realG

I searched a lot on the internet on how to do it. But its not as clean.

Here's what I did and now it works.

query += "select ...";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dAdapter.Fill(dSet);
dSet.WriteXml(@"D:\dSet.xml");
ReportDocument myReport = new ReportDocument();
myReport.Load(@"c:\reports.rpt");
CrystalReportViewer1.ReportSource = myReport;
CrystalReportViewer1.DataBind();

My question: isn't there any way to do it without explicitly saving the xml file or loading the rpt file by hardcoding the file names Can't we just make a stream of xml instead of saving it in a file And can't we load the absolute path of hte .rpt file


EDIT:
I managed teh absolute path of the rpt file by doing

myReport.Load(Server.MapPath("reports.rpt"));

The only thing I now need is I don't want the d:\dSet.xml to be present in my machine. Is there any way to do it And since I have a select statement that changes according to multiple criteria, I think I have no choice than to make a xml file.


EDIT2:
I mentioned above I'm a noobie at this. But I finally figured out how to do it. This is what I did.

query += "select ...";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dAdapter.Fill(dSet);
dSet.WriteXml(Server.MapPath("dSet.xml"));
ReportDocument myReport = new ReportDocument();
myReport.Load(Server.MapPath("reports.rpt"));
MemoryStream oStream;
oStream = (MemoryStream)myReport.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/pdf";
Response.BinaryWrite(oStream.ToArray());
Response.End();


And placed the dSet.xml inside the web app folder. Now this way, I get a direct PDF of the report, which is much better option for my case.

Thanks for eveverything.

Adam D. Turner

I'm confused on the need for the .xml file

The dataset is bound to the viewer. Are you creating an xml file for the report file itself

The easiest way is just to add a datasource and dataset to your project and use the properties of the report to bind the fields.

Does that answer your question

Adam

realG

Adam,

I have the xml because the data that is received by the rpt file is coming from the xml file. And now it works fine on my machine so I don't want to change anything. I did something I wanted to do and it works fine so I'm happy with it and don't want to bother too much about it right now.

But I have a new problem. After I setup the whole program in IIS, I get a logon prompt asking me to enter the server name, database name, userID, password. Because all the data I need is already stored in the xml file (which I checked by opening the xml file and the data in there is exactly what I wanted), why do I get the logon screen Even when I set the logon parameters correctly in the code, I still get this screen. Can you help me please.

Thanks.

~~~

EDIT:

Adam,

I now understand what you are saying. I managed to fix my problem without having to use the xml file. I got help from http://aspalliance.com/776_Creating_Dynamic_DataSets_for_Crystal_Reports_in_ASPNET_20.1
I used dataset to directly get the values into the report.

Thanks for your help. Like I said, I'm a noobie at Crystal Reports so this was a nice learning curve for me.

Adam D. Turner

Hi,

So you are no longer getting a login prompt

If so, when exactly are getting it

Adam

realG

Adam,

When I was binding my CrsytalReportViewer with the xml file, I was getting the logon prompt. But when I deleted teh xml file and binded teh CrystalReportViewer with the dataset, I had no problem.

Actually I had a problem, in the CrystalReportViewer format, the first page was fine, but when I did 2nd page, it was blank, maybe because when I wanted to goto 2nd page, the page would reload, and teh information in the dataset would be emptied, hence the blank 2nd page. Is there a solution for that

My code for this was:
string query = "select ... ";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
DataSet dSet = new DataSet();
dAdapter.Fill(dSet);
ReportDocument myReport = new ReportDocument();
myReport.Load(Server.MapPath("report.rpt"));
myReport.SetDataSource(dSet);
CrystalReportViewer1.ReportSource = myReport;
CrystalReportViewer1.DataBind();

I actually converted the whole report into pdf and instead of viewing the Crystal Reports, I get the whole thing in PDF format at the very first time, so I'm fine with what I have, but if you understand the blank page problem, can you help I just want to know how to fix it, not that I actually need to do it.

To get pdf the very first time, I changed the last two lines with:
MemoryStream oStream;
oStream = (MemoryStream)myReport.ExportToStream(ExportFormatType.PortableDocFormat);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/pdf";
Response.BinaryWrite(oStream.ToArray());
Response.End();

Thanks.

Adam D. Turner

A second page meaning that it's not a continuance of the first

If you add a "new" page, you need a new .rpt to bind a dataset to.

If your report exceeds 1 page, it will automagically create a new page for you.

Adam

realG

My reports have a lot of reports. So they won't fit in a single page. The first page can only contain a certain number of data. After that, the rest need to move to the next page, and so on. Now this next page is the one I refered to as second page.

In the CrystalReportViewer, there's the options to print, save, goto next page, goto selected page, and a lot of other options. Whenever I click on any of those I get a blank page. I don't get the information leftover from the first page that I need to see. My intuition says its because when I click on the goto next page button, it becomes a postback and hence the dataset I have becomes null, and hence the blank page. But I might be wrong.

Like I said, I managed the problem by directly displaying a complete report in pdf format, but I would like to know why the page was blank when selecting to goto the next page.

Thanks.