locked
sql query with CR RRS feed

  • Question

  • Hi, I am trying to produce this with Crystal Report and VB 2005.
    The problem is that the following sql query works fine in Access, and I could get a perfect report out of it. however, when I trying to use this query in VB, data won't show in the crystal report. I don't even get an error on this code. So, I couldn't figure it out why I don't get the result on Crystal Report.
    by the way, the result should be 5 distinct records and no field is NULL.( that's what I got from ACCESS)
    So, would you help me?
    I imported system.data and system.data.sqlclient. all the variables like connection string are already defined. following is just sub of cr load.


    Private Sub CRV1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CRV1.Load

    Dim s As String

    s = "SELECT tblconsumer.FirstName, tblconsumer.LastName, tblattendance.staff, tblattendance.a_time, tblattendancecode.reason, tblattendancecode.a_id, tblattendance.a_date " & _
    "FROM tblattendancecode RIGHT JOIN (tblconsumer INNER JOIN tblattendance ON tblconsumer.c_id = tblattendance.c_id) ON tblattendancecode.a_id = tblattendance.a_id " & _
    "WHERE (((tblattendancecode.a_id)<>7 Or (tblattendancecode.a_id) Is Null) AND ((tblattendance.a_date)=@date))"


    Dim today As String = Date.Now.ToString("MM/dd/yyyy")
    today = today & " 12:00:00 AM"

    RepdbDA.SelectCommand = New SqlCommand()
    RepdbDA.SelectCommand.Connection = RepdbConnection
    RepdbDA.SelectCommand.CommandText = s
    RepdbDA.SelectCommand.CommandType = CommandType.Text
    RepdbDA.SelectCommand.Parameters.AddWithValue("@date", today)

    RepdbDS = New DataSet()
    RepdbDA.Fill(RepdbDS, "rptdar")

    Dim dt As DataTable = New DataTable
    dt = RepdbDS.Tables(0)

    Dim DAReport = New rptdar() ' rptdar = new instance of C R
    DAReport.SetDataSource(dt)
    CRV1.ReportSource = DAReport

    End Sub
    Thursday, October 26, 2006 2:35 PM