[RESOLVED] Changing database location from CR 10

[JSteele94]

Hello again, I need the help of the guru's once again. I have searched the forums looking for an answer to this and couldn't find one so forgive me if I missed it. I have a VB.NET program that uses a crystal report viewer to view a CR I made with CR 10 Professional. When I made the report from within CR 10 Professional I had to set the database location using the wizard. What I am needing to do is have some way where it can find the database from the application startup directory. I was able to change it from within VB.NET but do not know how to do it from within CR 10. All the threads on here that I have found are telling me to add some code within VB.net to connect to my database. I'm not sure how to do that or where to do that. I have a form that has a CR viewer on it to view a report. I have the CR viewer looking in the application startup directory for the report. Now I'm looking for a way for my REPORT to look for the DATBASE. Any help would be great. Thanks!

[JSteele94]

I really need help with this PLEASE if anyone can help me with this!!! Then only thing I can think of is if I made a SQL expression from within CR 10 maybe I can tell it from the expression where the database is????? PLEASE HELP!!

[pnish]

Why do you need Crystal to figure out where the database is when you can get VB to tell it? Have a look at this thread:
http://www.vbforums.com/showthread.php?t=360605
See post #13
This is VB6, but you should be able to do something similar with VB.Net

[JSteele94]

Thank you for the reply, this looks like what I need. I didn't know I could have VB do it, I was looking in the wrong direction. I'll give this a try and let you know. Thank you very much!

[JSteele94]

Ok I have been searching for an answer to this for over a month now and I can't find anything!! Someone PLEASE help me with this!!! I'm not looking to change the report source I've already done that, I'm needing VB.NET to pass the database location to my REPORT so my database can be located in the application folder. Please someone HAS to know how to do this!!!!

[pnish]

I'm sorry you're still stuck on this. The only way I've ever done this using the Crystal Viewer is either

Dim crDB As CRAXDRT.DatabaseTable
    Set crDB = Report.Database.Tables(1)
    crDB.Location = App.Path & "\myDB.mdb"

or

Dim crProp As CRAXDRT.ConnectionProperties 
    Set crProp = Report.Database.Tables(1).ConnectionProperties
    crProp.Item("Database Name") = App.Path & "\mydb.mdb"

This is VB6 and Crystal 9, but you would think it would be similar with .Net & V10.

Have you looked at Crystal's KB on the web site?

Good luck.

[JSteele94]

I'm sorry you're still stuck on this. The only way I've ever done this using the Crystal Viewer is either

Dim crDB As CRAXDRT.DatabaseTable
    Set crDB = Report.Database.Tables(1)
    crDB.Location = App.Path & "\myDB.mdb"

or

Dim crProp As CRAXDRT.ConnectionProperties 
    Set crProp = Report.Database.Tables(1).ConnectionProperties
    crProp.Item("Database Name") = App.Path & "\mydb.mdb"

This is VB6 and Crystal 9, but you would think it would be similar with .Net & V10.

Have you looked at Crystal's KB on the web site?

Good luck.

Pete thank you for your reply I think I'm getting close but what do I set "report" to?

Set crProp = Report.Database.Tables(1).ConnectionProperties
.

[JSteele94]

crProp.Item("Database Name") = App.Path & "\mydb.mdb"[/Highlight]

"Item" is read only, that's the error I'm getting..

[pnish]

Try this (or the VB.Net equivalent)

Dim crApp As CRAXDRT.Application
    Dim crDB As CRAXDRT.DatabaseTable
    Dim Report As CRAXDRT.Report
 
    Set Report = crApp.OpenReport("c:\myreport.rpt")
    Set crDB = Report.Database.Tables([b][color=red]n[/color][/b])
    crDB.Location = App.Path & "\myDB.mdb"
    Report.ViewReport

Note n above. If there is more than one table in your report that needs its location changed, you must specify each one with its ordinal number, eg

Set crDB = Report.Database.Tables(1)
    Set crDB = Report.Database.Tables(2)
    Set crDB = Report.Database.Tables(3) ..... and so on

[JSteele94]

Try this (or the VB.Net equivalent)

Dim crApp As CRAXDRT.Application
    Dim crDB As CRAXDRT.DatabaseTable
    Dim Report As CRAXDRT.Report
 
    Set Report = crApp.OpenReport("c:\myreport.rpt")
    Set crDB = Report.Database.Tables([b][color=red]n[/color][/b])
    crDB.Location = App.Path & "\myDB.mdb"
    Report.ViewReport

Note n above. If there is more than one table in your report that needs its location changed, you must specify each one with its ordinal number, eg

Set crDB = Report.Database.Tables(1)
    Set crDB = Report.Database.Tables(2)
    Set crDB = Report.Database.Tables(3) ..... and so on

Pnish thank you again for the help your providing. I have tried the code and I am getting a weird error now. Here's my code

This is on form load:

Dim crApp As CRAXDRT.Application
        Dim crDB As CRAXDRT.DatabaseTable
        Dim Report As CRAXDRT.Report
 
        Report = crApp.OpenReport(Application.StartupPath & "\schedule.rpt")
        crDB = Report.Database.Tables(0)
        crDB.Location = Application.StartupPath & "\agam_data.mdb"
        Report.ViewReport()

When I run the program now, and click on the menu item to run my report my program exits with an error that says:

An unhandled exception of type 'System.OutOfMemoryException' occurred in system.windows.forms.dll

Additional information: Error creating window handle.

it then points me to the menu item i clicked to open the report:

Private Sub MnuViewSchedules_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuViewSchedules.Click
        Me.Cursor = Cursors.WaitCursor
        Dim child As New frmschedule()
        child.MdiParent = Me
        child.Show()
        Me.Cursor = Cursors.Default
    End Sub

It highlights "child.show()" but I have no clue why.






Edit: Added [vbcode][/vbcode] tags for clarity. - Hack

[pnish]

I think that the ordinals start at one, Report.Database.Tables(1)
See if that makes a difference.

[JSteele94]

No I changed it to 1 and it still is doing the same.

[pnish]

The way I would now tackle the problem would be to start a new project that just contains your report code. I think we're being sidetracked by other un-related issues. It's a pain I know, but it will make it a lot easier from a debugging point of view.

Give it a go and let me know how you get on.

[JSteele94]

If I take away your sample code then everything works fine. I've searched on the forms and have found that a lot of people speak of this same problems with mdi forms but no one lists any solutions.

[pnish]

I've got VB.Net loaded on a machine somewhere here in the office. I'll see if I can get an example working. I haven't used .Net before so this will be a learning experience for me.

[JSteele94]

If you could help me figure this out I couldn't thank you enough! This is the final step I am needing before my program is finished and I have done everything I can! Thank you!

[pnish]

OK. I think I've made some progress. It took me a while to get the hang of VB.Net but I think I could learn to like it. This is what I did.

Added a Crystal Report Viewer control to a form (I used the .Net version of the control which I assume you are too) and a command button. Here's the code

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
 
    Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click
 
        Dim cr As New ReportDocument
 
        cr.Load("MyReport.rpt", OpenReportMethod.OpenReportByDefault)
        cr.Database.Tables(0).Location = "MyDB.mdb"
 
        CrystalReportViewer1.ReportSource = cr
 
    End Sub

And as you can see the table ordinals start at zero, not one as I said previously. Anyway, this method worked without any problems. I didn't try setting up MDI & child forms, I just didn't have the time.

Hope this helps.

[JSteele94]

pnish,

First off I want to say thank you so very much for taking to time to help me try and figure this out, you really have no idea how much I appreciate it. I have tried what you said and have gotten very very close I think I am just doing something wrong. Here's my code:

Dim cr As New ReportDocument()

cr.Load("Schedule.rpt", OpenReportMethod.OpenReportByDefault)
cr.Database.Tables(0).Location = Application.StartupPath & "agam_data.mdb"

CrystalReportViewer1.ReportSource = cr

I have attache the error it gives me when I run it. Again thank you for your help!

[pnish]

Give me 10 minutes while I go and fire up VB.Net and see if I can replicate the error. Back in a flash.........

[pnish]

Ok, I get the exact same error if I change Tables(0).Location to anything other than zero.

Do you have more than one database or table referenced on your report?
Crystal uses the Tables(n) index to point to the correct table on the report. So if you had 2 databases, eg Customers.mdb & Sales.mdb and a table from each database, eg [Customer Details] & [Invoices] you need to determine each table's ordinal position on the report. To do this, open your report in Crystal and select Database Expert... from the Database menu.

The right-hand pane will list each database & table referenced by the report. The tables are numbered (internally) sequentially by Crystal, so if for example the Customers DB was listed first, followed by the Sales DB you would reference them like this

cr.Database.Tables(0).Location = "MyNewCustomers.mdb"
cr.Database.Tables(1).Location = "MyNewSales.mdb"

I'm hoping your problem is as simple as that...

[JSteele94]

I only have 1 database in my report so it should be table(0) but it's not working, I've also tried changing the number a few times but with no luck. I'm beginning to hate crystal reports!!

[pnish]

Is it possible for you to zip up your report & database (or a subset) and post them here? Maybe I'll be able to see what's going on. Very weird.....

I'm not at work until Tuesday but I may be able to check it out over the weekend.

[JSteele94]

pnish sorry it took me awhile to get back to you I've been out of town. I can see what I can do to zip it up. Any other ideas I feel we are so close to getting this to work and this is the last thing I need before my program is done and I can't beleive it's this hard to do. In any case thank you very much for all your help!

[JSteele94]

pnish,

I can't believe it I got it to work!!! You wouldn't believe what the problem was, you had to correct code all along I just forgot a "\"!!!! OMG I can't thank you enough!!!!!!!!!!!!

Here is the final code that makes it work:

Dim cr As New ReportDocument()
cr.Load("Schedule.rpt", OpenReportMethod.OpenReportByDefault)
cr.Database.Tables(0).Location = Application.StartupPath & "\agam_data.mdb"
CrystalReportViewer1.ReportSource = cr

[pnish]

Great stuff!!!!!

Isn't always something obvious (which neither of us picked up).

Cheers