Posted by: Red Souldier | June 5, 2009

Connecting Crystal Report ASP.NET using Dataset

Do you have so many problems with user logon database when you try to connecting asp.net to crystal report using SQL Server 2005 store procedure or table.

By using dataset, you dont have to worries about connection to sql server in crystal report.

First, you need to add or create new item of dataset object. Dataset object format as .xsd
Inside the dataset object, right click and add new datatable, after that right click on datatable and add column.
You can set the datatype as well.

Second step is creat new crystalreport, In field explorer, right click on database field and click database expert. In project data – ADO.NET Datasets choose dataset that you just created and add to the selected table. And you will have coulmn shows in the field explorer.

Thirs step, after you done with report, now we start in coding

This is the code
Dont forget to add this code first

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

   Private rpt As New ReportDocument
   Private Function GetData(ByVal strQuery As String, ByVal tableName As String) As Data.DataSet

        Dim ds As New DataSet

        'strConnection is you connection string
        Dim strConnString As String = strConnection
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand(strQuery, con)
        Dim sda As New SqlDataAdapter

        cmd.CommandType = CommandType.Text

        Try
            con.Open()
            sda.SelectCommand = cmd
            sda.Fill(ds, tableName)
        Catch ex As Exception
            Return ds
        Finally
            con.Close()
            cmd.Dispose()
            sda.Dispose()
        End Try
        Return ds
    End Function

   Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        rpt = New ReportDocument()
        rpt.Load(ServerPath & "CrystalReport2.rpt")

        rpt.SetDataSource(GetData("Select * from ctbankstatement0", "ctBankStatement0").Tables("ctBankStatement0"))

        'To pass paramater value with parameter name "field2"
        Dim field2 = New ParameterField
        field2.Name = "field2"
        Dim par_field2_value As New ParameterDiscreteValue
        par_field2_value.Value = 2
        field2.CurrentValues.Add(par_field2_value)
        CrystalReportViewer1.ParameterFieldInfo.Add(field2)

        If Not rpt Is Nothing Then
            CrystalReportViewer1.ReportSource = rpt
        End If
    End Sub
Advertisements

Responses

  1. Thanks so much for this post…

  2. Thank u very much…Very Helpful…..Great Work.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: