none
Strange Behavior of Date format in Report Viewer When i convert it to a string RRS feed

  • Question

  • I am using this code

    Using con As New SqlConnection(cs)
                con.Open()
                Using cmd As New SqlCommand(
                                  "select ID,
                                        MDate,
                                        EXName,
                                        EXData,
                                        TelNo,
                                        Amount,
                                        EmpName
                                    from ExpensesBill2")
                    cmd.CommandType = CommandType.Text
                    cmd.Connection = con
                    Using ds As New DataSet, da As New SqlDataAdapter()
                        da.SelectCommand = cmd
    
                        'Convert Datagridview to Datatable
                        Dim gdt As New DataTable
                        gdt.Columns.Add("ID")
                        gdt.Columns.Add("MDate")
                        gdt.Columns.Add("EXName")
                        gdt.Columns.Add("EXData")
                        gdt.Columns.Add("TelNo")
                        gdt.Columns.Add("Amount")
                        gdt.Columns.Add("EmpName")
    
                        For i = 0 To DataGridView1.Rows.Count - 1
                            gdt.Rows.Add(DataGridView1.Rows(i).Cells(0).Value,
                                DataGridView1.Rows(i).Cells(1).Value,
                                DataGridView1.Rows(i).Cells(2).Value,
                                DataGridView1.Rows(i).Cells(3).Value,
                                DataGridView1.Rows(i).Cells(4).Value,
                                DataGridView1.Rows(i).Cells(5).Value,
                                DataGridView1.Rows(i).Cells(6).Value)
                        Next
    
                        ReportExpForm34.ReportViewer1.LocalReport.DataSources.Clear()
                        ReportExpForm34.ReportViewer1.LocalReport.ReportPath = Reportpath + "Report9.rdlc"
                        
                        Dim rds = New ReportDataSource("DataSet1", gdt) 
                        ReportExpForm34.ReportViewer1.LocalReport.DataSources.Add(rds)
    
                        If ReportExpForm34.ShowDialog() = DialogResult.OK Then
                        Else
                        End If
                        ReportExpForm34.Dispose()
                    End Using
                End Using
            End Using

    and i am using this Expression {=Format(CDate(Fields!MDate.Value), "dd/MM/yyyy")} and this one also {=FormatDateTime(Fields!MDate.Value, FormatDate("dd/MM/yyyy"))} it works nice and gave me what i want ,

    But there is a big problem occurs when printing this report some of rows appear normal date like this (29/06/2020) and the above rows appear like (#Error) but when i remove the expression and return to the original format like this(29/06/2020 12:00:00)it prints the report normally  ..... My question is How can i get rid of the (#Error) to get all rows like this (29/06/2020) 


    Regards From Egypt


    • Edited by Amr_Aly Monday, June 29, 2020 1:08 PM
    Monday, June 29, 2020 11:12 AM

Answers

  • Here is what I'm talking about, did a few columns to give you an idea.

    Dim gdt As New DataTable
    gdt.Columns.Add(New DataColumn() With {.ColumnName = "Id", .DataType = GetType(Integer)})
    gdt.Columns.Add(New DataColumn() With {.ColumnName = "MDate", .DataType = GetType(Date)})
    gdt.Columns.Add(New DataColumn() With {.ColumnName = "EXName", .DataType = GetType(String)})
    
    For i As Integer = 0 To DataGridView1.Rows.Count - 1
        gdt.Rows.Add(
            {
                CInt(DataGridView1.Rows(i).Cells(0).Value),
                CDate(DataGridView1.Rows(i).Cells(1).Value),
                CStr(DataGridView1.Rows(i).Cells(2).Value)}
        )
    
    Next


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Monday, June 29, 2020 3:47 PM
    Moderator

All replies

  • First thing, strong type each DataColumn then when adding values strong type values into data columns. See if this changes the current issue or not as currently you have data columns all defined as string and pushing object values to the data table.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, June 29, 2020 11:52 AM
    Moderator
  • First thing, strong type each DataColumn then when adding values strong type values into data columns. See if this changes the current issue or not as currently you have data columns all defined as string and pushing object values to the data table.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thanks for fast reply , 

    But i don't know what is Strong type and how can i Strong type each data column

    I did this to the Datatable

    Dim gdt As New DataTable gdt.Columns.Add("ID").ToString() gdt.Columns.Add("MDate").ToString() gdt.Columns.Add("EXName").ToString() gdt.Columns.Add("EXData").ToString() gdt.Columns.Add("TelNo").ToString() gdt.Columns.Add("Amount").ToString() gdt.Columns.Add("EmpName").ToString() For i = 0 To DataGridView1.Rows.Count - 1 gdt.Rows.Add(

    DataGridView1.Rows(i).Cells(0).Value.ToString(), DataGridView1.Rows(i).Cells(1).Value.ToString(), DataGridView1.Rows(i).Cells(2).Value.ToString(), DataGridView1.Rows(i).Cells(3).Value.ToString(), DataGridView1.Rows(i).Cells(4).Value.ToString(), DataGridView1.Rows(i).Cells(5).Value.ToString(), DataGridView1.Rows(i).Cells(6).Value.ToString()) Next


    And the error still appears


    Regards From Egypt

    Monday, June 29, 2020 1:27 PM
  • Here is what I'm talking about, did a few columns to give you an idea.

    Dim gdt As New DataTable
    gdt.Columns.Add(New DataColumn() With {.ColumnName = "Id", .DataType = GetType(Integer)})
    gdt.Columns.Add(New DataColumn() With {.ColumnName = "MDate", .DataType = GetType(Date)})
    gdt.Columns.Add(New DataColumn() With {.ColumnName = "EXName", .DataType = GetType(String)})
    
    For i As Integer = 0 To DataGridView1.Rows.Count - 1
        gdt.Rows.Add(
            {
                CInt(DataGridView1.Rows(i).Cells(0).Value),
                CDate(DataGridView1.Rows(i).Cells(1).Value),
                CStr(DataGridView1.Rows(i).Cells(2).Value)}
        )
    
    Next


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Monday, June 29, 2020 3:47 PM
    Moderator
  • Thanks Karen ,

    That's it , My problem solved


    Regards From Egypt

    Tuesday, June 30, 2020 5:59 PM
  • Thanks Karen ,

    That's it , My problem solved


    Regards From Egypt

    Excellent :-)

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, June 30, 2020 7:09 PM
    Moderator