Setting the path for a crystal report

[Nucleus]

Ok well I am using crystal reports and I have tried linking a crystal report to a table in an access database manually which works on my pc, the problem is that when I release the app, the database is unlikely to be in the same location on the users pc.

So how do I programmatically set the path of the database used by the crystal report?

[hellswraith]

Don't link it to the database. Create a typed dataset, and link it to that. Then in your code, fill the dataset, and use that as the report source.

Much less headaches.

[Nucleus]

sure i did that too, so how do I change the path of the db for a typed dataset?

[Edneeis]

You shouldn't need to instead you just fill the typed dataset in the app itself then pass the filled dataset to the report. To change the path for the dataset just change the connectionstring you use for the connection.

[Nucleus]

Ok that might work Edneeis, do you have the code to change the connection string for a connection created in the server explorer?

[Edneeis]

You just change it before the fill:

'replace SqlConnection1 with the name of the connection you made at designtime
SqlConnection1.ConnectionString="Your new connectionstring here"

[Nucleus]

Are you sure that works with connections created with the server explorer?

I tried adding a oledbdataconnection object to the form, but when I tried to create the report, i couldn't use it to create the the dataset and I couldn't choose it from the project data section. So I assume you have to use the server connection object to create the dataset, but I couldn't find the code in the help files :shrug:

[Lunatic3]

You may create a dataset from a dataadapter. And a dataadapter needs a dataconnection. You can set the connection string in a way that it is read from config file. Once you have a dataset you may use it to build your report on it. However if you want to avoid datasets, you can easily link your report to a database. Want a sample?

[Nucleus]

well I tried making my oledb dataset a global dataset and then used this code to try and make it work but to no avail:

oRpt = New CrystalReport1()
oRpt.Load(oRpt.FilePath)
' make sure this line isn't duplicated in the module
Me.CrystalReportViewer1.ReportSource = ds.Tables("tblClients")

the report just comes out without any data.

Lunatic3, I tried linking it to a db directly, but when the user installs the app on their machine the db will be in a different location which makes the report fall over.

[Lunatic3]

You are doing things wrong. Read chapter 14 of this book available online.
http://www.crystalreportsbook.com/

And I know what you mean, you can ask the user for the path of the database and other credentials and then apply it to the report. Take a look at this thread. http://www.vbforums.com/showthread.p...hreadid=272958
You can use crystalreportviewer object too and apply the LogonInfo to that.

[Nucleus]

Have to register to read chapter 14!

[Lunatic3]

Originally posted by Nucleus
Have to register to read chapter 14!

It's free!

[Nucleus]

Well thanks Lunatic, read the chapter and it is easy to follow and concise and covers most things.

I still had problems after reading the chapter though so I thought that I would post the solution for other members

How to write a crystal report bound to an dataset.

The concept is simple. Create a strongly typed dataset to get the xml structure of the dataset, so that you can create the report based on the fields without needing any data. Then at runtime bind the report to the unbound dataset once it is populated.

Create the stongly typed dataset:
1. Use the server explorer to create a connection to the datasource.
2. Add new item to your project and make it a dataset.
3. drag the table(s) etc from the server explorer section onto the dataset.

Creating the report
Then add new item to your project and this time add a crystal report. Use the report expert and for the datasource select project data and then the dataset you just created. Then just follow the prompts and customise it however you want it.

Create the dataviewer on form to display the report.
Add a form to your project and drop on a crystal report viewer object from the bottom of the windows forms toolbox.

Finally Binding the report at runtime
In the form that contains the crystal report viewer, add this code to the load event. This assumes you already have the data you want to display in a dataset called ds.

Dim myReport As New CrystalReport1() ' whatever the name of the report in your project is but it should be CrystalReport1 if it is the first report in your project and you take the default name.
myReport.SetDataSource(ds.Tables(0)) 'to bind it to the first table in the dataset

CrystalReportViewer1.ReportSource = myReport



Troubleshooting:
And that is all there is to it, once you know how! One problem that most examples had was they had this line:

myReport.SetDataSource(ds)

Now even when I only had a single table in the dataset, it still caused an error, so you have to refer to the table in the dataset.