Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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?
Posted 10-Nov-12 1:16am
Edited 10-Nov-12 1:23am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The fields datatypes should be datetime instead of string.
And check this
How to change sort order of crystal report by using code?[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You've made the #1 rookie mistake of SQL. You stored your dates as string instead of the more appropriate datetime types. By doing this, you've made your database much harder to work with and lower performing as you're now converting strings to datetime on every record during every record scan.
 
You're also making the HUGE mistake of directly using user input in query strings. Google for "What is SQL Injection Attack" to find out why doing that will get you fired faster than you can blink your eyes.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 250
1 Jochen Arndt 155
2 PIEBALDconsult 150
3 DamithSL 125
4 Afzaal Ahmad Zeeshan 120
0 OriginalGriff 5,695
1 DamithSL 4,591
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,190


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 31 Oct 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100