• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

How to get data from two table inCrystal Report


I'm new to Crystal Report, my VB application connect to Oracle server.
I want to show data  form two tables (T_LOG, T_SAVE have same field). Im my Vb project, i choose add "Crystal Report 9", i choose two tables above

when i run it alway appears "Login Fail", but i sure that i'm right, because when i choose one of two tables above, the report show ok.

Please help me.thanks
  • 3
  • 2
1 Solution
Have you created a relationship (link) between these two tables?  If not then you're going to have problems.  If the two tables are related then use the database expert to link them and that should solve your problem.

If the two tables contain the same type of data and should not be linked then instead of adding the tables to the report you should instead add a 'Command' and manually enter a union SQL query such as:

Select * from T_LOG
Select * from T_SAVE

This will combine the output from both tables into a single recordset that Crystal can use for the report.

Finally a third option would be to create a view on your Oracle server that combines both tables and then just use this view as the datasource for your report.


You should select database, link expert. From here you should define you rlink to the two tables

1. Select the field from first table
2. Select the second field from tbl 2to link and drag and drop the mouse
3. The linking expert will ask you what is the type of the join.
INNER JOIN: everything that matches from the both tables
LEFT JOIN: everything from 1 and information from 2 that the linked fields find match about
RIGHT JOIN: everything from 2 and information from 1 that the linked fields find match about

Also you can create a sql stored procedure and make that your record source. In that case you have to define the links in the database. But after that you dont need to do anything. If you need more help. let me know


blueadminAuthor Commented:
I'm sorry for my late.

I had done as frodoman said (T_LOG, T_SAVE  have the same fields name and datatype), T_LOG is only save data all lastest 2~3 days, and T_SAVE save data alll over 2~3 days), twm table have linked already but it still apears error("Login Fail").

My application in VB do function: user provide "from  time"  and "to time"  to get data. If the interval is less than 3 days, i will get data from T_LOG, otherwise T_SAVE.
But in case the the interval time has small amount in 2~3 days, and small amount is over 3 days( "From time:10/11", "To time:10/12" , "asume that now is: 10/15" for example). so i must union two tables

I only have the user name and password to login Oracle server and get data, i can't not do any thing on  the server.

But it alway appears error "login fail".
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

blueadminAuthor Commented:
I have test on Access and SQL Server DB, it' ok, but with Oracle. It fail.

please help me.
How are you showing this report?  If you're calling it from your application with 2 tables then you'll have to provide authentication credential for both tables (even if the credentials are the same) -- sounds to me like you aren't doing that.

If you show your code I can probably help.  Basically any db authentication that you're doing needs to be done for .table(0) and for .table(1) in the cases where you're using both tables.

blueadminAuthor Commented:
In my project VB, i choose add "Crystal Report 9" on "frmReport", i choose two tables above (design detail: show data on report i'm not show here(GUI), and i choose two tables:T_TXNLOG, T_TXNLOG_HISTORY, and choose some field to view data). Here is the code to join two tables in "frmReport"
         Dim Report As New CryRBranch
         Dim CRXDatabase As CRAXDDRT.Database
          'Connect to DB it's ok
           zDBName = "dbname"
           szUserName = "user"
           szPass = "pass"
          If ConnectToDB(szDBName, szUserName, szPass) Then
             Set rs = New ADODB.Recordset
             rs.ActiveConnection = cn
         Dim dInterval As Integer
        Dim szStartFullDate As String
        Dim szEndFullDate As String
        '   Format time follow formula mm/dd/yyyy hh:mm:ss

        szStartFullDate = frmMain.dtpBegin & " " & frmMain.txtBeginhh & ":" & frmMain.txtBeginmm & ":" & frmMain.txtBeginss
        szEndFullDate = frmMain.dtpEnd & " " & frmMain.txtEndhh & ":" & frmMain.txtEndmm & ":" & frmMain.txtEndss
        ' Verify the start day to now
        dInterval = DateDiff("d", szStartFullDate, Now)
        If dInterval > 3 Then
            ' Verify End day to now
            dInterval = DateDiff("d", szEndFullDate, Now)
            If dInterval > 3 Then
                rs.Source = "select * from T_TXNLOG_HISTORY where(T_TXNLOG_HISTORY.C_DATE between '" & sFromDate & "'  and '" & sToDate & "') and  (T_TXNLOG_HISTORY.C_TIME between '" & sFromTime & "' and '" & sToTime & "' ) order by c_time asc"

                'Union T_TXNLOG and T_TXNLOG_HISTORY
               ' If  condition satisfy, it will appear error
                rs.Source = "(select * from T_TXNLOG where(T_TXNLOG.C_DATE between '" & sFromDate & "'  and '" & sToDate & "') and  (T_TXNLOG.C_TIME between '" & sFromTime & "' and '" & sToTime & "' )) UNION ALL (select * from T_TXNLOG_HISTORY where(T_TXNLOG_HISTORY.C_DATE between '" & sFromDate & "'  and '" & sToDate & "') and  (T_TXNLOG_HISTORY.C_TIME between '" & sFromTime & "' and '" & sToTime & "' ))"
            End If
            '   Only get data in T_TXNLOG
            rs.Source = "select * from T_TXNLOG where(T_TXNLOG.C_DATE between '" & sFromDate & "'  and '" & sToDate & "') and  (T_TXNLOG.C_TIME between '" & sFromTime & "' and '" & sToTime & "' ) order by c_time asc"
        End If
        MsgBox "Connect error"
    End If

    Set CRXDatabase = Report.Database
    CRXDatabase.SetDataSource rs


It will appear error on 'COMMENT' line
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now