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

DOnald

Is 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

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?