• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

how to make a Report in Crystal Reports using MySQL and VB6?

currently im using VB6 and MySql. how do i connect to MySQL databse using CrystalReports 8? how do i retrieve data from MySql?

a sample script would be a great help.


tnx,

ehyb
0
ehyb
Asked:
ehyb
1 Solution
 
hmadhurCommented:
I have not worked on MySQL. But I will try to help you.

Can you create an ODBC connection to MySQL database?
0
 
mlmccCommented:
From another EE question

You may use Data Environment to manage your database connection and sql command easily.
If you want to use data environment click "Project" -> "Add Data Environment" in menu bar

If you don't want to use data environment, you many use this connection string:
"DSN=TopSites;DATABASE=TopSites;UID=username;PASSWORD=password;PORT=3306;"
or just use
"DSN=TopSites;" if you have set username and password in ODBC manager.

mlmcc
0
 
hmadhurCommented:
Dear ehyb,

First create a odbc connection as suggested by mlmcc.

Choose Standard Report from Report Gallery (the dailog box shown when you choose new from File menu). The Report Expert Dialog Box will be display.

From the list on the left hand side you can choose SQL/ODBC option or scroll down to choose Active Data Option. If you choose SQL/ODBC you will be asked for the DSN name. Select the DSN created for your MySQL database and click OK. You will be prompted with all the tables and views you have created in your database.

Now if you choose Active Data, you will be prompted to choose a DSN. Select the DSN created for the Database and click Next. The next screen will display a dialog where you can select tables or you can write a SQL statement. Click on finish. The table / recordset(if you write a sql statement) will be selected. Rest is the same routine to create the report.

I feel Active Data is the best way to access data in Crystal Reports because you can enter a SQL Statement. You can write the SQL Statement that you cant depict in the visual linking expert.

Hope this helps.

Thanks mlmcc. Your comment will help me in my next project where I may need to use MySQL.

Regards
Madhur
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ehybAuthor Commented:
i have this connection type in my program:

Connection.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + sServer + ";DATABASE=" + sDatabase + ";UID=root;PASSWORD=test"


although mlmcc's connection string is correct, i would like to creat a Report from Crystal Reports without using a DNS. Would that be possible?

-ehyb
0
 
hmadhurCommented:
Yes,

You can use the ActiveData option. In ActiveData you can directly insert the connectionstring.

I would also like to add that if you select ActiveData and provide the DSN and then while displaying the report you can use the SetTablePrivateData function to provide a recordset at runtime. So you wont need to create the DSN on the clients side. I have tested this and it works. I will tell you how it works. You can use this with AcitveX Control and also the Report Designer Component. Follow the steps given below:

1. Create a new report by selecting ActiveData in the Report Expert dialog.
2. It will ask you for the data source.
3. Specify the DSN you created in ODBC(ADO) combobox and click on next.
4. Click on SQL and write the SQL statement that will extract the records for your report. (Dont select a table. You will be able to select only one table and no more).
5. Click on Finish.

Then create the report by selecting the fields you require and save it. For our reference we will save it as Report1.rpt.

Now open Visual Basic. Select the Component Crystal Report ActiveX Control and insert it in your form. Name it as CReport.

Now in the form load event write the following code:

Dim objAdoRset as new ADODB.Recordset
Dim objAdoCn as new ADODB.Connection
Dim strSql as String

objAdoCn.ConnectionString = <your connection string>
objAdoCn.Open

strSql = <the sql statement you gave while designing the report>
objAdoRset.Open strSql, objAdoCn, adOpenDynamic, adLockOptimistic

CReport.ReportFileName = app.path & "\Report1.rpt"
CReport.DiscardSavedData

CReport.SetTablePrivateData 0, 3, objAdoRset  'See Developers help for more information on this function
CReport.Action = 1

Execute the VB project and the report comes to life.

I have been using Crystal Reports for 2 years and I have been doing this without any bugs. You will need the DSN only while designing the report. Once the Report has been designed use it in VB as I have told you. You will not need the DSN on client side. The report will use the Recordset you provided at runtime.

If you find any problems I will be happy to send you a small sample application to you. You can contact me at hmadhur@indiatimes.com

Warm Regards
Madhur
0
 
ehybAuthor Commented:
tnx! it was a great help
0
 
SqueebeeCommented:
For future reference I have a full article on the subject at http://www.vbmysql.com/articles/crystalvbmysql.html

Regards,
Mike Hillyer
www.vbmysql.com
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now