Kingcoder210 0 Light Poster

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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.