Crystal Report参数中的日期格式问题 [英] Date format problem in parameters of Crystal report

问题描述

大家好！我使用以下代码在组合框中显示日期.

Hi everyone! I have used following code to show dates in combo boxes.

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 TheDate 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("TheDate")) Then
                        cmbisrbdt.Items.Add(reader("TheDate"))
                    End If
                    If Not cmbisredt.Items.Contains(reader("TheDate")) Then
                        cmbisredt.Items.Add(reader("TheDate"))
                    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

也许您已经了解到，我已经将日期转换为字符串以显示为以下格式，即DD-MM-YYYY.但是，在将日期转换为字符串之后，如果我尝试通过使用诸如ORDER BY YEAR(dt)ASC，MONTH(dt)ASC，DAY(dt)ASC之类的代码在Crystal报表中以升序模式显示日期，那么Crystal报表将无法理解它.结果，当用户输入开始&时，我必须将其转换回日期(格式为mm/dd/yyyy).显示报告的结束日期.为此，我使用了以下代码.请看一下.

Maybe you have already understood it that I have converted date as string to show it like following format I mean DD-MM-YYYY. However after converting dates as string if I try to show it in ascending mode there in Crystal report by using code like ORDER BY YEAR(dt) ASC, MONTH(dt) ASC, DAY(dt) ASC then Crystal report doesnt understand it. As a result I have to convert it back as date (format mm/dd/yyyy) when user will input beginning & ending date to show report. To do it I have used following code. Please take a look.

Private Sub cmbisrbdt_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbisrbdt.SelectedIndexChanged
        Dim bdsv As String = cmbisrbdt.SelectedItem
        Dim bdDate As Date
        bdDate = DateTime.Parse(bdsv, Globalization.CultureInfo.CreateSpecificCulture("en-CA"))
        TextBox1.Text = bdDate

    End Sub

    Private Sub cmbisredt_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbisredt.SelectedIndexChanged
        Dim edsv As String = cmbisredt.SelectedItem
        Dim edDate As Date
        edDate = DateTime.Parse(edsv, Globalization.CultureInfo.CreateSpecificCulture("en-CA"))
        TextBox2.Text = edDate
    End Sub

然后，我将这些日期用作参数.请检查我的代码.

Then I have used those dates as parameter. Please check my codes.

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,dt FROM Income_sheet WHERE dt BETWEEN '" & TextBox1.Text & "' AND '" & TextBox2.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

一切正常，但是当我选择其他日期时会出现问题.让我说清楚.我在Crystal报表中创建了两个参数.参数值类型为DateTime.甚至我都使用过以下代码

Everything works properly but it makes problem when I select different dates. Let me make it clear. I have created two parameters in Crystal report. Parameters value type is DateTime. Even I have used following code

{Income_sheet.dt} >= {?bdate} and {Income_sheet.dt} <= {?edate}

在Formula Workshop&参数DateOrder为crDayMonthYear.我在报表中使用了这些参数来显示开始&文本之间报表旁边的结束日期，以便用户了解日期范围.当我运行程序时在组合框中提供日期，例如开始日期01-07-2011&结束日期2011年7月31日单击显示报告"按钮，然后它将正确显示所有内容，但报告中的参数除外以显示开始&文本之间报表旁边的结束日期.它假定显示开始&报告之间的结束日期，例如报告介于2011年7月1日至2011年7月31日之间的文本之间，但显示2011年7月1日至2011年7月31日这是不对的！我的意思是，尽管它假定显示DD-MM-YYYY DD-MM-YYYY，但它显示MM-DD-YYYY DD-MM-YYYY格式.有趣的是，如果我选择2011年7月20日作为开始日期& 2011年7月31日作为组合框中的结束日期单击显示报告按钮，然后它会在报告中显示相同的内容，这是正确的，我的意思是在2011年7月20日至2011年7月31日之间报告.您能告诉我为什么会发生吗?当我选择01-07-2011&时，为什么以以下MM-DD-YYYY格式显示日期.为什么我选择20-07-2011时以正确的格式显示日期，我的意思是DD-MM-YYYY?请帮我解决这个问题.

in Formula workshop & parameters DateOrder is crDayMonthYear. I have used those parameters in report to show beginning & ending dates beside report between text so that user can understand date range. When I run my program & provide dates in combo boxes like beginning date 01-07-2011 & ending date 31-07-2011 & click on show report button then it shows everything properly except parameters in report to show beginning & ending dates beside report between text. It suppose to show beginning & ending dates beside report between text like Report between 01-7-2011 31-7-2011 but it shows 7-1-2011 31-7-2011 which is not right! I mean it shows MM-DD-YYYY DD-MM-YYYY format though it suppose to show DD-MM-YYYY DD-MM-YYYY. Interesting thing is that if I select 20-07-2011 as beginning date & 31-07-2011 as ending date in combo boxes & click on show report button then it shows same thing in report which is right I mean Report between 20-7-2011 31-7-2011. Would you please tell me why does it happen? Why does it show date in following MM-DD-YYYY format when I select 01-07-2011 & why does it show date in right format I mean DD-MM-YYYY when I select 20-07-2011? Please help me to solve this problem.

推荐答案

对不起，我有一个非常详细的解决方案，但是首先我的计算机崩溃了，我将它复制粘贴了下来，然后进行了此编辑窗口不支持Argh STRG-Z.

好吧，简单来说就是:

您的问题是因为SqlServer解释了Statement中的指定字符串

Sorry, i had a very detailed solution, but first my Computer crashed, the i copy-pasted it over and this edit window doesnt Support STRG-Z, Argh.

Ok, to be briefly:

Your issue is because SqlServer interprets the specified strings in Statement

BETWEEN '" & TextBox1.Text & "' AND '" & TextBox2.Text & "'

格式为MM-DD-YYYY.

虽然在2012年7月30日获得预期的结果，但这似乎没有问题，而在2012年1月7日供应的结果却很奇怪.也就是说，由于在01-07-2012的情况下，SqlServer可以解析MM-和DD-部分而不会产生任何抱怨，当格式化为DD-MM-YYYY时，会产生类似07-01-2012的日期.即使这就是造成问题的原因，但一切都按预期在这里进行.实际的问题是字符串30-07-2012:SqlServer最终出现诸如"hmmm，30成为MM-Portion的方式很高，因此它必须是DD-Portion ...因此07必须是MM-Portion"的情况. DD-Portion ..."-使用DD-MM-YYYY格式化时，日期时间看起来像2012年7月30日.有趣的是，这实际上是您所期望的，但是是偶然地实现的.

另一个细微的建议:
1.)将转换保留在一个技术域中！在提取数据并传递数据时在SqlServer中转换日期时间，或者在.NET中进行转换，切勿混用，因为您将像今天一样面对这样的问题.
2.)即使在肮脏的小原型中，也永远不要使用字符串连接来构建SQL语句，因为您将开发一个不好的习惯，使用可能的SqlInjections打开应用程序，并且失去自动类型转换的便利性(也许您会使用 SqlParameters [

in the format MM-DD-YYYY.

while you get expected result supplying 30-07-2012 this seems to be no Problem, while supplying 01-07-2012 keeps to be weird. that is, becaus in case of 01-07-2012, SqlServer can parse the MM- and DD-portions without complains, resulting in a dateime like 07-01-2012 when formatted as DD-MM-YYYY. Even if that is what makes problems, everything works as expected here. The actual Problem is the string 30-07-2012: SqlServer Ends up in a Situation like "hmmm, 30 is way to high to be the MM-Portion, so it must be the DD-Portion...therefore 07 must be the DD-Portion..." - resulting in a datetime that looks like 30-07-2012 when formatted with DD-MM-YYYY. Funny, that this was actually that what you expected, but achieved by accident.

another subtle advices:
1.)keep conversions in one Technology-Domain! either convert thos datetimes in SqlServer when pulling out data and passing in data or do it in .NET, never mix, as you will face exactly such a Problem like yours today.
2.) don''t ever use string-concatination for building SQL-Statements, even in dirty little prototypes, as you will develop a bad habbit opening your application with possible SqlInjections and lose the comfort of automatic type-conversions (maybe you wouldn''t have had this Problem when using SqlParameters[^]).

best regards!

这篇关于Crystal Report参数中的日期格式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！