Solved

Set Report Data Source to Application.Startup

Posted on 2006-11-27
29
741 Views
Last Modified: 2011-10-03
It seems as though this should be an easy thing to do, but...

I'm using VB.NET and have the built-in Crystal Reports, PLUS I purchased the Crystal Reports Developer Edition XI (just because I thought the HELP may contain a solution to this very problem).

I've created a dozen reports and they run fine on my machine, but I need them to be set up like VB (application.startup) for the database path, so they will run anywhere else.

I tried asking this same question in the VB.NET section, because I believed there should be a coding solution, but not one single person responded, so now I'm trying here.  

I'm putting up 500 points and if you can solve my problem, I'll give you the link to the question in the VB section so you can collect another 500 points.

Thanks!

<Duplicate of
    http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_22059035.html
  Points awarded in the VB.Net forum
mlmcc PE>
0
Comment
Question by:kerryw60
  • 13
  • 10
  • 4
29 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 18022483
0
 
LVL 42

Expert Comment

by:frodoman
ID: 18024046
Are you deploying the reports as part of your vb.net application?  If so, how are you calling them - particularly the database portion?  Often you can simply omit the path completely and Windows will look in the apps execution folder for the file/database by default.

frodoman
0
 
LVL 6

Author Comment

by:kerryw60
ID: 18027668
Thanks mlmcc!  I had previously tried a couple of the VB.NET examples at the business objects site, but will revisit after I check out the MSDN link you provided.

>>>>>>>>>>>>>>>>>>>>>>>>>>>

Thanks Frodoman! Yes, want to deploy reports as part of my vb.net application.  I call the reports by:

ReportSource = Application.StartupPath & "\" & "ReportName.rpt"

Calling the reports doesn't seem to be a problem. When the reports try to open they prompt me for the Access database location the reports are based on.  SO, everything runs/works fine until it is deployed and installed in another location.

kerry
0
 
LVL 42

Expert Comment

by:frodoman
ID: 18028673
What is the location of the access db?  I recommend putting it in the same folder as the application that way the relative path will be identical every time.

Another option that might work is to always install the application in the same folder.  In Crystal you can use File -> Options -> Data Source Defaults  and set the default path to the database.  If you always install it to c:\yourfolder\ then Crystal should know where to look for it.  I haven't done this myself because I don't report off Access DB's but as I understand it this should work.

frodoman
0
 
LVL 6

Author Comment

by:kerryw60
ID: 18028815
Hi Frodoman,
     The location of the Access DB is always installed into the same folder as the application, but users are allowed to install the application in any location.  Everything in project points to "application.startup" and everything works regardless of location installed, except for the Crystal Reports. They always want to look in the same location as the database they were originally constructed on.  Just as you can set everything else in the application to look to "application.startup" for the data source, it just seems you should be able to do the same for Crystal Reports.

Surely other people deploy Crystal Reports with developed applications??  This just doesn't seem like it should be a big deal.  Trying to work with someone from Business Solutions on this, but they are located in India (naturally) and there is a severe language barrier.

Thanks for trying!


0
 
LVL 42

Expert Comment

by:frodoman
ID: 18028870
You can set the location at runtime using the application.startup - just be aware you'll need to set it for each table in the report, including any subreports (even if they use the same base table(s).  The code sample for it is here:

http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do;jsessionid=607DA918E832D6C28228425E1019D3AE?cmd=displayKC&docType=kc&externalId=c2010490&sliceId=&dialogID=360384&stateId=1%200%20356275

Just substitute application.startup for the hardcoded value in the code sample.

frodoman
0
 
LVL 6

Author Comment

by:kerryw60
ID: 18028890
Hi mlmcc,
     Tried working through the solution at the MSDN link you provided.  Here's are steps Microsoft says to follow:

>>>>>>>>>>>>>>>>>>>>>>>>>>>
To deploy your application, you need to create a setup project and add the necessary merge modules to your application before you build your solution.

1. Open your Windows application.

2. In the Solution Explorer, right-click your Windows application solution, point to Add then click New Project.

3. In the Add New Project dialog box, select Setup and Deployment Projects in the Project Types pane and select Setup Project in the Templates pane. Click OK.

4. In the Solution Explorer, right-click the Setup Project, point to Add then click Project Output.

5. In the Add Project Output Group, select Primary output and click OK.
Any dependencies, such as the Crystal_Managed2003.msm, are added automatically.

6. In the Solution Explorer, right-click the Setup Project, point to Add then click Merge Module.

7. In the Add Modules dialog box, select the following merge modules to add to your Setup project and click Open:
• Crystal_Database_Access2003.msm
• Crystal_Database_Access2003_enu.msm
• Crystal_regwiz2003.msm
For a description of these merge modules see Crystal Reports Merge Modules.

8. In the Solution Explorer, right-click the Crystal_regwiz2003.msm file and choose Properties.

9. In the Properties window, expand MergeModuleProperties and enter a valid license key in the License Key properties box.
Note   Providing the license key is a mandatory step whenever you deploy a Crystal Reports for Visual Studio .NET application.
A license key will be given to you once you have registered Crystal Reports for Visual Studio .NET. You can find the license key in the About dialog box. From the Help menu, choose About Microsoft Development Environment. The keycode is listed in the Installed Products field.

10. From the Build menu, choose Build Solution to build your application.
When deploying to a client or server computer, always ensure that the computer already has the .NET Framework installed.
>>>>>>>>>>>>>>>>>>>>>>>>>>>

I complete step 5, but:
Any dependencies, such as the Crystal_Managed2003.msm, are NOT added. There are NO Crystal dependencies at all, but I have created more than a dozen reports within my project and all run fine in debug mode.

Moved on to step 6 and 7, but NONE of the msm Merge Modules are available.

Any suggestions?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 18029254
Did you install the merge modules when you installed Crystal?

Here are the latest merge modules for VS 2003
http://support.businessobjects.com/communityCS/FilesAndUpdates/cr_net_2003_mergemodules_en.zip.asp

mlmcc
0
 
LVL 6

Author Comment

by:kerryw60
ID: 18029276
Frodoman - SO CLOSE!!!

Here's my code based on your post:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Private Sub frmAvgRetreads_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim CrReport As New MyReportName()
Dim CrTables As Tables
Dim CrTable As Table

CrTables = CrReport.Database.Tables

For Each CrTable In CrTables
   CrTable.Location = Application.StartupPath & "\" & "Access.mdb"
Next

myCrystalReportViewer.ReportSource = CrReport

End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

The only problem with this is the report tries to load twice.  

When it looks for the the CrReport.Database.Tables, it goes into some auto-generated code with a warning - Code generated by tool, blah, blah, blah, changes may cause incorrect behavior and will be lost when code is regenerated.  It's in here when the report attempts to load and I get the same problem as before, it can't find the database, BUT when I click CANCEL and allow the routine above to finish processing, the report opens and displays exactly as it should.

Any ideas on how to stop it from loading when it goes into the auto-generated code?

THANKS!!!!
0
 
LVL 42

Expert Comment

by:frodoman
ID: 18030412
I'm not certain w/out having your entire code in front of me.  I suspect if you search for "Access.mdb" you'll be able to find the spot in the code where this is happening and siimply comment it out.  Alternately you can use the debugging tools and step through the application to find where this is ocurring.

frodoman
0
 
LVL 6

Author Comment

by:kerryw60
ID: 18030453
mlmcc,
     using VS 2005. After purchasing Crystal Reports Developer Edition, I installed and downloaded all available updates.  I would think this would have included the merge modules, but I'm researching to try to find out.

Thanks!
0
 
LVL 6

Author Comment

by:kerryw60
ID: 18030566
Hi Frodoman,
    yes, posted ALL my code in my module in my attempt to link my report to my mdb.

I did step through my code. When it hit the line:

CrTables = CrReport.Database.Tables

It goes into the background of the Crystal Report into code that has been Auto-Generated by Crystal Reports.

I can't simply "comment" it out as Crystal Reports will "Regenerate" the code wiping out any changes I make.

Thanks!
0
 
LVL 42

Expert Comment

by:frodoman
ID: 18030623
Okay, I've got it.  The app is trying to hit the database to confirm the table structure when you hit this line.

One possibility might be to check the 'save data with report' option so this line of code will use the saved table struture.  You can then call the discardsaveddata method before changing the location.  Not sure this will work (this is off the top of my head) but if not I'll keep thinking about it...
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 6

Author Comment

by:kerryw60
ID: 18031066
Sorry, can't find any place to check "save data with report"

Regarding this line of code:
CrTables = CrReport.Database.Tables

It goes into the Crystal auto-generated code to:

Public Overrides Property ResourceName() As String
   Get
     Return "MyReportName.rpt"
   End Get
End Property

I guess it has to do this to retreive the tables, but it is exactly on the Return line, in which the report attempts to open.

This is the ONLY code in the auto-generated it goes to.

Would there be another way of retreiving the table(s) a report is based on?

I do appreciate you sticking with me!!


0
 
LVL 6

Author Comment

by:kerryw60
ID: 18031227
Another thought...

All of my reports are based on a single table and will always be that way.  All data is processed in code and dumped into a single table for the report to display.

Since I know the table name, is there another way to do this?

I've been playing but can't get anything to work.

What I mean, is instead of:
CrTables = CrReport.Database.Tables

Is there a way to put the table name into the CrystalDecisions.CrystalReports.Engine.Tables?

Thanks!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 18032256
Do you have Release 2 of CR XI?  It is required to use CR XI with VS 2005
Here is a link to the free download.
http://www.businessobjects.com/products/reporting/crystalreports/compatibility_vs2005.asp

mlmcc
0
 
LVL 42

Expert Comment

by:frodoman
ID: 18032315
Okay, you don't have the SaveDataWithReport option if you're using the version that came with Visual Studio - that's only available in the standalone version.

You can reference a table by name, but you still have to have the link to the report so that isn't going to help you.  I don't have a quick answer for you - you could try using a try..catch block and see if there's an error that gets raised and perhaps you can just code your catch block to ignore it?

I'm still thinking about this one...
0
 
LVL 6

Author Comment

by:kerryw60
ID: 18036214
Thanks mlmcc, I do not have that required update.  Firewall at work prevents me from downloading it.  Will download from home tonight and revisit tomorrow.

>>>>>>>>>

Frodoman,
    I do have the stand alone version as well, just haven't used it yet.  If I need to recreate the reports in that version to get this to work, then I'll do it.  Hopefully it's not a big deal to incorporate a report from the stand alone Crystal into Visual Studio.  I may also give the try/catch block a go. Will get to that this afternoon and let you know how it goes.

THANKS!!
0
 
LVL 42

Expert Comment

by:frodoman
ID: 18036541
It's not a big deal to create the reports.  In fact, all you have to do is open the report file in the standalone version and check that option - you don't need to recreate from scratch.  In VS you will need to delete the report from your project (save a backup because VS will delete the .rpt file) and then readd the report with the same name.  Piece of cake.  As I said, I'm not certain it'll work though and given where you are at this point I think it less likely than I did before - I'd try the try..catch block first.

frodoman
0
 
LVL 6

Author Comment

by:kerryw60
ID: 18068944
Tried the Try...Catch block, but no exception is being thrown. Was pretty well what I expected.

We've now spent more time trying to solve this one little problem than we did in creating all of the reports to begin with.

The company Business Objects has been no help at all. SEVERE language barrier in dealing with their support people. They have managed to solve every problem we did not have.

Ready to ditch Crystal Reports completely at this point. They are entirely useless to us if we cannot use in production.

Are there any other decent products which will work with Visual Studios?  We don't need anything complex, we do all of our own calculations in code, we just need something to display neatly and provide graphs (and be able to see the database!!)

Thanks for all of your help!

0
 
LVL 6

Author Comment

by:kerryw60
ID: 18069403
Okay, have changed the code to this and much closer:


Dim rep As New MyReportName()
Dim t As New TableLogOnInfo

t.ConnectionInfo.DatabaseName = My.Application.Info.DirectoryPath.Replace("C:\VB\MileageMaster\FleetMaster\bin\Debug", Application.StartupPath & "\" & "Access.mdb")

rep.Database.Tables(0).ApplyLogOnInfo(t)
myCrystalReportViewer.ReportSource = rep

0
 
LVL 42

Expert Comment

by:frodoman
ID: 18070474
When you say "much closer", what does that mean - is it hitting the database now?
0
 
LVL 6

Author Comment

by:kerryw60
ID: 18070836
Sorry, I was so vague before.  It was working in debug mode but was prompting for login info, asking for username/password (there are none). Then it still wouldn't run in deployment, but it WAS finding the database which was a major victory.

Anyway, I finally figured it out!!!!!  Hopefully this will help out someone else in the future.  Here's the code:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Private Sub myFormName_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim rep As New myReportName()
    Dim t As New TableLogOnInfo
    t.ConnectionInfo.DatabaseName = My.Application.Info.DirectoryPath.Replace("C:\OLD DATABASE PATH", Application.StartupPath) & "\myAccess.mdb"
    t.ConnectionInfo.UserID = ""
    t.ConnectionInfo.Password = ""
    rep.Database.Tables(0).ApplyLogOnInfo(t)
    myCrystalReportViewer.ReportSource = rep
End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


A couple of key things to note in the above code:

In the DirectoryPath.Replace, you put in your database location up to, BUT NOT including the database name.  You can then follow it with VB's Application.StartupPath, then string your database name outside the parenthesis.

Even though I didn't have a Username/Password, it would prompt me for one, so by passing the empty strings for Username/Password, it prevents the user from seeing the prompt.

****************************************
FRODOMAN - I COULD NOT HAVE DONE THIS WITHOUT YOU!!!!    I WOULD LIKE TO MAKE THIS EASY ON ANYONE ELSE WHO MAY NEED THIS SOLUTION. PLEASE COPY/PASTE THE ABOVE CODE IN YOUR NEXT REPLY AND I WILL ACCEPT IT AS THE ANSWER.

ALSO, IF YOU WOULD PLEASE VISIT MY PROFILE, YOU WILL FIND AN OPEN VB.NET QUESTION REGARDING THIS.  IF YOU WILL SIMPLY POST A LINK TO THIS QUESTION, I WILL ACCEPT THERE AS WELL, GIVING YOU THOSE POINTS.

THANKS!!!

MERRY CHRISTMAS!!!

0
 
LVL 42

Accepted Solution

by:
frodoman earned 20 total points
ID: 18071410
Glad you've got it working!

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Private Sub myFormName_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim rep As New myReportName()
    Dim t As New TableLogOnInfo
    t.ConnectionInfo.DatabaseName = My.Application.Info.DirectoryPath.Replace("C:\OLD DATABASE PATH", Application.StartupPath) & "\myAccess.mdb"
    t.ConnectionInfo.UserID = ""
    t.ConnectionInfo.Password = ""
    rep.Database.Tables(0).ApplyLogOnInfo(t)
    myCrystalReportViewer.ReportSource = rep
End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>



frodoman
0
 
LVL 6

Author Comment

by:kerryw60
ID: 18073566
That's just wrong, this was really a VB.NET CODING question and NO ONE even attempted to answer FOR THREE WEEKS!!!

Frodoman,
     you stuck with me for days and far exceeded any help any one else bothers to give around here.  Most people just take easy questions for easy points. VERY RARE you find someone willing to stick with you and actually help.
     I'll contact you outside of EE, give me your address and I'll send you a Christmas card with $50 in it for your efforts.

THANKS!



0
 
LVL 100

Expert Comment

by:mlmcc
ID: 18073834
It may be "wrong" but there were askers posting the same question in several places to help their friends collect points and get free EE service.  Unfortunately a few abusers brought this on for all.

mlmcc
0
 
LVL 42

Expert Comment

by:frodoman
ID: 18076436
kerryw60 - Thanks, but I agree the 500 point limit is a good policy and I should have looked at the point value before cross posting.  I believe in good karma - either you or someone else will help me when I'm stuck on a tough one.

mlmcc - I don't care where the points are awarded.  For clarity I would leave them in vb.net forumn and just PAQ/Refund this question.  Sorry for the headache - frodoman
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now