problem with IF script

The simplest solution is to concatenate an empty string to the field, using the & operator, then check for a zero length, using the LEN() function.
Example:

<% If Len(rs_forms.Fields.Item("Blobdata").Value & "") = 0 Then 'script %>

field it IS NULL

<% End If %>
    
    <% If rs_forms.Fields.Item("Blobdata").Value <> ""  Then 'script %>

field as data

<% End If %>

Select allOpen in new window

=======================
If you needed to differentiate between Null and an empty string, you would need to have two separate IF statements.

I tried it but even though it has data is still shows its null.

This is a screenshot of the result of my query:


Yet, on the asp it shows all 'is null'  instead of two of the showing 'has data'


This is my code:

                        <% If Len(rs_forms.Fields.Item("Blobdata").Value & "") = 0 Then 'script %>

field it IS NULL

<% End If %>
    
    <% If rs_forms.Fields.Item("Blobdata").Value <> ""  Then 'script %>

field as data

<% End If %>

Select allOpen in new window

The actual query for those results is:

SELECT a.id ,         a.caseid ,         a.mainuserid ,         a.petitionerid ,         a.fname ,         a.fdescription ,         a.complete ,         a.qid ,         c.Blobid ,         c.Blobdata ,         d.FirstNm ,         d.MiddleNm ,         d.LastNm,         p.FirstNm AS pet_firstnm,         p.MiddleNm AS pet_middlenm,         p.LastNm AS pet_lastnm
FROM dbo.Formscase a         LEFT JOIN Forms b ON a.fname = b.FormName         LEFT JOIN FormsBlb AS c ON c.Activityid = a.id         INNER JOIN Users AS d ON d.UserId = a.mainuserid         LEFT JOIN Users AS p ON p.userid = a.petitionerid
WHERE a.firmid = 2 AND a.caseid = 11293 AND b.Qid   is not null

Select allOpen in new window

Convert that second IF statement into the Else clause of the first IF statement

I tried the code below same result:

                        <% If Len(rs_forms.Fields.Item("Blobdata").Value & "") = 0 Then 'script %>

field it IS NULL

<% Else %>
   

field as data

<% End If %>

Select allOpen in new window

Why are you adding text data to a blob field? Blobs should only be used for binary data, and you'll need to use a memory stream to pull the data from the db

Ill email you about it. That is a question for a separate conversation.  Sigh !

Please answer the question in this thread.

Agreed, please answer the questions here to be fair to other experts.

Because the field was already a text field and we simply used the existing infrastructure. Since the string is an actual XML we plan on switching it to datatype XML, but we will need more help.Now back to the initial question:  Does the data type text may be causing the issue of the IF statement not working ?

If you query the table, what is the length of the blob field?

depends on the record. But some show as NULL. Those are the ones I can to differentiate. Ill take a screenshot of the query results.

Here is my query:

SELECT  a.id ,
        a.caseid ,
        a.mainuserid ,
        a.petitionerid ,
        a.fname ,
        a.fdescription ,
        a.complete ,
        a.qid ,
        c.Blobid ,
        c.Blobdata ,
        d.FirstNm ,
        d.MiddleNm ,
        d.LastNm ,
        p.FirstNm AS pet_firstnm ,
        p.MiddleNm AS pet_middlenm ,
        p.LastNm AS pet_lastnm
FROM    dbo.Formscase a
        LEFT JOIN Forms b ON a.fname = b.FormName
        LEFT JOIN FormsBlb AS c ON c.Activityid = a.id
        INNER JOIN Users AS d ON d.UserId = a.mainuserid
        LEFT JOIN Users AS p ON p.UserId = a.petitionerid
WHERE   a.firmid = 2
        AND a.caseid = 11293
        AND b.Qid IS NOT NULL;

Select allOpen in new window

This are the results


As you can see "Blobdata"  has content in some records but the rest are NULL.

This is the code I am using that is not working:

   <% If Len(rs_forms.Fields.Item("Blobdata").Value & "") = 0 Then 'script %>Blobdata it IS NULL<% Else %>BlobData has data<% End If %>

Select allOpen in new window

I changed that field to be at the end of my recordset and it seems to be working now. Ill double check. I used the last code you sent.

That was the culprit. I ended up using:

                       <% If Isnull(rs_forms.Fields.Item("Blobdata").Value) Then 'script %>
    Blobdata it IS NULL
<% Else %>
        BlobData has data
    <% End If %>

It works. As long as Blobdata is the last field in my recordset.

Thanks for the patience.