VS 2022 Community 17.5.3, Console App. The last Console.WriteLine in the following throws an InvalidCastException.
Module Module1
Dim DT1 As DataTable = New DataTable("DT1")
' Add a column to the DataTable
Dim FieldType As Type = GetType(System.Int64)
Dim MyColumn As DataColumn = New DataColumn("Col1", FieldType)
DT1.Columns.Add(MyColumn)
' Add a Row to the DataTable and populate it
Dim NewRow As DataRow = DT1.NewRow
Dim RowArray() As Object = New Object() {1234}
NewRow.ItemArray = RowArray
DT1.Rows.Add(NewRow)
' Display formatted value from Row, Col1
Console.WriteLine("Col1 Value = " & NewRow.Item("Col1"))
Console.WriteLine("Col1 Type = " & NewRow.Item("Col1").GetType.ToString)
Console.WriteLine("Col1 Formatted Value = " & NewRow.Item("Col1").ToString("N0"))
End Sub
Output is: Col1 Value = 1234 Col1 Type = System.Int64
Then, the debugger kicks in with:
System.InvalidCastException
HResult=0x80004002
Message=Conversion from string "N0" to type 'Integer' is not valid.
Source=Microsoft.VisualBasic
StackTrace:
at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value) in f:\dd\vb\runtime\msvbalib\Helpers\Conversions.vb:line 788
at ConsoleTest.Module1.Main() in C:\Data\VB.NET\ConsoleTest\ConsoleTest\Module1.vb:line 1434
This exception was originally thrown at this call stack:
Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String, System.Globalization.NumberFormatInfo) in Conversions.vb
Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String) in Conversions.vb
Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String) in Conversions.vb
Inner Exception 1:
FormatException: Input string was not in a correct format.
Why can't I use ("N0") as the ToString format option? The column has a DataType of Int64 but converting it to a String (ToString) seems to be treating the format text ("N0") as an index of an array instead of a format code.
If instead of the the line:
Console.WriteLine("Col1 Formatted Value = " & NewRow.Item("Col1").ToString("N0"))
I use:
Dim MyInt64 As Int64 = NewRow.Item("Col1")
Console.WriteLine("Col1 Value Formatted = " & MyInt64.ToString("N0"))
I get the desired and expected result: Col1 Formatted Value = 1,234
You obviously have Option Strict Off if this works:
Dim MyInt64 As Int64 = NewRow.Item("Col1")
The DataRow.Item property is type Object so that code is relying on an implicit narrowing conversion from Object to Long, which is not allowed with Option Strict On. This is an example of why you should ALWAYS have Option Strict On. It forces you to be explicit with your typing so there can be no confusion. As it is, you are relying on the system to guess what you mean to do and, while it will get it right most of the time, sometimes it will get it wrong. This is one of those times. Here:
Dim MyInt64 As Int64 = NewRow.Item("Col1")
Console.WriteLine("Col1 Value Formatted = " & MyInt64.ToString("N0"))
you're calling ToString on a Long value so the system knows exactly what overload you want to call. Here:
Console.WriteLine("Col1 Formatted Value = " & NewRow.Item("Col1").ToString("N0"))
the system actually thinks that you're calling the Object.ToString method with no parameters and then indexing the resulting String to get the Char at that index. Because VB allows you to omit the empty parnetheses on a call to a method with no parameters, it's interpreting that code as this:
Console.WriteLine("Col1 Formatted Value = " & NewRow.Item("Col1").ToString()("N0"))
which is the same as this:
Console.WriteLine("Col1 Formatted Value = " & NewRow.Item("Col1").ToString().Chars("N0"))
That Chars property is expecting a Integer index but the String "N0" cannot be converted to an Integer, so that's what the error message is telling you. Because you have Option Strict Off, none of that check doesn't occur until run time, instead of being caught at compile time.
The solution is to ALWAYS have Option Strict On and be explicit with your typing. You can turn it On in the project properties and you should also turn it On in the VS options, so it will be On by default for all future projects. Once it's On, you'll probably get a lot of errors where your code relies on implicit conversions and late binding, so you'll have to fix each one with an appropriate cast or conversion.
The specific solution here is to actually get a Long value from the DataRow and then you can call that overload of ToString. There are various ways to do that but I would do this:
Console.WriteLine($"Col1 Formatted Value = {NewRow.Field(Of Long)("Col1"):N0}")
That uses string interpolation to clean things up a bit, which means no need to call ToString directly anyway. That Field method is part of LINQ to DataSet and allows you to get a field value as a specific type. Note that you could also use the composite formatting built right into Console.WriteLine:
Console.WriteLine("Col1 Formatted Value = {0:N0}", NewRow.Field(Of Long)("Col1"))