Re: Managing an invalid cast exception

Lets have the code you use to get the data from your excel spread***

--
OHM ( Terry Burns )

http://TrainingOn.net

"David" <david.colliver.NEWS@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23005MaNSFHA.2356@xxxxxxxxxxxxxxxxxxxxxxx
> Hi Terry,
>
> Thanks for the response.
>
> Say I have a spread*** 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 ***, 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 *** I am reading has number values,
> the other *** 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.
> http://www.BakewellFOCUS.com
> ~~
> http://www.FOCUSPortals.com - Portal Franchises available
>
>
> "OHM ( Terry Burns )" <me@xxxxxxxx> wrote in message
> news:OmnaG0LSFHA.1176@xxxxxxxxxxxxxxxxxxxxxxx
>> I'm struggling a little with your description of the data.
>>
>>> 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,
>>
>> 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.
>>
>>>then when reading it using ado, the data in row 10 is null.
>>
>> 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 ?
>>
>>>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. :-(
>>
>> 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 )
>>
>> http://TrainingOn.net
>>
>>
>>
>>
>> "David" <david.colliver.NEWS@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:%23L%23SiaLSFHA.1236@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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...
>>>
>>> Server.UrlEncode((string)DataBinder.Eval(Container.DataItem,
>>> "WinningDetail"))
>>>
>>> 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.
>>> http://www.SheffieldFOCUS.com
>>> ~~
>>> http://www.FOCUSPortals.com - Portal Franchises available
>>>
>>>
>>> "David" <david.colliver.NEWS@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>>> news:%23RFQewDSFHA.1500@xxxxxxxxxxxxxxxxxxxxxxx
>>>> 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 8.0;";
>>>> OleDbConnection excelConn = new OleDbConnection(excelConnectString);
>>>> excelConn.Open();
>>>>
>>>> OleDbCommand objCmdSelect = new OleDbCommand("Select * from
>>>> [Data***$]", excelConn);
>>>>
>>>> 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 spread*** 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 *** 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.
>>>>
>>>> Thanks.
>>>> Dave Colliver.
>>>> http://www.SwindonFOCUS.com
>>>> ~~
>>>> http://www.FOCUSPortals.com - Portal Franchises available
>>>>
>>>>
>>>> "Ken Cox [Microsoft MVP]" <BANSPAMken_cox@xxxxxxxxxxxx> wrote in
>>>> message news:OHiHWUDSFHA.3144@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> 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? <grin>
>>>>>
>>>>> 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
>>>>>
>>>>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskdisplayingsafeerrormessages.asp
>>>>>
>>>>>
>>>>> "David" <david.colliver.NEWS@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>>>>> news:%23Fx2C4BSFHA.3288@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>> Hi,
>>>>>>
>>>>>> 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 spread***
>>>>>> 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 spread*** 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 to.
>>>>>>
>>>>>> 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.
>>>>>> http://www.MatlockFOCUS.com
>>>>>> ~~
>>>>>> http://www.FOCUSPortals.com - Portal Franchises available
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.