Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Binding data to a Crystal report 1

Status
Not open for further replies.

drepp

Programmer
Jan 29, 2003
7
US
I'm building a VB.NET application for employees to enter their time worked in a week. Today, some of the users asked if I could add a report to the app that will allow supervisors to print off the timesheet they're viewing.

I've used Crystal reports extensively in ASP.NET, so I thought this would be a piece of cake. But I've gotten stuck on something.

What I'd like to do is populate the report using a dataset built dynamically in my code.

So I wrote the code below:

===========================

Dim Conn As New SqlConnection()
Dim Cmd As New SqlCommand()
Dim DA As New SqlDataAdapter()
Dim DS As New DataSet()

OpenConnection(Conn)
Cmd.Connection = Conn

Cmd.CommandText = "SELECT ... " 'removed for this post

Cmd.Parameters.Add("@TSID", SqlDbType.Int)
Cmd.Parameters("@TSID").Value = TSID 'TSID is a variable

DA.SelectCommand = Cmd
DA.Fill(DS, "Timesheet")

Dim oRpt As rptTimesheet
oRpt = New rptTimesheet()
oRpt.SetDataSource(DS)

CrystalReportViewer.ReportSource = oRpt

===========================

Now, here's where I'm getting stuck.

When I'm designing the report in VS.NET, if I do not associate the report with a database (via ODBC or whatever), I don't see any database fields. Therefore I can't place the fields I want on the report.

If, however, I link the report to a database during the design process, whenever I run the application, it keeps trying to use that connection. It's not disregarding the design connection for my dynamic code (as ASP.NET has always done). So suddenly users are being prompted for SQL Server login information.

Feeling like I was in a catch 22, I tried a third method. I figured I could create a dataset as an actual XSD design object (rather than doing it by code), and then link that to my report. Then I could just use a bunch of formula fields and such to correct for what the dynamic SQL would have done. Seemed really clunky, but it was worth a shot. Strangely, when I went to link the ADO.NET object to my report, the report designer kept insisting there were no fields in my dataset (even though I was looking right at them in the designer)!

So how can I build the report, populate it via a code-create dataset, and run it without users being prompted for login information?

I'd like to do this without storing any views, etc, on the SQL Server. I'd also like to create the dataset via code (rather than the designer) if at all possible.

Thanks for any help!
 
I upgraded to a version where you can make stand-alone reports. I design the reports with the connection the end user will be using, and then use a small VB program to change the record selection string on an individual basis, after which I refresh and export. I'm not sure this will be helpfull in your case but it did the trick for me...
 
create your dataset via code. use dataset.writexml to save it to a temp file, then link the report to the .xml file.

Over all I think CR is a giant steamy piece of .... but unfortunatly, it's the best for printing. If it weren't for printing requirements I would totally go with a web reporting solution.

Over the last 4 versions of CR I've battled with linking a dynamic set of data to a report with varying degrees of success. With v9 advanced and the version that comes with .Net useing XML seems to be the fastest and easiest way to get arround the issue.

-Rick

----------------------
 
Ok, it's finally working! Thanks to both of you for your advice!

Looks like the dataset problem was being caused by two things.

First, a bug in the older version of VS.NET was keeping me from seeing my dataset as a viable datasource. I imported it into VS.NET 2003, and suddenly the dataset started showing up just fine.

Second, I needed to change a line of code from...

oRpt.SetDataSource(DS)

...to...

oRpt.Database.Tables(0).SetDataSource(DS.Tables("Timesheet"))

Thanks again for your help! What you were telling me to do was dead-on. It just turns out the problem was a bug that no one (except Microsoft) could do anything about.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top