?
Solved

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

Posted on 2003-03-17
7
Medium Priority
?
411 Views
Last Modified: 2013-12-25
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
Comment
Question by:ehyb
[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
7 Comments
 
LVL 1

Expert Comment

by:hmadhur
ID: 8158279
I have not worked on MySQL. But I will try to help you.

Can you create an ODBC connection to MySQL database?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8158578
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
 
LVL 1

Expert Comment

by:hmadhur
ID: 8158800
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
Technology Partners: 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!

 

Author Comment

by:ehyb
ID: 8164623
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
 
LVL 1

Accepted Solution

by:
hmadhur earned 320 total points
ID: 8164772
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
 

Author Comment

by:ehyb
ID: 8184751
tnx! it was a great help
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9244217
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

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!

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

762 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