I've put up some tests using VisualStudio integrated test environment, they simultate what my web application would do by calling BLL methods (they only ones the UI layer should know and interact with)...
So if their behaviour is correct - I get my tests passed - why should I write tests for lower layers such as DAL/Stored Procedures as many literature suggest me to do?
End-to-End testing is good and makes sure that your application is working for certain scenarios.
Misko Hevery put a good blog post on the test categorization where he splits unit-test,integration test and end-to-end testing.
Unit-Testing Unit testing checks that the logic in that function method is doing the correct thing and that error handling is done correctly. These tests should ideally run in milliseconds not seconds. If a function needs to interact with something, like the DAL, you should mock that interface of the DAL since true interaction would take a long time to run. These offer the best Return on Investment
Integration Testing This level of testing checks that interaction between Business Logic layers do exactly what they should be doing. This is where your unit-test would interact with an interface,like the DAL, and check that the 'wiring' is correct. There should be a few of these but not too many as that would impact your build time
End-to-End Testing These are good as they check that everything hangs together from the UI all the way down to the DAL. These test a lot more of the 'wiring' and check that what your user can do won't kill your application. These can also include your FitNesse and Web Tests, like Selenium, are passing.
Unit Testing offers the best return on investment and will catch a lot more costly bugs than the other areas but its good to have a good mix of the lot.