ekendricks
Posts: 34
|
| Posted: 07/10/2005, 6:11 PM |
|
Using Sql Server 2k - CCS 2.3.2.24
Getting the following error when when the "Middle" name is <null>.
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/Inventory/Common.asp, line 1580
Using the the following string with SQl Server.
SQL = "SELECT [EmployeeID], [GroupID], [FName]+' '+[Middle]+' '+[LName] AS Employee FROM [Employees] etc.etc.etc.
How can I tell this string to ignore...... If IsNull Middle Name?
Thanks
Ernest
|
 |
 |
wkempees
|
| Posted: 07/11/2005, 1:50 AM |
|
http://msdn.microsoft.com/library/default.asp?url=/libr...erator_3qov.asp
says:
Remarks
When you concatenate null values, either the concat null yields null
setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the
behavior when one expression is NULL. With either concat null yields
null or SET CONCAT_NULL_YIELDS_NULL enabled ON, 'string' + NULL returns
NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL
is disabled, the result is 'string'.
I was trying to find the equivalent of concat_ws() for you but
apparently you have to SET CONCAT_NULL_YIELDS_NULL .
Also have a look at CONVERT and CAST, it seems that Middle_name could
supply a NULL or non string type thereby frustrating the typing of your
concatenated strings.
|
|
|
|
|
