Solved

VB6 and Crystal report - parameter passing

Posted on 2004-10-12
12
920 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
[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
  • 5
  • 3
12 Comments
 
LVL 101

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
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 
LVL 101

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 101

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 101

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 101

Expert Comment

by:mlmcc
ID: 12540881
Probably solved this.

mlmcc
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

707 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