Solved

How to get data from two table inCrystal Report

Posted on 2004-10-15
6
231 Views
Last Modified: 2012-06-27
Hello

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
0
Comment
Question by:blueadmin
  • 3
  • 2
6 Comments
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 12317865
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
UNION ALL
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.

HTH

frodoman
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12318558
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

Regards
Emre

0
 

Author Comment

by:blueadmin
ID: 12335378
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".
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:blueadmin
ID: 12335677
I have test on Access and SQL Server DB, it' ok, but with Oracle. It fail.

please help me.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12337977
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.

frodoman
0
 

Author Comment

by:blueadmin
ID: 12343721
ok
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
         '------------------------------
          Query
         '------------------------------
         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"
            Else

'COMMENT
               '-------------------------------------------------------
                '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
        Else
            '   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
       
        rs.Open
    Else
        MsgBox "Connect error"
    End If

    Set CRXDatabase = Report.Database
    CRXDatabase.SetDataSource rs

   CRViewer91.ViewReport


It will appear error on 'COMMENT' line
0

Featured Post

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.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
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.

757 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

20 Experts available now in Live!

Get 1:1 Help Now