Future is now: August 2013

Monday 12 August 2013

Calling cyrstal report from vb6

1. Add two components into project Crystal Report Control and Crystal report viewer control.
2. Add a reference to the project Crystal Report Activex Designer runtime.
2. Add a form into the project
3. Add a Crystal report control in the form
4. Add a second form from where you need to call the report.

Please confirm that crystal report 8.5 is installed in your machine.

Set CrysRep = CrysApp.OpenReport(ReportName)

Call CrysRep.Database.AddOLEDBSource("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=sa;Initial Catalog=" & db_name & ";Data Source=" & Srv_Name, m_DefaultTable)
' Sql = "SELECT employee_master.emp_code, employee_master.emp_name, SUM(employee_attendance.normal_hours)+100 AS nhrs, SUM(employee_attendance.ot_hours) " & _
" AS othrs, SUM(employee_attendance.incentive_hours) AS incthrs FROM employee_master INNER JOIN employee_attendance ON employee_master.emp_id = employee_attendance.emp_id GROUP BY employee_master.emp_code, employee_master.emp_name ORDER BY employee_master.emp_code"
If m_recSet.state = 1 Then m_recSet.close
m_recSet.Open m_SQL, m_Con
With CrysRep
.Database.Tables(1).SetDataSource m_recSet
.DiscardSavedData
End With
CRViewer.ReportSource = CrysRep
CRViewer.ViewReport

-------------------------------------------------------------------------

a. Crysrep is the viewer control runtime.

b. Second line gives the connection string for sqlserver, for connection string for oracle visit http://subhroneel.blogspot.in/2013/08/connection-string-to-connect-to.html

c. m_recSet is the ADODB.Recordset

d. CrViewer is the report control name in the form.

e. After CRViewer.ViewReport you need to add formname.Show, the form where the control CRViewer in embeded.

Connection String to connect to SQLSrver,Oracle MSAccess

1. Oracle Database

Provider=MSDAORA.1;User ID=schema username;Password=schema password;Data Source=tns service name;Persist Security Info=False

Example : - Provider=MSDAORA.1;User ID=scott;Password=tiger;Data Source=orcl;Persist Security Info=False

2. SQL Server

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=sa;Initial Catalog=greenply;Data Source=server name/server ip

Example : - Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=sa;Initial Catalog=master;Data Source=ServerNode

3. MSAccess Database

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\Desktop\example.mdb;Persist Security Info=False

Example : - Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database path\filename;Persist Security Info=False

4. Using Data Source Name (DSN)

Provider=MSDASQL.1;Persist Security Info=False;User ID=Database user name;Password=Database Password;Data Source=DSN Name;Initial Catalog=Database Path\Database Name

Example : - Provider=MSDASQL.1;Persist Security Info=False;User ID=ADMIN;Password=PECONS;Data Source=AccessLLH;Initial Catalog=E:\ERECB1\ERECB1