Crystal Report with VS 2005

[skinpower]

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 & DatagridView)
So, would you help me?

===================================================

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

Dim s As String

s = "SELECT FirstName, LastName, staff, a_time, reason FROM tblconsumer " & _
"Join tblattendance on tblattendance.c_id = tblconsumer.c_id " & _
"Join tblcode on tblattendance.a_id = tblcode.a_id " & _
"WHERE 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

[shakti5385]

There is difference between the connection with the Access and the SQL server.
So just make the connection with the SQL server for getting the connection help just click on the Database connection at my signature.
Related to the crystal report Just use the code, and change the connection and the SQL query according to your condition.

Dim CrystalReportViewer1 As CrystalDecisions.Windows.Forms.CrystalReportViewer = New CrystalDecisions.Windows.Forms.CrystalReportViewer
Dim Report As CrystalDecisions.CrystalReports.Engine.ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument
CrystalReportViewer1.ActiveViewIndex = 0
CrystalReportViewer1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
CrystalReportViewer1.DisplayGroupTree = False
CrystalReportViewer1.Dock = System.Windows.Forms.DockStyle.Fill
CrystalReportViewer1.Location = New System.Drawing.Point(0, 0)
CrystalReportViewer1.Name = "CrystalReportViewer1"

Dim QueryString As String = "select * from TableName" 'Your Query here and the table name
Dim Connection As New OleDbConnection(funcs.con) 'Your Database Connection Here
Connection.Open()

Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter(querystring, connection)
Dim DataSet As DataSet = New DataSet() 'DataSet
Adapter.Fill(DataSet)
Dim DataTable As DataTable = New DataTable 'DataTable
DataTable = DataSet.Tables(0)
Report.Load(Application.StartupPath & "/ReportName.rpt") 'Report Name Here
Report.SetDataSource(DataTable)
CrystalReportViewer1.ReportSource = Report

[skinpower]

But, I am using SQL instead of ACCESS. If I summarize the problem, the problem is not my sql query nor my connection to sql database. The problem is when I join 3 tables and make a dataset out of it, CR doesn't seem to accept that dataset. AND I don't understand why. This only happens when I JOIN tables. If I create a dataset out of ONE table, there is no problem at all. Any idea?

[shakti5385]

First thing is that above code I make for the SQL Server 2005, and you are saying that when you are joining the table then you are getting the problem.
Will you please tell me what type of Join you are using for Joining all the table.
Check the Join type at your hand and test the report.

[skinpower]

Following is my query string. I checked this query in sql and get the result. So, I think the query is correct. When I try to get fields from 3 different tables (even though i joined them in the dataset), I still have to select the field from different tables in CR design. I think that causes the problem. Am I right?

Dim s5 As String
s5 = "SELECT tblconsumer.FirstName, tblconsumer.LastName, tblattendance.a_date, tblattendance.a_time, tblattendance.staff, tblattendancecode.reason " & _
"FROM tblconsumer INNER JOIN tblattendance ON tblconsumer.c_id = tblattendance.c_id INNER JOIN " & _
"tblattendancecode ON tblattendance.a_id = tblattendancecode.a_id "

[shakti5385]

Are you joining all the table in the same way in the crystal report too, check the joining in the crystal report where you are linking the table.
Make the same joining there also.

[skinpower]

Shakti, thanks for the help. Could you look at this to make sure that I am having same join on both CR linking and query?

query s = SELECT * FROM tblconsumer
..............INNER JOIN tblattendance ON tblconsumer.c_id = tblattendance.c_id
..............INNER JOIN tblattendancecode ON tblattendancecode.a_id = tblattendance.a_id

Crystal Report : attached picture

Thanks.

[shakti5385]

I think it is correct but change the enforce join type and run the report.
And second think i want to say that check the field value in the table also is it correct or not?

[skinpower]

I changed enforce type (tried on each type), no luck.
And, what do you mean by check field value in the table? Could you explain it more? thanks

[shakti5385]

Check the filed value mean check the data type of the join statement is that correct or not.
Are you making any group in your report, Group the report according to CID that is in tblattendance.
After making the group CR display the data according to the grouping, so if you did not make any group then make it. :thumb: