Solved

How to get data from two table inCrystal Report

Posted on 2004-10-15
6
237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 

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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Suggested Courses

737 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