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.