Create a Crystal Report from ADO.NET Dataset using VB.NET

Generating an Object for the DataSet

Object for ADO.NET is a collection of dataset classes created in memory.

To create a dataset object from Northwind database in SQL Server, using ADO.NET

DataSet Designer.

1. In the Solution Explorer, right-click the project name, point to Add, and click Add New

Item.

2. In the Categories area of the Add New Item dialog box, expand the folder and select

Data.

3. In the Templates area, select Dataset.

4. Accept the default name Dataset1.xsd.

This creates a new schema file that will be used to generate a strongly typed dataset. The

schema file will be displayed in ADO.NET Dataset designer.
5. In the Solutions Explorer, click on Dataset1.xsd file, if now already the active view.

6. From the Server Explore, on the right connect to SQL Server and drill down to Northwind Database.

7. Highlight the Table Customers (or stored procedure if desired) and drag and drop it on

the Interface of Dataset1.xsd. Dataset1.xsd should now be displayed in the Dataset tab

as under

This creates a dataset object and contains only a description of the database based on the

schema in Dataset1.xsd. It does not contain the actual data.

Connecting Report to an ADO.NET Dataset Object

From ADO.NET Dataset Object you can add tables to Crystal Report using Database Expert in

Crystal Report Designer.

To create a new report and connect it to Dataset object which contains description for

Customers table in Northwind database

1. In the Visual Studio .NET Solution Explorer, right-click your project to display the
2. Point to Add and click Add New Item.

3. In the Add New Item dialog box, select Crystal Report from the Templates area. ClickOpen.
 

4. Crystal Report Gallery will be displayed, as shown below

5. You can choose from any of the options provided in Crystal Report Gallery. But for the

purpose of this walkthrough choose As a Blank Report and click OK.

 

6. On File menu, click Save to save the report.

7. Right click in the Report Designer, point to Database, and click Add/Remove Database.

8. You’ll be presented with Database Expert wizard

9.In the Database Expert wizard, expand the Project Data folder, expand the ADO.NETfolder and select the dataset object as shown below

Datasets

10. If you now drill down Database Fields node, in the Field Explorer, you can view
11. Drag and drop the fields onto the report and format them as required.

Pushing data into DataSet object and binding report to Windows Forms Viewer

In order to display actual data in the report, you should fill the dataset object with the data before

you bind the report to Windows Forms Viewer. You should do this in the corresponding source file

for Windows Form.

1. Drag and drop CrystalReportViewer control on Form1 and set the DisplayGroupTreeproperty to False, as shown below

2. Accept the default name as CrystalReportViewer1.

3. Open Form1 code editor and add the following code on Load event of Form1.

Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) .Load

Dim

Dim

Dim

Dim

Dim

Try

MyCommand.Connection = myConnection

MyCommand.CommandText = "SELECT * FROM Customers"

MyCommand.CommandType = CommandType.Text

myDA.SelectCommand = MyCommand

myDA.Fill(myDS, "Customers")

rpt.SetDataSource(myDS)

CrystalReportViewer1.ReportSource = rpt

Catch

MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK,

MessageBoxIcon.Error)

rpt As New CrystalReport1() 'The report you created. myConnection As SqlConnection MyCommand As New SqlCommand() myDA As New SqlDataAdapter() myDS As New Dataset1() 'The DataSet you created. myConnection = & _"Initial Catalog=northwind;")New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" Excep As Exception

End Try

End Sub

Private Sub

Handles MyBase

Customers table and all its fields

shortcut menu.