Managing an invalid cast exception

Discussion in 'ASP .Net' started by David, Apr 23, 2005.

  1. David

    David Guest


    I have built a web application that will be a very high profile application.
    We had tested it, demonstrated it and shown that it all works.

    On a dress rehearsal run through, it failed spectacularly. I was so
    embarrassed and felt like killing the person that made it fail. However,
    when it goes live, IT MUST NOT FAIL.

    The system has a backoffice system that takes an excel spreadsheet from the
    client and uploads it to the webserver. I suppose part of the original issue
    was lack of knowledge of excel. However, what I then do is open the
    spreadsheet and save the contents into SQL Server, the results being
    delivered from SQL to the website.

    The front end displays the results. I am using various controls, such as
    datagrids, datalists and even labels. Many of the datagrids/datalists etc
    are not autogenerating. I use container.dataitem to display the results, in
    most cases.

    The issue arose when we started uploading data in excel. During development
    and test, the data in excel was probably being entered in rows, one after
    the other. However, during dress rehearsal, the rows are being entered
    randomly. Some of you may be aware (as I am now) that excel doesn't
    initially care or even know what datatype is in a particular column, so if
    you have mixed numbers and words in the same column, depending what goes off
    in the first few rows of that column defines what the column data type is.
    This appears to override what the column format has been specifically set

    The result was that say the first 5 rows had numbers, the sixth row was text
    (with the whole column being defined as text), upload, drop direct into a
    datagrid or copy to database (database being defined as nvarchar for this
    field) the sixth row would then be null. (This was showing even by dropping
    the excel data direct into the datagrid)

    This absolutely caught me out. I was forced to accept responsibility even
    though it was not my fault. :-(

    Anyhow, now I have explained the situation, I have 2 questions.
    1. In the ASP.NET page, I am using <%# DataBinder.Eval(Container.DataItem,
    "WinningDetail") %> in an item template of a datagrid, which I have already
    bound to.

    Now, with the data coming out of the database being null, I had an invalid
    cast exception. How can I protect against that. I would rather fail
    gracefully so that I can try and fix it (I will be in the back office during
    the critical period) than to have it throw the yellow error screen. I can't
    write fixes for every possibility, so I need something like a try/catch but
    inside the aspx.

    2. This question is quite open ended but is about testing. Given my scenario
    above, how could/should I have tested in order that it wouldn't have failed?

    Thanks for your time.

    Best regards,
    Dave Colliver.
    ~~ - Portal Franchises available
    David, Apr 23, 2005
    1. Advertisements

  2. Hi Dave,

    The first thing that comes to mind ( and we are probably all guilty to some
    degree of this crime, at some time or another ) is that you should always
    create a test plan as part of the design, this helps crystallise one's
    thinking at the design stage, well before any coding takes place.

    Your plan should try and test the data limits which would have helped you
    find this particular bug because your data included various potential data
    types which is effect a range, along with any illegal things the user might
    do. In my experience, a large amount of code tends to be written to prevent
    things going wrong rather than simply executing the task you need, and the
    absence of a cogent design leads to even further coding. We cant forsee
    eveything but good planning is really worth while.

    You have two main options as I see it ( and there may well be more ), You
    can either validate at the source ( your excel spreadsheet ) or you can
    process and validate the data at the transaction stage. I guess the question
    is what do I do in each circumstance and how do I manage resolving this for
    the user ?, this is also part of your design or should be.

    I know this is probably not what you wanted to hear, and others will do
    doubt have other views or suggestions, but this is my two euros worth !
    OHM ( Terry Burns )
    OHM \( Terry Burns \), Apr 23, 2005
    1. Advertisements

  3. PS, this is not Matlock in Derby is it ? and BTW, nice looking site :)
    OHM \( Terry Burns \), Apr 23, 2005
  4. Hi David,

    Ah yes, the demo gods are the most fickle and unkind. The more important
    the audience, the more likely a perfectly-running app will misbehave.

    Be assured you are not the only victim of these gods' wrath. I once watched
    Bill Gates squirm before a huge audience when his demo failed. Did you
    notice that he now invites someone else to do the demo while he watches?

    It sounds like you've got to really scrub the data before you dare do
    anything with it. You'll want your SQL query to ensure that every field
    returns something that is acceptable as data to its consumer, such as
    changing a dbNull to a "" or 0 or false as appropriate.

    If something does slip by, you want to avoid yellow screen by using a custom
    error page that looks like a part of the site. It reports calmly and
    politely that the data provided was not in a usable format and then logs the
    real error to the event log:

    Displaying Safe Error Messages
    Ken Cox [Microsoft MVP], Apr 23, 2005
  5. David

    David Guest

    Hi Terry,

    Yes, it is Matlock in Derby and thanks for the compliment. The site is part
    of a network of portals I developed a few years ago. My local one is

    Anyhow, back to the subject...

    I have been developing ASP apps for quite a few years, only recently gone
    over to .NET. I have never written a test plan. I wouldn't know how to or
    where to start. Apart from that, this particular error was a late comer to
    the project.

    The spreadsheet was changed, but to me, the error is not the spreadsheet
    changing, it is excel for handling its data in a weird way. We had a similar
    issue quite early on, where numbers were not showing. I had to get the first
    row of each column that was numeric to have the number 0 in it, then
    overwrite it when it is needed. To me, this is a hack to overcome the
    limitations of excel. However, I could not have foreseen the issue that has
    given rise to my message.

    In fact, the issue is different on different machines. The site is hosted on
    Win2K. I need the first five rows of the spreadsheet to have text in the
    column. On my laptop running XP Pro, I have to have many more. I am puzzled.

    The only thing I can think of doing, that I don't really want to (as there
    are so many columns of data that I need to protect) is to put if statements
    around each line of the data coming in. However, this will stop the system
    from crashing but doesn't fix the issue. (The screen output can be giving
    false information)

    I saw on an article about test using something called
    NUnit. Without practice though, I don't know how I would set up to test. I
    will ask one of my colleagues if he has done this sort of testing.

    Apart from that, is there any way to wrap the DataBinder.Eval to give me a

    Dave Colliver.
    ~~ - Portal Franchises available
    David, Apr 23, 2005
  6. David

    David Guest

    Hi Ken,

    Thanks for that. Its funny when you see it happen to someone else. When it
    happens to you, you just want to curl up into a ball and die. This was a
    practice run through. My bosses bosses boss just happened to be there. Only
    a few days earlier, she was singing my praises after hearing so many
    positive remarks about me.

    Anyhow, back to the issue...

    I have

    string excelConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    + CurrentLocation + @"\upload\sourcedata.xls" + ";Extended Properties=Excel
    OleDbConnection excelConn = new OleDbConnection(excelConnectString);

    OleDbCommand objCmdSelect = new OleDbCommand("Select * from [DataSheet$]",

    OleDbDataAdapter excelAdapter = new OleDbDataAdapter();

    excelAdapter.SelectCommand = objCmdSelect;

    DataSet excelDataset = new DataSet();

    excelAdapter.Fill(excelDataset, "XLData");


    I am then doing a foreach on the datarows.

    Is there an option to open the spreadsheet and read all the raw data as
    data, rather than excel trying to intepret what it thinks I should be
    reading? (Mind you, that could be a problem as well, as the sheet I am
    reading also has formula. I need to read the results of the formulae.)

    I will look at the yellow page fix, as it might be suitable for the rest of
    our site. Hopefully, it will have the facility where I can set it to email
    us of any errors.

    Dave Colliver.
    ~~ - Portal Franchises available
    David, Apr 23, 2005
  7. David

    David Guest

    Right then,

    I think I have sorted the page to stop the invalid cast but the underlying
    issue will still be there.

    In my page, I had...


    If WinningDetail is null, then the cast to string won't work. The cast to
    string is needed for the server.urlencode to work.

    The fix was to add .ToString() to the end.

    The underlying issue though is Excel. If I insert data into a row, say row
    10, but the above rows are empty or have numbers in, then when reading it
    using ado, the data in row 10 is null. How can I force the data to be read
    as is? Even setting the column type to text doesn't fix it. Only after
    setting the first few rows with putting a text value (such as a space) will
    fix it. This is not reliable either. Win2K Server only requires 5 rows to be
    changed, my XP laptop requires many more. :-(

    Thanks for your help.

    Best regards,
    Dave Colliver.
    ~~ - Portal Franchises available

    David, Apr 24, 2005
  8. I'm struggling a little with your description of the data.
    Thats just it, you cant mix types where you are expecting a strongly typed
    data. If the column should have numbers, let it be numbers and nothing else.
    If the fields in the columns fields can have Nulls then one must deal with
    this when reading the data in.
    Are you saying that properly formatted cells ONLY in row 10 are not read. Do
    rows further down still get read? If so, have the row 10 cells been
    formatted as the correct type ?
    Consider not using Binding Statements in your aspx file. Instead, you can
    build a dataset in code and bind this to the page and controls before it
    renders, this will give you more control over the data when you read it.

    OHM ( Terry Burns )

    OHM \( Terry Burns \), Apr 24, 2005
  9. David

    David Guest

    Hi Terry,

    Thanks for the response.

    Say I have a spreadsheet with 21 rows. The first row is the column names.

    First column is purely numeric, numbered 1 to 20.
    Second column is a calculated or reflected field. (In my case, it is
    reflected from a cell on another sheet, which is calculated). The column has
    been set to text type by selecting the whole column, right click, format
    cell, choosing text. To me, this SHOULD make the column a text column.

    Now, the reflection, when empty puts a 0 in place else it puts in text
    word(s). (Say column 3, 4, 5 of the sheet I am reading has number values,
    the other sheet reads these values, does some sums, puts the result (as a
    specified word) into a specific cell, which is then read by column 2.)

    Because of the application, the values in column 3, 4, 5 are randomly
    entered (one row at a time). (Say, enter the values on row 10... return the
    words "Daves Keys" to column 2 on row 10). If I have nothing (or 0) in the
    first few rows, any rows further down will not be read. All return null,
    except where the value happens to be numeric (in my case, all 0)

    I am blaming excel. I would have thought that either I can:
    1. read from the column which has its text format set as demonstrated above.
    2. read the first data row and understand what the data format should be.

    However, unless I set the first few rows (my win2k server requires the first
    5 rows) with a text value, then I get this problem.

    It does happen to numbers as well. Where I am expecting numbers, if the
    first row is empty, then numbers further down don't appear to work. We then
    just stuck 0 in the first row and the numbers in that column will then work.

    I can live with what I have got, but to me, this is a very serious flaw in
    the way excel works. Not just that, the flaw is different on different
    machines/OSs. However, if there was a way around the problem so that I can
    keep for future reference, I would appreciate it.

    Best regards,
    Dave Colliver.
    ~~ - Portal Franchises available

    David, Apr 24, 2005
  10. Lets have the code you use to get the data from your excel spreadsheet

    OHM ( Terry Burns )

    OHM \( Terry Burns \), Apr 24, 2005
  11. David

    David Guest


    (Whoops, I sent a reply to you, not to group, my apologies, here it is for
    the group.)

    It is earlier on in this thread. A response to Ken.

    This is just the connection to the excel spreadsheet and dropping it into a

    Dave Colliver.
    ~~ - Portal Franchises available

    David, Apr 24, 2005
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.