Solved

VB6 and Crystal report - parameter passing

Posted on 2004-10-12
12
910 Views
Last Modified: 2008-02-01
I am using VB6 and crystal reports8.5
I have a field in which the user enters data and
which then generates report accordingly.

In crystal reports i have set the parameter field
as param1 - strings.

In crystal reports I have put an SQL query

SELECT YEAR,CITYCODE, HOTELNAME
FROM
   HOTELTARIFF                                        
WHERE
    (CITYCODE = ' '' & param1 & " ' )


AM I allowed to enter my param1 in my
SQL Query . If yes then how ??

and I also want to pass my param1 value from VB6
not by any prompt from CR8.5

In vb6 code I have entered as

cr1.ParameterFields(1) = "param1;" & Combo1.Text & "; TRUE"

Am I doing something wrong somewhere ???  Pls advise

thnks            


0
Comment
Question by:micky123
  • 5
  • 3
12 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I have never used a parameter that way but it may be allowed.

You need to use the correct name which is {?param1}

I usually will build the report on a query without the where clause and then add it through code

cr1.ReplaceSelectionFormula = "CITYCODE = ' '' & Combo1.Text  & " '"

mlmcc
0
 
LVL 13

Expert Comment

by:vidru
Comment Utility
You could use a Record Selection Formula in the report like this:

{HOTELTARIFF.CITYCODE} = {?param1}

Then, passing the parameters from VB like this should work:
cr1.ParameterFields(1) = "param1;" & Combo1.Text & "; TRUE"

The only issue I see there, is that the OCX uses 0-based arrays, so if you've only got one parameter field in the report, you'd need to change it to cr1.ParameterFields(0).....

-dave
0
 

Author Comment

by:micky123
Comment Utility
THIS IS MY QUERY ????  I HOPE I HAVE EXPLAINED IT PROPERLY ????

On my form it is

   Year    :   ........ ( user enters data)
Citycode :  ............. (user enters data)

paxtype      FIT ___   Group ____  (checkboxes which user selects)

********************************************************************
I have  my code  in VB6  as  :


Private Sub Cmdbrowse_Click()

If Check1.Value = 1 Then
  a = "F"
Else
  a = ""
End If
If Check2.Value = 1 Then
  b = "G"
Else
  b = ""
End If

*****  If user leaves year and citycode as BLANK then report
****    selects all records and report comes accordingly

If Val(Text1.Text) = 0 And Trim(Combo1.Text) = "" Then
 With CrystalReport1
 .ReportFileName = App.Path & "\rpt3-a.rpt"
 .DiscardSavedData = True
 .RetrieveDataFiles
 .ReportSource = 0
 .SQLQuery = "select citydata.citycode,hoteltariff.mapx,hoteltariff.apswb,hoteltariff.apx
from  citydata,hoteltariff
where
  (citydata.citycode=hoteltariff.citycode)and (hoteltariff.paxtype = '" & a & "' or hoteltariff.paxtype = '" & b & "')
order by
   roomcateg asc,paxtype asc"
 .Destination = crptToWindow
 .PrintFileType = crptCrystal
 .WindowState = crptMaximized
 .WindowMaxButton = False
 .WindowMinButton = False
 .PrintReport
End With


**********   This is vb6 code    *************


In Crystal    Sql Query I have put  


SELECT
    CITYDATA.CITYNAME, HOTELTARIFF.MAPDWB, HOTELTARIFF.MAPX,
    HOTELTARIFF.APSWB, HOTELTARIFF.APDWB, HOTELTARIFF.APX
FROM
    CITYDATA CITYDATA,
    HOTELTARIFF HOTELTARIFF              
WHERE
    (CITYDATA.CITYCODE = HOTELTARIFF.CITYCODE)          
ORDER BY
    CITYDATA.CITYNAME ASC,
    HOTELTARIFF.HOTELNAME ASC

**************  crystal query  **************

Uptil here I am Ok   (let me know if I am doing something wrong !!! )

Now when the user enters either of the data  ie  year or citycode or both
 I am handicapped.......

I have entered a param1(string )   in cr8 and in the record selection formula
 i have entered  
    {hoteltariff.citycode} = {?param1}

( When I run from crystal it asks me for an input n the result is OK)

But when I try from vb6 after putting this line

crystalreport1.parameterfields(0)="param1;"&combo1.text&";True"

I get all citycodes instead of the One the user has entered. Th other
funny part is that crystal also prompts me for entering the param1 Value
  WHY ????

PLS ADVISE n HELP ME

thnks in advance
 
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You might try joining the tables rather than using the where clause.  Also since you are using the hotel tariff code you need to return it from the database.

SELECT
    CITYDATA.CITYNAME, HOTELTARIFF.MAPDWB, HOTELTARIFF.MAPX,
    HOTELTARIFF.APSWB, HOTELTARIFF.APDWB, HOTELTARIFF.APX,

 HOTELTARIFF.CODE

FROM
    CITYDATA CITYDATA JOIN
    HOTELTARIFF HOTELTARIFF  ON
                 CITYDATA.CITYCODE = HOTELTARIFF.CITYCODE
ORDER BY
    CITYDATA.CITYNAME ASC,
    HOTELTARIFF.HOTELNAME ASC


mlmcc
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:micky123
Comment Utility
I will try the Join   BUT I am more interested in passing my parameter from VB6.
I have put my code above . I hope I have tried to explain myself

Thnks
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I think the parameter is being passed correctly.  The field you are comparing to doesn't exist in the query so the comparison returns true for all occurances.  One of the undocumented features I have found with Crystal is the NULL = Everything

mlmcc
0
 

Author Comment

by:micky123
Comment Utility
You are right . So where do i put the parameter in my query ??? In the Vb6 side

ie  crystalreport1..SQLQuery = "select hoteltariff.apswb,hoteltariff.apx .........
        from  citydata,hoteltariff
        where
          (CITYDATA.CITYCODE = HOTELTARIFF.CITYCODE)  and (citycode = ?param1)
       order by ......

Or  do I put my parameter inside the Crystal report Query line ????

Pls advise ??
thnks



0
 
LVL 100

Accepted Solution

by:
mlmcc earned 125 total points
Comment Utility
If you are going to use a parameter then it goes on the Crystal side since Crystal is the only piece that can use it.

Put it in the SQL or a better place in the RECORD SELECTION
Open the report
Click REPORT --> SELECT EXPERT

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Probably solved this.

mlmcc
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

9 Experts available now in Live!

Get 1:1 Help Now