Crystal Report: vb6

by: mlmccPosted on 2009-10-09 at 16:34:10ID: 25539683

Check this sample from Crystal

https://smpdl.sap-ag.de/~sapidp/012002523100006014062008E/scr_vb_api_security.exe

mlmcc

by: haroon-rashidPosted on 2009-10-09 at 22:43:32ID: 25540973

you need to create a function which re-connect your report with new database, e.g

Public Sub ConnectReportToDatabase(CR As CrystalReport)
'CR.LogonInfo(0) = "DSN =WapdaCSS; UID =" & DBUser & " ; PWD =" & DBPassword & " "
CR.Connect = "DSN =" & SystemDSN & "; UID =" & DBUser & " ; PWD =" & DBUserPwd & " "
End Sub

and call following statement in every report showing event.

Call ConnectReportToDatabase(Report)

Regards,

Haroon

by: ERWIINPosted on 2009-10-09 at 23:06:53ID: 25541013

Hi Haroon!
Sorry but can you correct my code below base of the code you had shared to me..
My Database is DB001 I'm using mssql7
My dbuser is OroSales
pwd = sa
I've got an error when I execute the code..
Thanks!

Public Sub Oro_Sold()
Call ConnectReportToDatabase(cr)
Dim str As String
 
            Set Report = New crysSOLDPRODUCT
            str = "{SOLDITEM.THEDATE} = #" & mainfrm.Mydate & "#"
            Report.RecordSelectionFormula = str
            Report.ReadRecords
            Call Report.SelectPrinter(Report.DriverName, Report.PrinterName, Report.PortName)
            Report.H1.SetText Header(0)
            Report.H2.SetText Header(1)
            Report.H3.SetText Header(2)
            cr.ReportSource = Report
            cr.ViewReport
            cr.Zoom 94
            Set Report = Nothing
End Sub
 
Public Sub ConnectReportToDatabase(cr As CrystalReport)
'cr.LogonInfo(0) = "DSN =WapdaCSS; UID =" & DBUser & " ; PWD =" & DBPassword & " "
cr.Connect = "DSN =" & SystemDSN & "; UID =" & DBUser & " ; PWD =" & DBUserPwd & " "
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

Select all Open in new window

by: Brook1966Posted on 2009-10-12 at 04:55:28ID: 25550360

Here is a function I use, passing the report byRef to connect it to a database

Public Sub SetReportDatabase(ByRef RPT As CRAXDRT.Report)
           Dim iErr As Integer
           Dim t As Integer
           Dim srT As Integer
10         iErr = 0
20         On Error GoTo PROC_ERR
30         For t = 1 To RPT.Database.Tables.count
'             RPT.Database.Tables(t).ConnectBufferString = "DSN=" & gDBNAME & ";;UseDSNProperties=0"
              ' YOUR CONNECTION FROM ABOVE
              RPT.Database.Tables(t).ConnectBufferString =  "DSN =" & SystemDSN & "; UID =" & DBUser & " ; PWD =" & DBUserPwd & " "
50         Next
           SetSubReportDatabase RPT
PROC_EXIT:
60         Exit Sub
PROC_ERR:
70         If InStr(1, UCase(ERR.Description), "READ-ONLY", vbTextCompare) > 0 Then Exit Sub
80         If iErr > 3 Then
90            ' YOUR ERROR TRAP HERE
100           Resume PROC_EXIT
110        Else
120           iErr = iErr + 1
130           Resume
140        End If
           
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

Select all Open in new window

by: ERWIINPosted on 2009-10-12 at 05:38:58ID: 25550655

Hi! Brook1966,
I've got an error that say's "Can't assign to read only property"

ConnectBufferString

I'm not sure my code below is correct! Can you tell what part of that code is wrong?
Thanks!

Public Sub IliganExpenses()
Call SetReportDatabase(Report)
 
Dim str As String
    Set Report = New crysExpensesIligan
    str = "{TblPora.Thedate} =#" & Format$(mainfrm.Mydate) & "# And {TblPora.Typep}='" & RP & "'"
    Report.RecordSelectionFormula = str
    Report.ReadRecords
    Call Report.SelectPrinter(Report.DriverName, Report.PrinterName, Report.PortName)
    Report.H1.SetText Header(0)
    Report.H2.SetText Header(1)
    Report.H3.SetText "Date: " & Format$(mainfrm.Mydate.Value, "MMMM DD YYYY")
    Report.H4.SetText Header(2)
    cr.ReportSource = Report
    cr.ViewReport
    cr.EnableExportButton = True
    cr.EnablePopupMenu = True
    cr.Zoom 94
    Set Report = Nothing
End Sub
Public Sub SetReportDatabase(ByRef RPT As CRAXDRT.Report)
           Dim iErr As Integer
           Dim t As Integer
           Dim srT As Integer
10         iErr = 0
20         On Error GoTo PROC_ERR
30         For t = 1 To RPT.Database.Tables.Count
             'RPT.Database.Tables(t).ConnectBufferString = "DSN=" & gDBNAME & ";;UseDSNProperties=0"
              ' YOUR CONNECTION FROM ABOVE
              RPT.Database.Tables(t).ConnectBufferString = "DSN =" & "Sample" & "; UID =" & "Winpos" & " ; PWD =" & "sa" & " "
50         Next
           SetSubReportDatabase RPT
PROC_EXIT:
60         Exit Sub
PROC_ERR:
70         If InStr(1, UCase(Err.Description), "READ-ONLY", vbTextCompare) > 0 Then Exit Sub
80         If iErr > 3 Then
90            ' YOUR ERROR TRAP HERE
100           Resume PROC_EXIT
110        Else
120           iErr = iErr + 1
130           Resume
140        End If
           
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:

Select all Open in new window

by: Brook1966Posted on 2009-10-12 at 06:01:49ID: 25550833

on what line of what module are you getting your error?
I had left the READ ONLY in the error trap to ignore such errors...

by: ERWIINPosted on 2009-10-12 at 06:14:39ID: 25550928

RPT.Database.Tables(t).ConnectBufferString = "DSN =" & SystemDSN & "; UID =" & DBUser & " ; PWD =" & DBUserPwd & " "

That line sir.. The ConnectBufferString

by: Brook1966Posted on 2009-10-12 at 06:20:13ID: 25550978

change line 70 based on the error you received..

70 If InStr(1, UCase(Err.Description), "READ-ONLY", vbTextCompare) > 0 Then Exit Sub

to
70 If InStr(1, UCase(Err.Description), "READ ONLY", vbTextCompare) > 0 Then Exit Sub

by: Brook1966Posted on 2009-10-12 at 06:58:27ID: 25551257

Your report objects
crysSOLDPRODUCT, crysExpensesIligan

Are these DSRs?

by: Brook1966Posted on 2009-10-12 at 07:21:10ID: 25551458

if they are created from a DSR then an alternate suggestion would be
for you to create your recordset and use that for your report.

dim RS as adodb.recordset
dim Conn as adodb.connection
conn.open "DSN =" & SystemDSN & "; UID =" & DBUser & " ; PWD =" & DBUserPwd & " "
SQL = "Your data Query"
RS.CursorLocation = adUseClient
RS.open SQL,conn,adOpenStatic, adLockReadOnly
set RS .activeconnection = nothing

m_Report.Database.SetDataSource RS

by: ERWIINPosted on 2009-10-12 at 07:31:15ID: 25551551

ICRDesigner Sir...
Still have the same error rigth now sir...

by: Brook1966Posted on 2009-10-12 at 07:47:04ID: 25551690

Remove the SetReportDatabase...

Try the ADO version instead.

by: ERWIINPosted on 2009-10-12 at 07:58:21ID: 25551786

I'll try it now.. Thanks!

by: ERWIINPosted on 2009-11-07 at 11:50:40ID: 31639516

Thank you so much..

Question

Crystal Report

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

vb6

Visual Basic Programming

Crystal Reports Software

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press