Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-02-14 : 08:07:25
|
| Joel writes "Hello,I'm trying to run an SQL query in Microsoft VB.NET v1.10 which fetches the difference between two varChar fields as one of its result fields. Obviously it throws errors when there are missing values and it attempts the calculation. I know how to exclude these rows from the query, but I can't use this option because I still need to query the rest of the data in these rows. When I run the query in Access it's fine, because it simply displays #Error when the calculations fail. However, when running it from VB.NET, the query fails and throws an error. Is there any way to ignore these errors and simply have them output as #Error or something of the form? If so, I can easily parse them out on the display side. Or do I have to do seperate queries and join them? (this would be a big mess)The Error Message I am currently recieving when the query runs on my page is as follows:Exception Details: System.InvalidOperationException: The provider could not determine the Double value. For example, the row was just created, the default for the Double column was not available, and the consumer had not yet set a new Double value.Here is an example of a query which produces the #Error cells in Access but will not run from VB.NET:SELECT [20050209_hierarchy].OrderNumber, [20050209_hierarchy].Branch, [20050209_macaulydur].CASE2, [20050209_macaulydur].CASE2-[20050209_macaulydur].CHRYLONG AS Expr1, [20050209_modifieddur].CASE2FROM (20050209_hierarchy INNER JOIN 20050209_macaulydur ON [20050209_hierarchy].OrderNumber = [20050209_macaulydur].OrderNumber) INNER JOIN 20050209_modifieddur ON [20050209_hierarchy].OrderNumber = [20050209_modifieddur].OrderNumber;I would really appreciate it if anyone can notify me of any response to this problem.Thanks in advance for any help or ideas,Joel Drouillard,Quantitative AnalystLegg Mason Canada" |
|
|
cas_o
Posting Yak Master
154 Posts |
Posted - 2005-02-14 : 09:35:25
|
| Is the error occuring where one of the varchar fields is null ?If use the IsNull function (see Books Online for details) IsNull(<colum_name>,'') will return the column value if there is one or a zero length string if the column is null.Hang on you're storing numbers as strings tut tut.Amend the above to IsNull(<column_name>,'0') that should do it.;-]... Quack Waddle |
 |
|
|
|
|
|
|
|
SQL Query excluding error cells