Sign in to follow this  

SQL Server - Select a blank image if field is null.

Recommended Posts

Lord Method Man    1,613

I'm working on a project that uses Infragistics NetAdvantage Reporting to generate a report. The fields on the report are populated from a database using a stored procedure.

 

My problem is that one of the fields on the report is an image, and not all records in the database have a picture and if the field is NULL or invalid Infragistics puts a red X on the report. I need it to just be blank.

 

I'm trying to use something like this:

CASE WHEN Signature IS NULL THEN CONVERT(VARBINARY(MAX), 0) ELSE Signature END AS Signature

However this still results in a Red X if the Signature field is NULL. Is there some way to alter the statement so it will select a valid-but-blank image from the table?

 

Share this post


Link to post
Share on other sites
+FloatingFatMan    13,176

Have you tried using the ISNULL function, to provide an alternative value when the primary is null?

 

I'm not familiar with NetAdvantage Reporting, I tend to use T-SQL mostly, but a simple ISNULL should work fine..

 

SELECT   ISNULL(Signature, CONVERT(VARBINARY(MAX), 0)) AS Signature
FROM table

Share this post


Link to post
Share on other sites
Haggis    981

^ this

 

or when you are adding the records to the database have it add a default image url if there is nothing in the Signature field

Share this post


Link to post
Share on other sites
Squirrelington    27

The problem with returning a single zero byte when the field is null is that is still not a valid image any more than no data and would still produce a red X I'd assume. You'd probably need to use ISNULL to return a valid 1x1 res image.

 

possibly like this.

 

ISNULL(ColumnName, CAST(0x424d42000000000000003e0000002800000001000000010000000100010000000000040000000000000000000000000000000000000000000000ffffff008000000000 as varbinary(max)))

 

that should be the hex for a monochrome white 1x1 res bmp.

Share this post


Link to post
Share on other sites
stumper66    37

How about having a default image somewhere in the database and if your image is null, then it returns the default image?

  • Like 1

Share this post


Link to post
Share on other sites
Lord Method Man    1,613

Update: I got this working using a "Visibility" property on the report field I hadn't noticed originally.

 

It allows you to define a boolean statement to determine whether the image field will be shown or not, standard stuff.

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.