Report with Crystal Reports, visual basic 2010 and mysql

Question

I'm trying to create a report in Crystal Reports, but when I assign the parameters to the report, it doesn't show the result from the select query. It shows all columns of the table, although the result of the select query is correct. This is my code:

    Dim ulogueado, consulta As String       
    consulta = "select nombre from usuario where usuario = @user"
    Dim lector1 As MySqlDataReader
    Dim comando As New MySqlCommand(consulta, conector)
    comando.Parameters.AddWithValue("@user", usuario_conectado)
    Try
        lector1 = comando.ExecuteReader()
        If lector1.Read Then
            ulogueado = lector1.GetString(0)
        End If
        Dim reporte1 As New CrystalReport3
        reporte1.SetDataSource(lector1.GetString(0))
        reportes1.CrystalReportViewer1.ReportSource = reporte1
        reportes1.CrystalReportViewer1.RefreshReport()
    Catch ex As Exception
    End Try
    reportes1.Show()

I'm almost sure that the problem could be in SetDataSource line, but I don't know what else can I do. I appreciate all your attention and collaboration with this issue.

haraman haraman · Accepted Answer · 2015-10-20T06:28:01

As the documentation says you need to pass a RecordSet or DataSet to the ReportEngine whereas you are passing a field of the DataReader which return a String

reporte1.SetDataSource(lector1.GetString(0))

Changing the statement to use the DataReader should solve the problem such as

reporte1.SetDataSource(lector1)

I have not used MySqlDataReader as data source but have used DataSet to generate the reports which works well.

Another thing you need to check is the saved data as mentioned in my comment above. If you are using parameters in your reports then there is other way to pass values to those parameters, see how-to-pass-values-to-two-different-parameters

EDIT: Using DataSet

Dim ulogueado, consulta As String       
consulta = "select nombre from usuario where usuario = @user"
Dim comando As New MySqlCommand(consulta, conector)
comando.Parameters.AddWithValue("@user", usuario_conectado)
Dim da As MySqlDataAdapter = New MySqlDataAdapter()
Dim ds As DataSet = New DataSet()
da.SelectCommand = comando
Try
    da.Fill(ds, "usuario")
    Dim reporte1 As New CrystalReport3
    reporte1.SetDataSource(ds)
    reportes1.CrystalReportViewer1.ReportSource = reporte1
    reportes1.CrystalReportViewer1.RefreshReport()
Catch ex As Exception
    'Check for errors here
    'MsgBox(ex.Message)
End Try
reportes1.Show()

For more details on DataAdapters, DataSet you may read this SO post connecting-to-a-mysql-db-with-c-sharp-need-some-with-datasets

Report with Crystal Reports, visual basic 2010 and mysql

1 Answers