Case statement based on data type

  • 11 years ago

    Hello. I'm trying to read an Excel file using ADO, but am having problems b/c data in the same column is sometimes text, sometimes numeric, sometimes a date, sometimes null. It seems vb.net tries to type everything in a given column the same.  


    I'm wondering if I need to do a SELECT CASE statement and test the "type" of each element.  Does anyone know of a way to do this.


    For example, one column of data might be called "Description".   In my code, through ADO I reference this as drv("Description").


    It seems to me I should then test the case, and assigned the value to a properly typed variable.  ie, if the value of that cell is a DATE, then I need to assign it to a variable typed as a date.  Etc.


    How would I set up a case statement like this?  And am I on the right track?


    Thanks much!


    Paul

  • 11 years ago

    maybe you can use  


    x = variable.getType
    select case x
    case "integer"


    case "string"
    .
    .
    .
    .


    end select

  • 11 years ago

    Dear Paul,


    You can just do


    dim s as string = drv("Description").ToString


    and it will get the vaule as a string and not cause a null value error.
    If you don't need to work with the value as the dataType it is then thats it. If you do then I would suggest using regular expressions to test if its a number, date, etc and once you know the type then use a select case and cast it accordingly.


    Regards


    Brin

  • 11 years ago
    Thanks Brin,

    I've tried that, and I too thought it would work.  But for some reason when I read an Excel file, it doesn't appear to work.  For example, I have an XLS which has (in the column labeled "Description") some strings, some currency, some dates, and some nulls.  I've set up a loop to read all Data Row Views and read them as strings (as you've described), but then when I look at the array which gets built, all the XLS rows which contained anything but string values show up as null in the array I've built.  I can't figure it out.  This is why I wonder if I need to test each single time I do the drv("Description") valuable, and set it to the appropriate type variable.  Can you shed any further light on this?

    If you're willing, I could email you the XLS and a code snippet.

    Thanks again for taking the time.

    G'day,

    Paul
  • 11 years ago

    Dear Paul,


    It makes no sence to me that you can not get the drv value as a string regardless of what characters it contains. To try and locate the problem I would first display the drv values (say just in a listbox ) and if thats OK progress through your other code untill you strike the problem.


    Let me know how you go.


    Regards


    Brin


  • 11 years ago

    When using ADO.NET to read data from an XLS file it will read a certain number of rows and make a best guess as to what the type of the data in each column is, which I would say is the type of the greatest number of fields in that column.  You can add something to the Extended Properties section of your connection string to specify how many rows are read to make this guess.  I believe that the maximum may be 8 or 16, and I can't remember the name of the property I'm afraid.  I believe I found it originally on MSDN so you could probably search for it, and then try Google if you don't see it there.

  • 11 years ago
    THANK YOU ... this was indeed the problem.

    Problem and solution are described at http://support.microsoft.com/kb/194124/EN-US/

    Thanks for pointing me to this ... I wasted WEEKS trying to figure out what was wrong.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Debugging is anticipated with distaste, performed with reluctance, and bragged about forever.” - Dan Kaminsky