Solved

VB6 and Crystal report - parameter passing

Posted on 2004-10-12
12
917 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
ID: 12286752
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
ID: 12287512
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
ID: 12295184
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
LVL 100

Expert Comment

by:mlmcc
ID: 12297263
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
 

Author Comment

by:micky123
ID: 12298019
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
ID: 12305157
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
ID: 12308923
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
ID: 12314804
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
ID: 12540881
Probably solved this.

mlmcc
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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