= Null problem

  • Thread starter Thread starter Mahesh.V
  • Start date Start date
M

Mahesh.V

Hi
Am using ADO.NET connection from my ASP.NET application to connect to MS
SQL SERVER 2000. I have a Stored procedure which works fine when I directly
connect to the Database using Native drivers, the same is not working
properly when I use ADO.NET connection. When I debugged the Stored procedure
I found that the following script was not working with ADO.NET

emp_name = Null

and the same was working fine when I changed the sql script to

emp_name IS Null.

I have few hundreds of stored procedure and It might not be possible for me
to alter the Stored Procedure.
1.Any body have faced the same problem?
2.Is there an alternative solution to overcome from this problem ?
3.Is this fixed in any service packs from Microsoft ?
4. Or there is no other option rather than change the sql script in Stored
Procedure to emp_name IS NULL ?

Any help would be greatly appreciated
Thank you
Mahesh.V
 
It's hard to believe that the same stored procedure would behave differently
depending on the caller. In the end it is always the SQL engine running it -
so it should always be the same.

In general, the correct way to check for the null of a field is with 'IS
Null'.
 
I don't know what you mean by "connecting to the Database using Native
drivers...". Your ADO.NET application should be using the SqlClient provider
which is the "native" managed provider for SQL Server.

If an application works with some users and not others I would suspect that
some are running different versions of the Framework or the application, or
you're using SSPI security and the rights for the various users is different
in some way.

One cannot compare anything to NULL. You can test for NULL with IS NULL...
as you have seen.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
See the SQL Server documentation for "SET ANSI_NULLS". It will allow to use
= and <> operators for NULL values (it was likely used by the other
driver)...

Once done and though it should work, I would still recommend to take then
the time to update at your own pace your stored procedures as IS NULL is
really the way to go.
 
Back
Top