[RESOLVED] solution Character is not valid in vb.net

[roy88]

Dear All master,

please help to provide a solution.

  Dim stream As FileStream = File.Open("C:\Sample-Sales-Data.xlsx", FileMode.Open, FileAccess.Read)
        Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)
        Dim result As DataSet = excelReader.AsDataSet(New ExcelDataSetConfiguration() With {
         .ConfigureDataTable = Function(underscore) New ExcelDataTableConfiguration() With {.UseHeaderRow = True}
        })
      For Each table As DataTable In result.Tables
            For Each row As DataRow In table.Rows
                Dim salesRepName = CType(row("Sales_Rep_Name"), String)
                If [String].Equals(salesRepName, "Janet") Then
                    Dim year = CType(CType(row("Year"), Double), Integer)
			Console.WriteLine($"Janet's year is {year}") 'problem this line
                End If
            Next
        Next

        excelReader.Close()
        Console.ReadLine()

Thanks
roy88

[jmcilhinney]

A solution to what? Maybe make the effort to actually explain the issue. If you're using an older version of VB that doesn't support string interpolation then either update to a newer version, e.g. 2019, or just don't use string interpolation. There are other ways to construct a String from parts and we shouldn't have to explain then to you. If the problem is something else then I have no idea what it might be, which is why you need to explain it.

[roy88]

A solution to what? Maybe make the effort to actually explain the issue. If you're using an older version of VB that doesn't support string interpolation then either update to a newer version, e.g. 2019, or just don't use string interpolation. There are other ways to construct a String from parts and we shouldn't have to explain then to you. If the problem is something else then I have no idea what it might be, which is why you need to explain it.

dear mr. jmcilhinney ,
I'm using visual studio 2010 and the code solution so I can run in visual studio 2010.
Thanks
roy88

[roy88]

dear mr. jmcilhinney ,
I'm using visual studio 2010 and the code solution so I can run in visual studio 2010.
Thanks
roy88

How do I get a particular value of the column named Year whose say, Sales_Rep_Name value is Janet?

[jmcilhinney]

How do I get a particular value of the column named Year whose say, Sales_Rep_Name value is Janet?

That is unrelated to the question you asked in the first place.

[jmcilhinney]

dear mr. jmcilhinney ,
I'm using visual studio 2010 and the code solution so I can run in visual studio 2010.
Thanks
roy88

The line you highlighted is using string interpolation, which is basically native language support for composite formatting. That was introduced in (I think) VB 2015, or it may have been 2017. Either way, it was well after 2010. There are plenty of other options from building Strings from parts though. Like I said, we shouldn't have to teach you the basics but it's probably not worth the pain to fight it, so here goes. You can use String.Format in any version of VB to perform composite formatting. String interpolation compiles to that anyway. Console.WriteLine also has overloads that perform composite formatting and you're already calling that method. There are other options too but, apart from anything else, you can just use the concatenation operator, as I'm sure you've done many times.

[Delaney]

As JMC said, for VB2010, replace your $"...." with string.format(" .... {0}.... {1}....", firstvariable, secondvariable, etc...) and the link to documentation https://docs.microsoft.com/fr-fr/dot...t?view=net-5.0

[Niya]

Like I said, we shouldn't have to teach you the basics but it's probably not worth the pain to fight it, so here goes.

This is one of those rare occasions where I don't really fault the OP. Even I do sometimes forgot what features were introduced when. This is especially true for C# that seems to get a thousand new features every week! The different possible combinations of Framework and IDE versions don't exactly help either. For example, Tasks are a good example of something that has implementations in both the language(Async/Await) and the Framework(The Task class). I can see how something like this can confuse people. It's kind of hard sometimes to keep in your head what you can and cannot do based on what IDE/Framework/language version you're currently using. I make mistakes like this sometimes when I switch between .Net 5 and the classic Framework.

[jmcilhinney]

This is one of those rare occasions where I don't really fault the OP.

I do. This is almost certainly a case of copying code off the internet and pasting it without any understanding of what it actually does. If they had made the effort to think about what that line was actually doing, they could have worked out how to do it without string interpolation. It shouldn't take much effort at all to work out what a call to Console.WriteLine is doing.

[roy88]

please help there is an error running the code. The error " Input string was not in a correct format."

Dim stream As FileStream = File.Open("C:\Sample-Sales-Data.xlsx", FileMode.Open, FileAccess.Read)
        Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)
        Dim result As DataSet = excelReader.AsDataSet(New ExcelDataSetConfiguration() With {
         .ConfigureDataTable = Function(underscore) New ExcelDataTableConfiguration() With {.UseHeaderRow = True}
        })
For Each table As DataTable In result.Tables
            For Each row As DataRow In table.Rows
                Dim salesRepName = CType(row("Sales_Rep_Name"), String)
                If [String].Equals(salesRepName, "Janet") Then
                    Dim year = CType(CType(row("Year"), Double), Integer)
                    Console.WriteLine(String.Format("Janet's year is {0} {year}", {year}))
                End If
            Next
        Next

        excelReader.Close()
        Console.ReadLine()

[jmcilhinney]

Perhaps you should read the documentation for the String.Format method to learn how it works. It's certainly not like that. Information already exists out there. You can go and find it, rather than waiting for it to come to you. The documentation is always in the same place so it's not like you have to search for it. Just click the method name and press F1 and you're there.

[Delaney]

please help there is an error running the code. The error " Input string was not in a correct format."

Dim stream As FileStream = File.Open("C:\Sample-Sales-Data.xlsx", FileMode.Open, FileAccess.Read)
        Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)
        Dim result As DataSet = excelReader.AsDataSet(New ExcelDataSetConfiguration() With {
         .ConfigureDataTable = Function(underscore) New ExcelDataTableConfiguration() With {.UseHeaderRow = True}
        })
For Each table As DataTable In result.Tables
            For Each row As DataRow In table.Rows
                Dim salesRepName = CType(row("Sales_Rep_Name"), String)
                If [String].Equals(salesRepName, "Janet") Then
                    Dim year = CType(CType(row("Year"), Double), Integer)
                    Console.WriteLine(String.Format("Janet's year is {0} {year}", {year}))
                End If
            Next
        Next

        excelReader.Close()
        Console.ReadLine()

of course it isn't , your variable is year not {year} and with string.format you don't put the variable inside the string, just the position reference of the variable as explained in the documentation (see the link I gave)

 Console.WriteLine(String.Format("Janet's year is {0} {year}", {year}))

should be

 Console.WriteLine(String.Format("Janet's year is {0}", year))

[jmcilhinney]

of course it isn't , your variable is year not {year} and with string.format you don't put the variable inside the string, just the position reference of the variable as explained in the documentation (see the link I gave)

 Console.WriteLine(String.Format("Janet's year is {0} {year}", {year}))

should be

 Console.WriteLine(String.Format("Janet's year is {0}", year))

You don't even need to call String.Format. As I already said, Console.WriteLine already has composite formatting built in.

Console.WriteLine("Janet's year is {0}", year)

[Delaney]

yes I know but I imagine the OP, at some point, may use it with something else than Console.WriteLine so we might as well give the full syntax.

[roy88]

of course it isn't , your variable is year not {year} and with string.format you don't put the variable inside the string, just the position reference of the variable as explained in the documentation (see the link I gave)

 Console.WriteLine(String.Format("Janet's year is {0} {year}", {year}))

should be

 Console.WriteLine(String.Format("Janet's year is {0}", year))

Dear Delaney,

sorry for late reply, your code solution works perfectly. Thank you.

Thanks
roy88

[roy88]

You don't even need to call String.Format. As I already said, Console.WriteLine already has composite formatting built in.

Console.WriteLine("Janet's year is {0}", year)

dear jmcilhinney
sorry for late reply, your code solution works perfectly. Thank you
thanks
roy88