Using MySQL, Visual Basic 6, and Crystal Reports 9
Introduction
So, you have a huge amount of data in your MySQL™ database, and your users can move data in and out at will with the VB application that you wrote, but now they want the data in a more meaningful form. In the first Visual Basic application I wrote, I tried to give them summaries and reports by creating forms with dynamically allocated arrays of labels, building the form as I worked my way through the data.
While such a method worked, and the form looked fairly nice, making changes was difficult. Moving elements around on the form required modification of various constants that were used to control placement and spacing, and changes required a re-compile (granted, I could have loaded the constants from a text file, but I was new). Later, I discovered Crystal Reports®, a powerful tool for report generation. With Crystal Reports, I was able to rebuild my code-based report in a matter of minutes, and modify it at will. When a change was made, I simply had to replace the report file, and Visual Basic would show the new report the next time it was viewed. This really helped me save time on data reporting, and I was able to churn out new reports any time a user would request one.
One of the challenges I had when trying to work with Crystal Reports was getting the report to show in my VB application with data generated using an ADO recordset and MySQL. In fact, with the version of Crystal Reports that comes included with VB6 (version 4.6 of Crystal Reports), this feature was not really supported. I personally prefer to develop software without using a DSN, but in this case I had to make an exception and set up a DSN on my target system so that Crystal Reports could access live data, with the VB application modifying the selection formula of the report in order to allow the user control over what data was displayed in the report.
Fortunately, when I found myself a developer position in a company that is a reseller for Crystal Reports, and I found myself able to work with Crystal Reports 9. Moving from Crystal Reports 4.6 to Crystal Reports 9 was an improvement similar to moving from hard-coding reports to using Crystal Reports. Version 6 of Crystal Reports introduced Active Data, which allows us to create reports based on ADO provided data. Version 8 of Crystal Reports introduced the Report Designer Component, which greatly improved the way reports are loaded and viewed in VB. In this article I will cover designing and displaying reports using Visual Basic 6, Crystal Reports 9, and MySQL. In a future article I will also cover displaying data with Crystal Reports versions 6 and 4.6.
This article will assume that the reader is using the MySQL RDBMS, but should apply to developers using other database management systems. For an overview of why MySQL is a good choice for Visual Basic developers, see the Why VB/MySQL article on this site. Further information on MySQL, along with full downloads, can be obtained from the MySQL web site. This sample will require the latest version of Connector / ODBC (formerly MyODBC), available for download here (currently 3.51.06). As I stated, this article will be based on Crystal Reports 9 (evaluation copy available for download here). I recommend installing maintenance pack 1 and all monthly hot fixes, available through support.crystaldecisions.com.
On the Visual Basic side, I recommend you have service pack 5 installed for VB, which you can download here. Additionally, the latest version of MDAC (Microsoft Data Access Components) should be installed, and can be found here (currently 2.7 SP1). Finally, this article applies to ADO 2.7 and VB6. It is not applicable to ADO.NET under VB.NET (Unless you reference ADODB in your project and use it for database access).
Creating The Report
Anatomy Of A Report
A crystal report is composed of several sections:
- Report Header
- Page Header
- Group Header
- Detail Line
- Group Footer
- Page Footer
- Report Footer
Anything placed in the report header and footer will be displayed once in the report. Items in the page header and footer will be displayed on every page. Group headers and footers will be shown once per group of detail lines, and there will be a detail line for every row of data. All sections can be either hidden or suppressed so that they do not appear in the report: hidden sections can be displayed by ‘drilling-down’ through double clicking on a related section that is displayed, suppressed sections are not displayed at all.
Various information can be placed in any of the report sections including:
- Text Labels
- Database Fields
- Images
- Graphs
- Summaries
- Formulas
Most of these are self-explanatory. Summaries include totals, counts, averages and other aggregate values based on different fields. Formulas allow the report designer to add complex logic and formatting to different fields.
Creating The Field Definitions
Creating a typical report is very simple. First we define the fields that we will be reporting on, then arrange those fields on the form, grouping and summarizing as needed. A field definition file allows us to define the data we will be accessing in our report. Lets start with the table we will be reporting from:
CREATE TABLE report(
transactionid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
salesman CHAR(20) NOT NULL,
region CHAR(20) NOT NULL,
amount FLOAT
);
To create a field definition, we bring up the Crystal Reports database expert. The database expert can be accessed by opening a blank report, or by clicking theDatabase Expert menu option under the Database drop-down menu. The available connections treeview will have a Create New Connection branch that when expanded will reveal the Field Definitions Only option. By clicking Create File… button on the file browser dialog, a tool for creating field definition files will be displayed.
Field definition files are tab separated with .ttx extensions with four values:
- field name
- field type
- field length (if string)
- sample data
Field name is taken directly from the name of the field in your recordset (this could be the field name from your underlying table, or the alias you assigned to it using the AS keyword in your query), field type is the datatype of the field, field length specifies the width of a string, and sample data is a static value used to assist in laying out the fields.
A field definition file for the table defined above might look like the following:
transactionid uLong 1 salesman String 20 George region String 20 Calgary amount Currency 30.00
Once we have created our field definition file, we can use it to design our report by dragging it from the ‘Available Data Sources’ box to the ‘Selected Tables’ box in the Database Expert and clicking the OK button. Before we go any further, it should be noted that a .ttx file can also be created automatically from a recordset. This can be useful for determining which report datatypes to use, But I find you still need to go in after and give meaningful sample values. Here is a quick sample of how to automatically generate a .ttx file, create a new project with a reference to ADO 2.7 and place the following code in the code section of form1.:
Option Explicit
'p2smon.dll MUST BE PRESENT IN SYSTEM DIRECTORY
'IT CAN BE FOUND IN THE \TOOLS\DEVELOPERS SUBFOLDER
'OF YOUR CR9 DIRECTORY
Private Declare Function CreateFieldDefFile Lib "p2smon.dll" (lpUnk As Object, _
ByVal fieldDefFile As String, ByVal bOverWriteFile As Long) As Long
Private Sub Form_Load()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim result As Long
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=123.456.789.101;" _
& "DATABASE=test;" _
& "UID=myuser;" _
& "PWD=mypass;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
conn.Open Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM report", conn, adOpenStatic, adLockReadOnly
'PASS RECORDSET, PATH TO GENERATED FILE, TRUE/FALSE TO OVERWRITE
result = CreateFieldDefFile(rs, App.Path & "\report.ttx", True)
If result Then
MsgBox "File " & App.Path & "\report.ttx created successfully", vbInformation, "Success!"
Else
MsgBox "Failed to create ttx file!", vbCritical
End If
End Sub
Placing Fields, Grouping, and Summarizing
By choosing the Field Explorer under the View menu, we can use the field explorer to choose fields and place them onto the form. After dragging each of the fields onto the form, we can group data to make it more meaningful.
Under the Report menu, the Group Expert option will bring up the Grouping Expert. With the grouping expert we can choose which fields we will be grouping by (in our case region, then salesman).
Once grouping is specified, we will see the two groups added to our report, with the group names now shown as part of the main report in the group header sections. By right-clicking on these two section headers, we can choose the suppress option to prevent these group headers from being displayed.
To summarize data, we click on the field we wish to summarize, and choose Summary from the Insert menu. The Insert Summary dialog gives us options to choose which field to summarize (in our case the amount field), what summary to perform (we will calculate the Sum), and where the resulting value is to be placed (will will place one in the footer for each group and in the report footer). The summaries are then available on the report for us to format and place.
Adding Formulas
Once our data is placed on the report, we can create calculated fields by creating formulas. Formulas can be written in either the Crystal Reports syntax, or in CR9, we can use Visual Basic syntax. Lets add a simple formula to display commission for our sales; in the field explorer, right click on the Formula Fields header and choose New, this will display the formula editor after prompting you for a formula name (commission will suffice for a name). In the top right you will find a drop-down box displaying Crystal Syntax, change it to say Basic Syntax so we can work with Visual Basic type syntax. Next, in the formula window type the following:
formula = {article.amount} * 0.05
Now you can click save and close the formula window. The new formula is now available in the field browser and can be placed on the report in the detail section. We can add summaries of the formula if we wish.
Well, that gives us a basic report, you can press the F5 key to see a preview of the report using the sample data you entered in your .ttx file. You next step will be to show the report to your users in your VB application using data retrieved from MySQL.
Displaying The Data
With Crystal Reports 8 and higher, the OCX Crystal Viewer is done away with to be replaced with the Report Designer Component. This approach allows for a more object-oriented approach to displaying reports. One benefit is that the report viewer is not placed directly on the form, allowing for the report to appear in a specific region of the form without spawning a separate form.
The process for displaying the report in VB is fairly simple:
- instantiate ADODB Connection and Recordset objects
- populate recordset object with query
- instantiate Crystal Reports Application and Report objects
- open report
- bind report to recordset
- bind report to viewer
- show report
To display a report in your Vb application you will need the following:
References:
- Microsoft ActiveX Data Objects 2.7 Library
- Crystal Reports 9 ActiveX Designer Run Time Library
Components:
- Crystal Reports Viewer Control 9
You will also need a Crystal Reports Viewer Control placed on your form named CRViewer.
Preparing Data
First we are going to need the data that we will be reporting on. This is done using ADO:
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient 'SERVER-SIDE NOT RECCOMENDED
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=123.456.789.101;" _
& "DATABASE=test;" _
& "UID=myusername;" _
& "PWD=mypass;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841 'SET ALL PARAMETERS
conn.Open 'THESE OPTION VALUES ARE BEST FOR VB
conn.Execute "DROP TABLE IF EXISTS report", , adExecuteNoRecords
'ADEXECUTENORECORDS IS USED FOR
'QUERIES THAT RETURN NO DATA
'(CREATE, INSERT, DELETE, ETC.)
conn.Execute "CREATE TABLE report(" _
& "transactionid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY," _
& "salesman CHAR(20) NOT NULL," _
& "region CHAR(20) NOT NULL," _
& "amount Float)", , adExecuteNoRecords 'THE TABLE WE WILL REPORT OFF OF
'PUT SOME DATA IN THE TABLE
conn.Execute "INSERT INTO report(salesman, region, amount) VALUES('George','Calgary',30.00)", , adExecuteNoRecords
conn.Execute "INSERT INTO report(salesman, region, amount) VALUES('George','Calgary',15.00)", , adExecuteNoRecords
conn.Execute "INSERT INTO report(salesman, region, amount) VALUES('George','San Jose',10.00)", , adExecuteNoRecords
conn.Execute "INSERT INTO report(salesman, region, amount) VALUES('Jerry','Calgary',30.00)", , adExecuteNoRecords
conn.Execute "INSERT INTO report(salesman, region, amount) VALUES('Jerry','San Jose',100.00)", , adExecuteNoRecords
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM report", conn, adOpenStatic, adLockReadOnly
Preparing and Displaying the Report
Now that we have a recordset, we just need to load the report, link it to the recordset and the viewer, and display it:
Dim crystal As CRAXDRT.Application 'LOADS REPORT FROM FILE Dim report As CRAXDRT.report 'HOLDS REPORT CRViewer.DisplayBorder = False 'MAKES REPORT FILL ENTIRE FORM CRViewer.DisplayTabs = False 'THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED CRViewer.EnableDrillDown = False 'REPORT DOES NOT SUPPORT DRILL-DOWN CRViewer.EnableRefreshButton = False 'ADO RECORDSET WILL NOT CHANGE, NOT NEEDED Set crystal = New CRAXDRT.Application 'MANAGES REPORTS Set report = crystal.OpenReport(App.Path & "\report1.rpt") 'OPEN OUR REPORT report.DiscardSavedData 'CLEARS REPORT SO WE WORK FROM RECORDSET report.Database.SetDataSource rs 'LINK REPORT TO RECORDSET CRViewer.ReportSource = report 'LINK VIEWER TO REPORT CRViewer.ViewReport 'SHOW REPORT Do While CRViewer.IsBusy 'ZOOM METHOD DOES NOT WORK WHILE DoEvents 'REPORT IS LOADING, SO WE MUST PAUSE Loop 'WHILE REPORT LOADS. CRViewer.Zoom 94
Once the report is displayed, we can then cleanup out objects:
rs.Close 'ALL BELOW HERE IS CLEANUP Set rs = Nothing conn.Close Set conn = Nothing Set crystal = Nothing Set report = Nothing
Finally, we can add some code to the Form_Resize event to ensure that our report occupies the entire form:
Private Sub Form_Resize() 'MAKE SURE REPORT FILLS FORM CRViewer.Top = 0 'WHEN FORM IS RESIZED CRViewer.Left = 0 CRViewer.Height = ScaleHeight CRViewer.Width = ScaleWidth End Sub
Conclusion
And there you have it. If all goes well, you now have a report displaying on your VB form, generated from MySQL data. From here you can look at creating more complex formulas, pulling data from multiple recordsets, and reporting on queries more complex than your typical SELECT * query.
Crystal Reports is a registered trademark of Crystal Decisions: www.crystaldecisions.com
Nice.
how can i change the connection string at runtime? tks.
pls do try to give examples on how to print official receipt using vb6-Mysql… thanh you.
Hi,
Would it be possible for you to let me know how to create a Data Definition file ( Ascii text file with placeholders to represent data fields). I would appreciate your assistance in this matter.
Thanks and regards
Why cant i get to see someone acctually showing this sort of thing using ODBC instead of ADODB or OLEDDB………….. so frustrated i need to do my project soon im bloody stuck on this thing if anyone some samples how to display data using crystal report would be nice. i am using Visual Studio.NEt 2003 and MySQL i need help.
email tosi_uk@yahoo.co.uk
I solved the problem I was having. I was picking up the data from Oracle recordset and this was filling up the data in the data definition table. Thanks for the solution.
Very nice…
That really helped me.
hi all,
if anyone needs help on displaying the data on CR from VB.NEt using odbc let me know sorted…
can u tell me that.. how to attache crystal report 8 with viusal basic 6.0..through parameters.
thx
COOL! Thanx! This helped me!
Please send me a sample program Using MySQL, Visual Basic 6, and Crystal Reports 9
please send me a simple programe how to make creystal report in visual basic
how we use sql as back hand in visual basic
Can someone send me code for displaying crystal report using VB6, Crystal Report 11 and ODBC Connection…
Thanks
Hi
can anyone tell me how to read the used Database fields alone from a crystal report file using Visual Basic Code
HI,
I still can view full data list of a table. I can not see separate rows of data when run a query accordingly.
Povil
HI,
I still can view full data list of a table. I can not see separate rows of data when run a query accordingly. View all the data at design time still.
Povil
Please send me a sample program Using MySQL, Visual Basic 6, and Crystal Reports 9
hi
i want to display start date and end date in report header ; directly to report(not through any database field) how can i do?
i used vb2005 and crystal report 11.
pls help me
Private Sub cmdPreview_Click()
dbOpen
CRViewer.DisplayBorder = False ‘MAKES REPORT FILL ENTIRE FORM
CRViewer.DisplayTabs = False ‘THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer.EnableDrillDown = False ‘REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer.EnableRefreshButton = False ‘ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
sSql2 = “SELECT * FROM employee WHERE salary>50000″
Set rst3 = cnn.Execute(sSql2)
Set crystal = New CRAXDRT.Application
Set Report = crystal.OpenReport(App.Path & “\crReportFirst.rpt”, 1)
Report.Database.SetDataSource rst3, 3, 1
Report.DiscardSavedData
CRViewer.ReportSource = Report
CRViewer.ViewReport
End Sub
Soryy. That was wrong. Here is the code.
Private Sub cmdPreview_Click()
dbOpen
CRViewer.DisplayBorder = False ‘MAKES REPORT FILL ENTIRE FORM
CRViewer.DisplayTabs = False ‘THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer.EnableDrillDown = False ‘REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer.EnableRefreshButton = False ‘ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
sSql2 = “SELECT * FROM employee WHERE salary
sorry here is the full code. Pl discard previous two.
Private Sub cmdPreview_Click()
dbOpen
CRViewer.DisplayBorder = False ‘MAKES REPORT FILL ENTIRE FORM
CRViewer.DisplayTabs = False ‘THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer.EnableDrillDown = False ‘REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer.EnableRefreshButton = False ‘ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
sSql2 = “SELECT * FROM employee WHERE salary
sorry here is the full code. Pl discard previous two.
Private Sub cmdPreview_Click()
dbOpen
sSql2 = “SELECT * FROM employee WHERE salary
I want to display the image in crystal report using VB 6.0, I ve tried but the data is being displayed but Image is not displayed, Anyone can help me?
Thank u
this code definitely help me in making dynamic crystal report,but can u provide any method to set the field header
I can not change the data dynamically. Report always gives data like at design time. The code is showing below.
Private Sub cmdPreview_Click()
sSql = “SELECT * FROM employee WHERE salary
SORRY AGAIN. I HAVE A PROBLEM OF SENDING A COMMENT TOO.
. Only the most upper part of my comment is viewed. IS THIS A ERROR OF MY BROWSER OR CONNECTION? WHAT I WANTED TO KNOW IS DESCRIBED BELOW. HOPE THIS TIME I CAN SEND ALL.
!!
AAAAHHHHHHHH. Can anyone help me pl? I can’t send a comment.
Hi, I am using MYSQL 5.0, and Microsoft Visual Studio 2005, I cant seem to figure out how to get Crystal Reports to connect to my database, if anyone could give me a quick crash course in what I am doing wrong I would really appreciate it.
how to use vb and cr9 with subreport?
i have 2 field definition file in my report and 1 subreport that use field definition too.
the report successfull bring data when i using:
report.database.setdatasource rs,,1
report.database.setdatasource rs,,2
but the sub report didn’t display any record.
i tring to use
report.opensubreport(“mysubreport.rpt”).database.setdatasource rs2,,1
but it failed too..
how to make myreport display record from sub report using vb?
YO Kool but can someone help me? I need to allow the report to change the table name at runtime
eg. “select * from orders……..
to “select * from temp_orders ……..
all this at run time
use alter table before select statement
sql=”alter table orders rename temp_orders”
conn.execute sql
sql=”select * from temp_orders”
conn.execute sql
i think it’s just like that..
I am using the designer of cystal report 9 in MS Visual Basic 6 and I am using data field definition for the stored procedure that I create to view the records, thru recordset. The problem is if I am using a parameter in my stored procedure to filter the records there is no result set in my crystal report viewer at runtime in VB, but if “I am not using a parameter” to filter the records it shows “all the records at run time in MS VB6″??? just like I wanted in my project, how can I show the records at runtime in MS VB6 thru recordset using ADO in crystal report by using “parameters” to filter out my data? can anyone help me, thanks!!
additional to my questions is that, I am using a parameters in my stored procedure so that the recordset in my project is alredy filtered.
i have made crystal reports and m using it in vb6.0. they are working fine with .net framework 1.1 but not working with 2.0 can anyone suggest me some solution
How to display crystal report XI using vb 6.0
Thanks for the tip on CRViewer.Zoom, I’ve been struggling to get that to work for ages
please tell me the code to display the crystal report 11 using vb 6.0
I think It is very help full to me becaus it is simple and nice i not tried yed but when we use RedcordSet object it eliminate but one Question “where we use selection formula “
Hi,
Am using the Crystal Report Engine Automation API.
I display the report(s) using the syntax below:
frmPreview.RunRpt report_path ,param1, param2, param2
frmPreview has the following code:
Private CrystalApp As New CRPEAuto.Application
Private WithEvents CrystalRpt As CRPEAuto.Report
Dim dboRepDB As CRPEAuto.database
Dim tbsRepDB As CRPEAuto.DatabaseTables
Public Sub RunRpt(rptPath As String, sTitle As String, Optional rFromDate As String, Optional rToDate As String)
Dim intTeller As Integer
Set CrystalRpt = CrystalApp.OpenReport(rptPath)
Set dboRepDB = CrystalRpt.database
Set tbsRepDB = dboRepDB.Tables
CrystalRpt.DiscardSavedData
CrystalRpt.FormulaFields(“Title”).Text = “‘” & param1 & “‘”
If param2 “” Then
CrystalRpt.FormulaFields(“param2 “).Text = “‘” & param2 & “‘”
End If
If param3 “” Then
CrystalRpt.FormulaFields(“param3 “).Text = “‘” & param3 & “‘”
End If
If Not CrystalRpt Is Nothing Then
Me.Caption = sTitle
CrystalRpt.Preview sTitle, , , , , 16777216 + 524288 + 131072 + 65536, Me.hwnd
End If
Screen.MousePointer = vbDefault
Set tbsRepDB = Nothing
Set dboRepDB = Nothing
Exit Sub
ErrHandler:
Screen.MousePointer = vbDefault
Select Case err.Number
Case 20545
Exit Sub
Case Else
MsgBox err.Description & “: ” & err.Number & “, ” & err.Source
End Select
Set tbsRepDB = Nothing
Set dboRepDB = Nothing
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Set CrystalRpt = Nothing
End Sub
Sub on_close()
Unload Me
End Sub
Uisng the above code I get the error: Run-time error ’20599′ Cannot open SQL Server
I have tried this atciles and it’s work, but i had modify the code in query sql, i add parameter in query to control which record will be display, but it didn’t work cant anyone help me, here the code :
Dim crystal As CRAXDRT.Application ‘LOADS REPORT FROM FILE
Dim Report As CRAXDRT.Report ‘HOLDS REPORT
Private Sub Form_Load()
If sConn.State = 1 Then
Tutup
End If
Buka
If sRec.State = adStateOpen Then sRec.Close
sComm.CommandText = “select * from exim_beacukai_trc_bc40,exim_beacukai_trc_bc40_dtl where exim_beacukai_trc_bc40_dtl.no=exim_beacukai_trc_bc40.no and exim_beacukai_trc_bc40.no=’” & FlagDataSearch1 & “‘”
sComm.ActiveConnection = sConn
sRec.Open sComm, , adOpenDynamic, adLockOptimistic
CRViewer91.DisplayBorder = False ‘MAKES REPORT FILL ENTIRE FORM
CRViewer91.DisplayTabs = False ‘THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer91.EnableDrillDown = False ‘REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer91.EnableRefreshButton = False ‘ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
Set crystal = New CRAXDRT.Application ‘MANAGES REPORTS
Set Report = crystal.OpenReport(App.Path & “\ReportProgram\Report1.rpt”) ‘OPEN OUR REPORT
‘Report.DiscardSavedData ‘CLEARS REPORT SO WE WORK FROM RECORDSET
‘Report.Database.SetDataSource sRec ‘LINK REPORT TO RECORDSET
Report.ParameterFields(0).AddCurrentValue FlagDataSearch1
CRViewer91.ReportSource = Report ‘LINK VIEWER TO REPORT
CRViewer91.ViewReport ‘SHOW REPORT
Do While CRViewer91.IsBusy ‘ZOOM METHOD DOES NOT WORK WHILE
DoEvents ‘REPORT IS LOADING, SO WE MUST PAUSE
Loop ‘WHILE REPORT LOADS.
CRViewer91.Zoom 94
sRec.Close ‘ALL BELOW HERE IS CLEANUP
Set sRec = Nothing
Set crystal = Nothing
Set Report = Nothing
End Sub
Private Sub Form_Resize()
CRViewer91.Top = 0
CRViewer91.Left = 0
CRViewer91.Height = ScaleHeight
CRViewer91.Width = ScaleWidth
End Sub
but viewer didn’t control which record will be load based on my parameter ( flagdatasearch1)
Anyone can help me.
Thx’s
sir ,
i am having a problem in a my school s/w .i want to create a long sheet to print a big data sheet. which is only created by crystal report ,but i dont know anything abt crystal report so plz help me if posible to anyone
Hi,
Anybody know how to connect a mysql database with CR11.
When I try to make a connection through ‘Create New Connection’->’ODBC RDO’ or ‘Create New Connection’->’OLE DB (ADO)’ it give error and application close automatically.
can any body tell me how i can make ledger in crystal report.
my email address is muddasirmunir@yahoo.com
Anyone can tell me, how to print half page of A4, with CR9, about the custom size , i tried make custome size for half page, page it still print full page!, please advice me at acholyte_rbo@yahoo.co.id. Thx
hi,
i have developed a web browser in visual basic 6 when im trying to open a crystal report its not opening can any tell how to display crystal reports in VB
no body is there to give the correct solution.
Please let me know
what we need to do to display the report(view the report using vb)
using sybase.
Dim RptName As String
Dim tmpName As String
Dim q As String
RptName = App.Path & “\exec\r1a15b_1.rpt”
tmpName = “Report”
On Error GoTo Chk_Err
q = “SELECT * from ap_voucher_upload ”
Crpt.ReportFileName = RptName
Crpt.SQLQuery = q
Crpt.WindowTitle = tmpName
Crpt.Connect = “DSN=phase2;UID=” & user_name & “;PWD=” & user_pwd & “;database=fasctpr50″
Crpt.UserName = user_name
Crpt.Password = user_pwd
Crpt.PrintReport
Exit Sub
Chk_Err:
If Err.Number = 20507 Then
MsgBox tmpName & ” [" & RptName & "] could not be found.” & Chr(13) & “Please make sure the Report exists in” & Chr(13) & App.Path & ” before proceeding.”, vbExclamation, “GL Cashflow Report”
End If
How about if you’re using visual foxpro 9 as your platform in programing?How can you create a crystal report? thanks..
I have created a browser in VB i want to create a product key and license for my browser can anyone plz help me
How to Using SQLServer, Visual Basic 6, and Crystal Reports 9
Tell Me Pls.
Kow
how about if you are using an microsoft visual fox pro 9? Can you have an idea on how to connect one table to another table of a database?…i am presently creating my research proposal (Thesis) and my topic is all bout microsoft visual foxpro 9 connected to mySQL..if you dont mind please create me a simple program showing the procedures..This will help me very much!thanks….i’ll wait for you’re response regarding my topic..hope you’ll grant my wish!thanks again!
I need your help ? Urgent ..
I’ve got 1 textbox in Visual Baasic 6 with value is 31 , I want to assign value of this textbox to Report of Crystal Report. Pls help me
I need your help ? Urgent ..
I’ve got 1 textbox in Visual Baasic 6 with value is 31 , I want to assign value of this textbox to Header of Report (Crystal Report). Pls help me
have report showing in vb6 (designed in crystal tied to database fields) but only the numeric fields show – no alpha text fields show as well as a blob field from the database. any suggestions?
also, one of my numeric fields needs to be converted to alpha (ie 1 = mr, 2 = mr & mrs, etc.) anyone tell me how to do that? is it done in crystal design or on the vb side?
thanks!
Hi all. I need to change the SQL statement of a .DSR (CR9) thru VB6. Basically, I need the same form, but I want the data to be retrieved from other tables.
However, when I right-click on Database fields, I can only see Show SQL Query, and the SQL itself within is greyed out. I can change the SQL in no way.
Please help.
Thanks & regards,
Mark
How to make connection string in VB6 to change connection in ODBC DSN with multiple ms ACCESS database Coz my Chrystal report only connect with one ms ACCESS database if i use ODBC Connection. I’m so confuse. Please give me an answer. I really need this source code.
hi
i want to display start date and end date in report header ; directly to report(not through any database field) how can i do?
I am woking on crystal report 9.0
My Mail Id is…..sonagupta04@gmail.com
pls help me
Hi everyone!
I am a new programmer and I have this fondness for vb6. I will eventually transfer to JAVA. I ws just wondering if nyone can send me a simple vb6 program with full mySQL database usage? Please??? Here’s my e-mail, blood_left_the_ruins@yahoo.com. Thanks!!
upto how many groups does crystal report support
hi! can someone tell me how to connect the database to crystal report? i’ having problem on it!
(vb 6.0-sp6, Crystal report 9, mySql)
can some one tell me,how to increase length of datafield in crystal report.i had a field in database which consist 1000 characters but in crystal report it shows only 100 characters……..
arsal_2009@yahoo.com
Format the field and select Paragraph Formatting tab in the Text Interpretation tab select RTF format or HTML format to display the entire content.
can somebody help me with the codes linking vb to mysql
hi this is very sufull me..
this is a beautyfull programming my best help me….
hie pliz help!im trying to print crystal reports in visual basic 6.
Dear Experts,
I am using visual Basic 6.0 as front end and MySQL 4.0.16 as a database. I want to create report with Crystal Report 8.5. How can I connect this and print this report.Please sent me the step by step. Please reply me.
Thanking you,
Sudip
Dear Experts,
My trouble like Sudip, when i connect to mysql i’ve got error
run-time error ’394′ :
Property is write only
please help me…
Thank & Regrad’s
Fitri
Hello,
Microsoft Coporation why always develops such type of langauge which is very diffcult to adopt i.e.
vb to vb.net
Why the reporting of VB/VB.Net is not better than Delphi.
HI.. this is mehul here.. i m using Sybase SQL Anywhere as backend database and i m using VB… so pls anyone can help me out.. i created a report in crystal report 8.5 and using VB i wanna get connected to crystal report and also wanna use formula in that.. so please anyone can help me out in this pls…
plz help me to make A report using crystal report with visual basic 6.0. using SQL server ??!! plz.. thanx
HI,
Many tnaks for code for suing craxdrt.
I need how to insert formula firled in report at run time.
also, how to add or delete a line from report at run time
how I can display Multiple images in Crystal Report with vb 6.0
Please send sample source code
what is crystal reports. What is its use and application. How will have to use in vb 6.0. I use the database oracle 10g.
what is current version of crystal reports. plz, explain in details.
how to use the tools of the data report in vb 6.0?
hi
Can some one help me plz ?
i use visual basic.6 and Seagate Crystal Reports 8
can anybady help me to show Report ?
thx for every bady !
hi
i am using vb 8.0 and if i use
“Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset ”
i am getting a error a plz kindly help to
save a textbox value to a database in vb 8.0
I have developed a software in VB 6.0 and i want to view the reports in Crystal report 11, can any body knows something about it
how to resize image to Crystal Reports 9 from Microsoft Visual Studio 6.0
Hi,
I want to make simple report using crystal reports from a vb6 application. The application does a real time monitoring of data streaming in through the serial port. I have label controls showing these values. We can freeze the screen at any point of time and print out a report. I need to transfer values from the label controls to the report. Could somebody help me out.
thanks in advance.
sumalatha
HI!
I’m currently developing a system in vb6 and I’m wondering if it is possible to connect to crystal report without using a ODBC? Can anybody help me.?
Hi RHEN, try it:
Dim Appn As CRAXDRT.Application
Dim CREPORT As CRAXDRT.Report
Set Appn = CreateObject(“crystalruntime.application”)
Set CREPORT = Appn.OpenReport(informe)
‘
For i = 1 To CREPORT.Database.Tables.Count
CREPORT.Database.Tables(i).ConnectBufferString = “DRIVER={MySQL ODBC 5.1 Driver}; SERVER=172.26.0.2 ; DATABASE=database; UID=root; PWD=root; PORT=3306″
Next
CREPORT.PrintOut True
hi every one!!!!!!!
can any body tell me, how to set or change the sort field of crystal report11 at runtime through vb6
How can I connect to website mysql database? The error always keep on telling me that “unable to connect this mysql server”.. can you please help me!! I really need to make these work.
The error is “Connection Failed: [HY000] [Mysql] [odbc 5.1]” host ‘monix’ is not allowed to connect to this mysql server”.
Please help!
Anyone help me!
I can’t connect Crystal Report XII 2008 to VB 6.0, Pls send me code of connection.
this really helps, i wonder why some of the commentator disAgree on showing the exact code!
they are so selfish!.
anyways! thanks man! you really save me!
you are a HOPE for us newbies!
can we get formula value from crystal report or not
I have developed a database program using Visual Studio 2005 & have incorporated Crystal Reports 10 which works quite nicely (but a bit slow).
My problem is if I try accessing the reports from any other PC, I get an error.
The VB program works fine & other users can acces & update the data without problem.
See Error below
System.IO.FileNotFoundException: Could not load file or assembly ‘CrystalDecisions.Windows.Forms, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304′ or one of its dependencies. The system cannot find the file specified.
File name: ‘CrystalDecisions.Windows.Forms, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304′
at PlaneEarthCustMaint.frmViewReport.InitializeComponent()
at PlaneEarthCustMaint.frmViewReport..ctor()
at PlaneEarthCustMaint.Form34.btnClientList_Click(Object sender, EventArgs e) in C:\Documents and Settings\Sam\My Documents\Visual Studio 2005\Projects\PlanetEarthCustMaint\PlanetEarthCustMaint\Form34.vb:line 8
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].
Very useful. This really helped me in solving my proble. Great Thanks.
Thank you VERY MUCH !!, you saved my life… you are a genius
I was struggling for 2 days in the sense that the report didn’t accept my SQL sentence, it was solved with only report.DiscardSavedData method
Thank you
Thank you
*****
It is a wonderful article.
I am a beginner of crystal report 9
everything is clearly explained.
But i am getting the “Just in time” debugging error,
when i am executing the following line.
report.Database.SetDataSource rs
can anyone help me?
Thanks in Advance
I have a number of programs that use VB6, RDO and MySQL Connector 3.51 very well.
If I try to use Connector 5.1 then it connects OK but when I try to open a resultset using either of the following…
Set Updated = cnMySql.CreateQuery(“StatusTable1″, “select * from StatusTableOut”) ‘ order by status”)
Set RS = Updated.OpenResultset(rdOpenKeyset, rdConcurRowVer)
or..
SQL = “select * from StatusTableOut”
Set RS = cnMySql.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
Then it disconnects from MySQL via the disconnect event and crashes the program.
Any suggestions gratefully received…
Thanks
Andy.
i am studying on how to use Crystal Report insteadof Data report. Because data report is very limited.. any one who can tell me where i found Crystal Report Designer and Viewer? and .ocx required to view in VB 6.0
Thank in Advance.
anyway this is my email ricocepe@gmail.com
im studying now as beginer in using cyrstal report,im confusing there’s anyone code conection in crystal report using VBasic 6.0… .please help me!.. send to my email add Christan_jaquar84@yahoo.com!..
Thanks Sir, My Problem is solved …
So, Thank You very much…. For How To Close Crystal Report and Doevent when Crystal is Busy
please i wish to study sql. i want you to put me through.
Hi there. I was facing a huge problem with VB + MySQL + CR9. Something like amnesya, after 5 years I simply forgot everything about Crystal and had never did it with MySQL (in the past I did work with M$Sql). Thank you so much for this step-by-step, just saved my life!
i like your comment. and want to create simple program based on vb+ mysql+ cr9. please guide me.
hi, i used this connectivity with sql server 2000. when i loaded the form a error message shows “Server yet not connected”. could anybody help me
plz send the project of employee management system in visual basic
i want to display reports created in crystal report 2008 through form created in vb 6.0 but i m not able to do so becoz the required components are not available in vb can anyone suggest me the code and the components which are required to display the report
hello everyone
i am new to vb.net, creating a mysql database with VB(Front end), and i would like to use search options in my form, searching a particular column of the database table…….
Thanks in advance
Help will be really appreciated …….
Can you give us simple programs and source codes made up from visual basic 6.0 that connnect to Crystal Report 9.0 and MYSQL server… pLease???
Kindly give me some samples For me to download and try this codes??
Visual Basic Programs That uses MySQL and Crystal Report??? Please??
hello!
i’m developing a system in VB6, Crystal Report 8.5 and without ODBC Connection…
i can’t display a report!!
gracias!!!!
I am using vb6, crystal 8.5 and sql 2005.
Using vb6 to open crystal works with system dsn (windows log in) but not with sql login.
works fine with system dsn but not with file dsn
Could you please help?
i work with mysql database server 5.0 how can i back-up my database??
using vb 6.0
I have done installation at Client Place. Now I made new changes in Database(MsAccess) and EXE(VB6.0). How can I update in Client Place?.
I am Using VB6.0 and MsAccess. How can i Create new tables and new fields through VB Code.
I want copy and paste from one location to another location through VB 6.0 coding
I want to bill using Cr 9 without viewing the file on Screen.. Can ANy One Help Me.
I want to Print bill using Cr 9 without viewing the bill on Screen.. Can ANy One Help Me.
Thanks, this code was helping a lot!!!
Hi, we are using Crystal 8.5 with IIS.
When i generate a reports for a host of clients , there is no issue when previewing.
If i print directly to a printer or PDF creator , it throws out a Memory Full error and stops printing.
Pls help as a major release into Production is stuck due to this problem not being solved.
Hi, i am working on a Erp project which has vb as front end and oracle 9i as back end. Earlier the project has many reports in crystal report 4.6 and now we people are creating new reports in cr 9. It shows error when we run the project “Unable to Load the Report” . If we changed the dll (crpe32.dll) with new version of 9.0. it will load the report without any error but the earlier report’s wont generate in required format. Can any body help that what should i do to continue with my project so we can use a single dll and can be able to generate all the reports in the required format.
thanks
I do think diferrent because my friends and family use another brand name.It’s pleasant and i love it’s very much.But next drill driver I may think of this as drill driver stuffs you present.Appreciate!!!
Hi,
I am using Crystal Repot 9 and VB6.0. I can display the report through Crystal report viewer properly. But the print button in the Crystal report viewer does not work. My printer is in network. If I print any other file such as .doc, .txt then the printer prints the file. But when I click the print button in the Crystal report viewer then it neither prints the report nor through any error message. Where is the problem? How can I solve this?
Great Information, thanks for the useful Article. Also check these nice Website. estate agents belfast
We are using vb 6.0 and ms access in our school… our prof wants us to embeed mysql database to vb 6.0.. how are we going to do that? we required to rendered a report in vb 6.0(hard-coded) using data report, crystal report, PDF report and ms excel… and using odbc connection for lan-based application… pls help me doing this… i want to learn how to use this because it will help us in doing our system… thanx po.. this is my email: cha_alyza@yahoo.com
pls send to my email… godbless
We are using vb 6.0 and ms access in our school… our prof wants us to embeed mysql database to vb 6.0.. how are we going to do that? we required to rendered a report in vb 6.0(hard-coded) using data report, crystal report, PDF report and ms excel… and using odbc connection for lan-based application… pls help me doing this… i want to learn how to use this because it will help us in doing our system… thanx po.. this is my email: cha_alyza@yahoo.com
You helped me .. thanks alot
how to code syntax of “like” in vb 6.0? tnx
Hi i have a problem with my code it did not run can you help me
heres the code..
Private Sub Command1_Click()
‘Define the three objects that we need,
‘ A Connection Object – connects to our data source
‘ A Command Object – defines what data to get from the data source
‘ A RecordSet Object – stores the data we get from our data source
Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim rstRecordSet As New ADODB.Recordset
‘Defines the connection string for the Connection. Here we have used fields
‘Provider, Data Source and Mode to assign values to the properties
‘ conConnection.Provider and conConnection.Mode
conConnection.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _
App.Path & “\” & “database.mdb;Mode=Read|Write”
‘Define the location of the cursor engine, in this case we are opening an Access database
‘and adUseClient is our only choice.
conConnection.CursorLocation = adUseClient
‘Opens our connection using the password “Admin” to access the database. If there was no password
‘protection on the database this field could be left out.
conConnection.Open
‘Defines our command object
‘ .ActiveConnection tells the command to use our newly created command object.
‘ .CommandText tells the command how to get the data, in this case the command
‘ will evaluate the text as an SQL string and we will return all
‘ records from a table called tabTestTable
‘ .CommandType tells the command to evaluate the .CommandText property as an SQL string.
With cmdCommand
.ActiveConnection = conConnection
.CommandText = “SELECT * FROM tabTestTable;”
.CommandType = adCmdText
End With
‘Defines our RecordSet object.
‘ .CursorType sets a static cursor, the only choice for a client side cursor
‘ .CursorLocation sets a client side cursor, the only choice for an Access database
‘ .LockType sets an optimistic lock type
‘ .Open executes the cmdCommand object against the data source and stores the
‘ returned records in our RecordSet object.
With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With
‘Firstly test to see if any records have been returned, if some have been returned then
‘the .EOF property of the RecordSet will be false, if none have been returned then the
‘property will be true.
If rstRecordSet.EOF = False Then
‘Move to the first record
rstRecordSet.MoveFirst
‘Lets move through the records one at a time until we reach the last record
‘and print out the values of each field
Do
‘Access the field values using the fields collection and print them to a message box.
‘In this case I do not know what you might call the columns in your database so this
‘is the safest way to do it. If I did know the names of the columns in your table
‘and they were called “Column1″ and “Column2″ I could reference their values using:
‘ rstRecordSet!Column1
‘ rstRecordSet!Column2
MsgBox “Record ” & rstRecordSet.AbsolutePosition & ” ” & _
rstRecordSet.Fields(0).Name & “=” & rstRecordSet.Fields(0) & ” ” & _
rstRecordSet.Fields(1).Name & “=” & rstRecordSet.Fields(1)
‘Move to the next record
rstRecordSet.MoveNext
Loop Until rstRecordSet.EOF = True
‘Add a new record
With rstRecordSet
.AddNew
.Fields(0) = “New”
.Fields(1) = “Record”
.Update
End With
‘Move back to the first record and delete it
rstRecordSet.MoveFirst
rstRecordSet.Delete
rstRecordSet.Update
‘Close the recordset
rstRecordSet.Close
Else
MsgBox “No records were returned using the query ” & cmdCommand.CommandText
End If
‘Close the connection
conConnection.Close
‘Release your variable references
Set conConnection = Nothing
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
End Sub
Man – there are some rude bastards on the roads today. Particularly one Merc driver
i need to know how to set up parameters using vb6 to select a date range for records for a crystal report display on a mysql database platform
I need to make one time automated testing for many reports with Crystal Reports 9.0 with Microsoft SqL 2005 server. How do I work to use ?
Thank you
@LoveJ :
i am using ms access database with CRViewer in a form with CrystalReport designer in another form
also using connection ADODC via code, SELEKSI is string for the range, awl_T as start date and akh_T as end date,
RJual is CrytalReport Design Form
SELEKSI = “{ado.Tanggal} in DateTime (” & Year(awl_T.Text) & “, ” & _
Month(awl_T.Text) & “, ” & Day(awl_T.Text) & “, 00, 00, 00) to DateTime (” & _
Year(akh_T.Text) & “, ” & Month(akh_T.Text) & “, ” & Day(akh_T.Text) & “, 00, 00, 00)”
RJual.RecordSelectionFormula = SELEKSI
CRViewer1.ReportSource = RJual
CRViewer1.ViewReport
I REQUIRE CRYSTAL REPORT
a really great tutorial.. it’s so easy to understand thank you very much.. but i have some question about this line :
report.DiscardSavedData ‘CLEARS REPORT SO WE WORK FROM RECORDSET
report.Database.SetDataSource rs ‘LINK REPORT TO RECORDSET ———-> especially this one
is it possible to set multiple recordset to a report.Database.SetDataSource ?
i want to create a report using multiple: 2-3 table source ttf file.
Do you have any reference on how to solve this kind of problem?
thanks b4