Re: Managing an invalid cast exception

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


.

Quantcast