Crystal报表中按升序排列的日期 [英] Dates in ascending order in Crystal report

问题描述

嗨！我应该如何获得日期，月份和时间在Crystal报告中有哪些升序？我在我的表单中写了以下代码来显示报告。

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

正确显示日期，月份和时间年份在组合框中按升序排列。我用了两个组合框来插入参数。

但问题是当我插入参数&点击显示报告，然后显示报告，但不显示日期，月份和年按升序排列！我在水晶报告中创建了两个参数： bdate & edate ，其值类型为字符串。即使我写了以下代码 {Income_sheet.dt}> = {？bdate} 和 {Income_sheet.dt}< = {？edate} 那里有水晶报告的方程式工作坊。

请你告诉我如何获得日期，月份和时间Crystal报表中按升序排列的年份？

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?

推荐答案

字段数据类型应该是datetime而不是string。

并检查这个

如何通过使用更改水晶报表的排序顺序代码？ [ ^ ]

The fields datatypes should be datetime instead of string.
And check this
How to change sort order of crystal report by using code?[^]

你已经犯了SQL的＃1新手错误。您将日期存储为字符串而不是更合适的日期时间类型。通过这样做，您使数据库更难以使用并降低性能，因为您现在在每次记录扫描期间将字符串转换为每个记录的日期时间。



您还犯了在查询字符串中直接使用用户输入的巨大错误。谷歌为什么是SQL注射攻击找出为什么这样做会让你发射得比你眨眼更快。

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.

这篇关于Crystal报表中按升序排列的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！