Crystal Reports from SQL Query String

In VB.NET, Crystal Reports can be generated by utilizing SQL Query Strings. In this context, we develop a Strongly Typed dataset specifically designed for Crystal Reports. Additionally, we create a connection object and execute the SQL Query String to retrieve the required data.

It is essential to note that all the programming samples provided in these tutorials for Crystal Reports are based on the crystaldb database. Prior to commencing this tutorial, I recommend acquainting yourself with the structure of the database. To access the database structure, kindly click on the following link: Click here to view the Database Structure.

To acquire a comprehensive understanding of Crystal Reports creation, I suggest referring to the previous section of this tutorial. It provides detailed step by step Crystal Report guidance on creating Crystal Reports. Familiarizing yourself with the basics outlined in the step-by-step Crystal Report guide will prove beneficial before proceeding with this tutorial.

Generating a Strongly Typed DataSet

In the preceding section, a comprehensive tutorial was presented, detailing the process of create a strongly typed datset and designing Crystal Reports based on it. Once the dataset and Crystal Reports design have been established, the subsequent step involves creating a connection object and retrieving data from the database.

Select the default form (Form1.vb) you created in VB.NET and drag one button and CrystalReportViewer control to your form.

Put the following source code in the button click events

Full Source VB.NET
Imports System.Data.SqlClient Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Imports System.Data Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim ds As New DataSet1 Dim cnn As SqlConnection Dim connectionString As String Dim sql As String connectionString = "data source=servername; _ initial catalog=crystaldb;user id=username;password=password;" cnn = New SqlConnection(connectionString) cnn.Open() sql = "SELECT Product_id,Product_name,Product_price FROM Product" Dim dscmd As New SqlDataAdapter(sql, cnn) Dim ds As New DataSet1 dscmd.Fill(ds, "Product") MsgBox(ds.Tables(1).Rows.Count) cnn.Close() Dim objRpt As New CrystalReport1 objRpt.SetDataSource(ds.Tables(1)) CrystalReportViewer1.ReportSource = objRpt CrystalReportViewer1.Refresh() End Sub End Class

You have to provide the necessary databse information to Connection String.