Help With NULL Database Field Value & VBScript

Hi Gang

I have a database field in an MSSQL database called followupstatus.
This field allows zero length. So some of the values may be
"Complete", "Duplicate", "Flagged In Error", "Not Feasable" or it just
may be "".

I have the following code that is checking the field and I know for a
particular record that I'm checking there is no value in this field.
The problem is that this if statement does not work. It should see
that it is not equal to Complete and process it. However it's just
going right to the else condition. It's almost as if the blank value
in the field is messing up the if condition. This should work. Can
someone help????

If RSFollowup("followupstatus") <> "Complete" Then
..... should process this here
Else
..... but it processes here
End If



Thanks
Andy

Re: Help With NULL Database Field Value & VBScript

See if this works for you ....

Dim sTemp
If Len(Trim(RSFollowup("followupstatus"))) = 0 Then sTemp = "NULL" else
sTemp = RSFollowup("followupstatus")

If NOT sTemp = "Complete" Then
'// Do something
Else
'// Do something else
End If

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Andy" wrote in message
news:1126037762.388790.95740@g47g2000cwa.googlegroups.com...
> Hi Gang
>
> I have a database field in an MSSQL database called followupstatus.
> This field allows zero length. So some of the values may be
> "Complete", "Duplicate", "Flagged In Error", "Not Feasable" or it just
> may be "".
>
> I have the following code that is checking the field and I know for a
> particular record that I'm checking there is no value in this field.
> The problem is that this if statement does not work. It should see
> that it is not equal to Complete and process it. However it's just
> going right to the else condition. It's almost as if the blank value
> in the field is messing up the if condition. This should work. Can
> someone help????
>
> If RSFollowup("followupstatus") <> "Complete" Then
> .... should process this here
> Else
> .... but it processes here
> End If
>
>
>
> Thanks
> Andy
>

Re: Help With NULL Database Field Value & VBScript

And this is being caused by null values? I think there's a part missing in
your explanation here, but for the heck of it, try this:

If RSFollowup.Fields.Item("followupstatus").Value & "" <> "Complete" Then
''.... should process this here
Else
''.... but it processes here
End If


Ray at work


"Andy" wrote in message
news:1126037762.388790.95740@g47g2000cwa.googlegroups.com...
> Hi Gang
>
> I have a database field in an MSSQL database called followupstatus.
> This field allows zero length. So some of the values may be
> "Complete", "Duplicate", "Flagged In Error", "Not Feasable" or it just
> may be "".
>
> I have the following code that is checking the field and I know for a
> particular record that I'm checking there is no value in this field.
> The problem is that this if statement does not work. It should see
> that it is not equal to Complete and process it. However it's just
> going right to the else condition. It's almost as if the blank value
> in the field is messing up the if condition. This should work. Can
> someone help????
>
> If RSFollowup("followupstatus") <> "Complete" Then
> .... should process this here
> Else
> .... but it processes here
> End If
>
>
>
> Thanks
> Andy
>

Re: Help With NULL Database Field Value & VBScript

Andy wrote:
> Hi Gang
>
> I have a database field in an MSSQL database

What version?

> called followupstatus.
> This field allows zero length.

That's not a property available in SQL Server (it's only available in
Access), so, given your subject, I would guess that you mean that it allows
NULLs. Or maybe not.

> So some of the values may be
> "Complete", "Duplicate", "Flagged In Error", "Not Feasable" or it just
> may be ""..

Oh, so I gather that it's a varchar column (datatype is always helpful)

>
> I have the following code that is checking the field and I know for a
> particular record that I'm checking there is no value in this field.

Does it contain NULL or an empty string? From your above statement, it might
be an empty string ("") which is different from a NULL.

> The problem is that this if statement does not work. It should see
> that it is not equal to Complete and process it. However it's just
> going right to the else condition. It's almost as if the blank value
> in the field is messing up the if condition. This should work. Can
> someone help????
>
> If RSFollowup("followupstatus") <> "Complete" Then
> .... should process this here
> Else

Put a Response.Write RSFollowup("followupstatus") statement here and let us
know the result

> .... but it processes here
> End If
>
>
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Help With NULL Database Field Value & VBScript

Hi Ray & Steven

I'm not sure if it is a null problem. All I know is there there was
nothing entered in the database field. So really I'm not sure what it
is. When I do the compare to <> "Complete it should catch it but
doesn't. I'll try what you suggested above.

Thanks
Andy

Re: Help With NULL Database Field Value & VBScript

Hi Everyone

I still need help with this. I have a field called
FollowUpPersonResponsible that is a varchar of length 25 and the Null
column is checked. We're using SQL Server 2000 standard.

I have the following code where the first line spits out
RSFollowup(followuppersonresponsible) = '' and then it bypasses the if
statement.

response.write "RSFollowup(followuppersonresponsible) = '" &
RSFollowup("followuppersonresponsible") & "'
"
If RSFollowup("followuppersonresponsible") = "" Then
response.write "Oh, yeah... it's null baby!
"
End If

Please, please help.

Re: Help With NULL Database Field Value & VBScript

Andy wrote:
> Hi Everyone
>
> I still need help with this. I have a field called
> FollowUpPersonResponsible that is a varchar of length 25 and the Null
> column is checked. We're using SQL Server 2000 standard.
>
> I have the following code where the first line spits out
> RSFollowup(followuppersonresponsible) = '' and then it bypasses the if
> statement.
>
> response.write "RSFollowup(followuppersonresponsible) = '" &
> RSFollowup("followuppersonresponsible") & "'
"
> If RSFollowup("followuppersonresponsible") = "" Then
> response.write "Oh, yeah... it's null baby!
"
> End If
>
> Please, please help.
You are missing the difference between Null (value unknown) and "" (empty
string). You are testing for an empty string. Obviously the field contains
Null. You test for Nulls either using the vbscript IsNull() function or via
the Is operator:
dim followuppersonresponsible
followuppersonresponsible = _
RSFollowup("followuppersonresponsible")

If IsNull(followuppersonresponsible ) then
response.write "Oh, yeah... it's null baby!
"
else
response.write "It's not null!
"
end if

'or

If followuppersonresponsible Is Null Then
response.write "Oh, yeah... it's null baby!
"
else
response.write "It's not null!
"
end if

My preference would be to prevent the query used to open the recordset from
returning a Null. T-SQL has two functions that you can use to do this:
COALESCE() which is the ANSI-standard function, and ISNULL(), which is a
proprietary (SQL Server-only) function.

SELECT COALESCE(followuppersonresponsible, '') AS
followuppersonresponsible, ...

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Help With NULL Database Field Value & VBScript

Hi Bob

Thanks so much for your help. I eventually had to check for both
cases; Null and blank. Sometimes it was null and sometimes it was just
blank in the same field. I don't understand but this code now works
for me 100% of the time.

If IsNull(RSFollowup("followuppersonresponsible")) Or
RSFollowup("followuppersonresponsible") = "" Then

Again thanks for your help!!!!

Andy