Re: Managing an invalid cast exception

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
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Loading