Results 1 to 8 of 8

Thread: Crystal Report - two databases

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Crystal Report - two databases

    Hello. I have a report that wants to run a query against three tables. Jobs and xtblProjects are in database A and p21_view_customer is in database B. The report file has already been written and if I preview it in Visual Studio it's fine. My problem is when I run my C# application. I was getting "The table 'p21_view_customer' could not be found." I fixed that, and now my report is blank :/.

    These are the things I've been trying. I have a routine called SetupForTheReport() that is called to loop through the report tables and set the logon info. The logon info was always database A and then I hit this report that also wanted to use database B. So I thought this logic would help:
    Code:
            private void SetupForTheReport(string filenameOfReport)
            {
                ConnectionInfo crConnectionInfo = new ConnectionInfo();
                crConnectionInfo.ServerName = "nn.nn.nn.nn";
                crConnectionInfo.DatabaseName = "Database A";
                crConnectionInfo.IntegratedSecurity = true;
    
                crReport = new ReportDocument();
                crReport.Load(filenameOfReport);
    
                Tables crTables;
                TableLogOnInfo crTableLogonInfo = new TableLogOnInfo();
                crTables = crReport.Database.Tables;
                foreach (Table crTable in crTables)
                {
                    if (crTable.Name == "p21_view_customer")
                    {
                        ConnectionInfo crConnectionInfoP21 = new ConnectionInfo();
                        crConnectionInfoP21.ServerName = "nn.nn.nn.nn"; // It's the same server as above
                        crConnectionInfoP21.DatabaseName = "Database B";
                        crConnectionInfoP21.IntegratedSecurity = true;
    
                        crTableLogonInfo = crTable.LogOnInfo;
                        crTableLogonInfo.ConnectionInfo = crConnectionInfoP21;
                        crTable.ApplyLogOnInfo(crTableLogonInfo);
    
                        crConnectionInfoP21 = null;
                    }
                    else
                    {
                        crTableLogonInfo = crTable.LogOnInfo;
                        crTableLogonInfo.ConnectionInfo = crConnectionInfo;
                        crTable.ApplyLogOnInfo(crTableLogonInfo);
                    }
                }
            }
    But no, same error that the table couldn't be found.

    Then in Visual Studio, I started scrutinizing the report and in Field Explorer under Database Fields the three tables are listed. They were all pointing to Database A. Now it does look like p21_view_customer is correctly saying Catalog: Database B. But still, table couldn't be found. (This didn't make sense to me because the preview worked in Visual Studio and how could it have if the Catalog of the Database B table was pointing to Database A?)

    Then I found a tutorial on YouTube and I changed datasources...it really seemed like I was just redoing things I'd already done, but a new sql query was generated. It has two parts. Part 1 selects from Jobs and xtblProjects in A and the second part selects from p21_view_customer in B. And it looks like they get connected by "EXTERNAL JOIN", and that is giving me the blank report. I don't know if this is progress because it found the table finally and I just have debug why it's blank?

    So my question is, isn't there a straightforward way to run a query against tables in 2 databases that are on the same server?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Crystal Report - two databases

    The way I've done this before is to do all the linking from the server side. The report then only has one connection, to the main database, and the sproc/view then is responsible for jumping the connection to the second database/server (it'll need to anyways because of joins).
    Rarely do I let reports tap the tables directly, I always do it through sprocs or views.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Crystal Report - two databases

    I was thinking of going that route, the only problem I have with that is pride. The report exists, VB6 is calling it, and it works. I am converting this to .NET and I have to tell the other programmer that I'm changing his design because I can't get it to work. But I do agree that would be the better approach!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Crystal Report - two databases

    You might not want to help me, because I am not taking your advice. I am working with the other programmer trying to get this working. It's more his call than mine what we do so I'm trying to cooperate with him. But here's a question. What is the purpose of this code? I've googled and found it, and my coworker must've also found it because it's in the "sample" code he gave me.
    Code:
            private void SetupForTheReport(string filenameOfReport)
            {
                ConnectionInfo crConnectionInfo = new ConnectionInfo();
                crConnectionInfo.ServerName = "nn.nn.nn.nn";
                crConnectionInfo.DatabaseName = Globals.Variables.gDatabaseSelected;
                crConnectionInfo.IntegratedSecurity = true;
    
                crReport = new ReportDocument();
                crReport.Load(filenameOfReport);
    
                Tables crTables;
                TableLogOnInfo crTableLogonInfo = new TableLogOnInfo();
                crTables = crReport.Database.Tables;
                foreach (Table crTable in crTables)
                {
                    crTableLogonInfo = crTable.LogOnInfo;
                    crTableLogonInfo.ConnectionInfo = crConnectionInfo;
                    crTable.ApplyLogOnInfo(crTableLogonInfo);
                }
            }
    If I am loading the .rpt file and this information is already all set up in there and I don't want to change any of it, I can dispense with this code, right? Because I commented it out and the report almost worked (other reports worked without it).

    My report is blank, but I think that's because I am not passing it any parameters so it doesn't know what data to get. Again, this report was written to work with VB6, and somehow VB6 passes to it the resultset of a query it runs. I think I just need to change it to take parameters to filter on, then I'll have my report.

    What do you think? Besides I should write a stored procedure...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Crystal Report - two databases

    It's been a while since I've used Crystal, but I recognize the pattern. That looks like a C# version of what we did in VB6. More often than not, reports are built against a dev database, not production. So when you deploy it, you need to change the connection (and possibly the login) to the database. So that's what that code does. It's overwriting what was stored in the report to go against the correct database. It's looping through each table (datasource) in the report and connecting it to the database at run-time. What I don't see is it looking at any sub-reports and changing the datasource on those... so that might be where the problem lays. That was something we discovered by accident. We had some reports that made use of sub-reports and the connection infor wasn't passed down through to them, so we had to alter thing to also look at sub-reports as well.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Crystal Report - two databases

    Yes. My coworker and I had a discussion about subreports. That's what he said the problem was. So I said okay, I'll go fix. Then thinking about it, I realized maybe some other reports will have to do that, but not this one that is giving me this headache. It's one stinking field called "customer name" and the thing is we have it in two different databases and I guess they want both on the report. There can be a slight variation because the customer in P21 that we invoice might be a town name and if we have two jobs we've done for them it might be different department names. So it's subtle thing like that. But it's definitely one field from one table that links to the other fields in the other tables - not a whole different subreport.

    Point also taken about changing databases. We have a xxx1 database (live) and a xxx1dev database (dev). And then the P21 database - there's only one because it's a vendor database for invoicing that's readonly. So even in test and development we point to that DB. They are all on the same server. My coworker who created the Crystal Reports years ago hardcoded the live DB in the rpt file. I did point that out to him! The only problem that might cause is when you're testing, the application data might come from dev but the report data is always live which makes it hard to compare. But that's irrelevant to what I'm trying to troubleshoot at the moment.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Crystal Report - two databases

    I'm back. This never got solved and I'm working on it again now.

    Again - I have three tables in my report. Two come from one database, one comes from another. Both databases are on the same server.

    In my code as I am looping through the tables, I see that all three are set to the same database. So I'm trying to "fix" the one that is not in said database. Here's my code:
    Code:
                        TableLogOnInfo crTableLogonInfoP21 = new TableLogOnInfo();
                        crTableLogonInfoP21.ConnectionInfo.AllowCustomConnection = true;
                        crTableLogonInfoP21.ConnectionInfo.ServerName = "nn.nn.nn.nn";
                        crTableLogonInfoP21.ConnectionInfo.DatabaseName = "P21";
                        crTableLogonInfoP21.ConnectionInfo.IntegratedSecurity = true;
                        crReport.SetDatabaseLogon(crTableLogonInfoP21.ConnectionInfo.UserID, crTableLogonInfoP21.ConnectionInfo.Password, crTableLogonInfoP21.ConnectionInfo.ServerName, crTableLogonInfoP21.ConnectionInfo.DatabaseName, true);
                        // Apply it to this table
                        TableLogOnInfo tableLogOnInfo = crTable.LogOnInfo;
                        tableLogOnInfo.ConnectionInfo = crTableLogonInfoP21.ConnectionInfo;
                        crTable.ApplyLogOnInfo(tableLogOnInfo);
    I have watches set up and as I am stepping through the code above, before I get to the last instruction, crTable.LogOnInfo.ConnectionInfo.DatabaseName is P21, but after executing ApplyLogOnInfo() it gets wiped out and back to what it was. This has to be the reason I am getting an error when I try to load the report that the table isn't found. But it's not letting me tell it where the table is! Why does ApplyLogOnInfo() have no return type, it's void, if it's going to get an error?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  8. #8
    New Member
    Join Date
    Jul 2018
    Posts
    7

    Re: Crystal Report - two databases

    Hello,
    I try to create report using Crystal report viewer 9 ,MS SQL and VB6. I designed the report as Picture "CR1". My code is,
    Code:
    Dim Appl As New CRAXDRT.Application
    Dim Report As New CRAXDRT.Report
    
    Private Sub Print_Click()
    
       Set Appl = New CRAXDRT.Application
       Set Report = Appl.OpenReport(App.Path & "\Bill1.rpt")
       
       Set rs = New ADODB.Recordset
       
       strReso = "SELECT BI_BILLNO,IM_Description,IM_SPrice,BI_QUANTITY,BI_AMOUNT " + _
       "From BILLITEM,ITEMMASTER Where BI_BILLNO = " + Trim(txtBillNo.Text) + _
       " and BI_ITEMCODE = IM_ItemCode"
       rs.Open strReso, conMain, adOpenStatic, adLockReadOnly
       
       Report.DiscardSavedData
       Report.ParameterFields.GetItemByName("BillNo").AddCurrentValue txtBillNo.Text
       Report.Database.SetDataSource rs
       
       CRViewer91.ReportSource = Report
       
       CRViewer91.ViewReport
       
    
    End Sub
    Connection String is "str1 = "Provider=SQLOLEDB.1;User ID = hemantha; Password=19760207;Initial Catalog=Waruni_Sale_System;Data Source=""

    It is working. But, when I used two table as Picture "CR2", occur a error message as Picture "Err".
    What is the wrong?anyone can help?
    Name:  CR1.jpg
Views: 1178
Size:  34.8 KB Name:  CR2.jpg
Views: 1061
Size:  35.1 KBName:  Err.jpg
Views: 1844
Size:  27.3 KBPlease
    Last edited by plaahemantha; Jul 17th, 2018 at 02:47 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width