About Crystal Report Viewer using Dataset

[drexler_kk]

Dear All, I have manage to process some sorting and calculation in a few columns of my Dataset. I want to do a printout for the data now inside the dataset using Crystal Report Viewer. Can anyone told me how should I put my Crystal Report viewer to display this Dataset? I'm using VB.NET WINFORM 2.0 with ACCESS DATABASE. This is my code now to process my data inside the dataset:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim i As Integer
    Dim BATCH, TRNCDJ, RESONJ, REFNOJ, INVT, CUSTCODE, CUSTNAME, ADD1, ADD2, ADD3 As String
    Dim DD, MM, YY, INVNO, QTY, PONO As Integer
    Dim Day, Month, Year, D1, ADDRSS, INVNUM As String


    Dim mydatatable As New DataTable
    Dim ukCulture As CultureInfo = New CultureInfo("en-GB")
    Dim myDateTime As DateTime

    Try

        If ComboBox1.SelectedItem <> "" Then
            cn.Open()

            MyDataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT \* FROM DATA WHERE ITEMJ='" & ComboBox1.Text & "' ORDER BY DD,MM,YY ASC", cn)
            MyDataAdapter.TableMappings.Add("Table", "OrderTable")
            DtSet = New System.Data.DataSet

            MyDataAdapter.Fill(DtSet)
            dt = DtSet.Tables("OrderTable")
            DataGridView1.DataSource = DtSet.Tables(0)

            cn.Close()

            If Trim(TextBox1.Text) <> "" Then
                STOCK\_BAL = Trim(TextBox1.Text)
            Else
                STOCK\_BAL = 0
            End If

            mydatatable.Columns.Add("Date", Type.GetType("System.String"))
            mydatatable.Columns.Add("CUSTNAME", Type.GetType("System.String"))
            mydatatable.Columns.Add("ADDRESS", Type.GetType("System.String"))
            mydatatable.Columns.Add("INVOICE\_NO", Type.GetType("System.String"))
            mydatatable.Columns.Add("PO\_NO", GetType(Integer))
            mydatatable.Columns.Add("BATCH\_NO", Type.GetType("System.String"))
            mydatatable.Columns.Add("Qty", GetType(Integer))
            mydatatable.Columns.Add("BALANCE", Type.GetType("System.String"))



            For i = 0 To dt.Rows.Count - 1
                'MessageBox.Show("This is " & i & " ")

                BATCH = Trim(DataGridView1.Item(3, i).Value)

                DD = Trim(DataGridView1.Item(4, i).Value)

[Mycroft Holmes]

Let me get this right! You get a datatable from Access Then bind the datatable to a DGV Then you create another datatable to format the data into, by the looks to faff about with the date and some order types you then bind that result to a second DGV. I believe Access supports named queries (something like a stored proc), which is where all this formatting should be done. I would recommend SQL Express as a better solution. With CR I used to design a report that was serviced by a stored proc but I believe you can pass a dataset to a designed report.

[drexler_kk]

Hello Mycroft, I'm not so familiar with the stored procedure as you mentioned. Can you show me some sample,how to do this as what you told me? I need to printout the data by using crystal report. Thank you~!

[David Mujica]

This is what I have done in the past ... Since you already have the data in a dataset, you need to create a schema file for Crystal so that you can design the report. 1) Place this in your code somewhere after the datatable is defined. mydatatable.writeXMLSchema("C:\TEMP\mytable.xsd") You will only need this once, or you will need it again if you change the structure of your datatable. 2) from your solution explorer, Add New Item, "Crystal Report" 3) Select, "Use report wizard" to create the report 4) choose "Create New Connection\Database Files" as the data source 5) Select the "c:\temp\mytable.xsd" file 6) choose the "table" to add to your list of selected tables. Click Next 7) Select the fields from the table to include in your report 8) follow the rest of the prompts through the wizard, "grouping","Summaries", "Sorted by", "Chart", "Record Selection", "Styles" 9) This is how I load the reports and attach it to a dataset:

Protected Sub displayRpt(ByVal sRptName As String, ByVal ds As DataSet, ByVal crParamFields As ParameterFields)
Dim RptPath As String
Dim rpt As New ReportDocument

' I store my reports in a folder called, "RptSource", one level down from the main website.

    RptPath = Server.MapPath("~/Rptsource")

    rpt.Load(RptPath + "\\" + sRptName)

    rpt.SetDataSource(ds)

' Use the following only if you are passing parameters to the report. In my example, I am not passing parameters
'CrystalReportViewer1.ParameterFieldInfo = crParamFields

    CrystalReportViewer1.HyperlinkTarget = "\_blank"  ' This causes the report to pop into a new browser.
    CrystalReportViewer1.DisplayGroupTree = False
    CrystalReportViewer1.HasToggleGroupTreeButton = False
    CrystalReportViewer1.HasDrillUpButton = False
    CrystalReportViewer1.HasViewList = False
    CrystalReportViewer1.HasSearchButton = False
    CrystalReportViewer1.EnableDrillDown = False    ' hmmm
    CrystalReportViewer1.Height = 800
    CrystalReportViewer1.Width = 900

    CrystalReportViewer1.ReportSource = rpt
    'CrystalReportViewer1.RefreshReport()

End Sub

[i i i]

Through Crystal report try this article : Choosing a printer when printing from Crystal Reports in C#[^] while their are some Printable Reports Library for Windows Forms also try this Printable Reports Library for Windows Forms[^]

Best Of Regards, SOFTDEV Sad like books with torn pages, sad like unfinished stories ...