-
Jan 2nd, 2018, 12:35 PM
#1
Thread Starter
New Member
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
-
Jan 2nd, 2018, 03:06 PM
#2
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"
-
Jan 3rd, 2018, 01:52 AM
#3
Thread Starter
New Member
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|