Sheerwood John Caday Sheerwood John Caday - 7 days ago 6
Vb.net Question

How to display data in crystal report without duplicating the data?

i have a problem in displaying the data in crystal reports using left join because its duplicating my data even though i am using GROUP BY in sqlcommand.

here is the list of my tables:

table 1: complaint


enter image description here

table 2: errordesc (error description)


enter image description here

here is my code in crystal reports:

Dim objConn As MySqlConnection
Dim daT1, daT2 As MySqlDataAdapter
Dim activecomp As DataSet
Dim strConnection As String
Dim strSQL As String

strConnection = "server=localhost;user id=root;password=;database=ticketing_system;"
objConn = New MySqlConnection(strConnection)
objConn.Open()

strSQL = "SELECT * FROM errordesc LEFT JOIN complaint ON errordesc.tran_no=complaint.tran_no WHERE errordesc.status='On-process' group by errordesc.err_id "
daT1 = New MySqlDataAdapter(strSQL, objConn)
activecomp = New DataSet
daT1.Fill(activecomp, "comp")
daT1.Fill(activecomp, "active")

Dim rpt As New CrystalReport1
rpt.SetDataSource(activecomp)
CrystalReportViewer1.ReportSource = rpt

objConn.Close()


and my outout was this:
output

it duplicated my errordesc.err_id even thought i grouped it. :( please help if anyone know. thanks in advance...

Answer

oohh. i solved my own problem by coding my to crystal reports like this:

Dim objConn As MySqlConnection
    Dim daT1, daT2 As MySqlDataAdapter
    Dim activecomp As DataSet
    Dim strConnection As String
    Dim strSQL As String

    strConnection = "server=localhost;user id=root;password=;database=ticketing_system;"
    objConn = New MySqlConnection(strConnection)
    objConn.Open()


    strSQL = "SELECT * FROM errordesc WHERE errordesc.status='On-process' group by errordesc.err_id "
    daT1 = New MySqlDataAdapter(strSQL, objConn)
    activecomp = New DataSet
    daT1.Fill(activecomp, "errordesc")

    strSQL = "SELECT * FROM complaint left join errordesc on complaint.tran_no=errordesc.tran_no group by complaint.tran_no"
    daT2 = New MySqlDataAdapter(strSQL, objConn)
    daT2.Fill(activecomp, "complaint")

    Dim rpt As New CrystalReport1
    rpt.SetDataSource(activecomp)
    CrystalReportViewer1.ReportSource = rpt

    objConn.Close()
Comments