Results 1 to 14 of 14

Thread: [RESOLVED] conversion from string " " to type 'Double' is not valid

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    15

    Resolved [RESOLVED] conversion from string " " to type 'Double' is not valid

    I don't even use Double datatype in my code, even though I assume it come from CDec function.
    I set a condition to make the value in it to 0 when it is null (code not included), it still get error
    Here is my code.
    Code:
            Dim di = New DirectoryInfo("E:\ขาย2564\R-HS2564\R-HS64-05") 
    'I Dim all variable As string, except ef As Boolean
            result = "E:\ภาษีขาย64\รายงานภาษีขายอัตโนมัติ.xlsx"
            ExcelApp2 = CreateObject("Excel.Application")  
            ExcelWorkbook2 = ExcelApp2.Workbooks.Open("C:\Users\ACER\Desktop\ต้นแบบ\แบบฟอร์มรายงานภาษีขาย.xlsx")
            For Each fi In di.EnumerateFileSystemInfos()
                If (fi.Attributes & FileAttributes.Directory) = FileAttributes.Directory Then
                    Console.WriteLine(fi.FullName + " is a directory")
                Else
                    pathfile = fi.FullName           
                    checkfile = Dir$(pathfile)
                    ExcelApp = CreateObject("Excel.Application")  
                    ExcelWorkbook = ExcelApp.Workbooks.Open(pathfile)
                    'MessageBox.Show(fi.FullName + " is a file")
    
                    'check vat not equal to real price
                    If ExcelApp.Worksheets("R-HS").Range("K31").Value = ExcelApp.Worksheets("SO").Range("E26").Value - ExcelApp.Worksheets("SO").Range("E27").Value Then 'yes vat equal no prob
                    Else    'vat not equal we got a prob
                        ExcelApp.Worksheets("R-HS").Range("K30").Value = CDec(ExcelApp.Worksheets("R-HS").Range("K30").Value) + CDec(ExcelApp.Worksheets("SO").Range("E26").Value) - CDec(ExcelApp.Worksheets("SO").Range("E27").Value) - CDec(ExcelApp.Worksheets("R-HS").Range("K31").Value)
                    End If
    
                    For n As Integer = 2 To 4     'ExcelApp2.Worksheets.Count 'page 'find free page and position
                        'if consider for page 2 or more
                        tp = "หน้า" & n.ToString
                        For r As Integer = 9 To 55 'only 45 list per page
                            If ExcelApp2.Worksheets(tp).Range("A" & r).Value Is Nothing Then
                                freepage = n.ToString
                                freeline = r.ToString
                                ef = True 'find target line set ef for loop exit
                                Exit For
                            End If
                        Next
    
                        If ef = True Then
                            Exit For
                        End If
                    Next
                    If freepage = 2 Then
                        ExcelApp2.Worksheets("หน้า" & freepage).Range("A" & freeline).Value = freeline - 8
                    ElseIf freepage = 3 Then         'page 3 
                        ExcelApp2.Worksheets("หน้า" & freepage).Range("A" & freeline).Value = freeline - 8 + 44
                    ElseIf freepage = 4 Then      'page 4
                        ExcelApp2.Worksheets("หน้า" & freepage).Range("A" & freeline).Value = freeline - 8 + 88
                    End If
    
                    ExcelApp2.Worksheets("หน้า" & freepage).Range("B" & freeline).Value = ExcelApp.Worksheets("SO").Range("E6").Value
                    ExcelApp2.Worksheets("หน้า" & freepage).Range("C" & freeline).Value = Split(fi.Name, ".")(0)
                    ExcelApp2.Worksheets("หน้า" & freepage).Range("D" & freeline).Value = ExcelApp.Worksheets("SO").Range("B7").Value
                    If ExcelApp.Worksheets("SO").Range("D8").Value IsNot Nothing Then
                        ExcelApp2.Worksheets("หน้า" & freepage).Range("E" & freeline).Value = ExcelApp.Worksheets("SO").Range("D8").Value
                    Else : ExcelApp2.Worksheets("หน้า" & freepage).Range("E" & freeline).Value = "-"
                    End If
    
                    ExcelApp2.Worksheets("หน้า" & freepage).Range("F" & freeline).Value = "สำนักงานใหญ่"
                    ExcelApp2.Worksheets("หน้า" & freepage).Range("G" & freeline).Value = "-"
                    ExcelApp2.Worksheets("หน้า" & freepage).Range("H" & freeline).Value = ExcelApp.Worksheets("R-HS").Range("K29").Value
    
                    ExcelApp2.DisplayAlerts = False
                    ExcelApp2.Worksheets("หน้า1").SaveAs(filename:=result)         
                    ExcelApp2.DisplayAlerts = True
    
                    ExcelApp.DisplayAlerts = False
                    ExcelApp.Quit()
                    ExcelApp.DisplayAlerts = True
                End If
            Next
            MessageBox.Show("d o n e")
    I try to find the root of problem all day, I think I am dead end now.
    Thank you

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: conversion from string " " to type 'Double' is not valid

    We shouldn't have to trawl through your code, hoping to find a location that could throw that exception. The exception itself tells you exactly where it was thrown. At the very least you should be providing us with that information but you should be using it to debug your code yourself. Set a breakpoint before where the exception is thrown, step through the code and see what data is in use when the exception is thrown and, if necessary, debug further to see where it came from. There's a lot that you can and should be doing before posting here. Even if you still can't solve the problem yourself, you'll have a lot more relevant information to provide to us.

  3. #3
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: conversion from string " " to type 'Double' is not valid

    Quote Originally Posted by fubao View Post
    I don't even use Double datatype in my code, even though I assume it come from CDec function.
    I set a condition to make the value in it to 0 when it is null (code not included), it still get error
    If you are calling CDec than you are using doubles, so presumably somewhere you are reading what looks like an empty string, or a single space and trying to convert it to a double - that is what is failing.

    Checking for null and setting it to 0 would work if you are actually getting a null, if it is anything other than a null or a double you will still get an error.

    My advice would be to look at using something like Double.TryParse or even String.IsNullOrWhiteSpace to check your values and act accordingly.

  4. #4
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: conversion from string " " to type 'Double' is not valid

    Uh, isn't CDec for the Decimal data type? CDbl is for the Double data type.

  5. #5
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: conversion from string " " to type 'Double' is not valid

    Quote Originally Posted by Peter Swinkels View Post
    Uh, isn't CDec for the Decimal data type? CDbl is for the Double data type.
    Good point, it has been so long since I used any of the Cxxx functions I completely forgot which was which...

    In that case I would probably also suggest putting Option Strict On as well to catch these implicit conversions.

  6. #6
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: conversion from string " " to type 'Double' is not valid

    here you have something :

    If ExcelApp2.Worksheets(tp).Range("A" & r).Value Is Nothing Then
    you should have r.tostring

    to know where is the error would help a lot...
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  7. #7
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: conversion from string " " to type 'Double' is not valid

    I just noticed the string concatenation operator ("&") is being used to build strings.

    Code:
    "A" & r
    Can be changed to:

    Code:
    $"A{r}"
    This is done using string interpolation. This goes for just about any other use of the concatenation operator (EDIT: or ("+")) as well.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: conversion from string " " to type 'Double' is not valid

    Quote Originally Posted by Delaney View Post
    you should have r.tostring
    Actually, the & operator is defined for types String and Integer. The issue arises if you use + to concatenate strings rather than &, because adding two types that have different definitions of addition is fraught. There's only one definition of concatenation so there's no ambiguity.

  9. #9
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: conversion from string " " to type 'Double' is not valid

    ok thanks.
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  10. #10

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    15

    Re: conversion from string " " to type 'Double' is not valid

    Sorry for late reply, I was struggling with another problem for couple of days.(excel.quit still got exce.exe, and some loop problem)
    But they are solved now.
    Quote Originally Posted by PlausiblyDamp View Post
    If you are calling CDec than you are using doubles, so presumably somewhere you are reading what looks like an empty string, or a single space and trying to convert it to a double - that is what is failing.

    Checking for null and setting it to 0 would work if you are actually getting a null, if it is anything other than a null or a double you will still get an error.

    My advice would be to look at using something like Double.TryParse or even String.IsNullOrWhiteSpace to check your values and act accordingly.
    Absolutely, sir. This is exactly where the problem is. It is a single space, I didn't even think about that, All I thought is Null. I kept finding Null and Double

    Likewise, This give me a headache, At first, I pay attention in Double which doesn't exist, Until you tell
    me sir, CDec does give Double when option strict is off.

    Thank you for your help, pointing directly into the root of prob.

  11. #11

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    15

    Re: conversion from string " " to type 'Double' is not valid

    Quote Originally Posted by Delaney View Post
    here you have something :



    you should have r.tostring

    to know where is the error would help a lot...
    Quote Originally Posted by Peter Swinkels View Post
    I just noticed the string concatenation operator ("&") is being used to build strings.

    Code:
    "A" & r
    Can be changed to:

    Code:
    $"A{r}"
    This is done using string interpolation. This goes for just about any other use of the concatenation operator (EDIT: or ("+")) as well.
    There is something here also, it seldom seem going wrong, but I don't really know what exactly it is now. But I use bad code but working for temporary solution now.

    I am considering your both advice in advance.

    Thank you, sir , Delaney, Peter Swinkels.

  12. #12

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    15

    Re: conversion from string " " to type 'Double' is not valid

    Quote Originally Posted by jmcilhinney View Post
    We shouldn't have to trawl through your code, hoping to find a location that could throw that exception. The exception itself tells you exactly where it was thrown. At the very least you should be providing us with that information but you should be using it to debug your code yourself. Set a breakpoint before where the exception is thrown, step through the code and see what data is in use when the exception is thrown and, if necessary, debug further to see where it came from. There's a lot that you can and should be doing before posting here. Even if you still can't solve the problem yourself, you'll have a lot more relevant information to provide to us.
    ีuhmm. Normally, If debug it in coding PC, it throws exception directly to where problem occurs. But this case, I test on another PC(which really use program but no IDE). So it just throw error but don't tell where.

    Should I use "windbg" or something similar ?

    By the way, you reply me a couple thread. I do remember to add what you tell me to do before posting to my to do list. So I may post again, if there is new stuff I should do, you can always tell me.
    By another way, yesterday I solve several big problems(to me) without posting threat ! haha.

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: conversion from string " " to type 'Double' is not valid

    Quote Originally Posted by fubao View Post
    ีuhmm. Normally, If debug it in coding PC, it throws exception directly to where problem occurs. But this case, I test on another PC(which really use program but no IDE). So it just throw error but don't tell where.

    Should I use "windbg" or something similar ?
    You should log the exceptions thrown by your application. VB.NET applications have an UnhandledException event that lets you log the exception and close the app cleanly instead of crashing. You then have access to all the information the exception provides. If you deploy the PDB file(s) with your EXE, I believe that you can get the line number for the exception, even for a Release build.

  14. #14

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    15

    Re: conversion from string " " to type 'Double' is not valid

    OK, I remember it and will do that next times.
    Thank you, Sir.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width