This is part of my select statement within an formview which works fine till it hits a null value during the update process.
(SELECT TOP 1 F.tel_id FROM TELEPHONE as F where F.tel_type_id = 3 AND F.client_id = @id
ORDER BY sort_no ) AS faxid
so i tried using the ISNULL function in the following way but it throws error. how can it be done?
ISNULL((SELECT TOP 1 F.tel_id FROM TELEPHONE as F where F.tel_type_id = 3 AND F.client_id= @id ORDER BY sort_no ) AS faxid ,0) AS faxid
I think I've found the cause of the problem. And if that is really the one then I'm almost certain that someone else would have spotted it sooner than I did if you had formatted your second code snippet more nicely.
So, here goes your code snippet, slightly reformatted:
ISNULL(
(
SELECT TOP 1 F.tel_id
FROM TELEPHONE as F
where F.tel_type_id = 3
AND F.client_id= @id
ORDER BY sort_no
) AS faxid,
0
) AS faxid
The highlighted part, the AS fixed
bit immediately after the subquery, is erroneous, it just shouldn't be there. Probably you just overlooked it.