Jump to content



Photo

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

sql sql server

  • Please log in to reply
5 replies to this topic

#1 Lord Method Man

Lord Method Man

    Neowinian Senior

  • Tech Issues Solved: 1
  • Joined: 18-September 12
  • OS: Windows 8.1 Pro
  • Phone: Nokia Lumia 1520

Posted 13 November 2013 - 22:11

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?

 




#2 FloatingFatMan

FloatingFatMan

    Resident Fat Dude

  • Tech Issues Solved: 1
  • Joined: 23-August 04
  • Location: UK

Posted 14 November 2013 - 08:37

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


#3 Haggis

Haggis

    Neowinian Senior

  • Tech Issues Solved: 10
  • Joined: 13-June 07
  • Location: Near Stirling, Scotland
  • OS: Debian 7
  • Phone: Samsung Galaxy S3 LTE (i9305)

Posted 14 November 2013 - 08:47

^ 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



#4 Squirrelington

Squirrelington

    Squirrelies!

  • Tech Issues Solved: 1
  • Joined: 26-December 02
  • Location: Oshkosh, WI, USA
  • OS: Windows 8.1 64-bit Enterprise
  • Phone: HTC One X (GSM (International) Tegra 3 - S720e / HTC__203) - AT&T - 4.2.2

Posted 20 November 2013 - 04:14

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.



#5 stumper66

stumper66

    Neowinian

  • Joined: 08-January 09
  • Location: Dallas, TX, USA

Posted 13 December 2013 - 01:24

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



#6 OP Lord Method Man

Lord Method Man

    Neowinian Senior

  • Tech Issues Solved: 1
  • Joined: 18-September 12
  • OS: Windows 8.1 Pro
  • Phone: Nokia Lumia 1520

Posted 17 December 2013 - 17:14

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.