ADO.NET Not Returning Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

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)
 
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
achobbs said:
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
Click to expand...
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.
 
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.
achobbs said:
**** 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
Click to expand...
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
Click to expand...
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.
 
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.
William Ryan eMVP said:
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.
achobbs said:
**** 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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
 
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

William Ryan eMVP said:
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.
achobbs said:
**** 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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
 
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.
Ashton Hobbs said:
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

William Ryan eMVP said:
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.
achobbs said:
**** Are you sure you aren't eating the exception? you are calling what,
**** cmd.Execute??? Infomessage is only fired for certain severities
Click to expand...
so
you
**** can'[t count on it.

I am calling SqlCommand.ExecuteReader and trapping the call in a try
Click to expand...
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
Click to expand...
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
Click to expand...
notified
of
by ADO.
Click to expand...
Click to expand...
 
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

William Ryan eMVP said:
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.
Ashton Hobbs said:
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
Click to expand...
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
Click to expand...
notified
of
by ADO.

Thanks,

Ashton Hobbs
http://www.mssqled.com
Click to expand...
Click to expand...
Click to expand...
 
Back
Top