ADO.NET Not Returning Error

[Guest]

If you run the following query

select
'CategoryID = ' + c.CategoryID
from
northwind.dbo.Categories

In Query Analyzer against the Northwind database, QA returns an error saying it cannot convert 'CategoryID = ' to an int. However, if you execute this statement using ADO.NET, no error is returned, no infomessage event is fired, you essentially get nothing back even though there was an error

Is there any way to force ADO.NET to throw a SqlException or something when this error occurs

Thanks

Ashton Hobb
http://www.mssqled.co
(e-mail address removed)

 

[William Ryan eMVP]

Are you sure you aren't eating the exception? you are calling what,
cmd.Execute??? Infomessage is only fired for certain severities so you
can'[t count on it.

What does this query do though? Don't you want it to read 'CategoryID =
c.CategoryID'?

I used this too and it worked fine:
select
'CategoryID = ' +CAST( c.CategoryID AS VARCHAR)
from
northwind.dbo.Categories c

If you run the following query:

select
'CategoryID = ' + c.CategoryID
from
northwind.dbo.Categories c

In Query Analyzer against the Northwind database, QA returns an error

saying it cannot convert 'CategoryID = ' to an int. However, if you execute
this statement using ADO.NET, no error is returned, no infomessage event is
fired, you essentially get nothing back even though there was an error.

 

[William Ryan eMVP]

I'm using it with both the way you presented it and the one that QA can
parse. Here's the deal. when you hit dr.Read you blow up if you use the
incorrect one. You're fine otherwise. I'm catching the exception each time
as soon as I try read. If I do it with the cast, all is good.

**** Are you sure you aren't eating the exception? you are calling what,
**** cmd.Execute??? Infomessage is only fired for certain severities so you
**** can'[t count on it.

I am calling SqlCommand.ExecuteReader and trapping the call in a try with

a catch (Exception e) so the error should be captured if there is one. I
also get other info messages (not that this should show an info message),
but I get nothing back from ADO for this statement. No Exception, No
InfoMessage, and No DataReader. ADO should at least give me an error or
infomessage back since the statement essentially fails because of a
conversion error.

**** I used this too and it worked fine:
**** select
**** 'CategoryID = ' +CAST( c.CategoryID AS VARCHAR)
**** from
**** northwind.dbo.Categories c

Yes, the query is wrong but the situation is that we let people type in

queries and when a user types in a query of this format, they don't get
anything back and think they just didn't have any results when the actual
reason is that an error was in the sql statement that we weren't notified of
by ADO.

 

[William Ryan eMVP]

I've tested this a bunch, can't find the documentation so it's a bit
counterintuitive, but it seems to be the case nonetheless.

I'm using it with both the way you presented it and the one that QA can
parse. Here's the deal. when you hit dr.Read you blow up if you use the
incorrect one. You're fine otherwise. I'm catching the exception each time
as soon as I try read. If I do it with the cast, all is good.

**** Are you sure you aren't eating the exception? you are calling what,
**** cmd.Execute??? Infomessage is only fired for certain severities so you
**** can'[t count on it.

I am calling SqlCommand.ExecuteReader and trapping the call in a try

with
a catch (Exception e) so the error should be captured if there is one. I
also get other info messages (not that this should show an info message),
but I get nothing back from ADO for this statement. No Exception, No
InfoMessage, and No DataReader. ADO should at least give me an error or
infomessage back since the statement essentially fails because of a
conversion error.

**** I used this too and it worked fine:
**** select
**** 'CategoryID = ' +CAST( c.CategoryID AS VARCHAR)
**** from
**** northwind.dbo.Categories c

Yes, the query is wrong but the situation is that we let people type in

queries and when a user types in a query of this format, they don't get
anything back and think they just didn't have any results when the actual
reason is that an error was in the sql statement that we weren't notified of
by ADO.

Thanks,

Ashton Hobbs
http://www.mssqled.com

 

[Ashton Hobbs]

That was it.

I was using .NET 1.1 so I was checking HasRows and if it was false I wasn't
reading. When I called Read I got the SqlException.

Any reason that the SqlException didn't happen on the call to ExecuteReader
but did happen when trying to read from the reader???

Thanks,

Ashton Hobbs
http://www.mssqled.com

I'm using it with both the way you presented it and the one that QA can
parse. Here's the deal. when you hit dr.Read you blow up if you use the
incorrect one. You're fine otherwise. I'm catching the exception each time
as soon as I try read. If I do it with the cast, all is good.

**** Are you sure you aren't eating the exception? you are calling what,
**** cmd.Execute??? Infomessage is only fired for certain severities so you
**** can'[t count on it.

I am calling SqlCommand.ExecuteReader and trapping the call in a try

with
a catch (Exception e) so the error should be captured if there is one. I
also get other info messages (not that this should show an info message),
but I get nothing back from ADO for this statement. No Exception, No
InfoMessage, and No DataReader. ADO should at least give me an error or
infomessage back since the statement essentially fails because of a
conversion error.

**** I used this too and it worked fine:
**** select
**** 'CategoryID = ' +CAST( c.CategoryID AS VARCHAR)
**** from
**** northwind.dbo.Categories c

Yes, the query is wrong but the situation is that we let people type in

queries and when a user types in a query of this format, they don't get
anything back and think they just didn't have any results when the actual
reason is that an error was in the sql statement that we weren't notified of
by ADO.

Thanks,

Ashton Hobbs
http://www.mssqled.com

 

[William Ryan eMVP]

I have a pretty good idea but I'm going to hush until I can verify it.
Basically though it's that the rubber doesn't hit the road with the query
until you call read but that's just my guess. I've been through the
documentation and can't find out exactly, so let me look some more. Or maybe
Bill V or Sceppa will read this, they'll know.

That was it.

I was using .NET 1.1 so I was checking HasRows and if it was false I wasn't
reading. When I called Read I got the SqlException.

Any reason that the SqlException didn't happen on the call to ExecuteReader
but did happen when trying to read from the reader???

Thanks,

Ashton Hobbs
http://www.mssqled.com

I'm using it with both the way you presented it and the one that QA can
parse. Here's the deal. when you hit dr.Read you blow up if you use the
incorrect one. You're fine otherwise. I'm catching the exception each time
as soon as I try read. If I do it with the cast, all is good.

**** Are you sure you aren't eating the exception? you are calling what,
**** cmd.Execute??? Infomessage is only fired for certain severities

so
you

**** can'[t count on it.

I am calling SqlCommand.ExecuteReader and trapping the call in a try

with
a catch (Exception e) so the error should be captured if there is one. I
also get other info messages (not that this should show an info message),
but I get nothing back from ADO for this statement. No Exception, No
InfoMessage, and No DataReader. ADO should at least give me an error or
infomessage back since the statement essentially fails because of a
conversion error.

**** I used this too and it worked fine:
**** select
**** 'CategoryID = ' +CAST( c.CategoryID AS VARCHAR)
**** from
**** northwind.dbo.Categories c

Yes, the query is wrong but the situation is that we let people type

in
queries and when a user types in a query of this format, they don't get
anything back and think they just didn't have any results when the actual
reason is that an error was in the sql statement that we weren't

notified

of
by ADO.

 

[Ashton Hobbs]

William,

I checked the docs and SqlCommand.ExecuteNonQuery lists SqlException as a
possible exception that can be generated, however, neither
SqlDataReader.Read nor IDataReader.Read list any exceptions that can be
thrown so I think this is either a "bug" or an undocumented "feature" since
the Read method does not list any possible exceptions as do other methods in
the documentation.

I think this is why this error confused me because every other error I have
dealt with either came back through a SqlException on the execute or as
InfoMessage in some cases.

Thanks for the help.

Ashton Hobbs
http://www.mssqled.com

I have a pretty good idea but I'm going to hush until I can verify it.
Basically though it's that the rubber doesn't hit the road with the query
until you call read but that's just my guess. I've been through the
documentation and can't find out exactly, so let me look some more. Or maybe
Bill V or Sceppa will read this, they'll know.

That was it.

I was using .NET 1.1 so I was checking HasRows and if it was false I wasn't
reading. When I called Read I got the SqlException.

Any reason that the SqlException didn't happen on the call to ExecuteReader
but did happen when trying to read from the reader???

Thanks,

Ashton Hobbs
http://www.mssqled.com

severities

so

you
**** can'[t count on it.

I am calling SqlCommand.ExecuteReader and trapping the call in a try with
a catch (Exception e) so the error should be captured if there is one. I
also get other info messages (not that this should show an info message),
but I get nothing back from ADO for this statement. No Exception, No
InfoMessage, and No DataReader. ADO should at least give me an error or
infomessage back since the statement essentially fails because of a
conversion error.

**** I used this too and it worked fine:
**** select
**** 'CategoryID = ' +CAST( c.CategoryID AS VARCHAR)
**** from
**** northwind.dbo.Categories c

Yes, the query is wrong but the situation is that we let people type in
queries and when a user types in a query of this format, they don't get
anything back and think they just didn't have any results when the actual
reason is that an error was in the sql statement that we weren't

notified

of
by ADO.

Thanks,

Ashton Hobbs
http://www.mssqled.com