Hi! How should I get date, month & year in ascending order there in Crystal report? I have written following code there in my form to show report.
Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class Income_Sheet_Report
Private Sub Income_Sheet_Report_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cmbisrbdt.Items.Clear()
cmbisrbdt.Text = "BEGINNING DATE"
cmbisredt.Items.Clear()
cmbisredt.Text = "END DATE"
Me.FormBorderStyle = Windows.Forms.FormBorderStyle.None
Dim con As New SqlConnection(ConnectionString)
Dim com As SqlCommand = Nothing
Dim reader As SqlDataReader = Nothing
Try
con.Open()
com = New SqlCommand("Select CONVERT(varchar, dt, 105) AS dt From Income_sheet ORDER BY YEAR(dt) ASC, MONTH(dt) ASC, DAY(dt) ASC", con)
reader = com.ExecuteReader(CommandBehavior.CloseConnection)
If reader.HasRows Then
cmbisrbdt.Items.Clear()
cmbisredt.Items.Clear()
While reader.Read
If Not cmbisrbdt.Items.Contains(reader("dt")) Then
cmbisrbdt.Items.Add(reader("dt"))
End If
If Not cmbisredt.Items.Contains(reader("dt")) Then
cmbisredt.Items.Add(reader("dt"))
End If
End While
End If
reader.Close()
Catch ex As Exception
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
End Sub
Private Sub butisrsho_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butisrsho.Click
If cmbisrbdt.Text = "BEGINNING DATE" Then
MsgBox("PLEASE FILL THE COMBO BOX.")
ElseIf cmbisredt.Text = "END DATE" Then
MsgBox("PLEASE FILL THE COMBO BOX.")
Else
Dim con As New SqlConnection(ConnectionString)
Try
con.Open()
Dim com As New SqlCommand("SELECT Dev_charge,Tui_f,Exm_f,Reg_f,Form_f_f,Hostel_f,Delay_f,Bank,Others,Tot,CONVERT(varchar, dt, 105) AS dt FROM Income_sheet WHERE CONVERT(varchar, dt, 105) BETWEEN '" & cmbisrbdt.Text & "' AND '" & cmbisredt.Text & "' ORDER BY YEAR(dt) ASC, MONTH(dt) ASC, DAY(dt) ASC", con)
Dim adapter As New SqlDataAdapter(com)
Dim table As New DataTable("Income_sheet")
adapter.Fill(table)
con.Close()
Dim cryRpt As New ReportDocument
cryRpt.Load(Application.StartupPath & "\Reports\CrystalReport12.rpt")
cryRpt.SetDataSource(table)
Dim crParameterFieldDefinitions As ParameterFieldDefinitions
Dim crParameterFieldDefinition As ParameterFieldDefinition
Dim crParameterFieldDefinitions1 As ParameterFieldDefinitions
Dim crParameterFieldDefinition1 As ParameterFieldDefinition
Dim crParameterValues As New ParameterValues
Dim crParameterValues1 As New ParameterValues
Dim crParameterDiscreteValue As New ParameterDiscreteValue
Dim crParameterDiscreteValue1 As New ParameterDiscreteValue
crParameterDiscreteValue.Value = cmbisrbdt.Text
crParameterDiscreteValue1.Value = cmbisredt.Text
crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields
crParameterFieldDefinition = crParameterFieldDefinitions.Item("bdate")
crParameterFieldDefinitions1 = cryRpt.DataDefinition.ParameterFields
crParameterFieldDefinition1 = crParameterFieldDefinitions.Item("edate")
crParameterValues = crParameterFieldDefinition.CurrentValues
crParameterValues1 = crParameterFieldDefinition1.CurrentValues
crParameterValues.Clear()
crParameterValues1.Clear()
crParameterValues.Add(crParameterDiscreteValue)
crParameterValues1.Add(crParameterDiscreteValue1)
crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)
crParameterFieldDefinition1.ApplyCurrentValues(crParameterValues1)
CrystalReportViewer12.ReportSource = cryRpt
CrystalReportViewer12.Refresh()
MsgBox("INCOME SHEET REPORT HAS BEEN SHOWN SUCCESSFULLY.")
cmbisrbdt.Text = "BEGINNING DATE"
cmbisredt.Text = "END DATE"
Catch ex As Exception
If con.State = ConnectionState.Open Then
con.Close()
End If
MessageBox.Show(ex.ToString, "An error occured", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End If
End Sub
Private Sub butinsrclo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butinsrclo.Click
Me.Close()
End Sub
End Class
Its properly showing date, month & year in ascending order in combo boxes. I have used two combo boxes to insert parameter.
But problem is that when I insert parameter & click to show report then it shows report but it doesn't show date, month & year in ascending order! I have created two parameters there in crystal report which are bdate & edate and their value type is string. Even I have written following code {Income_sheet.dt} >= {?bdate} and {Income_sheet.dt} <= {?edate} there in Formula workshop of Crystal report.
Would you please tell me how should I get date, month & year in ascending order in Crystal report?