?
Solved

VB6 and Crystal report - parameter passing

Posted on 2004-10-12
12
Medium Priority
?
936 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
9 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
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
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 500 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Loops Section Overview
Suggested Courses

809 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