Managing an invalid cast exception

[David]

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

 

[OHM \( Terry Burns \)]

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 )

http://TrainingOn.net

 

[OHM \( Terry Burns \)]

PS, this is not Matlock in Derby is it ? and BTW, nice looking site

 

[Ken Cox [Microsoft MVP]]

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/d...con/html/vbtskdisplayingsafeerrormessages.asp

 

[David]

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
www.AshfieldFOCUS.com.

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 4guysfromrolla.com 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
chance???

Thanks.
Dave Colliver.
http://www.DerbyFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available

 

[David]

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 [DataSheet$]",
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 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.

Thanks.
Dave Colliver.
http://www.SwindonFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available

 

[David]

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

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 [DataSheet$]",
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 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.

Thanks.
Dave Colliver.
http://www.SwindonFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available

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/d...con/html/vbtskdisplayingsafeerrormessages.asp

 

[OHM \( Terry Burns \)]

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

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

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
[DataSheet$]", 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 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.

Thanks.
Dave Colliver.
http://www.SwindonFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available

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/d...con/html/vbtskdisplayingsafeerrormessages.asp


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

 

[David]

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.
http://www.BakewellFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available

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

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

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
[DataSheet$]", 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 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.

Thanks.
Dave Colliver.
http://www.SwindonFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available


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/d...con/html/vbtskdisplayingsafeerrormessages.asp


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

 

[OHM \( Terry Burns \)]

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

--
OHM ( Terry Burns )

http://TrainingOn.net

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.
http://www.BakewellFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available

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

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


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
[DataSheet$]", 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 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.

Thanks.
Dave Colliver.
http://www.SwindonFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available


message 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/d...con/html/vbtskdisplayingsafeerrormessages.asp


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

 

[David]

Hi,

(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
dataset.

Regards,
Dave Colliver.
http://www.LincolnFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available

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

--
OHM ( Terry Burns )

http://TrainingOn.net

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.
http://www.BakewellFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available

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




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


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
[DataSheet$]", 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 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.

Thanks.
Dave Colliver.
http://www.SwindonFOCUS.com
~~
http://www.FOCUSPortals.com - Portal Franchises available


message 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/d...con/html/vbtskdisplayingsafeerrormessages.asp


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