Creating a Crystal Report

I am using Vb.net 2008 and SQL 2008. I have created an application and I try to create a crystal report. I don't know why the report does not produce any data.

Here is my code on the form for Crystal Report.


Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Windows.Forms
Imports CrystalDecisions.ReportSource

Public Class Invoice

Private Sub InvoiceLoad(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
PrintSelectedInvoice()
End Sub
Private Sub PrintSelectedInvoice()

Dim Cnn As New SqlConnection
Try
If Cnn.State = ConnectionState.Open Then Cnn.Close()
Cnn.ConnectionString = My.MySettings.Default.GarageConnectionString
Cnn.Open()

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "error")
End
End Try


Dim InvoiceNoFind As Integer

If CreateInvoiceQuery.InvoiceWorksDataGridView.SelectedRows.Count = 0 And CreateInvoiceQuery.InvoiceWorksDataGridView.RowCount = 0 And CreateInvoiceQuery.InvoiceWorksDataGridView.SelectedCells.Count = 0 Then
MessageBox.Show("Print not allowed as no row/cell is selected!", "Garage", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Exit Sub
End If

'If cell only is selected, use this to find the UserID
If CreateInvoiceQuery.InvoicesDataGridView.SelectedRows.Count = 0 And CreateInvoiceQuery.InvoicesDataGridView.SelectedCells.Count > 0 Then
InvoiceNoFind = CType(CreateInvoiceQuery.InvoicesDataGridView.Rows(CreateInvoiceQuery. InvoicesDataGridView.SelectedCells(0).RowIndex).Cells("DataGridViewTextBoxColumn8").Value, Integer)
'If full row is selected, use this to find the UserID
ElseIf CreateInvoiceQuery.InvoicesDataGridView.SelectedRows.Count > 0 And CreateInvoiceQuery.InvoicesDataGridView.SelectedCells.Count > 0 Then
InvoiceNoFind = CType(CreateInvoiceQuery.InvoicesDataGridView.Rows(CreateInvoiceQuery. InvoicesDataGridView.SelectedCells(0).RowIndex).Cells("DataGridViewTextBoxColumn8").Value, Integer)
End If


Dim Report As New InvoiceReport
Dim Dt As New DataTable
Dim query As String
Dim Com As New SqlCommand
Dim DA As New SqlDataAdapter
Dim applicationPath As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutin gAssembly.GetModules()(0).FullyQualifiedName)


query = "SELECT * FROM Invoices WHERE(" + "InvoiceNo" + ")LIKE('" + InvoiceNoFind.ToString + "%');"

Com = New SqlCommand(query, Cnn)
DA.SelectCommand = Com
DA.SelectCommand.CommandType = CommandType.Text
DA.Fill(Dt)
Report.SetDataSource(Dt)
CrystalReportViewer1.ReportSource = Report
CrystalReportViewer1.Show()

I found out that the problem why the report is not shown is because it uses two tables Invoices and InvoiceWorks which are joint with InvoiceNo

The problem is that I don't know how to fix it.

Do I need to change the query?

It does seem that the query is the problem. Assuming the report is opening (showing field labels etc.) but there is no data, then it is definitely the issue.

As you know, the current query does not include the second table at all, so that is a problem . I don't know SQL2008, but I think your query has other problems besides missing the second table. I don't think you need the parentheses and + signs around your field names. Also, I don't think you need to use LIKE, since you know the specific Invoice #. I'm not to keen on using * instead of specifying field names either

Assuming "InvoiceNo" is the field name in the Table, your current query might at least fetch the Master Record if it read like this:

"SELECT * FROM Invoices WHERE Invoices.InvoiceNo = " + InvoiceNoFind.ToString

I don't know if any of this stuff "%'); is needed at the end of your query, for SQL2008, but I would think not.

See if you get anything at all with this -

A correction (hopefully) to the SQL statement - in order to add the ' (single quotes) around the Value to be found, you may need to use this:

"SELECT * FROM Invoices WHERE Invoices.InvoiceNo = '" + InvoiceNoFind.ToString + "'"