Results 1 to 3 of 3

Thread: Crystal Report Using MYSQL Database

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    12

    Crystal Report Using MYSQL Database

    Hello,

    I am trying to create crystal report having fields from two related tables of MYSQL database.

    I created a dataset with two data tables
    and created all fields of both tables

    then created a relationship between them

    Table 1: Customer
    ID
    full_name
    etc.

    Table 2: Customer_Payments
    customer_id
    balance
    etc.


    Then I Created a crystal report and set the datasource to the DataSet XML


    Below is my code the show report which showing fields from the table customer but not from customer_payments

    Plz help me.

    Code:
     Public Sub show_Customer() 'Procedure to retrieve record and store to datagridview
            Me.WindowState = FormWindowState.Maximized
            Try
                sqL = "SELECT c.ID, c.full_name, c.shop_name,c.Address, c.city,c.phone,c.mobile,sum(customer_payments.balance) as balance FROM Customer c LEFT OUTER JOIN customer_payments ON customer_payments.customer_id = c.ID GROUP BY c.ID ORDER BY STR_TO_DATE(c.cDate,'%m/%d/%Y %h:%i:%s %p') DESC"
                ConnDB()
                cmd = New MySqlCommand(sqL, conn)
                da = New MySqlDataAdapter
                dt = New DataTable
                da.SelectCommand = cmd
                da.Fill(dt)
                'Fill DataGridView
                custDGV.DataSource = dt
    
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                conn.Close()
                cmd.Dispose()
            End Try
        End Sub

    Fill DataTable from DataGridView and set as datasource for report document

    Code:
     Private Sub ToolStripButton5_Click(sender As Object, e As EventArgs) Handles ToolStripButton5.Click
            dt = New DataTable
            With dt
                .Columns.Add("ID")
                .Columns.Add("full_name")
                .Columns.Add("shop_name")
                .Columns.Add("address")
                .Columns.Add("city")
                .Columns.Add("phone")
                .Columns.Add("mobile")
                .Columns.Add("balance")
            End With
            For Each dr As DataGridViewRow In custDGV.Rows
                dt.Rows.Add(dr.Cells(0).Value, dr.Cells(1).Value, dr.Cells(2).Value, dr.Cells(3).Value, dr.Cells(4).Value, dr.Cells(5).Value, dr.Cells(6).Value, dr.Cells(7).Value)
                'MsgBox(dt.Rows(dr.Index).Item(1) & "  " & dt.Rows(dr.Index).Item(7))
            Next
    
            Dim rptDoc As CrystalDecisions.CrystalReports.Engine.ReportDocument
            rptDoc = New CrystalReport2
            rptDoc.SetDataSource(dt)
            frmCustomerPrint.CustReportViewer.ReportSource = rptDoc
            frmCustomerPrint.ShowDialog()
            frmCustomerPrint.Dispose()
        End Sub

  2. #2
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: Crystal Report Using MYSQL Database

    The problem is in your SQL. Try this:
    Code:
    sqL = "SELECT c.ID, c.full_name, c.shop_name,c.Address, c.city,c.phone,c.mobile, d.balance FROM Customer AS c LEFT OUTER JOIN (SELECT sum(balance) AS balance, customer_id FROM customer_payments GROUP BY customer_id) AS d ON d.customer_id = c.ID ORDER BY STR_TO_DATE(c.cDate,'%m/%d/%Y %h:%i:%s %p') DESC"
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    12

    Re: Crystal Report Using MYSQL Database

    Dear Jenner,

    Thanks for the reply, I changed the code but the result is the same, DataGridView populated with correct data as it was before but dont show data in the balance field in Crystal Report.

    Below are the pics of my XSD, and Crystal Report.

    Name:  TSoftPOS_Ssd.png
Views: 532
Size:  12.8 KB

    Name:  TCrystalReport.jpg
Views: 607
Size:  39.5 KB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width