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

VB6 and Crystal report - parameter passing

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
micky123
Asked:
micky123
  • 5
  • 3
1 Solution
 
mlmccCommented:
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
 
vidruCommented:
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
 
micky123Author Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
mlmccCommented:
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
 
micky123Author Commented:
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
 
mlmccCommented:
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
 
micky123Author Commented:
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
 
mlmccCommented:
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
 
mlmccCommented:
Probably solved this.

mlmcc
0
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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