Null values in table

Question

Null values in table

inliten

SSC Enthusiast

Points: 180
More actions
July 23, 2009 at 11:08 am

#385043

Hi,
I want to concatenate fields from a table into a combo box. I entered the following SQL Select statement:
SelectCommand="SELECT Project + ' ' + Task + ' ' + [Sub Task] + ' ' + Description AS RAS FROM RAS_Codes">
The [Sub Task] field has empty rows and when the query returns the results it omits all of the rows in which [Sub Task] is empty.
How do I get the query to return all of the rows - even those with no values in fields?
Thank you,
John

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

SelectCommand="SELECT Project + ' ' + Task + ' ' + isnull([Sub Task],'') + ' ' + Description AS RAS FROM RAS_Codes">

Try something like that. If a null value is concatenated to a string the entire string is null. Using the isnull() function will allow you to replace the null with a blank string.

inliten SSC Enthusiast Points: 180 More actions · Answer 2

Mr. Wilhoite,

Thank you for your response. The Sub Task column also has content in some rows and nothing in others. I tried your suggestion of SelectCommand="SELECT Project + ' ' + Task + ' ' + isnull([Sub Task],'') + ' ' + Description AS RAS FROM RAS_Codes"> and got the following exception:

Exception Details: System.Data.OleDb.OleDbException: Wrong number of arguments used with function in query expression 'Project + ' ' + Task + ' ' + isnull([Sub Task],'') + ' ' + Description'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[OleDbException (0x80040e14): Wrong number of arguments used with function in query expression 'Project + ' ' + Task + ' ' + isnull([Sub Task],'') + ' ' + Description'.]

John

matt6288 SSCertifiable Points: 5058 More actions · Answer 3

CREATE TABLE #temp (test INT, test2 VARCHAR(50))

INSERT INTO #temp VALUES (1,'matt')

INSERT INTO #temp VALUES (2,NULL)

INSERT INTO #temp VALUES (3,'test')

SELECT test, 'Row 2 will be null ' + test2,

'All rows will populate ' + ISNULL(test2,'')

FROM #temp

This gives this result set:

test(No column name) (No column name)

1Row 2 will be null matt All rows will populate matt

2NULL All rows will populate

3Row 2 will be null test All rows will populate test

So I don't think it is a syntax error.

Can you post the SQL statement you are trying to run?

inliten SSC Enthusiast Points: 180 More actions · Answer 4

Mr. Wilhoite,

Wow! Thank you very much. I did the following and it works fine.

SelectCommand="SELECT Project + ' - ' + Task + ' - ' + Iif(isnull(SubTask),' (No Sub Task) ',SubTask) + ' - ' + Description AS RAS FROM RAS_Codes">

John