Click here to Skip to main content
15,891,529 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everyone! I have used following code to show dates in combo boxes.

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

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

VB
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
VB
{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.
Posted

1 solution

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

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


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!
 
Share this answer
 
Comments
kingcoder0110 24-Nov-12 3:09am    
Thank you very much for trying to help me to solve my problem. You said it that "The actual Problem is the string 30-07-2012". But didnt you check my codes where I have changed it back from string to date? Please check following code

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

First of all I have collected string date (DD-MM-YYYY) from combo boxes & converted it back as date (MM-DD-YYYY) & got that converted result in text boxes & used those text boxes as parameter. So what does it make problem?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900