Dynamic Crystal Reports 9 with VB6

[JerseyKid]

I am loading a crystal report via VB6 and the report is going to show mobile phone numbers and ICCID numbers, Using the following code I can get the report to show the first 1000 mobile numbers. But if I want to get the report to show a different range of numbers it just brings back the same set of 1000 numbers

Private Sub Form_Load()
 
Dim Appl As New CRAXDRT.Application
Dim Report As New CRAXDRT.Report
Dim Rs As ADODB.Recordset
Dim Conn As Connection
Dim strSQL As String
 
Set Conn = New Connection
 
With Conn
    'Set up an ODBC connection to Cerillion to the database defined in SystemDSN
    .ConnectionString = "Provider=OraOLEDB.Oracle;User Id=Super;Password=Password;Data Source=" & ServerName & ";"
    'Open the ODBC connection
    .Open
 
End With
 
If frmJTPrint.optAll = True Then
 
    strSQL = "select Access_NO,ICCID from Test_Dec_Puk_Details tdPD, Test_Dec_GSM_PP_Access_Numbers tdGPAN where tdPD.ICCID = Ltrim(tdGPAN.Assoc_Access_No) and ICCID_Full = '" & FileIDToPrint & "'"
 
ElseIf frmJTPrint.optPhoneNumberRange = True Then
 
    strSQL = "select Access_NO,ICCID from Test_Dec_Puk_Details tdPD, Test_Dec_GSM_PP_Access_Numbers tdGPAN where tdPD.ICCID = Ltrim(tdGPAN.Assoc_Access_No) and ICCID_Full = '" & FileIDToPrint & "' and Access_NO >= '" & tempFrom & "' and Access_NO <= '" & tempTo & "'"
 
End If
 
Set Rs = New ADODB.Recordset
Set Rs = Conn.Execute(strSQL)
Set Appl = New CRAXDRT.Application
Set Report = Appl.OpenReport(App.Path & "\Labels.rpt", 1)
Report.Database.SetDataSource Rs, 3, 1
 
CRView.Refresh
 
CRView.ReportSource = Report
CRView.ViewReport
End Sub

I am using two sql statements (one for "All" mobile numbers to be shown with a specific ID and one for a range of numbers, from A to B)
These dont seem to be working, any help would be appreciated

Thanks

[Hack]

You are calling this from Form_Load. This would mean that in order to run it again you would have to close the form and open it again. Since it runs the moment the form is loaded, how do you get to pick which option you want in the first place?

If one is set as default, then that would be the one that run everytime the form loaded.

[JerseyKid]

There is a form before this form where the user selected to view all mobile numbers or just a selection (which they enter there and then). Then a "Preview" button is selected and the program runs this form

[Hack]

If you select the number range first, does it display properly?

[JerseyKid]

no it just displays the first lot of numbers it calls from the DB, I have tried taking and loading both SQL statements into and SQL editor and they both bring back the correct information...

[Hack]

Try this experiment and tell me what happens

'comment out the lines in your program that I have commented out here
'If frmJTPrint.optAll = True Then
 
   ' strSQL = "select Access_NO,ICCID from Test_Dec_Puk_Details tdPD, Test_Dec_GSM_PP_Access_Numbers tdGPAN where tdPD.ICCID = Ltrim(tdGPAN.Assoc_Access_No) and ICCID_Full = '" & FileIDToPrint & "'"
 
'ElseIf frmJTPrint.optPhoneNumberRange = True Then
 
    strSQL = "select Access_NO,ICCID from Test_Dec_Puk_Details tdPD, Test_Dec_GSM_PP_Access_Numbers tdGPAN where tdPD.ICCID = Ltrim(tdGPAN.Assoc_Access_No) and ICCID_Full = '" & FileIDToPrint & "' and Access_NO >= '" & tempFrom & "' and Access_NO <= '" & tempTo & "'"
 
'End If

In other words, the only SQL statement that can possibly run is the one for the number range, so lets see what happens.

[JerseyKid]

It just brings back ALL the numbers from the DB and it ends up be like 143 pages long, lol...The user should have to enter two mobile numbers (from - to) for this too work...

[Hack]

The query for optPhoneNumberRange brings back ALL numbers?

On the form with the option button, do you have two textboxes in which the user can enter a number range if that is what they want?

[JerseyKid]

yes that is correct and in here they enter the range of numbers they want to see then select a "Display" button which displays the numbers they want to print via a listbox...This display via a listbox works fine for viewing both all the numbers or a select range...

[Hack]

Put a break on the Form Load and step through you code to see what the program is doing.

You say the queries work fine when you run them in SQL/Plus right?

Also, do a Debug.Print strSQL right after your End If. I want to see exactly what is getting passed back to your database.

[JerseyKid]

This is what is being parsed to the DB at the end of the IF STATEMENT:

select Access_NO,ICCID from Test_Dec_Puk_Details tdPD, Test_Dec_GSM_PP_Access_Numbers tdGPAN where tdPD.ICCID = Ltrim(tdGPAN.Assoc_Access_No) and ICCID_Full = '3'

[JerseyKid]

This is just a quick bump for this post... Still having the same problem with all data being returned? I have a feeling the Where Clause isnt being parsed to my CR9? It seems to bring back the data upto the point of the Where Clause...Not sure if this is the answer to the problem but any help would be much appreciated?