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 15  2209 
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
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
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
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
PS, this is not Matlock in Derby is it ? and BTW, nice looking site :)
--
OHM ( Terry Burns ) http://TrainingOn.net
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
PS, this is not Matlock in Derby is it ? and BTW, nice looking site :)
--
OHM ( Terry Burns ) http://TrainingOn.net
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
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/de...ormessages.asp
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
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/de...ormessages.asp
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
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
"OHM ( Terry Burns )" <me@mine.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... PS, this is not Matlock in Derby is it ? and BTW, nice looking site :)
--
OHM ( Terry Burns )
http://TrainingOn.net
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
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
"OHM ( Terry Burns )" <me@mine.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... PS, this is not Matlock in Derby is it ? and BTW, nice looking site :)
--
OHM ( Terry Burns )
http://TrainingOn.net
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
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
"Ken Cox [Microsoft MVP]" <BA************@sympatico.ca> wrote in message
news:OH**************@tk2msftngp13.phx.gbl... 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/de...ormessages.asp
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
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
"Ken Cox [Microsoft MVP]" <BA************@sympatico.ca> wrote in message
news:OH**************@tk2msftngp13.phx.gbl... 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/de...ormessages.asp
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
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" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... 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
"Ken Cox [Microsoft MVP]" <BA************@sympatico.ca> wrote in message
news:OH**************@tk2msftngp13.phx.gbl... 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/de...ormessages.asp
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
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" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2******************@TK2MSFTNGP14.phx.gbl... 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" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... 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
"Ken Cox [Microsoft MVP]" <BA************@sympatico.ca> wrote in message
news:OH**************@tk2msftngp13.phx.gbl... 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/de...ormessages.asp
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
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
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
"OHM ( Terry Burns )" <me@mine.com> wrote in message
news:Om**************@TK2MSFTNGP12.phx.gbl... 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" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2******************@TK2MSFTNGP14.phx.gbl... 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" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... 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
"Ken Cox [Microsoft MVP]" <BA************@sympatico.ca> wrote in message
news:OH**************@tk2msftngp13.phx.gbl...
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/de...ormessages.asp
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
> 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
>
>
>
Lets have the code you use to get the data from your excel spreadsheet
--
OHM ( Terry Burns ) http://TrainingOn.net
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
"OHM ( Terry Burns )" <me@mine.com> wrote in message
news:Om**************@TK2MSFTNGP12.phx.gbl... 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" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2******************@TK2MSFTNGP14.phx.gbl... 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" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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
"Ken Cox [Microsoft MVP]" <BA************@sympatico.ca> wrote in
message news:OH**************@tk2msftngp13.phx.gbl...
> 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/de...ormessages.asp
>
>
> "David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
> news:%2****************@TK2MSFTNGP14.phx.gbl...
>> 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
>>
>>
>>
>
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
"OHM ( Terry Burns )" <me@mine.com> wrote in message
news:ui**************@TK2MSFTNGP14.phx.gbl... Lets have the code you use to get the data from your excel spreadsheet
--
OHM ( Terry Burns )
http://TrainingOn.net
"David" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... 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
"OHM ( Terry Burns )" <me@mine.com> wrote in message
news:Om**************@TK2MSFTNGP12.phx.gbl... 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" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2******************@TK2MSFTNGP14.phx.gbl...
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" <da*****************@revilloc.REMOVETHIS.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
> 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
>
>
> "Ken Cox [Microsoft MVP]" <BA************@sympatico.ca> wrote in
> message news:OH**************@tk2msftngp13.phx.gbl...
>> 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/de...ormessages.asp
>>
>>
>> "David" <da*****************@revilloc.REMOVETHIS.com> wrote in
>> message news:%2****************@TK2MSFTNGP14.phx.gbl...
>>> 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
>>>
>>>
>>>
>>
>
>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Simon |
last post by:
Hi all,
Do you think the best way to avoid the problems of nulls in the database is
just to provide default values via the db schema?
Alternatively, is it better to allow nulls, seeing as the...
|
by: Alan Z. Scharf |
last post by:
Win Server 2003
VS.Net 2003
---------------
1. I'm having the same problem below on all six of my pages with a
datagrid
item.
2. These pages all worked fine for months until problem started....
|
by: Nick Flandry |
last post by:
I'm running into an Invalid Cast Exception on an ASP.NET application that runs fine in my
development environment (Win2K server running IIS 5) and a test environment (also Win2K server
running IIS...
|
by: John Howard |
last post by:
Making the following call to a local MSAccess database works fine:
Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
Dim intRows As Integer
Dim strSQL As String
Dim ds As New...
|
by: Hifni Shahzard |
last post by:
Hi,
I got a stored procedure, where it returns a value. But if I execute it. It
gives an error as "Invalid cast from System.Int32 to System.Byte.". To make
clear how do I execute this, below I'm...
|
by: buzz |
last post by:
I am attempting to pass data between two asp.net web forms pages. I
have found the method to do this on the msdn site here:
...
|
by: David |
last post by:
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...
|
by: cs_hart |
last post by:
I am getting an invalid cast exception - cast from string to type
double is not valid.
Dim curName As String
Dim prevName As String = ""
curName = CStr(rows.Item(i).Item(colSchName)) ' extract...
|
by: Chris Thunell |
last post by:
I'm trying to loop through an exchange public folder contact list, get some
information out of each item, and then put it into a vb.net datatable. I
run though the code and all works fine until i...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
