0

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

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 to show report. 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

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}

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} 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.

2
Contributors
1
Reply
5
Views
4 Years
Discussion Span
Last Post by G_Waddell
1

Hi,

Sorry I'll confess I didn't go through all that code BUT I find the best way to handle dates is to put them into DD MMM YYYY format i.e. 23 Nov 2012 Or YYYY-MM-DD 2012-11-23. To get your value back from SQL in DD MMM YYYY format instead of
CONVERT(varchar, dt, 105) AS TheDate
try CONVERT(varchar, dt, 106) AS TheDate

I can't off the top of my head remember the code value for the other one (Serial date format,) but I'm fairly sure Crystal will be able to understand either... It's actually how SQL store dates natively

Edited by G_Waddell: Friday night rush home

Votes + Comments
Thank you very much! It works! But it shows date like 01-Jan-2012 there in combo box. But my client wants to see date like 01-01-2012. Would you please tell me what should I do?
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.