IS NULL query problem

Hi Folks - a little problem thats baffling me.....

Im using SQL 2000 db

Im querying a field using 'IS NULL' as the criteria, if I add data to the field and then delete and then try to query using 'IS NULL' as the criteria then the record will not be returned in my query even though the fields empty.....

Can anyone help please ?

Thanks in advance

DOnaldIs the field really empty (NULL)? Or is there an empty string now? They are different in SQL Server.|||Hi - in the database it shows as an empty string (nothing in it!)

So I need the criteria to query IS NULL and an empty field if that makes sense.....

Any idea's... ?

Thanks for the quick reply !

Donald|||SELECT *
FROM yourTable
WHERE yourField IS NULL OR yourField = ''

should do the trick.|||Many thanks it worked !|||The worked fine in SQL, but not using VB / ASP...

Here's the string of code that im having problems with;

<%If BecamePartAss <> "" or not isnull(BecamePartAss) then%>

The above works fine if the field 'IS NULL', but when an empty string exists then your code does not - as below;

SELECT *
FROM yourTable
WHERE yourField IS NULL OR yourField = ''

Thsi may be out of your realm here, but would like you to take a look if you can ?

Regards

Donald|||I'm sorry; I'm not that good at VBScript.
Hopefully someone else can help you with that.|||how are you connecting exactly?

Try WHERE ISNULL(yourField, "") = ""|||When you want to "empty" a column, can't you just assign it a NULL value instead of using an empty string? That sure sounds like the best long term answer to me.

-PatP|||Hi - that sounds the best course of action, would you have any information on how to do this ?

Thanks

Donald|||Please post the code you're using to assign an empty string, and I'll gleefully modify it to use a NULL instead.

-PatP|||Hi Pat - below is the line of code that has the query in thats not working, I have also attached the page.

The code is at line 225 on teaminfo.asp

<%If BecamePartAss <> " " or not isnull(BecamePartAss) then%>

Its an SQL 2000 db, and the field is varchar 50

This problem has come about I have entered text in the field and then deleted, then when I query looking for nulls it does not see an 'empty string'...

Many thanks for taking time to help.

Donald|||<%If BecamePartAss <> " " or not isnull(BecamePartAss) then%>
This problem has come about I have entered text in the field and then deleted, then when I query looking for nulls it does not see an 'empty string'...

Your code doesn't check for empty string. It checks for a string containing a space.|||Your code doesn't check for empty string. It checks for a string containing a space.

I tried it like tis too;

<%If BecamePartAss <> "" or not isnull(BecamePartAss) then%>

Can you tell me where im going wrong... ?

Donald|||No, I can't, I'm sorry.
I see you use the same code for two other fields. Do they work with empty strings?|||The code snippets that you show test the string. What I need to see and help you fix is where you assign the empty string to the column.

-PatP|||Hi Pat, what info do I need to give you ?

A little lost with this now... ?

DOnald|||The code that you posted is checking the colum to see if it is "empty" (or in your case NULL). What we need to fix is a different piece of code that is making the column empty, in other words where the column value is assigned.

-PatP|||if u enter text, then delete it, it is not null anymore, its an empty string ''. to make it null :
- If directly edited the table : press ctrl+0 in the field
- If use script : update Staff set BecamePartAss=(case when rtrim(newBecamePartAss)='' then null else newBecamePartAss end)

to get / show null or empty string value :
- SQL : select * from Staff where BecamePartAss is null or rtrim(BecamePartAss)=''
- ASP : if isnull(BecamePartAss) or BecamePartAss=""

to get / show the contrary (have value, not null, not empty string) :
- SQL :
select * from Staff where not(BecamePartAss is null or BecamePartAss='')
or
select * from Staff where BecamePartAss is not null and BecamePartAss<>''
- ASP :
if not(isnull(BecamePartAss) or BecamePartAss='')
or
if not isnull(BecamePartAss) and BecamePartAss<>""


hope its the right answer as what u need|||Many thanks JL - that has sorted my problem !!!!!!! :D|||<%If BecamePartAss <> ' ' or not isnull(BecamePartAss) then%>
This is not the same as the negation of BecamePartAss = ' ' or isnull(BecamePartAss)
Try using BecamePartAss <> ' ' AND not isnull(BecamePartAss)|||Your code doesn't check for empty string. It checks for a string containing a space.
The two are actually equivalent provided the column has data type CHAR(n).
Only for VARCHAR, an empty string and a single-space string compare different.

As this might be platform specific, you can play safe and use the conditionRTRIM(BecamePartAss) = ''(but beware that scalar functions in conditions may degrade performance drastically ...)|||The two are actually equivalent provided the column has data type CHAR(n).
The code I was referring to was checking a value in a variable, not a database column.|||And what is the data type of the variable?
Also there, CHAR variables will compare '' and ' ' equal, while VARCHAR will not.|||I understand it's javascript, and afaik, JS's string-type is the same as varchar. Also from what I can see in the code that was attached earlier, there's no explicit variable declaration in JS.

I'm no expert on JS though.