I have a form on which I have checkboxes - on checking these boxes, the names appear in the crystal reports columns while unchecked ones don't appear. I want assistance to change these code such that instead of using the checkboxes on form, the crystal reports displays the columns which have data, while those ones without data are not displayed automatically.
I will appreciate assistance. Thanks.
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data.OleDb
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Windows.Forms
Public Class Form1
Inherits Form
Private objRpt As CrystalReport1
Public Sub New()
InitializeComponent()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Private Function CreateSelectQueryAndParameters() As String
Dim reportDocument As ReportDocument
Dim paramFields As ParameterFields
Dim paramField As ParameterField
Dim paramDiscreteValue As ParameterDiscreteValue
reportDocument = New ReportDocument()
paramFields = New ParameterFields()
Dim query As String = "SELECT "
Dim columnNo As Integer = 0
If chbCode.Checked Then
columnNo += 1
query = query.Insert(query.Length, "CustomerCode as Column" + columnNo.ToString())
paramField = New ParameterField()
paramField.Name = "col" + columnNo.ToString()
paramDiscreteValue = New ParameterDiscreteValue()
paramDiscreteValue.Value = "Customer Code"
paramField.CurrentValues.Add(paramDiscreteValue)
'Add the paramField to paramFields
paramFields.Add(paramField)
End If
If chbFirstName.Checked Then
columnNo += 1
If query.Contains("Column") Then
query = query.Insert(query.Length, ", ")
End If
query = query.Insert(query.Length, "FirstName as Column" + columnNo.ToString())
paramField = New ParameterField()
paramField.Name = "col" + columnNo.ToString()
paramDiscreteValue = New ParameterDiscreteValue()
paramDiscreteValue.Value = "First Name"
paramField.CurrentValues.Add(paramDiscreteValue)
'Add the paramField to paramFields
paramFields.Add(paramField)
End If
If chbLastName.Checked Then
columnNo += 1
'To determine Column number
If query.Contains("Column") Then
query = query.Insert(query.Length, ", ")
End If
query = query.Insert(query.Length, "LastName as Column" + columnNo.ToString())
paramField = New ParameterField()
paramField.Name = "col" + columnNo.ToString()
paramDiscreteValue = New ParameterDiscreteValue()
paramDiscreteValue.Value = "Last Name"
paramField.CurrentValues.Add(paramDiscreteValue)
'Add the paramField to paramFields
paramFields.Add(paramField)
End If
If chbAddress.Checked Then
columnNo += 1
If query.Contains("Column") Then
query = query.Insert(query.Length, ", ")
End If
query = query.Insert(query.Length, "Address as Column" + columnNo.ToString())
paramField = New ParameterField()
paramField.Name = "col" + columnNo.ToString()
paramDiscreteValue = New ParameterDiscreteValue()
paramDiscreteValue.Value = "Address"
paramField.CurrentValues.Add(paramDiscreteValue)
'Add the paramField to paramFields
paramFields.Add(paramField)
End If
If chbPhone.Checked Then
columnNo += 1
If query.Contains("Column") Then
query = query.Insert(query.Length, ", ")
End If
query = query.Insert(query.Length, "Phone as Column" + columnNo.ToString())
paramField = New ParameterField()
paramField.Name = "col" + columnNo.ToString()
paramDiscreteValue = New ParameterDiscreteValue()
paramDiscreteValue.Value = "Phone"
paramField.CurrentValues.Add(paramDiscreteValue)
'Add the paramField to paramFields
paramFields.Add(paramField)
End If
'if there is any remaining parameter, assign empty value for that
'parameter.
For i As Integer = columnNo To 4
columnNo += 1
paramField = New ParameterField()
paramField.Name = "col" + columnNo.ToString()
paramDiscreteValue = New ParameterDiscreteValue()
paramDiscreteValue.Value = ""
paramField.CurrentValues.Add(paramDiscreteValue)
'Add the paramField to paramFields
paramFields.Add(paramField)
Next
CrystalReportViewer1.ParameterFieldInfo = paramFields
query += " FROM Customer"
Return query
End Function
Private Sub btnViewReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewReport.Click
objRpt = New CrystalReport1()
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb"
'Get Select query String and add parameters to the
'Crystal report.
Dim query As String = CreateSelectQueryAndParameters()
'if there is no item select, then exit from the method.
If Not query.Contains("Column") Then
MessageBox.Show("No selection to display!")
Return
End If
Try
Dim Conn As New OleDbConnection(connString)
Dim adapter As New OleDbDataAdapter(query, connString)
Dim Ds As New DataSet1()
adapter.Fill(Ds, "Customer")
objRpt.SetDataSource(Ds)
CrystalReportViewer1.ReportSource = objRpt
Catch oleEx As OleDbException
MessageBox.Show(oleEx.Message)
Catch Ex As Exception
MessageBox.Show(Ex.Message)
End Try
End Sub
End Class