Sending SQL query to Crystal Reports

[zoom14151]

Hi there,

I hope someone can help?
I'm querying a database and attempting to send just the SQL query to a created report in crystal reports. I used a data control to display the data to MSflex grid and tried to print the data with the crystal report activeX control but cannot get the right printout. Seagate crystal reports stated I had to pass the SQl query to the activeX control. Can someone help with the code?

[Shah]

Hi,

You could try the following code:

CrystalReport1.SelectionFormula = "SQL Query code"
CrystalReport1.ReportFileName "name of report template.rpt"
CrystalReport1.Action = 1 'Send report out

Hope this will help.

[valkyrie]

Hi,
I'm assuming that you are using SQL server as your back-end and that your Crystal Reports is using a SQL Server database as your data source... so, here is a way to pass your SQL query to Crystal Reports.

1. First, build your SQL Select statement as a string, e.g.:

strSQLQuery = "SELECT * FROM pubs"



2. Then pass this string to Crystal reports like this,

CrystalReport1.SQLQuery = strSQLQuery




Hope that this helps... I find using the SelectionFormula property a little buggy at times... =)

Cheers

____________________________________
The VB Bugs in my Life...

[zoom14151]

The problem is I'm not using SQL server as the back end. I'm using Access and I hope thats not the problem. This is the code I used maybe it will help Remember I'm a beginner.
[Dim db As Database
Dim sqlsel As String
Dim Rs As Recordset
sqlsel = ("select * from Business1 where [beat] = '1331';")
Set db = Workspaces(0).OpenDatabase("A:\Business.MDB")
Set Rs = db.OpenRecordset(sqlsel, dbOpenSnapshot)
Set FrmDataGrid.DataAddR.Recordset = Rs]
I then display the data on a form named (FrmDataGrid) with a data control named (DataAddR) I tried what you said and just the data from the query is displayed, but not in the created crystal report, in the standard report.

[valkyrie]

Hi,
Do you mean that you want the result of this query to come out in your Crystal reports? I think having Access as your backend is of no problem. However, I need to know which data source you are using in Crystal Reports.
If everything is right, you must also realise that the Crystal Reports Active X control needs a connection of its own...

Cheers,


____________________________________
The VB Bugs in my Life...

[zoom14151]

That's correct, I need the result of the query to be printed with a report that I created. The crystal report activeX control is using the same data control as the one that MSflexGrid is using. If the Crystal report Control needs a connection of its own that means I need a second data control, but how do I pass just query to that control?

[valkyrie]

Hmmm...
Not exactly. In my current project, I am not using any data controls at all!! I just have a Datagrid to display my results via ADO. The Crystal Report ActiveX control's connection does not neccessary need a new data control of its own. It just needs some information on how to connect to your data provider, which in this case is Access. So your Crystal Report's ActiveX control's connection would follow your Flexgrid or data control's connection string.
I used to pass some SQL Query to my Crystal Reports after displaying it on my datagrid, but the report didn't print out correctly, that is, it didn't do any filtering, until I discovered that Crystal Report's ActiveX needed a connection too. I'm wondering, which Crystal Report version are you using? I'm using CR version 6.0 on VB5.
Hope that this helps...

____________________________________
The VB Bugs in my Life...

[zoom14151]

The version I'm using is the one that came with VB5 and I done know the version number. When you say filtering, do you mean the result of your query. I'm a little lost on what you mean by Crystal reports ActiveX needing is own connection. One quick question, if the SQL query is the recordset supplied to the data control, I'm I correct. That recordset is the filtered records from the database, then why is the crystal report control displaying the entire database only when I use the report that I created. I'm sorry but I'm starting to get lost!

[valkyrie]

Hi,
I think you are using Crystal Reports 4.6, the one which came with VB 5. Anyway, I think here is an example code about how my Crystal Reports ActiveX Control gets a hold of my data.
1. I setup my connection to MsAccess, via ADO (as an example)

Dim cnOne as ADODB.Connection 'Declaration

'In my module (code or form), I setup the connection

set cnOne = new ADODB.Connection
cnOne.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51" _
& "Data Source=C:\test.mdb;"
cnOne.Open 'Open the connection



2. Then I setup my Data control to connect to my table in the MDB file.

3. Then I setup my Crystal Report's ActiveX control's connection to connect to my MDB file (as a data source)

CrystalReport1.Connect = "DSN=TEST;DSQ=C:\test.mdb"



The DSN refers to the Data Source you have setup in the ODBC Data Sources in the Control Panel, which is like this:
1. Select the System DSN tab of the ODBC Data Sources
2. Select Add
3. Select the "Microsoft Access Driver"
4. Enter the Name of your Datasource (which is TEST in this case)
5. Select the location of where your Database is under the Database frame (which is C:\test.mdb in this case)
That is it!
This example shows how you have setup your Data control to use a database and then, having Crystal Report connect to the same database as your data control. I think when you build your report in the Crystal Report Designer program, you will have to setup your datasource connection as well, unless you have a mixture of SQL and MsAccess in your report... (which gave me weird results...)

Anyway, I hope this helps... sorry to have confused you... (I think the Visual Basic Book Online is of a little help... but... is somewhat lacking on the coding side)

If you have any other questions, I think you can visit the Seagate site,
http://www.seagatesoftware.com


Cheers,

____________________________________
The VB Bugs in my Life...

[zoom14151]

You lost me!!! I'm about to throw the project out the window.

[valkyrie]

Hi,
Don't give up just yet. I am building a sample project and will send it to you. Can you give me your e-mail address?



____________________________________
The VB Bugs in my Life...

[zoom14151]

My E-mail address is [email protected] .